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"`
}

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

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"

Note

To keep the implementation simple, only ExecContext and QueryContext callbacks are supported. If you need to intercept other database operations, such as sql.DB.BeginTx, consider using ngrok/sqlmw instead.

Integration tests cover the following databases and drivers:

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

🚧 TODOs

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