-
José Carlos Nieto authored67a3fbfb
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. - The
upper.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 tellsqlbuilder
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")