Executing statements

In the expression API documentation above, there is only documentation for generating sql & bindings from helper functions. Now, we'll be dealing with executing statements using qb engine.

Executing Inserts, Upserts, Updates & Deletes

The following table & qb initializations are assumed;

Go

package main

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

func main() {
	db, _ := qb.New("mysql", "root:@tcp(localhost:3306)/qb_test?charset=utf8")
	defer db.Close()

	users := qb.Table(
		"user",
		qb.Column("id", qb.Varchar().Size(36)),
		qb.Column("email", qb.Varchar().Unique().NotNull()),
		qb.Column("name", qb.Varchar().NotNull()),
		qb.PrimaryKey("id"),
	)

	metadata := qb.MetaData(db.Dialect())
	metadata.AddTable(users)
	err := metadata.CreateAll(db.Engine())
	checkErr(err)
  defer metadata.DropAll(db.Engine())
  
  // insert examples here
}

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

A simple insert statement can be executed by the following;

Go

ins := users.
		Insert().
		Values(map[string]interface{}{
			"id":    "f82cdc-4d21-473b-a175-cbc3f9119eda",
			"email": "[email protected]",
			"name":  "Al Pacino",
		}).
		Build(db.Dialect())

res, err := db.Engine().Exec(ins)
checkErr(err)

insertId, err := res.LastInsertId()
checkErr(err)

rowsAffected, err := res.RowsAffected()
checkErr(err)

fmt.Printf("insertId: %d\n", insertId)
fmt.Printf("rowsAffected: %d\n", rowsAffected)

The output would be the following;

Text

insertId: 0
rowsAffected: 1

The Update, Upsert & Delete statements are executed using Exec functions just the same with Insert.

You can use Exec() in any sql statements that inserts or updates data. Sqlite & mysql works fine in LastInsertId() & RowsAffected() functions. However, due to the driver implementation of postgres, the driver satisfies the exec interface but does give meaningless results.

❗️

Postgres driver does not support LastInsertId() & RowsAffected()

Therefore, there is an optional Returning clause just for postgres to keep retrieving results. Here's the following example shows how to retrieve by returning clause;

Go

package main

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

func main() {
	db, _ := qb.New("postgres", "user=postgres dbname=qb_test sslmode=disable")
	defer db.Close()

	db.Dialect().SetEscaping(true)

	users := qb.Table(
		"user",
		qb.Column("id", qb.Varchar().Size(36)),
		qb.Column("email", qb.Varchar().Unique().NotNull()),
		qb.Column("name", qb.Varchar().NotNull()),
		qb.PrimaryKey("id"),
	)

	metadata := qb.MetaData(db.Dialect())
	metadata.AddTable(users)
	err := metadata.CreateAll(db.Engine())
	defer metadata.DropAll(db.Engine())
	checkErr(err)

	ins := users.
		Insert().
		Values(map[string]interface{}{
			"id":    "f82cdc-4d21-473b-a175-cbc3f9119eda",
			"email": "[email protected]",
			"name":  "Al Pacino",
		}).
		Returning(users.C("id")).
		Build(db.Dialect())

	var id string
	db.Engine().QueryRow(ins).Scan(&id)
	checkErr(err)
	fmt.Printf("id: %s\n", id)
}

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

This outputs the following;

Text

id: f82cdc-4d21-473b-a175-cbc3f9119eda

The Returning clause is available for Insert(), Upsert(), Update() & Delete() functions.

Executing selects

The select executor functions are Query(), QueryRow(), Get() & Select().

Select using Query() & QueryRow()

Selects can be executed using Query() & QueryRow() functions as the following;

Go

sel := users.
	Select(users.C("id")).
	Where(users.C("email").Eq("[email protected]")).
	Build(db.Dialect())

var id string
db.Engine().QueryRow(sel).Scan(&id)
fmt.Println("id:", id)

Output is;

Text

id: f82cdc-4d21-473b-a175-cbc3f9119eda

The Query function can also be used in the following example;

Go

sel := users.
		Select(users.C("id")).
		Where(users.C("email").Eq("[email protected]")).
		Build(db.Dialect())

ids := []string{}
rows, err := db.Engine().Query(sel)
checkErr(err)
for rows.Next() {
	var id string
	err = rows.Scan(&id)
	checkErr(err)
	ids = append(ids, id)
}
fmt.Println("ids:", ids)

Output is;

Text

ids: [f82cdc-4d21-473b-a175-cbc3f9119eda]

Therefore, Query() function can be used to extract multiple rows from db.

Select using Get() & Select()

Get() & Select() functions of Engine is for mapping results to queries. Check out the following example with Get();

Text

sel := users.
	Select(users.C("id")).
	Where(users.C("email").Eq("[email protected]")).
	Build(db.Dialect())

type User struct {
	ID string
}

var user User
err = db.Engine().Get(sel, &user)
checkErr(err)

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

Output is;

Text

User{ID:f82cdc-4d21-473b-a175-cbc3f9119eda}

The same example can be done by using Select() & user slice;

Go

sel := users.
		Select(users.C("id")).
		Where(users.C("email").Eq("[email protected]")).
		Build(db.Dialect())

type User struct {
	ID string
}

var selUsers []User
err = db.Engine().Select(sel, &selUsers)
checkErr(err)

fmt.Printf("Users%+v\n", selUsers)

As you might notice, Select() can be used for mapping result sets to multiple structs.

🚧

Joins are not "Select() & Get()"able

Currently, Select() & Get() functions can be used only while retrieving result sets from a single table. This issue will be addressed in 0.3 milestone of qb.