SQL builder API
The SQL builder API provides tools to represent SQL expressions with Go code. This gives you some additional advantages over regular string queries:
- We can benefit from the Go compiler syntax check.
- It is easier to compose and reuse queries.
When you need more power than what the agnostic data API gives you.
Using the agnostic data API or the SQL API depends on the specific needs of your application.
Database session
The SQL builder methods are available on all SQL adapters:
sess, err := postgresql.Open(settings)
...
sqlbuilder := sess.SQL()
Select statement
Use the Select()
method on a session to begin a SELECT statement (a
db.Selector
).
q = sess.SQL().
Select("id", "name")
If you compiled the select statement at this point it would look like SELECT "id", "name";
which is an incomplete SQL query, you still need to specify
which table to select from, chain the From()
method to do that:
q = sess.SQL().
Select("id", "name").From("accounts")
Now you have a complete query that can be compiled into valid SQL:
var accounts []Account
q = sess.SQL().
Select("id", "name").From("accounts")
fmt.Println(q) // SELECT id, name FROM accounts
This query is wired to the database session, but it's not compiled nor executed
unless you require data from it, use the All()
method on a query to execute
it and map all the resulting rows into a slice of structs or maps:
var accounts []Account
...
// All() executes the query and maps the resulting rows into an slice of
// structs or maps.
err = q.All(&accounts)
...
If you're only interested in one result, use One()
instead of All()
and
provide a single pointer to struct or map:
var account Account
...
err = q.One(&account)
...
To select all the columns instead of specific ones, you can use the
SelectFrom()
method:
// SELECT * FROM accounts
q = sess.SQL().
SelectFrom("accounts")
...
err = q.All(&accounts)
...
// Which is basically equivalent to:
q = sess.SQL().
Select().From("accounts")
If you're working with large datasets using All()
could be expensive, it's
probably more efficient to get results one by one using an iterator:
var account Account
iter := q.Iterator()
for iter.Next(&account) {
log.Printf("account: %v", account)
...
}
// Remember to check for iterator errors
if err = iter.Err(); err != nil {
...
}
Iterators are automatically closed at the end of the Next()
-based loop, but
in case you need to exit the iterator before the loop is completed use
iter.Close()
:
for iter.Next() {
if somethingHappened() {
iter.Close()
break
}
}
You have to decide whether you want to use All()
, One()
or an Iterator
depending on your specific needs.
SELECT statements and joins
The Join()
method is part of a Selector
, you can use it to represent SELECT
statements that use JOINs.
q = sess.SQL().
Select("a.name").From("accounts AS a").
Join("profiles AS p").
On("p.account_id = a.id")
...
q = sess.SQL().
Select("name").From("accounts").
Join("owners").
Using("employee_id")
...
In addition to Join()
you can also use FullJoin()
, CrossJoin()
,
RightJoin()
and LeftJoin()
.
INSERT statement
The InsertInto()
method begins an INSERT statement (a
db.Inserter
).
q = sess.SQL().
InsertInto("people").
Columns("name").
Values("John")
res, err = q.Exec()
...
You don't have to use the Columns()
method, if you pass a map or a struct,
you can omit it completely:
account := Account{
...
}
q = sess.SQL().
InsertInto("people").Values(account)
res, err = q.Exec() // res is a sql.Result
...
UPDATE statement
The Update()
method takes a table name and begins an UPDATE statement (an
db.Updater
):
q = sess.SQL().
Update("people").
Set("name", "John").
Where("id = ?", 5)
res, err = q.Exec()
...
You can update many columns at once by providing column-value pairs to Set()
:
q = sess.SQL().
Update("people").
Set(
"name", "John",
"last_name", "Smith",
).Where("id = ?", 5)
res, err = q.Exec()
...
You don't always have to provide column-value pairs, Set()
also accepts maps
or structs:
q = sess.SQL().
Update("people").
Set(map[string]interface{}{
"name": "John",
"last_name": "Smith",
}).Where("id = ?", 5)
res, err = q.Exec()
...
DELETE statement
You can begin a DELETE statement with the DeleteFrom()
method (a
db.Deleter
):
q = sess.SQL().
DeleteFrom("accounts").Where("id", 5)
res, err = q.Exec()
...
WHERE clause
The Where()
method can be used to define conditions on a db.Selector
,
db.Deleter
or db.Updater
interfaces.
For instance, let's suppose we have a db.Selector
:
q = sess.SQL().
SelectFrom("accounts")
We can use the Where()
method to add conditions to the above query. How about
constraining the results only to rows that match id = 5
?:
q = q.Where("id = ?", 5)
We use a ?
as a placeholder for the argument, this is required in order to
sanitize arguments and prevent SQL injections. You can use as many arguments as
you need as long as you provide a value for each one of them:
q = q.Where("id = ? OR id = ?", 5, 4) // Two place holders and two values.
The above condition is a list of ORs and sometimes things like that can be rewritten into things like this:
q = q.Where("id IN ?", []int{5,4}) // id IN (5, 4)
Placeholders are not always necessary, if you're looking for the equality and
you're only going to provide one argument, you could drop the ?
at the end:
q = q.Where("id", 5)
...
q = q.Where("id IN", []int{5,4})
...
It is also possible to use other operators besides the equality, but you have to be explicit about them:
q = q.Where("id >", 5)
...
q = q.Where("id > ? AND id < ?", 5, 10)
...
You can also use db.Cond
to define conditions for Where()
just like you
would normally do when using Find()
:
// ...WHERE "id" > 5
q = q.Where(db.Cond{
"id >": 5,
})
...
// ...WHERE "id" > 5 AND "id" < 10
q = q.Where(db.Cond{"id >": 5, "id <": 10})
...
// ...WHERE ("id" = 5 OR "id" = 9 OR "id" = 12)
q = q.Where(db.Or(
db.Cond{"id": 5},
db.Cond{"id": 9},
db.Cond{"id": 12},
))
Remember that if you want to use db.Cond
you'll need to import
github.com/upper/db
into your app:
import "github.com/upper/db/v4"
Plain SQL statements
If the builder does not provide you with enough flexibility to create complex SQL queries, you can always use plain SQL:
rows, err = sess.SQL().
Query(`SELECT * FROM accounts WHERE id = ?`, 5)
...
row, err = sess.SQL().
QueryRow(`SELECT * FROM accounts WHERE id = ? LIMIT ?`, 5, 1)
...
res, err = sess.SQL().
Exec(`DELETE FROM accounts WHERE id = ?`, 5)
...
The Query
method returns a *sql.Rows
object and of course you can do
whatever you would normally do with it:
err = rows.Scan(&id, &name)
...
If you don't want to use Scan
directly, you could always create an iterator
using any *sql.Rows
value:
rows, err = sess.SQL().
Query(`SELECT * FROM accounts WHERE last_name = ?`, "Smith")
...
var accounts []Account
iter := sess.SQL().NewIterator(rows)
err = iter.All(&accounts)
...