sqlr

README

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 heavy 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, ….
• Minimal default API: New, Write/Writef, Bind, Preview/Build, Exec, ScanOne, ScanAll.
• 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

The module declares Go 1.24.5 as its minimum language/toolchain baseline. Development and release checks use Go 1.26.1.

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, _ := sql.Open("postgres", "<dsn>")
	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)
    }
}

One default path, optimized automatically

Write(...).Bind(...) is the canonical path. When the same final SQL is used repeatedly on one *SQLR, sqlr adapts automatically:

• the first occurrence uses the dynamic parser;
• the second occurrence compiles and admits the SQL;
• later occurrences use the compiled template;
• binding independently selects generated accessors, map/pair fast paths, or cached reflection;
• scanning independently selects a generated mapper or the cached-reflection fallback.

The cache is concurrency-safe and bounded to 512 compiled statements. A separate bounded two-hit admission set prevents one-off dynamic SQL from polluting it, and SQL strings larger than 16 KiB are not retained. Reuse the *SQLR instance to benefit from the cache; constructing New for every query remains correct but intentionally starts with an empty cache. Larger statements can still be precompiled explicitly with Compile.

For eager validation or explicit prewarming, Compile and MustCompile remain available:

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)

Template is safe to share across goroutines. Each bind returns a new single-use Builder. Placeholder expansion and final arguments are still computed per call; only repeated SQL token discovery is skipped.

Use Compile when you want an error instead of a panic. It validates placeholder names and bulk-row syntax immediately. A template belongs to the *SQLR that compiled it and therefore retains that dialect and configuration.

Appending SQL with Write or Writef to a template-backed builder is supported, but switches that builder to the dynamic parser. Prefer SQLR.Write directly for genuinely conditional SQL composition.

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()

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)

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)
// returns sql.ErrNoRows if none; sqlr.ErrMoreThanOneRow if >1

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)

• 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)

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()

// q (pretty-printed):
// SELECT * FROM t WHERE id IN ($1, $2, $3) AND active=$4
// args: [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()

// q:
// INSERT INTO users (id,name) VALUES ($1, $2), ($3, $4), ($5, $6)
// args: [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()

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

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)

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)

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

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

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

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 { /* ... */ }

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)

Alternatives to Bind("k", v)

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.

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)

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)

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)

Bind already selects the best available resolver automatically. Advanced code can use the equivalent non-variadic methods when an explicit interface contract is useful:

q, args, err := s.MustCompile("SELECT :id, :active").
  BindValue("id", 42).
  BindValue("active", true).
  Build()

q, args, err = s.MustCompile("SELECT :id").
  BindMap(sqlr.P{"id": 42}).
  Build()

These are optional conveniences, not faster defaults. Normal application code should prefer Bind.

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)

BindNamed is only an optional compile-time assertion of the interface. 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)

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 }

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 }

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 }

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.
• 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.
• 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

Build, Exec, ScanOne, and ScanAll release the builder's internal state back to a pool. The *Builder identity itself is never pooled and remains permanently released, so even a later Release call cannot affect a different query. Don’t keep using it after a terminal call; repeated Release calls are safe no-ops. Use Preview if you need to inspect without releasing.

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.
• A compiled *Template is also reusable and thread-safe. Each bind starts a separate single-use builder.
• Builder lifecycle: Build, Exec, ScanOne, and ScanAll release internal state. The builder stays permanently invalid; Release is idempotent. Use Preview to inspect without releasing.
• 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.
• 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
Historical v0.1.4 dynamic short bind, inline map	515 ns	432	4
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).

Documentation

Index

• Variables
• func Scalar(v any) any
• type Builder
  • func (b *Builder) Bind(args ...any) *Builder
  • func (b *Builder) BindMap(values map[string]any) *Builder
  • func (b *Builder) BindNamed(values NamedBinder) *Builder
  • func (b *Builder) BindRows(name string, rows RowSource) *Builder
  • func (b *Builder) BindValue(name string, value any) *Builder
  • func (b *Builder) Build() (string, []any, error)
  • func (b *Builder) Exec(db Execer) (sql.Result, error)
  • func (b *Builder) ExecContext(ctx context.Context, db Execer) (sql.Result, error)
  • func (b *Builder) Preview() (string, []any, error)
  • func (b *Builder) Release()
  • func (b *Builder) ScanAll(db Queryer, dest any) error
  • func (b *Builder) ScanAllContext(ctx context.Context, db Queryer, dest any) error
  • func (b *Builder) ScanOne(db Queryer, dest any) error
  • func (b *Builder) ScanOneContext(ctx context.Context, db Queryer, dest any) error
  • func (b *Builder) Write(sql string) *Builder
  • func (b *Builder) Writef(format string, args ...any) *Builder
• type Config
• type Dialect
  • func (d Dialect) String() string
• type Execer
• type GeneratedScanPlan
• type NamedBinder
• type NamedRowsBinder
• type P
• type Queryer
• type RowSource
• type SQLR
  • func New(dialect Dialect, cfg ...Config) *SQLR
  • func (s *SQLR) Compile(query string) (*Template, error)
  • func (s *SQLR) MustCompile(query string) *Template
  • func (s *SQLR) Write(sql string) *Builder
• type ScanPlanner
• type Template
  • func (t *Template) Bind(args ...any) *Builder
  • func (t *Template) BindMap(values map[string]any) *Builder
  • func (t *Template) BindNamed(values NamedBinder) *Builder
  • func (t *Template) BindRows(name string, rows RowSource) *Builder
  • func (t *Template) BindValue(name string, value any) *Builder
  • func (t *Template) Builder() *Builder
  • func (t *Template) SQL() string

Variables

var (
	ErrParamMissing     = errors.New("sqlr: missing parameter")
	ErrSliceEmpty       = errors.New("sqlr: empty slice")
	ErrRowsEmpty        = errors.New("sqlr: empty rows")
	ErrRowsMalformed    = errors.New("sqlr: malformed :rows placeholder")
	ErrColumnNotFound   = errors.New("sqlr: column not found")
	ErrTooManyParams    = errors.New("sqlr: too many parameters")
	ErrParamNameTooLong = errors.New("sqlr: parameter name too long")
	ErrFieldAmbiguous   = errors.New("sqlr: ambiguous field name")
	ErrBuilderReleased  = errors.New("sqlr: builder already released; call Write() on *SQLR for a new query")
	ErrMoreThanOneRow   = errors.New("sqlr: more than one row")
)

Functions

func Scalar

func Scalar(v any) any

Scalar wraps a value to force it to be treated as a single scalar argument even if it is a slice/array. Useful for ANY(:ids)-style idioms.

Types

type Builder

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

Builder assembles a single SQL statement and bound parameters. It is NOT safe for concurrent use and is single-use: after Build() it is automatically released and must not be used again.

func (*Builder) Bind

func (b *Builder) Bind(args ...any) *Builder

Bind enqueues a parameter source. Supported forms:

• nil (ignored)
• generated or hand-written NamedBinder / NamedRowsBinder implementations
• struct with `db` tags (flattened through nested structs)
• map[string]any or any reflect.Map
• RowSource for reflection-free bulk rows
• []struct / []map for :rows{...}
• slices of primitives for :name expansion
• k/v pairs (even number of args, first is string key)

Multiple Bind() calls are allowed; resolution is "last one wins".

func (*Builder) BindMap

func (b *Builder) BindMap(values map[string]any) *Builder

BindMap appends an exact map[string]any source without variadic dispatch. Bind selects the same fast path automatically.

func (*Builder) BindNamed

func (b *Builder) BindNamed(values NamedBinder) *Builder

BindNamed appends a generated or hand-written NamedBinder source without reflection. A source may additionally implement NamedRowsBinder. Bind also discovers this interface automatically.

func (*Builder) BindRows

func (b *Builder) BindRows(name string, rows RowSource) *Builder

BindRows binds a reflection-free RowSource to a bulk rows placeholder.

func (*Builder) BindValue

func (b *Builder) BindValue(name string, value any) *Builder

BindValue binds one named value without variadic argument dispatch. Bind is the canonical default; this method is useful when an explicit form is wanted.

func (*Builder) Build

func (b *Builder) Build() (string, []any, error)

Build concatenates the query, performs binding, and RELEASES the builder's internal state back into the pool. After Build(), the builder must not be used again.

func (*Builder) Exec

func (b *Builder) Exec(db Execer) (sql.Result, error)

Exec is a convenience that builds and executes the statement with context.Background().

func (*Builder) ExecContext

func (b *Builder) ExecContext(ctx context.Context, db Execer) (sql.Result, error)

ExecContext builds and executes the statement with the provided context.

func (*Builder) Preview

func (b *Builder) Preview() (string, []any, error)

Preview renders the SQL statement and bound args without releasing the Builder. Safe to call multiple times; identical to Build() except it does NOT Release(). Use this to log/inspect the exact SQL and args that would be produced.

If the builder has already been released, it returns ErrBuilderReleased.

func (*Builder) Release

func (b *Builder) Release()

Release clears the builder and returns its internal state to the pool. It is safe to call Release multiple times; subsequent calls are no-ops, even after another Builder has been acquired from the same SQLR.

func (*Builder) ScanAll

func (b *Builder) ScanAll(db Queryer, dest any) error

ScanAll builds and runs the statement, scanning all rows into dest slice.

func (*Builder) ScanAllContext

func (b *Builder) ScanAllContext(ctx context.Context, db Queryer, dest any) error

ScanAllContext is the context-aware variant of ScanAll.

func (*Builder) ScanOne

func (b *Builder) ScanOne(db Queryer, dest any) error

ScanOne builds and runs the statement, scanning exactly one row into dest. It returns sql.ErrNoRows if no rows are returned. It errors if more than one row.

func (*Builder) ScanOneContext

func (b *Builder) ScanOneContext(ctx context.Context, db Queryer, dest any) error

ScanOneContext is the context-aware variant of ScanOne.

func (*Builder) Write

func (b *Builder) Write(sql string) *Builder

Write appends a raw SQL fragment. No auto-spacing is performed.

func (*Builder) Writef

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

Writef appends a formatted SQL fragment. No auto-spacing is performed.

type Config

type Config struct {
	// MaxParams limits the total number of placeholders that can be emitted by
	// a single Build().
	// If = 0 (or omitted), it uses a sensible per-dialect default.
	// If < 0, it's treated as "unlimited".
	MaxParams int
	// MaxNameLen limits the maximum allowed length of a placeholder name,
	// e.g. ":this_is_a_name". Names longer than this cause ErrParamNameTooLong.
	MaxNameLen int
}

Config defines limits and behavior tweaks for the parser/binder.

type Dialect

type Dialect int

Dialect identifies the SQL dialect for placeholder rendering and a few dialect-specific parsing behaviors.

const (
	Postgres Dialect = iota
	MySQL
	SQLite
	SQLServer
)

func (Dialect) String

func (d Dialect) String() string

String returns the string representation of the dialect.

type Execer

type Execer interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
}

Execer abstracts *sql.DB / *sql.Tx ExecContext for easy testing.

type GeneratedScanPlan

type GeneratedScanPlan interface {
	SQLRScanTargets(destination any, targets []any, sinks []any) error
	SQLRNeedsSinks() bool
}

GeneratedScanPlan fills rows.Scan targets for one destination value without reflective field lookup. A plan is immutable and may be shared concurrently.

type NamedBinder

type NamedBinder interface {
	SQLRBindValue(name string) (value any, found bool, err error)
}

NamedBinder is the optional reflection-free binding contract. Generated or hand-written parameter types can implement it. Returning found=false retains normal resolution, including the same value's reflective fallback.

type NamedRowsBinder

type NamedRowsBinder interface {
	SQLRBindRows(name string) (rows RowSource, found bool, err error)
}

NamedRowsBinder is the optional reflection-free contract for named bulk-row parameters such as :batch{id,name}.

type P

type P = map[string]any

P is a convenient alias for map[string]any to use with Bind().

type Queryer

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

Queryer abstracts *sql.DB / *sql.Tx QueryContext for easy testing.

type RowSource

type RowSource interface {
	Len() int
	SQLRAppendRow(row int, columns []string, args []any) ([]any, error)
}

RowSource exposes bulk rows without reflection. Implementations append one complete row in requested column order directly to args. Len must be stable and non-negative for the duration of one Build call.

type SQLR

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

SQLR is the main entry point. It holds the selected dialect, configuration, a bounded adaptive template cache, and a pool of reusable builder state. A single SQLR instance is safe for concurrent use.

func New

func New(dialect Dialect, cfg ...Config) *SQLR

New returns a new SQLR for the given dialect. Optionally provide a Config; unspecified fields fall back to sensible per-dialect defaults.

func (*SQLR) Compile

func (s *SQLR) Compile(query string) (*Template, error)

Compile eagerly parses query and returns a reusable Template. Write already compiles repeated SQL automatically; use Compile for early validation, explicit prewarming, or direct ownership of the template. Syntax errors in rows blocks and parameter names that exceed Config.MaxNameLen are reported at compile time.

func (*SQLR) MustCompile

func (s *SQLR) MustCompile(query string) *Template

MustCompile is like Compile but panics if query cannot be compiled.

func (*SQLR) Write

func (s *SQLR) Write(sql string) *Builder

Write starts a new statement and returns a single-use Builder. Repeated final SQL strings are compiled automatically after their first use. You can add more chunks via Write/Writef, and bind data via Bind().

type ScanPlanner

type ScanPlanner interface {
	SQLRScanPlan(columns []string) (GeneratedScanPlan, error)
}

ScanPlanner is the optional generated mapping contract. SQLRScanPlan is called only when a column/type plan is absent from sqlr's cache.

type Template

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

Template is an immutable, concurrency-safe compiled SQL statement. It retains the lexical structure of a query so repeated builds skip SQL token discovery. Scalar-only statements also retain their rendered SQL; runtime slice and row expansions preserve the normal Builder binding semantics.

func (*Template) Bind

func (t *Template) Bind(args ...any) *Builder

Bind starts a template-backed Builder and applies the supplied bindings. The returned Builder supports the same Build, Exec, and Scan methods as a Builder created by SQLR.Write.

func (*Template) BindMap

func (t *Template) BindMap(values map[string]any) *Builder

BindMap starts a template-backed Builder with an exact map source.

func (*Template) BindNamed

func (t *Template) BindNamed(values NamedBinder) *Builder

BindNamed starts a template-backed Builder with a reflection-free source.

func (*Template) BindRows

func (t *Template) BindRows(name string, rows RowSource) *Builder

BindRows starts a template-backed Builder with a reflection-free bulk source.

func (*Template) BindValue

func (t *Template) BindValue(name string, value any) *Builder

BindValue starts a template-backed Builder with one explicit named value.

func (*Template) Builder

func (t *Template) Builder() *Builder

Builder returns a single-use Builder backed by the compiled template.

func (*Template) SQL

func (t *Template) SQL() string

SQL returns the original, unrendered SQL text.