README.md

upper.io/db.v2/builder

Package upper.io/db.v2/builder provides tools to build and execute SQL queries and map their results to Go structs.

upper.io/db.v2/builder is the engine that powers upper.io/db.

Mapping structs to tables

builder reads the db field tag to determine how to map Go fields to table columns and vice versa.

// Book is a struct that uses the `db` tag to map each one of its fields to
// table columns.
type Book struct {
  // The ID fields uses "omitempty" to skip it when inserting it to database
  // whenever it has the zero value.
  ID         int       `db:"id,omitempty"`
  Title      string    `db:"title"`
  Author     string    `db:"author"`
  CatalogID  int       `db:"catalog_id"`
  CategoryID int       `db:"category_id"`
  DateAdded  time.Time `db:"date_added"`
}

Field types are converted automatically to the type the database expects, so you can safely use types like time.Time and expect them to be mapped to their equivalent database representation when saving.

Instantiating a builder

builder is composed of many different packages that perform specific tasks:

• The upper.io/db.v2/builder package defines abstract interfaces and functions.
• Theupper.io/db.v2/builder/sqlbuilder package is ready to represent SQL queries but it does not know what template to use to build SQL query strings.
• Packages under the upper.io/db.v2/builder/template/ prefix are template packages that can be used to tell sqlbuilder how to generate SQL strings.

In order to have a working program that actually generates SQL you'll need at least two of the above packages.

import (
  "upper.io/db.v2/builder/sqlbuilder"
  "upper.io/db.v2/builder/template/sqlite"
)
...

The builder package does not handle SQL sessions by itself. Use the sqlbuilder.New() function to create a builder and tie it to a database session and a template:

// Let's open a sqlite3 database.
db, err = sql.Open("sqlite3", "./test.db")
...

// Then create a SQL builder with it by passing the database and a template.
bob, err := sqlbuilder.New(db, sqlite.Template)
...

If you're ussing upper.io/db you can use the Builder() method on your db.Session variable to create a builder:

sess, err = db.Open(...)
...
bob = sess.Builder() // look ma! no need to pass a template or a session.
...

Selector

builder.Selector provides methods to represent SELECT queries.

q := bob.Select("id", "name")

The above statement is somewhat incomplete because it does not actually say which table to select from, that can be easily fixed by chaining the From() method.

q := bob.Select("id", "name").From("accounts")

The above statement is equivalent to SELECT "id", "name" FROM "accounts". We can also add conditions to reduce the number of matches:

q := bob.Select("id", "name").From("accounts").
  Where("last_name = ?", "Smith")

And change the way the results are sorted:

q := bob.Select("id", "name").From("accounts").
  Where("last_name = ?", "Smith").
  OrderBy("name").Limit(10)

builder.Selector implements fmt.Stringer, you can call the String() to build the SQL query:

// SELECT "id", "name" FROM "accounts"
// WHERE "last_name" = ? ORDER BY "name"
// LIMIT 10
s := q.String()

You can then pass it to sql.DB.Query() if you want, but maybe you want to build and execute the SQL query without handling it yourself: builder.Selector implements the builder.Getter interface which provides Query() (*sql.Rows, error) and QueryRow() (*sql.Row, error) which are similar to the database/sql methods.

rows, err := q.Query()
...
defer rows.Close()

for rows.Next() {
  var id int
  var name string
  err = rows.Scan(&id, &name)
  ...
}
err = rows.Err() // get any error.
...

The real power of builder.Selector comes with the builder.Iterator interface, builder.Iterator provides convenient methods to iterate and map query results to Go structs.

var accounts []Account
...
iter := q.Iterator()
err = iter.All(&accounts) // map all results from the query
...

var account Account
...
iter := q.Iterator()
err = iter.One(&account) // maps the first result from the query
...

// no need to close it when using One()/All()

If you rather walk over each row step by step instead of using All() you can use Next and Scan() or One().

iter := q.Iterator()
defer iter.Close() // remember to close it when you're done.

for iter.Next() {
  err = iter.One(&account)
  ...
  // err = iter.Scan(&id, &name)
  ...
}

err = iter.Err() // get any error.
...

builder.Builder provides different ways to create a builder.Selector, for instance this shortcut:

q := bob.SelectAllFrom("users")

Inserter

builder.Inserter provides methods to represent INSERT queries.

Use the InsertInto() method on a builder to create a builder.Inserter:

q := bob.InsertInto("accounts")