Querying

Both DB and Tx have these three methods:

Query(ctx context.Context, query string, args ...any) *Scanner
QueryRow(ctx context.Context, query string, args ...any) *Scanner
Exec(ctx context.Context, query string, args ...any) (sql.Result, error)

Query

Queries the database and returns a Scanner object that automatically scans all rows. Errors are deferred to the scanner for easy chaining, until Err() or Scan() is called.

Simple query with automatic scan into a struct slice:

var users []User
db.Query(ctx, "SELECT * FROM user").Scan(&users)
// users variable now contains data from query

Auto expanding "IN" clauses out of the box:

args := []int{4, 8, 16}
var users []*User
db.Query(ctx, "SELECT * FROM user WHERE id IN (?)", args).Scan(&users)
// executed as "SELECT * FROM user WHERE id IN (?,?,?)"

QueryRow

Queries the database and returns a Scanner object that automatically scans at most one row. Errors are deferred to the scanner for easy chaining, until Err() or Scan() is called. If query result is empty, scanner returns sql.ErrNoRows:

var user User
err = db.QueryRow(ctx, "SELECT * FROM user WHERE id = ?", 42).Scan(&user)
if err != nil {
  if sqlz.IsNotFound(err) {
    log.Fatal("user not found!")
  }
  log.Fatal(err)
}

TIP

IsNotFound is a helper function to check for sql.ErrNoRows using errors.Is, although sqlz does not decorate the error.

Exec

Exec is very similar to standard library, it returns the same sql.Result object, which has two methods:

• LastInsertId(): returns the integer generated by the database from an auto-increment column when inserting a new row. Not all databases support this feature. In MySQL it works as expected, but in PostgreSQL this information is only available by using the "RETURNING" clause in a query.
• RowsAffected(): returns the number of rows affected by an update, insert, or delete. Not every database or driver may support this feature.

result, err := db.Exec(ctx, "INSERT INTO user (name) VALUES (?)", "Alice")
id, err := result.LastInsertId()

Note about placeholders

It is a good practice to always use placeholders to send parameters to the database, as they will prevent SQL injection attacks. On databases/drivers that support it, the query is prepared on the server before execution.

Placeholder syntax is database specific:

• MySQL uses the ? variant shown above
• PostgreSQL uses an enumerated $1, $2, etc.
• SQL Server uses an enumerated @p1, @p2, etc.
• SQLite accepts both ? and $1 syntax
• Oracle uses a :param syntax

All the above syntaxes are supported by sqlz.

NOTE

Placeholders are only used for parameterization, and are not allowed to change the structure of an SQL statement. For instance, a placeholder won't be able to change the table or field name from a "SELECT" statement.

When querying using placeholders, all parameters must have their correct position based on the order they appear in the query, just like fmt.Sprintf.

Named queries

Passing struct or map[string]any as an argument makes sqlz parse it as a named query. Named queries are easier to work with, because you don't need to refer to parameters positionally. sqlz replaces :param syntax with the driver's placeholder and rearrange the arguments, meaning it works independently of the driver.

loc := Location{Country: "Brazil"}
var users []User
db.Query(ctx, "SELECT * FROM user WHERE country = :country", loc).Scan(&users)

Passing a slice of structs or maps works as a batch insert, it expands the insert syntax:

users := []User{
  {Name: "Alice", Email: "alice@example.com"},
  {Name: "Rob", Email: "rob@example.com"},
  {Name: "John", Email: "john@example.com"},
}
db.Exec(ctx, "INSERT INTO user (name, email) VALUES (:name, :email)", users)
// executed as "INSERT INTO user (name, email) VALUES (?, ?), (?, ?), (?, ?)"

To access nested fields from a struct or map, use dot notation:

order := Order{
  Store: Store{Id: 42},
  Customer: Customer{Id: 69},
}

var orders []Order
db.Query(
  ctx, `
  SELECT * FROM orders
  WHERE store_id = :store.id
    AND customer_id = :customer.id`,
  order,
).Scan(&orders)

TIP

Named struct queries follow the same rules from Struct scanning.

Context parameter

In case you're wondering why each query method has a context.Context as first parameter: it's strongly recommended to always use context when working with I/O operations. For example, if you're developing a web server, pass the context from the request to these methods; this will ensure that all DB connections bound to that request are closed if the client disconnects. It also makes it easier to add deadlines or timeouts to any query.