sqlr — a tiny, SQL-first builder & mapper for Go

sqlr is a minimal SQL builder and result mapper designed to stay very close to the SQL you already write. It focuses on keeping things simple: turn :named placeholders into driver args, expand IN (...) automatically, support bulk VALUES, and scan rows into your structs efficiently — all without a ORM or a fluent DSL.

Features:

• SQL-first, no DSL: you write the SQL, sqlr doesn’t invent a DSL; it just binds and scans.
• Multiple dialects: Postgres, MySQL, SQLite, SQL Server.
• Placeholder rendering per dialect: Postgres → $1, $2, …; MySQL/SQLite → ?; SQL Server → @p1, @p2, ….
• One normal workflow: start with Write, pass values with Bind, then execute, scan, or build the query.
• Automatic compilation and reuse of repeated SQL without changing binding or scan semantics.
• Optional sqlrgen fast paths for workloads where cached reflection is still measurable.
• Typed scans, fast: struct mapping via db tags or field names, nested struct flattening, pointer/null handling.
• Bulk insert made simple: :name{a,b,c} emits (...), (...), ... with bound args; place it after VALUES in the SQL.
• Plays well with handcrafted SQL (CTEs, JSON ops, window functions…).
• No runtime dependencies: the production package imports only the standard library. The test suite uses go-sqlmock.
• Performance-minded: single-pass dynamic parser, compiled templates, pooled internal state, cached struct plans, and optional generated accessors.
• Safe binding by design: values passed through Bind become driver parameters and are never interpolated into SQL. Fragments passed to Write/Writef are raw SQL and must be trusted.
• Concurrency: share one *SQLR across goroutines; each *Builder is single-use.

Installation:

go get github.com/gandaldf/sqlr@latest

Examples:

Quick start

package main

import (
	"database/sql"
	"log"

	_ "github.com/lib/pq"
	"github.com/gandaldf/sqlr"
)

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

func main() {
	db, err := sql.Open("postgres", "<dsn>")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	s := sqlr.New(sqlr.Postgres) // create once and share across the application

	var users []User
	err = s.Write("SELECT id, name FROM users WHERE id IN (:ids) AND active=:active").
		Bind("ids", []int{1,2,3}).
		Bind("active", true). // later binds can add/override keys
		ScanAll(db, &users)
	if err != nil {
		log.Fatal(err)
	}
}

// Preview result:
// SQL:  SELECT id, name FROM users WHERE id IN ($1, $2, $3) AND active=$4
// Args: []any{1, 2, 3, true}

The basic flow: write, bind, run

Every sqlr query follows the same small pattern:

1. Create one *SQLR for your database dialect and reuse it.
2. Start a query with Write.
3. Pass values with Bind:
   • use Bind("name", value) for individual parameters;
   • use Bind(mapOrStruct) when values already belong together.
4. Finish with one terminal operation:
   • Exec executes a statement that does not return rows;
   • ScanOne reads exactly one row or scalar value;
   • ScanAll reads every returned row into a slice;
   • Build returns the rendered SQL and arguments without executing them.

Use Preview when you only want to inspect the SQL and arguments. Unlike the terminal operations above, Preview keeps the builder usable.

Repeated queries are normally optimized automatically when the same *SQLR instance is reused.

Execute a statement

res, err := sqlr.New(sqlr.MySQL).
  Write("UPDATE products SET price=:price WHERE id IN (:ids)").
  Bind("price", 999, "ids", []int{7,8,9}).
  Exec(db)
if err != nil { return err }
rows, _ := res.RowsAffected()

// Preview result:
// SQL:  UPDATE products SET price=? WHERE id IN (?, ?, ?)
// Args: []any{999, 7, 8, 9}

Read a single scalar

var count int
err := sqlr.New(sqlr.Postgres).
  Write("SELECT COUNT(*) FROM orders WHERE customer_id=:c AND status=:s").
  Bind("c", 42, "s", "paid").
  ScanOne(db, &count)

// Preview result:
// SQL:  SELECT COUNT(*) FROM orders WHERE customer_id=$1 AND status=$2
// Args: []any{42, "paid"}

One row exactly

var u User
err := sqlr.New(sqlr.Postgres).
  Write("SELECT id, name FROM users WHERE email=:e").
  Bind("e", email).
  ScanOne(db, &u)

// Preview result:
// SQL:  SELECT id, name FROM users WHERE email=$1
// Args: []any{email}

Struct scans (tags, flattening, NULLs)

type Audit struct {
	CreatedAt time.Time `db:"created_at"`
}
type Row struct {
	ID    int     `db:"id"`
	Name  string  `db:"name"`
	Note  *string `db:"note"` // pointer handles NULL
	Audit Audit
}

var out []Row
err := sqlr.New(sqlr.Postgres).
  Write(`SELECT id, name, note, created_at FROM users WHERE active=:a`).
  Bind("a", true).
  ScanAll(db, &out)

// Preview result:
// SQL:  SELECT id, name, note, created_at FROM users WHERE active=$1
// Args: []any{true}

• created_at maps into Audit.CreatedAt via flattening.
• Pointers become nil when the DB returns NULL.

Bulk insert

type NewUser struct {
	ID   int    `db:"id"`
	Name string `db:"name"`
}
rows := []NewUser{{1,"Anna"},{2,"Luca"},{3,"Mia"}}

_, err := sqlr.New(sqlr.SQLite).
  Write("INSERT INTO users (id,name) VALUES :batch{id,name}").
  Bind("batch", rows).
  Exec(db)

// Preview result:
// SQL:  INSERT INTO users (id,name) VALUES (?, ?), (?, ?), (?, ?)
// Args: []any{1, "Anna", 2, "Luca", 3, "Mia"}

The placeholder is called :batch{...} here, but the name is arbitrary. It is a regular named parameter with a column list in curly braces, not a keyword.

Expansion in action

sqlr expands at build time based on your bound values. You write :named params; sqlr turns them into the right placeholders for the dialect, expands slices/rows, and builds the final args in one pass.

IN (...) slice expansion

q, args, _ := sqlr.New(sqlr.Postgres).
  Write("SELECT * FROM t WHERE id IN (:ids) AND active=:a").
  Bind("ids", []int{10,11,12}).
  Bind("a", true).
  Preview()

// Preview result:
// SQL:  SELECT * FROM t WHERE id IN ($1, $2, $3) AND active=$4
// Args: []any{10, 11, 12, true}

VALUES :rows{...} bulk expansion

type NewUser struct{ ID int `db:"id"`; Name string `db:"name"` }
rows := []NewUser{{1,"Anna"},{2,"Luca"},{3,"Mia"}}

q, args, _ := sqlr.New(sqlr.Postgres).
  Write("INSERT INTO users (id,name) VALUES :rows{id,name}").
  Bind("rows", rows).
  Preview()

// Preview result:
// SQL:  INSERT INTO users (id,name) VALUES ($1, $2), ($3, $4), ($5, $6)
// Args: []any{1, "Anna", 2, "Luca", 3, "Mia"}

Prevent slice expansion (keep one placeholder)

ids := []int64{1,2,3}

_, _, _ = sqlr.New(sqlr.Postgres).
  Write("SELECT * FROM t WHERE id = ANY(:ids)").
  Bind("ids", sqlr.Scalar(ids)). // keeps a single param
  Build()

// Preview result:
// SQL:  SELECT * FROM t WHERE id = ANY($1)
// Args: []any{[]int64{1, 2, 3}}

Using a driver.Valuer (e.g. pq.Array(ids)) also prevents expansion.

Scalar controls only sqlr's expansion behavior; it does not encode the value for a database driver. The standard database/sql conversion accepts []byte, but not arbitrary slices such as []int64. When executing the query, use a driver-supported value or a driver.Valuer such as pq.Array(ids).

Scalar binding via struct tag

// Bind a slice as a single scalar param using the ",scalar" option.
type Filter struct {
	IDs    []int  `db:"ids,scalar"` // <- prevents expansion of :ids
	Active bool   `db:"active"`
}

f := Filter{IDs: []int{1, 2, 3}, Active: true}

q, args, err := sqlr.New(sqlr.Postgres).
  Write(`SELECT id FROM users WHERE id = ANY(:ids) AND active = :active`).
  Bind(f). // struct tags control binding behavior
  Build()

if err != nil { return err }
_ = q
_ = args // contains the []int as one argument

// Preview result:
// SQL:  SELECT id FROM users WHERE id = ANY($1) AND active = $2
// Args: []any{[]int{1, 2, 3}, true}

The ,scalar option on the db tag tells sqlr not to expand the slice; it remains one placeholder whose value is the whole slice. As with Scalar, executing this example requires a driver that accepts that value type. A field whose value implements driver.Valuer is already treated as scalar automatically.

driver.Valuer (Postgres array)

import "github.com/lib/pq"

ids := []int64{1,2,3}
var out []int64

err := sqlr.New(sqlr.Postgres).
  Write("SELECT id FROM users WHERE id = ANY(:ids)").
  Bind("ids", pq.Array(ids)). // single placeholder; driver handles encoding
  ScanAll(db, &out)

// Preview result:
// SQL:  SELECT id FROM users WHERE id = ANY($1)
// Args: []any{pq.Array(ids)}

Valuer + Scanner (JSONB round-trip)

type JSONB map[string]any

func (j JSONB) Value() (driver.Value, error) { // driver.Valuer
    b, err := json.Marshal(j)
    return b, err
}
func (j *JSONB) Scan(src any) error { // sql.Scanner
    switch v := src.(type) {
    case []byte:
        return json.Unmarshal(v, j)
    case string:
        return json.Unmarshal([]byte(v), j)
    default:
        return fmt.Errorf("unsupported: %T", src)
    }
}

type Row struct {
    Meta JSONB `db:"meta"`
}

var rows []Row
err := sqlr.New(sqlr.Postgres).
  Write("SELECT meta FROM users WHERE active=:a").
  Bind("a", true).
  ScanAll(db, &rows)

// Preview result:
// SQL:  SELECT meta FROM users WHERE active=$1
// Args: []any{true}

In short: Valuer controls how a value is sent to the driver; Scanner controls how a column is read into your type. sqlr lets database/sql do its job here.

Dynamic composition + Writef()

table := "audit_events" // trusted constant, not user input
since := time.Now().Add(-6 * time.Hour)

b := sqlr.New(sqlr.Postgres).
	Write("").
  Writef("/* tenant=%d */ ", tenantID). // annotate the query
  Writef("SELECT id, ts, kind FROM %s WHERE ts >= :since", table).
  Bind("since", since)

sql, args, _ := b.Preview() // use Exec/Scan to run; Preview does not release the builder.

// Preview result (tenantID and since shown symbolically):
// SQL:  /* tenant=<tenantID> */ SELECT id, ts, kind FROM audit_events WHERE ts >= $1
// Args: []any{since}

Writef() is for safe, non-user interpolation (comments, known identifiers). Never put untrusted values in Writef().

Conditional composition & many Bind() calls

b := sqlr.New(sqlr.Postgres).
  Write(`SELECT id, name, created_at FROM users WHERE 1=1`)

if namePrefix != "" {
  b.Write(` AND name ILIKE :name_prefix`).
    Bind("name_prefix", namePrefix+"%")
}
if len(ids) > 0 {
  b.Write(` AND id IN (:ids)`).
    Bind("ids", ids) // expands only at build time
}
if since != nil {
  b.Write(` AND created_at >= :since`).
    Bind("since", *since)
}

var users []User
if err := b.ScanAll(db, &users); err != nil { /* ... */ }

// Preview result (assuming all conditions are active and len(ids) == 2):
// SQL: SELECT id, name, created_at FROM users WHERE 1=1 AND name ILIKE $1 AND id IN ($2, $3) AND created_at >= $4
// Args: []any{namePrefix + "%", ids[0], ids[1], *since}

Why many Bind() calls are cheap

• Key/value Bind calls write into a small reusable bag owned by the builder. A one-argument Bind (map, struct, or rows slice) is queued as a source and resolved at Build time, without copying the source.
• Bind sources retain their exact call order, so last-write-wins also holds when key/value pairs, maps, and structs are interleaved.
• There’s no SQL parse and no args slice churn on every Bind. The heavy work happens once at Build/Exec/Scan:
  • a single-pass parse for a first-seen SQL string, then automatic template rendering after admission,
  • placeholder numbering per dialect,
  • slice/rows expansion,
  • final []any allocation and fill.
• Complexity is roughly O(L + H·S + E) in the general case, with an O(1) lookup fast path for the common final map[string]any/pair bag, where:
  • L = SQL length scanned once,
  • H = number of placeholders,
  • S = number of queued Bind sources (usually one),
  • E = total items produced by expansions (IN (:ids), :rows{...}, etc).
• Struct reflection is backed by a bounded field-index cache. Generated binders and mappers can bypass it on selected types; repeated Bind("k", v) pairs are essentially single map writes.

This design lets you compose queries freely with negligible per-bind overhead, while keeping all value interpolation strictly parameterized.

JOIN into two structs with overlapping field names

type User struct {
	ID   int    `db:"u_id"` // note the alias-tag mapping
	Name string `db:"u_name"`
}
type Order struct {
	ID     int     `db:"o_id"` // overlaps on name "id", so we alias
	Total  float64 `db:"total"`
}
type Row struct {
	User  User
	Order Order
}

var rows []Row
err := sqlr.New(sqlr.Postgres).
  Write(`
    SELECT
      u.id   AS u_id,
      u.name AS u_name,
      o.id   AS o_id,
      o.total
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE o.status = :st
  `).
  Bind("st", "paid").
  ScanAll(db, &rows)

// Preview result (SQL whitespace compacted below for readability):
// SQL:  SELECT u.id AS u_id, u.name AS u_name, o.id AS o_id, o.total
//       FROM users u JOIN orders o ON o.user_id = u.id WHERE o.status = $1
// Args: []any{"paid"}

Ways to pass values with Bind

When you have many parameters—or they already live in a struct/map—it’s often nicer to bind them in one shot instead of writing multiple Bind("k", v) calls. sqlr accepts a literal param map (P{}), maps with string-compatible keys, or a struct using db tags or field names. Sources are retained in call order without being copied, can be mixed freely, and follow last-write-wins when keys overlap.

Binding rules

• Parameter names are case-sensitive and use the form [A-Za-z_][A-Za-z0-9_]*.
• Use Bind("name", value) when binding a value, slice, array, or RowSource to a specific placeholder.
• Use Bind(mapOrStruct) when parameter names come from map keys, db tags, or Go field names.
• Bind(nil) is a no-op. Use Bind("name", nil) to bind SQL NULL.
• Non-byte slices and arrays expand into multiple placeholders. []byte, Scalar(...), and values implementing driver.Valuer remain a single argument.
• Every occurrence of a parameter produces its own placeholder and argument.
• Maps, structs, and row slices passed as one-argument sources are retained until the query is built; do not mutate them concurrently.

Bind a param map with P{}

err := sqlr.New(sqlr.Postgres).
  Write("SELECT * FROM products WHERE brand=:b AND price<=:p").
  Bind(sqlr.P{"b": "Acme", "p": 100}).
  ScanAll(db, &out)

// Preview result:
// SQL:  SELECT * FROM products WHERE brand=$1 AND price<=$2
// Args: []any{"Acme", 100}

Bind a struct (uses db tags or field names)

type Filter struct {
  Brand string `db:"b"`
  MaxP  int    `db:"p"`
}
f := Filter{"Acme", 100}

err := sqlr.New(sqlr.Postgres).
  Write("SELECT * FROM products WHERE brand=:b AND price<=:p").
  Bind(f).
  ScanAll(db, &out)

// Preview result:
// SQL:  SELECT * FROM products WHERE brand=$1 AND price<=$2
// Args: []any{"Acme", 100}

Bind a generic map

m := map[string]any{"b": "Acme", "p": 100}

err := sqlr.New(sqlr.Postgres).
  Write("SELECT * FROM products WHERE brand=:b AND price<=:p").
  Bind(m).
  ScanAll(db, &out)

// Preview result:
// SQL:  SELECT * FROM products WHERE brand=$1 AND price<=$2
// Args: []any{"Acme", 100}

Optional: compile sqlr placeholders at startup

Normal application code only needs Write. If you want sqlr to validate parameter names and bulk-row placeholder syntax during startup, Compile returns an error and MustCompile panics on invalid sqlr syntax.

Compile does not validate the complete SQL statement against the database, does not call database/sql.Prepare, and does not create a server-side prepared statement.

s := sqlr.New(sqlr.Postgres)
findActive := s.MustCompile(
  "SELECT id, name FROM users WHERE active=:active AND id IN (:ids)",
)

var users []User
err := findActive.
  Bind("active", true, "ids", []int{1, 2, 3}).
  ScanAll(db, &users)

// Preview result:
// SQL:  SELECT id, name FROM users WHERE active=$1 AND id IN ($2, $3, $4)
// Args: []any{true, 1, 2, 3}

A Template is safe to share across goroutines and retains the dialect and configuration of the *SQLR that created it. Each call to Bind starts a separate single-use builder. Appending SQL with Write or Writef is supported, but for conditional query composition it is clearer to start directly from SQLR.Write.

Optional generated fast paths

The normal API needs no code generation. Struct metadata and scan plans are cached, so the fallback remains appropriate for most applications. For CPU-sensitive paths, sqlrgen can emit direct field accessors while leaving the source-level query API intact.

//go:generate go run github.com/gandaldf/sqlr/cmd/sqlrgen -type QueryParams,User,NewUser

type QueryParams struct {
  Active bool  `db:"active"`
  IDs    []int `db:"ids"`
}

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

type NewUser struct {
  ID   int    `db:"id"`
  Name string `db:"name"`
}

Run:

go generate ./...

By default the command writes sqlr_gen.go in the current package. The generated code provides:

• a named binder for the requested struct;
• a bulk-row binder for its supported []struct fields;
• a cached scan-plan provider used automatically by ScanOne and ScanAll;
• SQLRRowsOf<Type>(rows) for an explicit reflection-free RowSource when the requested row type is fully supported.

Use a pointer to let the ordinary Bind method select the generated binder:

params := QueryParams{Active: true, IDs: []int{1, 2, 3}}

err := s.Write(
  "SELECT id, name FROM users WHERE active=:active AND id IN (:ids)",
).
  Bind(&params).
  ScanAll(db, &users)

// Preview result:
// SQL:  SELECT id, name FROM users WHERE active=$1 AND id IN ($2, $3, $4)
// Args: []any{true, 1, 2, 3}

No separate generated-code API is required: Bind(&params) discovers the generated implementation automatically. Passing params by value remains valid and uses the cached-reflection fallback. A generated binder may also be partial: names it does not handle fall back to the ordinary resolver.

For scalar-only templates, the ordinary struct fallback also caches a template/type binding plan and can be as fast as—or slightly faster than—a generated named binder. The generator's larger wins are reflection-free bulk rows and result mapping; generate simple parameter structs for type safety or consistency, not because it is mandatory for good scalar performance.

For bulk rows independent of a containing parameter struct:

rows := []NewUser{{ID: 1, Name: "Anna"}, {ID: 2, Name: "Luca"}}

_, err := s.Write(
  "INSERT INTO users(id,name) VALUES :rows{id,name}",
).Bind("rows", SQLRRowsOfNewUser(rows)).Exec(db)

// Preview result:
// SQL:  INSERT INTO users(id,name) VALUES ($1, $2), ($3, $4)
// Args: []any{1, "Anna", 2, "Luca"}

Generated files are written atomically and are ordinary Go source that should normally be committed. Regenerate them when a selected struct or its db tags change. The requested types must be named, non-generic structs; sqlrgen rejects generic types explicitly instead of producing invalid code. sqlrgen is an optimization tier, not a replacement API: maps, pairs, non-generated structs, and unsupported generated cases continue through the existing paths.

ExecContext with timeout

ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
defer cancel()

res, err := sqlr.New(sqlr.Postgres).
  Write("UPDATE products SET price=:p WHERE id IN (:ids)").
  Bind("p", 999, "ids", []int{7,8,9}).
  ExecContext(ctx, db)
if err != nil { return err }

// Preview result:
// SQL:  UPDATE products SET price=$1 WHERE id IN ($2, $3, $4)
// Args: []any{999, 7, 8, 9}

ScanAllContext with cancellation

ctx, cancel := context.WithCancel(context.Background())
defer cancel()

var users []User
err := sqlr.New(sqlr.Postgres).
  Write("SELECT id, name FROM users WHERE active=:a").
  Bind("a", true).
  ScanAllContext(ctx, db, &users)
if err != nil { return err }

// Preview result:
// SQL:  SELECT id, name FROM users WHERE active=$1
// Args: []any{true}

ScanOneContext with deadline

deadline := time.Now().Add(500 * time.Millisecond)
ctx, cancel := context.WithDeadline(context.Background(), deadline)
defer cancel()

var count int
err := sqlr.New(sqlr.Postgres).
  Write("SELECT COUNT(*) FROM orders WHERE status=:s").
  Bind("s", "paid").
  ScanOneContext(ctx, db, &count)
if err != nil { return err }

// Preview result:
// SQL:  SELECT COUNT(*) FROM orders WHERE status=$1
// Args: []any{"paid"}

Configuration and parser limits

Pass one Config to New when the defaults are not appropriate:

s := sqlr.New(sqlr.SQLite, sqlr.Config{
  MaxParams:  500,
  MaxNameLen: 48,
})

MaxParams == 0 selects the dialect default; a negative value disables the limit. MaxNameLen <= 0 selects the default of 64 bytes.

New accepts exactly one of the four exported dialect constants and at most one Config. An invalid dialect or more than one configuration is a programmer error and causes a panic at construction time.

Dialect	Default MaxParams
PostgreSQL	65,535
MySQL	65,535
SQLite	999
SQL Server	2,100

sqlr returns ErrTooManyParams before emitting a query that exceeds the configured limit. It does not automatically split or chunk a statement.

Scanning behavior

• ScanOne requires a non-nil pointer. Primitive, time.Time, and sql.Scanner destinations require exactly one result column; structs use column names and db tags.
• Column and field matching is exact and case-sensitive: a column named id does not automatically match a Go field named ID. Use db:"id" tags or explicit SQL aliases. Unmatched result columns are discarded.
• ScanOne returns sql.ErrNoRows for zero rows. If more than one row is returned, it scans the first row into the destination and then returns ErrMoreThanOneRow.
• ScanAll requires a non-nil pointer to a slice. It resets the slice length to zero and reuses existing capacity when possible; reused value elements are zeroed before scanning and results never append to the previous logical contents.
• If scanning fails after some rows have already been read, the destination slice may contain partial results. Treat the destination as invalid when ScanAll returns an error.
• Primitive, time.Time, and sql.Scanner element types in ScanAll require exactly one result column.
• Result columns with no matching struct field are scanned and discarded. Struct fields with no matching result column are not assigned by ScanOne; reused value elements in ScanAll start from their zero value.
• Nested structs are flattened. time.Time and sql.Scanner types are leaves. The db:",scalar" option affects binding only and does not change scan mapping.
• SQL NULL maps naturally to pointer fields, pointer slice elements such as []*int/[]*time.Time, and sql.Null*/custom Scanner fields. Scanning NULL into a non-nullable value normally returns a driver scan error.

Builder lifecycle & SQLR reuse

All terminal operations — Build, Exec/ExecContext, ScanOne/ScanOneContext, and ScanAll/ScanAllContext — release the builder's internal state back to a pool. The *Builder identity itself is never pooled and remains permanently released. Do not use it after a terminal operation. Repeated Release calls are safe no-ops.

Preview is not terminal and leaves the builder usable.

The same lifecycle applies to builders returned by a compiled Template. The Template itself is immutable and reusable; its builders are not.

Don’t reuse after Exec/Build

b := sqlr.New(sqlr.Postgres).
  Write("UPDATE t SET a=:a WHERE id=:id").
  Bind("a", 1, "id", 7)

_, err := b.Exec(db) // releases b
if err != nil { return err }

// b.Write(" AND ...") // DON'T: b is released

Inspect, then execute (Preview doesn’t release)

b := sqlr.New(sqlr.Postgres).
  Write("SELECT * FROM t WHERE id IN (:ids)").
  Bind("ids", []int{1,2,3})

q, args, _ := b.Preview() // still usable
_ = q; _ = args

var out []int
if err := b.ScanAll(db, &out); err != nil { /* ... */ } // releases here

Start fresh when you need a new query

b := sqlr.New(sqlr.Postgres)

// first query
if _, err := b.Write("DELETE FROM sessions WHERE user_id=:u").
  Bind("u", userID).
  Exec(db); err != nil { return err }

// second query → new builder
var user User
if err := b.Write("SELECT id,name FROM users WHERE id=:u").
  Bind("u", userID).
  ScanOne(db, &user); err != nil { return err }

Transactions

b := sqlr.New(sqlr.Postgres)

ctx := context.Background()
tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

// 1) debit
if _, err := b.Write("UPDATE accounts SET balance=balance-:amt WHERE id=:id").
  Bind("amt", 50, "id", 1001).
  ExecContext(ctx, tx); err != nil { return err }

// 2) credit
if _, err := b.Write("UPDATE accounts SET balance=balance+:amt WHERE id=:id").
  Bind("amt", 50, "id", 2002).
  ExecContext(ctx, tx); err != nil { return err }

// 3) read something within the same tx
var total int
if err := b.Write("SELECT COUNT(*) FROM ledger WHERE ok=:ok").
  Bind("ok", true).
  ScanOneContext(ctx, tx, &total); err != nil { return err }

return tx.Commit()

Gotchas & tips:

• The *SQLR instance is reusable and thread-safe across the app; each Write returns a new single-use builder backed by pooled internal state.
• Reusing that instance also enables automatic two-hit template compilation. Calling New per query is valid but gives up this cache.
• Automatic template caching retains SQL strings up to 16 KiB. Larger queries remain fully supported but are not cached automatically; use Compile when explicit compilation is useful.
• A compiled *Template is also reusable and thread-safe. Each bind starts a separate single-use builder.
• Builder lifecycle: all terminal operations — Build, Exec/ExecContext, ScanOne/ScanOneContext, and ScanAll/ScanAllContext — release internal state. The builder stays permanently invalid; Release is idempotent. Preview is not terminal.
• Raw SQL: both Write and Writef append trusted SQL text. Only values supplied through Bind are parameterized; never concatenate untrusted identifiers or values into either method.
• A row slice passed directly as Bind(rows) is resolved only for a bulk placeholder named :rows{...}. Prefer Bind("name", rows) when using another placeholder name or when explicit code is clearer.
• Empty inputs:
  • IN (:ids) with an empty slice → error (ErrSliceEmpty). Decide your own fallback (WHERE 1=0, omit the clause, etc.).
  • :name{...} with an empty slice → error (ErrRowsEmpty).
• Missing binds: referencing :name that isn’t provided yields ErrParamMissing.
• Ambiguous mapping: two struct fields mapping to the same column name cause ErrFieldAmbiguous. Disambiguate with tags/aliases (as in the JOIN example).
• NULL into non-pointer: scanning NULL into a non-pointer field triggers a driver scan error. Use *T, sql.Null*, or a custom sql.Scanner.
• Quotes/comments follow the selected dialect: PostgreSQL dollar quotes and E'...' strings, MySQL # comments and whitespace-sensitive -- comments, SQL Server/SQLite bracket identifiers, and nested block comments for PostgreSQL/SQL Server are recognized. PostgreSQL dollar quoting is not applied to the other dialects.
• Lexing models normal server defaults. In particular, MySQL strings use backslash escapes and ordinary PostgreSQL strings assume standard_conforming_strings=on; sqlr cannot infer per-connection modes such as MySQL NO_BACKSLASH_ESCAPES. MySQL executable/version comments (/*! ... */) are treated as comments, not as bindable SQL.
• RowSource implementations must return a stable, non-negative Len during one build and append exactly one value per requested column. Prefer sqlrgen's implementations unless you need a custom source.

Errors are exported sentinel values and can be checked with errors.Is: ErrParamMissing, ErrSliceEmpty, ErrRowsEmpty, ErrRowsMalformed, ErrColumnNotFound, ErrTooManyParams, ErrParamNameTooLong, ErrFieldAmbiguous, ErrBuilderReleased, and ErrMoreThanOneRow.

Benchmarks:

Representative local results on an Apple M1 Pro with Go 1.26.1 (-benchmem, medians rounded):

Path	Time	Bytes/op	Allocs/op
Default adaptive Write, inline map	222 ns	368	3
Default adaptive Write, reused P	94 ns	32	1
Explicit compiled template, reused P	91 ns	32	1
Adaptive Write, parallel hot query	24 ns	32	1
Adaptive Write, parallel 2,048-query rotation	638 ns	202	2
Default adaptive three-field struct bind	115 ns	48	1
Default adaptive generated binder	125 ns	64	2
Reflective bulk rows, 200 × 2 fields	16.8 µs	13,933	205
RowSource bulk rows, 200 × 2 fields	11.9 µs	13,933	205
Cached reflective mapping, 1,000 × 3 fields	152 µs	93,928	1,012
Generated mapping, 1,000 × 3 fields	123 µs	93,928	1,012

These micro-benchmarks isolate sqlr overhead; database/network latency usually dominates real queries. The parallel figures report aggregate Go benchmark throughput with 10 logical workers, not single-request latency. Results vary by query shape, types, driver, CPU, and Go release. Reproduce them with go test -run=^$ -bench=. -benchmem ./....

Performance notes

• Builder state is pooled while builder identities are not; this keeps reuse safe without giving up the hot-path allocation savings. Scanning uses cached plans and reuses holders.
• Small key/value bags are reused with bounded retention; oversized builder buffers are dropped before pooling.
• Field and scan metadata share a bounded, lazily allocated two-generation cache.
• Repeated Write SQL is admitted to a bounded compiled-template cache after two observations; a lock-free last-template check keeps the common path close to explicit Compile performance.
• Dynamic and compiled execution share one SQL lexer, preventing quote/comment handling from drifting between paths.
• Scalar-only templates pre-render their final SQL and rebuild only the argument slice. Slice and row expansions are still rendered for every execution.
• Repeated scalar struct binding caches field paths for the template/type pair, avoiding per-placeholder metadata lookups while retaining reflective fallback semantics.
• Generated code removes sqlr's reflective field traversal for selected pointer values. It cannot remove interface boxing, driver conversion, or reflection internal to database/sql.
• Differential fuzz tests verify that compiled templates preserve dynamic-parser behavior.

Contributing:

Issues and PRs are welcome — especially additional tests, micro-benchmarks, and dialect edge-cases.

License:

MIT (see LICENSE).