The 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;

Go

// 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.

Go

package main

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

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);

Go

// generate an insert statement and add it to current transaction
db.Add(User{Name: "Aras Can Akin"})

// 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.

SQL

INSERT INTO user
(name)
VALUES ($1);
[Aras Can Akin]

Updating

To update rows, use Update(table).Set(map[string]interface{}) chain as in the following example;

Go

query := db.Update("user").
  Set(map[string]interface{}{
    "name": "Aras Akin",
  }).
	Where(db.Eq("name", "Aras Can Akin")).
  Query()

db.AddQuery(query)
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;

SQL

UPDATE user
SET name = $1
WHERE name = $2;
[Aras Akin, Aras Can Akin]

Deleting

Deletes are done by the following session call;

Go

// 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;

SQL

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()

Go

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;

SQL

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;

Go

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;

SQL

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;

Go

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.