queries

README

queries

Convenience helpers for working with SQL queries.

🚀 Features

• Builder: a strings.Builder wrapper with added support for placeholder verbs to easily build raw SQL queries conditionally.
• Scanner: sql.DB.Query/QueryRow wrappers that automatically scan sql.Rows into the given struct. Inspired by golang/go#61637.
• Interceptor: a driver.Driver wrapper to easily add instrumentation (logs, metrics, traces) to the database layer. Similar to gRPC interceptors.

📦 Install

Go 1.23+

go get go-simpler.org/queries

📋 Usage

Builder

columns := []string{"id", "name"}

var qb queries.Builder
qb.Appendf("SELECT %s FROM users", strings.Join(columns, ", "))

if role != nil { // "admin"
    qb.Appendf(" WHERE role = %$", *role)
}
if orderBy != nil { // "name"
    qb.Appendf(" ORDER BY %$", *orderBy)
}
if limit != nil { // 10
    qb.Appendf(" LIMIT %$", *limit)
}

db.QueryContext(ctx, qb.Query(), qb.Args()...)
// Query: "SELECT id, name FROM users WHERE role = $1 ORDER BY $2 LIMIT $3"
// Args: ["admin", "name", 10]

The following database placeholders are supported:

• ? (used by MySQL and SQLite)
• $1, $2, ..., $N (used by PostgreSQL)
• @p1, @p2, ..., @pN (used by MSSQL)

Scanner

type User struct {
    ID   int    `sql:"id"`
    Name string `sql:"name"`
}

// single column, single row:
name, _ := queries.QueryRow[string](ctx, db, "SELECT name FROM users WHERE id = 1")

// single column, multiple rows:
names, _ := queries.Collect(queries.Query[string](ctx, db, "SELECT name FROM users"))

// multiple columns, single row:
user, _ := queries.QueryRow[User](ctx, db, "SELECT id, name FROM users WHERE id = 1")

// multiple columns, multiple rows:
for user, _ := range queries.Query[User](ctx, db, "SELECT id, name FROM users") {
    // ...
}

Interceptor

interceptor := queries.Interceptor{
    Driver: // database driver of your choice.
    ExecContext: func(ctx context.Context, query string, args []driver.NamedValue, execer driver.ExecerContext) (driver.Result, error) {
        slog.InfoContext(ctx, "ExecContext", "query", query)
        return execer.ExecContext(ctx, query, args)
    },
    QueryContext: func(ctx context.Context, query string, args []driver.NamedValue, queryer driver.QueryerContext) (driver.Rows, error) {
        slog.InfoContext(ctx, "QueryContext", "query", query)
        return queryer.QueryContext(ctx, query, args)
    },
}

sql.Register("interceptor", interceptor)
db, _ := sql.Open("interceptor", "dsn")

db.ExecContext(ctx, "INSERT INTO users VALUES (1, 'John Doe')")
// stderr: INFO ExecContext query="INSERT INTO users VALUES (1, 'John Doe')"

db.QueryContext(ctx, "SELECT id, name FROM users")
// stderr: INFO QueryContext query="SELECT id, name FROM users"

Integration tests cover the following databases and drivers:

• PostgreSQL with jackx/pgx
• MySQL with go-sql-driver/mysql

🚧 TODOs

• Add more tests for different databases and drivers. See https://go.dev/wiki/SQLDrivers.
• Add examples for tested databases and drivers.
• Add benchmarks.

Documentation

Overview

Package queries implements convenience helpers for working with SQL queries.

Index

• func Collect[T any](seq iter.Seq2[T, error]) ([]T, error)
• func Query[T any](ctx context.Context, q Queryer, query string, args ...any) iter.Seq2[T, error]
• func QueryRow[T any](ctx context.Context, q Queryer, query string, args ...any) (T, error)
• type Builder
  • func (b *Builder) Appendf(format string, args ...any)
  • func (b *Builder) Args() []any
  • func (b *Builder) Query() string
• type Interceptor
  • func (i Interceptor) Open(name string) (driver.Conn, error)
  • func (i Interceptor) OpenConnector(name string) (driver.Connector, error)
• type Queryer

Functions

func Collect

func Collect[T any](seq iter.Seq2[T, error]) ([]T, error)

Collect is a slices.Collect variant that collects values from an iter.Seq2[T, error]. If an error occurs during the collection, Collect stops the iteration and returns the error.

func Query

func Query[T any](ctx context.Context, q Queryer, query string, args ...any) iter.Seq2[T, error]

Query executes a query that returns rows, scans each row into a T, and returns an iterator over the Ts. If an error occurs, the iterator yields it as the second value, and the caller should then stop the iteration. Queryer can be either sql.DB or sql.Tx, the rest of the arguments are passed directly to [Queryer.QueryContext]. Query fully manages the lifecycle of the sql.Rows returned by [Queryer.QueryContext], so the caller does not have to.

The following Ts are supported:

• int (any kind)
• uint (any kind)
• float (any kind)
• bool
• string
• time.Time
• sql.Scanner (implemented by sql.Null types)
• any struct

See the sql.Rows.Scan documentation for the scanning rules. If the query has multiple columns, T must be a struct, other types can only be used for single-column queries. The fields of a struct T must have the `sql:"COLUMN"` tag, where COLUMN is the name of the corresponding column in the query. Unexported and untagged fields are ignored.

Query panics if:

• The query has no columns.
• A non-struct T is specified with a multi-column query.
• The specified struct T has no field for one of the query columns.
• An unsupported T is specified.
• One of the fields in a struct T has an empty `sql` tag.

If the caller prefers the result to be a slice rather than an iterator, Query can be combined with Collect.

func QueryRow

func QueryRow[T any](ctx context.Context, q Queryer, query string, args ...any) (T, error)

QueryRow is a Query variant for queries that are expected to return at most one row, so instead of an iterator, it returns a single T. Like sql.DB.QueryRow, QueryRow returns sql.ErrNoRows if the query selects no rows, otherwise it scans the first row and discards the rest. See the Query documentation for details on supported Ts.

Types

type Builder

type Builder struct {
	// contains filtered or unexported fields
}

Builder is a raw SQL query builder. The zero value is ready to use. Do not copy a non-zero Builder.

func (*Builder) Appendf

func (b *Builder) Appendf(format string, args ...any)

Appendf formats according to the given format and appends the result to the query. It works like fmt.Appendf, i.e. all rules from the fmt package are applied. In addition, Appendf supports %?, %$, and %@ verbs, which are automatically expanded to the query placeholders ?, $N, and @pN, where N is the auto-incrementing counter. The corresponding arguments can then be accessed with the Builder.Args method.

IMPORTANT: to avoid SQL injections, make sure to pass arguments from user input with placeholder verbs.

Placeholder verbs map to the following database placeholders:

• MySQL, SQLite: %? -> ?
• PostgreSQL: %$ -> $N
• MSSQL: %@ -> @pN

TODO: document slice arguments usage.

func (*Builder) Args

func (b *Builder) Args() []any

Args returns the query arguments.

func (*Builder) Query

func (b *Builder) Query() string

Query returns the query string.

type Interceptor

type Interceptor struct {
	// Driver is a database driver.
	// It must implement [driver.Pinger], [driver.ExecerContext], [driver.QueryerContext],
	// [driver.ConnPrepareContext], and [driver.ConnBeginTx] (most drivers do).
	// Required.
	Driver driver.Driver

	// ExecContext is a callback for both [sql.DB.ExecContext] and [sql.Tx.ExecContext].
	// The implementation must call execer.ExecerContext(ctx, query, args) and return the result.
	// Optional.
	ExecContext func(ctx context.Context, query string, args []driver.NamedValue, execer driver.ExecerContext) (driver.Result, error)

	// QueryContext is a callback for both [sql.DB.QueryContext] and [sql.Tx.QueryContext].
	// The implementation must call queryer.QueryContext(ctx, query, args) and return the result.
	// Optional.
	QueryContext func(ctx context.Context, query string, args []driver.NamedValue, queryer driver.QueryerContext) (driver.Rows, error)

	// PrepareContext is a callback for [sql.DB.PrepareContext].
	// The implementation must call preparer.ConnPrepareContext(ctx, query) and return the result.
	// Optional.
	PrepareContext func(ctx context.Context, query string, preparer driver.ConnPrepareContext) (driver.Stmt, error)
}

Interceptor is a driver.Driver wrapper that allows to register callbacks for database queries. It must first be registered with sql.Register with the same name that is then passed to sql.Open:

interceptor := queries.Interceptor{...}
sql.Register("interceptor", interceptor)
db, err := sql.Open("interceptor", "dsn")

func (Interceptor) Open

func (i Interceptor) Open(name string) (driver.Conn, error)

Open implements driver.Driver.

func (Interceptor) OpenConnector

func (i Interceptor) OpenConnector(name string) (driver.Connector, error)

OpenConnector implements driver.DriverContext.

type Queryer

type Queryer interface {
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
}

Queryer is an interface implemented by sql.DB and sql.Tx.