The Builder

The builder api is a way to build complex queries without performance losses from functions using reflection api. The builder uses none of reflection api calls.

This project was started as the only query builder. As the table definitions needed, it is decided to build all the needed toolkit for db based app development.

In the examples, the following initializations are assumed;

Go

package main

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

func main() {

	engine, err := qb.NewEngine(
		"mysql",
		"root@tcp(localhost:3306)/qb_test?charset=utf8"
  )

	if err != nil {
		panic(err)
	}
  
  // create a builder instance
  b := qb.NewBuilder(engine.Driver())
}

In qb, Engine is one of the core objects that handle db connections & query execution.

Optional escaping

qb also provides query escaping for building query which may contain keywords of the database driver. However, due to community feedback, escaping idea was not found as any good. So, there is optional escaping implemented in builder. You can set the escaping method by the following;

Go

// create a builder instance
b := qb.NewBuilder(engine.Driver())
b.SetEscaping(true)

Log flags

The builder provides a simple logging mechanism to log queries and bindings. The logging happens when Query() function is called. The log flags can be changed by the following;

Go

// create a builder instance
b := qb.NewBuilder(engine.Driver())
b.SetLogFlags(qb.LQuery|qb.LBindings)

Log flag	Description
LDefault	The default log flag of qb that means no logging at all.
LQuery	The Query() call would only log the sql statement that is generated
LBindings	The Query() call would only log the bindings array that is generated

Inserting

A simple insert statement generation would be the following;

Go

query := b.
  Insert("user").
  Values(map[string]interface{}{
    "name":  "Aras Can",
    "email": "[email protected]",
  }).Query()

fmt.Println(query.SQL())
fmt.Println(query.Bindings())

// outputs

// sql
// INSERT INTO user
// (name, email)
// VALUES (?, ?);

// bindings
// [Aras Can [email protected]]

The Query() call produces a Query struct that has sql string and bindings array. Moreover, Values() call understands the column names and escapes them correctly.

Updating

A simple update statement generation would be the following;

Go

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

fmt.Println(query.SQL())
fmt.Println(query.Bindings())

// outputs
// UPDATE user
// SET name = ?
// WHERE name = ?;
// [Aras Can Akin, Aras Can]

Deleting

A simple delete example is the following;

Go

query := b.
  Delete("user").
  Where(b.Eq("name", "Aras Can")).
  Query()

fmt.Println(query.SQL())
fmt.Println(query.Bindings())

// outputs
// DELETE FROM user
// WHERE name = ?;
// [Aras Can]

Selecting

The builder api provides functionality for building complex select statements. All the function supports can be seen at here.

A basic select statement would be the following;

Go

query := b.
  Select("id", "name").
	From("user").
  Where(b.Eq("name", "Aras Can")).
  Query()

fmt.Println(query.SQL())
fmt.Println(query.Bindings())

// outputs
// SELECT id, name
// FROM user
// WHERE name = ?;
// [Aras Can]

You can achieve multiple where conditioning using b.And(queries ...string) and b.Or(queries ...string) as follows;

Text

query := b.
		Select("id", "name").
		From("user").
		Where(
			b.And(
				b.Eq("name", "Aras Can"),
				b.NotEq("id", 1),
			),
		).
    OrderBy("name ASC").
		Query()

fmt.Println(query.SQL())
fmt.Println(query.Bindings())

// outputs
// SELECT id, name
// FROM user
// WHERE (name = ? AND id != ?);
// ORDER BY name ASC;
// [Aras Can 1]

The or function is used just as the same with And(). The only difference is Or() function generates "OR" between conditions.

Comparators

The following table shows the helper comparator functions in builder;

Go

// NotIn function generates "%s not in (%s)" for key and adds bindings for each value
NotIn(key string, values ...interface{}) string

// In function generates "%s in (%s)" for key and adds bindings for each value
In(key string, values ...interface{}) string

// NotEq function generates "%s != placeholder" for key and adds binding for value
NotEq(key string, value interface{}) string

// Eq function generates "%s = placeholder" for key and adds binding for value
Eq(key string, value interface{}) string

// Gt function generates "%s > placeholder" for key and adds binding for value
Gt(key string, value interface{}) string

// Gte function generates "%s >= placeholder" for key and adds binding for value
Gte(key string, value interface{}) string

// St function generates "%s < placeholder" for key and adds binding for value
St(key string, value interface{}) string

// Ste function generates "%s <= placeholder" for key and adds binding for value
Ste(key string, value interface{}) string

Note that you can still build where clauses using plain text as in the following;

Go

query := b.
		Select("id", "name").
		From("user").
		Where("name = ?", "Aras Can").
		Query()

// outputs
// SELECT id, name
// FROM user
// WHERE name = ?;
// [Aras Can]

As you can see, where also accepts plain conditionals. However, sql statement would not use escape characters. Therefore, you need to manually write escape characters or call b.Adapter.Escape("name") which escapes the string.

❗️

Where

Plain text where doesn't escape column conditionals. Be careful!

Aggregates

The builder api also provides support for aggregate queries like max, min, count. The following list shows all the aggregate queries supported by builder api;

Go

// GroupBy generates "group by %s" for each column
GroupBy(columns ...string) *Builder

// Having generates "having %s" for each expression
Having(expressions ...string) *Builder

// Avg function generates "avg(%s)" statement for column
Avg(column string) string

// Count function generates "count(%s)" statement for column
Count(column string) string

// Sum function generates "sum(%s)" statement for column
Sum(column string) string

// Min function generates "min(%s)" statement for column
Min(column string) string

// Max function generates "max(%s)" statement for column
Max(column string) string

Note here that only GroupBy() & Having() functions can be used by chaining. The other functions are required to be called by using builder instance.

Executing

Unless the session api executing queries requires to have engine object initialized. The engine initialization is shown at the top of The Builder documentation. After building a query, the engine helper functions should execute the Query object that builder has built.

The following example shows how to execute Insert & Update based statements;

Go

query := b.
		Insert("user").
		Values(map[string]interface{}{
			"name": "Aras",
			"id":   1,
		}).
		Query()

result, err := engine.Exec(query)
if err != nil {
  fmt.Println(err)
  return
}

lid, err := result.LastInsertId()
ra, err := result.RowsAffected()

fmt.Println(lid)
fmt.Println(ra)

// outputs
// 1
// 1

As it can be seen, the Exec function returns sql.Result in the database/sql package.

Selective statements use Query() & QueryRow() functions (which doesn't use reflection api) as well as sqlx package functions like Get() & Select(). The following example would show a simple execution of select statements.

Go

var id int
var name string

query := b.
  Select("id", "name").
  From("user").
  Where(b.Eq("name", "Aras")).
  Limit(0, 1).
  Query()

engine.QueryRow(query).Scan(&id, &name)

fmt.Printf("<User id=%d name=%s>\n", id, name)

// outputs
// <User id=1 name=Aras>

Selecting multiple rows can be achieved by Query() as in the following;

Go

var id int
var name string

query := b.
  Select("id", "name").
  From("user").
  Query()

rows, err := engine.Query(query)

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

defer rows.Close()
for rows.Next() {
  err := rows.Scan(&id, &name)
  if err != nil {
    fmt.Println(err)
    return
  }

  fmt.Printf("<User id=%d name=%s>\n", id, name)
}

// outputs
// <User id=1 name=Aras>
// <User id=2 name=Can>

The reflection heavy Get() & Select() functions are also available for qb.
The Get() example is the following;

Go

type User struct {
  ID   int
  Name string
}

var user User

query := b.
  Select("id", "name").
  From("user").
  Where(b.Eq("name", "Aras")).
  Limit(0, 1).
  Query()

err = engine.Get(query, &user)

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

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

// outputs
// <User id=1 name=Aras>

The Select() example is the following;

Go

type User struct {
  ID   int    `db:"id"`
  Name string `db:"name"`
}

users := []User{}

query := b.
  Select("name", "id").
  From("user").
  Query()

fmt.Println(query.SQL())
fmt.Println(query.Bindings())

err = engine.Select(query, &users)

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

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

// outputs
// <User id=1 name=Aras>
// <User id=2 name=Can>