Session

In qb, the session object is the most useful & simplest object. It has all the building blocks of qb. A typical session object has the following dependencies;

// Session is the composition of engine connection & orm mappings
type Session struct {
	queries  []*Query  // queries in the current transaction
	mapper   *Mapper   // mapper object for shortcut funcs
	metadata *MetaData // metadata object to keep table registry
	tx       *sql.Tx   // active transaction
	builder  *Builder  // query builder
  mutex    *sync.Mutex // mutex for preventing race conditions while opening a transaction
}

The following object is the only object required to use every single function in qb. The other structs that can also build sql statements such as Builder is transactionless.

From this point, the following qb initialization is assumed.

package main

import (
	"github.com/aacanakin/qb"
)

type User struct {
  ID string `qb:"constraints:primary_key"`
  Name string `qb:"constraints:not_null"`
  Email string `qb:"constraints:unique, not_null"`
}

func main() {

	db, err := qb.New("postgres", "user=postgres dbname=qb_test sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	db.Metadata().Add(User{})
	db.Metadata().CreateAll()
}

Inserting

To insert rows on a table just add sample models and call db.Add(model);

// generate an insert statement and add it to current transaction
user := User{Name: "Al Pacino"}
db.Add(user)

// commit
db.Commit()

You can add multiple models by calling Add function sequentially. This would generate the following sql statements and bindings, add it to the current transaction.

INSERT INTO user
(name)
VALUES ($1);
[Al Pacino]

Updating

Suppose you have inserted the user variable in the insert example above.To update rows, update the changes in user struct & use Add() function

user.Name = "Robert Downey Jr."

db.Add(user)
db.Commit()

As it can be easily noticed Update statement is a little more unique for the flexibility of update statements.

This type of syntax is also supported in Select statements.

The following sql statement & bindings will be produced within a transaction;

# for sqlite
REPLACE INTO user(name) VALUES(?)
[Robert Downey Jr.]

# for postgres
INSERT INTO user(name) VALUES($1) ON CONFLICT(id) DO UPDATE SET name = $2;
[Robert Downey Jr. Robert Downey Jr.]

# for mysql
INSERT INTO user(name) VALUES(?) ON DUPLICATE KEY UPDATE name = ?;
[Robert Downey Jr. Robert Downey Jr.]

Deleting

Deletes are done by the following session call;

// insert the row
db.Add(User{Name: "Aras Can Akin"})
db.Commit()

// delete it
db.Delete(User{Name: "Aras Can Akin"})
db.Commit()

The statement would produce the following sql statements & bindings within a transaction;

DELETE FROM user
WHERE name = $1;
[Aras Can Akin]

Selecting

There are more than one way to build select queries. In qb.Session, there exists a shortcut namely Find() that finds a model that matches the struct values as in the following;

Find.One()

db.Add(User{Name: "Aras Can Akin"})
db.Commit()

var user User
err = db.Find(User{Name: "Aras Can Akin"}).One(&user)
if err != nil {
  fmt.Println(err)
}

fmt.Printf("id=%d, name=%s\n", user.ID, user.Name)

The Find() call would produce the following sql statement with bindings;

SELECT name, id
FROM user
WHERE name = $1;
[Aras Can Akin]

Find().All()

Find(model interface{}).All(models interface{}) returns all rows that is matched by struct example as in the following;

db.Add(User{Name: "Aras Can Akin", Email: "[email protected]"})
db.Add(User{Name: "Aras Can Akin", Email: "[email protected]"})
db.Commit()

var users []User
err = db.Find(User{Name: "Aras Can Akin"}).All(&users)
if err != nil {
  fmt.Println(err)
}

for _, u := range users {
  fmt.Printf("<User id=%d name=%s email=%s>\n", u.ID, u.Name, u.Email)
}

Find().All() call would produce the following sql statements and bindings;

SELECT id, name, email
FROM user
WHERE name = $1;
[Aras Can Akin]

The Builder] explained how sql statements are built by function chaining. The more complex select statements can be built by both builder and the session. Query building by func chaining can be also achieved using qb.Session. Lets make a complex selective query using joins;

type User struct {
  ID    int64  `qb:"type:bigserial; constraints:primary_key"`
  Name  string `qb:"constraints:not_null"`
  Email string `qb:"constraints:not_null, unique"`
}

type Session struct {
  UserID    int64  `qb:"constraints:ref(user.id)"`
  AuthToken string `qb:"type:uuid"`
  Agent     string `qb:"constraints:not_null"`
}

db, err := qb.New("postgres", "user=postgres dbname=qb_test sslmode=disable")
if err != nil {
  panic(err)
}
defer db.Close()

db.Metadata().Add(User{})
db.Metadata().Add(Session{})
db.Metadata().CreateAll()

db.Add(User{Name: "Aras Can Akin", Email: "[email protected]"})
db.Add(User{Name: "Aras Can Akin", Email: "[email protected]"})

db.Add(Session{
  UserID:    1,
  AuthToken: "f1d2a8af-b048-479a-99c8-3725805299cf",
  Agent:     "android"})

db.Add(Session{
  UserID:    1,
  AuthToken: "9bb95918-1cc1-4ab1-b3b1-29bf385d00bb",
  Agent:     "chrome"})

db.Add(Session{
  UserID:    2,
  AuthToken: "0470f8ae-3e36-4a83-a4d1-7562173c48c6",
  Agent:     "ios"})

db.Commit()

var sessions []Session
err = db.
  Select("s.user_id, s.auth_token, s.agent").
  From("session s").
  InnerJoin("user u", "s.user_id = u.id").
  Where("u.name = ?", "Aras Can Akin").
  OrderBy("s.agent").
  All(&sessions)

if err != nil {
  fmt.Println(err)
}

for _, s := range sessions {
  fmt.Printf("<Session user_id=%d auth_token=%s agent=%s>\n",
             s.UserID,
             s.AuthToken,
             s.Agent)
}

// outputs
// <Session user_id=1 auth_token=f1d2a8af-b048-479a-99c8-3725805299cf agent=android>
// <Session user_id=1 auth_token=9bb95918-1cc1-4ab1-b3b1-29bf385d00bb agent=chrome>
// <Session user_id=2 auth_token=0470f8ae-3e36-4a83-a4d1-7562173c48c6 agent=ios>

Let's take a look at the last complex Select statement with use of qb.Session. The chain starting with Select() is just as the same as the builder way. However, builder doesn't have any All() functions which parses the struct and maps the values for each iteration in the result set.

So, the very first question comes to mind is;
Why do I have to bother with the builder api?

The One() and All() function calls uses sqlx's mapper functions which uses a lot of reflection. Therefore, it may slow down your query calls when dealing with large & complex structs. Therefore, performance critical apis should use builder api.

Query().Filter()

There is an optional way to build select statements in an ormish way. This way uses Query().Filter() functions to chain where statements and retrieve the result set using One() & All(). This feature can also be used for partial extraction.

package main

import (
	"fmt"
	"github.com/aacanakin/qb"
)

func main() {

	type User struct {
		ID   string `qb:"type:uuid; constraints:primary_key"`
		Name string `qb:"constraints:not_null"`
	}

	db, err := qb.New("postgres", "user=postgres dbname=qb_test sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	db.Dialect().SetEscaping(true)

	db.AddTable(User{})
	db.CreateAll()

	user := &User{
		ID:   "98d7fa05-72a3-466a-9a46-995d9f662312",
		Name: "Al Pacino",
	}

	db.Add(user)
	err = db.Commit()
	checkErr(err)

	var selUser User
	db.Query(db.T("user").C("name")).
		Filter(db.T("user").C("id").Eq("98d7fa05-72a3-466a-9a46-995d9f662312")).
		One(&selUser)

	fmt.Printf("User%+v\n", selUser)

}

func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}

The output would be the following;

User{ID: Name:Al Pacino}

As it might be noticed, the ID field is not initialized because in the example above, Query() function only takes "name" column as parameter. Moreover, db has T(name string) function for retrieving table object from metadata and inside the table object there exists C(name string) function for retrieving column object of the table.

All() is also available for slice of structs. No further example is required.