sqlb

package module
v0.6.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 17, 2026 License: MIT Imports: 11 Imported by: 1

README

sqlb

Lightweight, type-safe, and reflection-free helpers for database/sql.

go reference

go get go.senan.xyz/sqlb                   # library
go get -tool go.senan.xyz/sqlb/cmd/sqlbgen # tool for codegen

Documentation

Overview

Package sqlb provides lightweight, type-safe, and reflection-free helpers for database/sql.

sqlb tries to make few assumptions about how you write queries or read results. Scanner and SQLer interfaces can be implemented to help you extend the library. Built-in implementations are provided to cover common cases.

Reading rows

Query functions execute queries and read results into a Scanner:

  • QueryRow: read first row into dest
  • QueryRows: read all rows into dest
  • Rows: returns an iterator over all rows
  • Each: returns an iterator with control over reading
  • Exec: execute without returning rows

A Scanner controls how rows are read. Built-in helpers cover common patterns:

  • Append: append rows to *[]T
  • AppendPtr: append row pointers to *[]*T
  • AppendValue: for primitives, append single column values to *[]P
  • SetValue: for primitives, insert single column values into map[P]struct{}
  • MapValues: for primitives, insert two column values into map[K]V
  • Scan: for primitives, read columns into pointers

Or implement Scanner yourself for full control. Scanner implementations can also be generated by the companion tool `sqlbgen`. 

var task Task
sqlb.QueryRow(ctx, db, &task, "SELECT * FROM tasks WHERE name = ?", "alice")

var tasks []Task
sqlb.QueryRows(ctx, db, sqlb.Append(&tasks), "SELECT * FROM tasks ORDER BY name")

for task, err := range sqlb.Rows[Task](ctx, db, "SELECT * FROM tasks ORDER BY name") {
    // ...
}

Writing rows

InsertSQL and UpdateSQL generate SQLer fragments for types implementing Insertable or Updatable: 

sqlb.QueryRow(ctx, db, &user, "INSERT INTO users ? RETURNING *", sqlb.InsertSQL(user))
sqlb.QueryRow(ctx, db, &user, "UPDATE users SET ? WHERE id = ? RETURNING *", sqlb.UpdateSQL(user), user.ID)

Query building

Query provides composable query building with argument tracking: 

var q sqlb.Query
q.Append("SELECT * FROM users WHERE 1")
if name != "" {
    q.Append("AND name = ?", name)
}
q.Append("ORDER BY name")

Any argument implementing SQLer is expanded in-place. Several are built-in: 

subquery := sqlb.NewQuery("SELECT id FROM admins WHERE level > ?", 5)
q.Append("AND id IN (?)", subquery)                       // built-in SQLer
q.Append("AND role IN ?", sqlb.InSQL("editor", "admin"))  // built-in SQLer
q.Append("AND ?", myCustomSQLer(x))                       // bring-your-own SQLer

Code generation

Use sqlbgen to generate Scanner, Insertable, and Updatable implementations: 

//go:generate go tool sqlbgen type User generated ID -- user.gen.go

Statement caching

StmtCache wraps a database connection to cache prepared statements: 

cache := sqlb.NewStmtCache(db)
defer cache.Close()
sqlb.QueryRows(ctx, cache, sqlb.Append(&users), "SELECT * FROM users")

Logging

Use WithLogFunc to add query logging via context: 

ctx := sqlb.WithLogFunc(ctx, func(ctx context.Context, typ, query string, dur time.Duration) {
    slog.DebugContext(ctx, "query", "type", typ, "query", query, "dur", dur)
})
Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	task := Task{Name: "alice", Age: 30}
	if err := sqlb.QueryRow(ctx, db, &task, "INSERT INTO tasks ? RETURNING *", sqlb.InsertSQL(task)); err != nil {
		panic(err)
	}
	fmt.Println("inserted:", task.ID, task.Name, task.Age)

	task.Age = 31
	if err := sqlb.QueryRow(ctx, db, &task, "UPDATE tasks SET ? WHERE id = ? RETURNING *", sqlb.UpdateSQL(task), task.ID); err != nil {
		panic(err)
	}
	fmt.Println("updated:", task.ID, task.Name, task.Age)

	var readTask Task
	if err := sqlb.QueryRow(ctx, db, &readTask, "SELECT * FROM tasks WHERE id = ?", task.ID); err != nil {
		panic(err)
	}
	fmt.Println("read:", readTask.ID, readTask.Name, readTask.Age)
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
inserted: 1 alice 30
updated: 1 alice 31
read: 1 alice 31

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func Each added in v0.5.0 

func Each(ctx context.Context, db QueryDB, dest Scanner, query string, args ...any) iter.Seq[error]

Each returns an iterator that reads each row into dest. Unlike Rows, it reuses the same dest each iteration, suitable for use with Scanner helpers like Scan.

func Exec 

func Exec(ctx context.Context, db ExecDB, query string, args ...any) error

Exec executes a query without returning any rows.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	if err := sqlb.Exec(ctx, db, "INSERT INTO tasks (name) VALUES (?)", "alice"); err != nil {
		panic(err)
	}
	fmt.Println("inserted")
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

Output:
inserted

func QueryRow added in v0.5.0 

func QueryRow(ctx context.Context, db QueryDB, dest Scanner, query string, args ...any) error

QueryRow executes the query and reads the first row into dest, which is typically a native Scanner type or one created with a Scanner helper such as Scan. Returns sql.ErrNoRows if no rows are found.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	_ = sqlb.Exec(ctx, db, "INSERT INTO tasks ?", sqlb.InsertSQL(Task{Name: "alice", Age: 30}))

	var task Task
	if err := sqlb.QueryRow(ctx, db, &task, "SELECT * FROM tasks WHERE name = ?", "alice"); err != nil {
		panic(err)
	}
	fmt.Println(task.Name, task.Age)
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
alice 30

func QueryRows added in v0.5.0 

func QueryRows(ctx context.Context, db QueryDB, dest Scanner, query string, args ...any) error

QueryRows executes the query and reads all rows into dest, which is typically a native Scanner type or one created with a Scanner helper like Append.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	_ = sqlb.Exec(ctx, db, "INSERT INTO tasks ?", sqlb.InsertSQL(
		Task{Name: "alice", Age: 30},
		Task{Name: "bob", Age: 25},
		Task{Name: "carol", Age: 35},
	))

	var tasks []Task
	if err := sqlb.QueryRows(ctx, db, sqlb.Append(&tasks), "SELECT * FROM tasks ORDER BY name"); err != nil {
		panic(err)
	}
	for _, t := range tasks {
		fmt.Println(t.Name, t.Age)
	}
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
alice 30
bob 25
carol 35

func Rows added in v0.4.0 

func Rows[T any, pT ScannerPtr[T]](ctx context.Context, db QueryDB, query string, args ...any) iter.Seq2[T, error]

Rows returns an iterator over query results, allocating a new T per row. T must implement Scanner via its pointer type.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	_ = sqlb.Exec(ctx, db, "INSERT INTO tasks ?", sqlb.InsertSQL(
		Task{Name: "alice", Age: 30},
		Task{Name: "bob", Age: 25},
	))

	for task, err := range sqlb.Rows[Task](ctx, db, "SELECT * FROM tasks ORDER BY name") {
		if err != nil {
			panic(err)
		}
		fmt.Println(task.Name, task.Age)
	}
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
alice 30
bob 25

func WithLogFunc added in v0.3.0 

func WithLogFunc(ctx context.Context, lf LogFunc) context.Context

WithLogFunc returns a context that will log queries using the provided function.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	ctx = sqlb.WithLogFunc(ctx, func(ctx context.Context, typ, query string, dur time.Duration) {
		fmt.Printf("type=%s query=%s\n", typ, query)
	})

	var x int
	_ = sqlb.QueryRow(ctx, db, sqlb.Scan(&x), "SELECT 42")
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

Output:
type=query query=SELECT 42

Types

type ExecDB 

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

ExecDB is an interface compatible with *sql.DB or *sql.Tx for executing queries.

type Insertable 

type Insertable interface {
	IsGenerated(column string) bool
	Values() []sql.NamedArg
}

Insertable represents a type that can provide column values for insertion.

type JSON 

type JSON[T any] struct {
	Data T
}

JSON is a wrapper type for JSON-encoded database columns.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db, _ := sql.Open("sqlite3", ":memory:")
	defer db.Close()
	_ = sqlb.Exec(ctx, db, `CREATE TABLE config (id INTEGER PRIMARY KEY, data JSON)`)

	config := sqlb.NewJSON(map[string]string{"theme": "dark", "lang": "en"})
	_ = sqlb.Exec(ctx, db, `INSERT INTO config (data) VALUES (?)`, config)

	var data sqlb.JSON[map[string]string]
	_ = sqlb.QueryRow(ctx, db, sqlb.Scan(&data), "SELECT data FROM config LIMIT 1")
	fmt.Println(data.Data["theme"])
}

Output:
dark

func NewJSON 

func NewJSON[T any](t T) JSON[T]

func (*JSON[T]) Scan 

func (j *JSON[T]) Scan(value any) error

func (JSON[T]) Value 

func (j JSON[T]) Value() (driver.Value, error)

type LogFunc 

type LogFunc = func(ctx context.Context, typ string, query string, dur time.Duration)

LogFunc is a callback for logging query execution.

type PrepareDB added in v0.2.5 

type PrepareDB interface {
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

PrepareDB is an interface compatible with *sql.DB or *sql.Tx for preparing statements.

type Query 

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

Query represents a composable SQL query builder with arguments.

Example 
package main

import (
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	var q sqlb.Query
	q.Append("SELECT * FROM users WHERE 1")
	q.Append("AND name = ?", "alice")
	q.Append("AND age > ?", 18)

	query, args := q.SQL()
	fmt.Println(query)
	fmt.Println(args)
}

Output:
SELECT * FROM users WHERE 1 AND name = ? AND age > ?
[alice 18]
Example (Nested) 
package main

import (
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	var whereA sqlb.Query
	whereA.Append("three=?", 3)

	whereB := sqlb.NewQuery("four=?", 4)

	var whereC sqlb.Query
	whereC.Append("? or ?", whereA, whereB)

	where := sqlb.NewQuery("?", whereC)

	var b sqlb.Query
	b.Append("select * from (?) union (?)",
		sqlb.NewQuery("select * from tasks where a=?", "a"),
		sqlb.NewQuery("select * from tasks where a=? and ?", "aa", sqlb.NewQuery("xx=?", 10)),
	)
	b.Append("where ?", where)

	query, args := b.SQL()
	fmt.Println(query)
	fmt.Println(args)
}

Output:
select * from (select * from tasks where a=?) union (select * from tasks where a=? and xx=?) where three=? or four=?
[a aa 10 3 4]
Example (Subquery) 
package main

import (
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	subquery := sqlb.NewQuery("SELECT id FROM admins WHERE level > ?", 5)

	var q sqlb.Query
	q.Append("SELECT * FROM users WHERE id IN (?)", subquery)

	query, args := q.SQL()
	fmt.Println(query)
	fmt.Println(args)
}

Output:
SELECT * FROM users WHERE id IN (SELECT id FROM admins WHERE level > ?)
[5]

func NewQuery 

func NewQuery(query string, args ...any) Query

NewQuery creates a new Query by appending the initial query string and arguments.

Example 
package main

import (
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	q := sqlb.NewQuery("SELECT * FROM users WHERE name = ?", "bob")
	query, args := q.SQL()
	fmt.Println(query)
	fmt.Println(args)
}

Output:
SELECT * FROM users WHERE name = ?
[bob]

func (*Query) Append 

func (q *Query) Append(query string, args ...any)

Append adds a SQL fragment and corresponding arguments to the Query. The number of arguments must match the number of '?' placeholders in the fragment.

func (Query) SQL 

func (q Query) SQL() (string, []any)

SQL returns the composed SQL string and flattened argument slice. If any argument implements SQLer, they are expanded recursively in-place.

type QueryDB added in v0.5.0 

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

QueryDB is an interface compatible with *sql.DB or *sql.Tx for querying rows.

type SQLer 

type SQLer interface {
	SQL() (string, []any)
}

SQLer is implemented by types that can be embedded as query arguments.

func InSQL added in v0.2.7 

func InSQL[T any](items ...T) SQLer

InSQL builds a SQLer for an IN clause or value tuple, e.g. (?, ?, ?). Panics if called with zero items.

Example 
package main

import (
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ids := []int{1, 2, 3}

	var q sqlb.Query
	q.Append("SELECT * FROM users WHERE id IN ?", sqlb.InSQL(ids...))

	query, args := q.SQL()
	fmt.Println(query)
	fmt.Println(args)
}

Output:
SELECT * FROM users WHERE id IN (?, ?, ?)
[1 2 3]

func InsertSQL 

func InsertSQL[T Insertable](items ...T) SQLer

InsertSQL builds a SQLer representing an INSERT for one or more Insertable items. Generated columns (where [Insertable.IsGenerated] returns true) are skipped. Panics if called with zero items.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	task := Task{Name: "alice", Age: 30}
	if err := sqlb.QueryRow(ctx, db, &task, "INSERT INTO tasks ? RETURNING *", sqlb.InsertSQL(task)); err != nil {
		panic(err)
	}
	fmt.Println(task.ID, task.Name, task.Age)
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
1 alice 30
Example (Many) 
package main

import (
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	tasks := []Task{
		{Name: "alice", Age: 30},
		{Name: "bob", Age: 25},
	}

	q := sqlb.NewQuery("INSERT INTO tasks ?", sqlb.InsertSQL(tasks...))
	query, args := q.SQL()
	fmt.Println(query)
	fmt.Println(args)
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
INSERT INTO tasks (name, age) VALUES (?, ?), (?, ?)
[alice 30 bob 25]

func UpdateSQL 

func UpdateSQL(item Updatable) SQLer

UpdateSQL builds a SQLer representing an UPDATE for an Updatable item. Generated columns (where [Updatable.IsGenerated] returns true) are skipped.

Example 
package main

import (
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	task := Task{ID: 1, Name: "alice", Age: 31}

	q := sqlb.NewQuery("UPDATE tasks SET ? WHERE id = ?", sqlb.UpdateSQL(task), task.ID)
	query, args := q.SQL()
	fmt.Println(query)
	fmt.Println(args)
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
UPDATE tasks SET name=? , age=? WHERE id = ?
[alice 31 1]

type Scanner added in v0.6.0 

type Scanner interface {
	ScanFrom(columns []string, rows *sql.Rows, buf []any) error
}

Scanner represents a type that can read itself from a row.

func Append added in v0.3.0 

func Append[T any, pT ScannerPtr[T]](dest *[]T) Scanner

Append returns a Scanner that appends each row to dest.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	_ = sqlb.Exec(ctx, db, "INSERT INTO tasks ?", sqlb.InsertSQL(Task{Name: "one"}, Task{Name: "two"}))

	var tasks []Task
	if err := sqlb.QueryRows(ctx, db, sqlb.Append(&tasks), "SELECT * FROM tasks ORDER BY id"); err != nil {
		panic(err)
	}
	fmt.Println(len(tasks))
	fmt.Println(tasks[0].Name)
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
2
one

func AppendPtr added in v0.3.0 

func AppendPtr[T any, pT ScannerPtr[T]](dest *[]*T) Scanner

AppendPtr returns a Scanner that appends a pointer to each row to dest.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	_ = sqlb.Exec(ctx, db, "INSERT INTO tasks ?", sqlb.InsertSQL(Task{Name: "one"}, Task{Name: "two"}))

	var tasks []*Task
	if err := sqlb.QueryRows(ctx, db, sqlb.AppendPtr(&tasks), "SELECT * FROM tasks ORDER BY id"); err != nil {
		panic(err)
	}
	fmt.Println(len(tasks))
	fmt.Println(tasks[0].Name)
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
2
one

func AppendValue added in v0.3.0 

func AppendValue[T any](s *[]T) Scanner

AppendValue returns a Scanner that appends a single column value to dest per row. For primitive types that don't need a full Scanner implementation.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	_ = sqlb.Exec(ctx, db, "INSERT INTO tasks ?", sqlb.InsertSQL(
		Task{Name: "alice"},
		Task{Name: "bob"},
		Task{Name: "carol"},
	))

	var names []string
	if err := sqlb.QueryRows(ctx, db, sqlb.AppendValue(&names), "SELECT name FROM tasks ORDER BY name"); err != nil {
		panic(err)
	}
	fmt.Println(names)
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
[alice bob carol]

func MapValues added in v0.6.0 

func MapValues[K comparable, V any](m map[K]V) Scanner

MapValues returns a Scanner that reads two columns into dest as key-value pairs per row. For primitive types that don't need a full Scanner implementation.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	_ = sqlb.Exec(ctx, db, "INSERT INTO tasks ?", sqlb.InsertSQL(
		Task{Name: "alice", Age: 30},
		Task{Name: "bob", Age: 25},
		Task{Name: "carol", Age: 40},
	))

	ages := make(map[string]int)
	if err := sqlb.QueryRows(ctx, db, sqlb.MapValues(ages), "SELECT name, age FROM tasks"); err != nil {
		panic(err)
	}
	fmt.Println(ages["alice"])
	fmt.Println(ages["bob"])
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
30
25

func Scan 

func Scan(dests ...any) Scanner

Scan returns a Scanner that reads columns into the provided pointers. For primitive types that don't need a full Scanner implementation.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	var x, y int
	if err := sqlb.QueryRow(ctx, db, sqlb.Scan(&x, &y), "SELECT 10, 20"); err != nil {
		panic(err)
	}
	fmt.Println(x, y)
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

Output:
10 20

func SetValue added in v0.3.5 

func SetValue[T comparable](s map[T]struct{}) Scanner

SetValue returns a Scanner that inserts a single column value into dest per row. For primitive types that don't need a full Scanner implementation.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	_ = sqlb.Exec(ctx, db, "INSERT INTO tasks ?", sqlb.InsertSQL(
		Task{Name: "alice"},
		Task{Name: "bob"},
		Task{Name: "alice"},
	))

	names := make(map[string]struct{})
	if err := sqlb.QueryRows(ctx, db, sqlb.SetValue(names), "SELECT name FROM tasks"); err != nil {
		panic(err)
	}
	fmt.Println(len(names))
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

type Task struct {
	ID   int
	Name string
	Age  int
}

func (Task) IsGenerated(c string) bool {
	return c == "id"
}

func (t Task) Values() []sql.NamedArg {
	return []sql.NamedArg{sql.Named("id", t.ID), sql.Named("name", t.Name), sql.Named("age", t.Age)}
}

func (t *Task) ScanFrom(columns []string, rows *sql.Rows, buf []any) error {
	for _, c := range columns {
		switch c {
		case "id":
			buf = append(buf, &t.ID)
		case "name":
			buf = append(buf, &t.Name)
		case "age":
			buf = append(buf, &t.Age)
		default:
			return fmt.Errorf("unknown column name %q", c)
		}
	}
	return rows.Scan(buf...)
}

Output:
2

type ScannerPtr added in v0.6.0 

type ScannerPtr[T any] interface {
	Scanner
	*T
}

ScannerPtr is a constraint for pointer types that implement Scanner, allowing allocation of a new T and read into *T. NOTE: It should not be used directly, since Go will infer it from destination arguments.

type StmtCache added in v0.2.5 

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

StmtCache wraps a database connection to cache prepared statements.

Example 
package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/ncruces/go-sqlite3/driver"
	"go.senan.xyz/sqlb"
)

func main() {
	ctx := context.Background()
	db := newDB(ctx)
	defer db.Close()

	cache := sqlb.NewStmtCache(db)
	defer cache.Close()

	var x int
	_ = sqlb.QueryRow(ctx, cache, sqlb.Scan(&x), "SELECT 1")
	_ = sqlb.QueryRow(ctx, cache, sqlb.Scan(&x), "SELECT 1") // uses cached statement
	fmt.Println(x)
}

func newDB(ctx context.Context) *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table tasks (id integer primary key autoincrement, name text not null default "", age integer not null default 0)`); err != nil {
		panic(err)
	}
	if err := sqlb.Exec(ctx, db, `create table books (id integer primary key autoincrement, details json)`); err != nil {
		panic(err)
	}
	return db
}

Output:
1

func NewStmtCache added in v0.2.5 

func NewStmtCache(db PrepareDB) *StmtCache

NewStmtCache creates a new statement cache wrapping the provided database connection.

func (*StmtCache) Close added in v0.2.5 

func (sc *StmtCache) Close() error

func (*StmtCache) ExecContext added in v0.2.5 

func (sc *StmtCache) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

func (*StmtCache) QueryContext added in v0.2.5 

func (sc *StmtCache) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)

type Updatable 

type Updatable interface {
	IsGenerated(column string) bool
	Values() []sql.NamedArg
}

Updatable represents a type that can provide column values for updates.

Source Files

View all Source files

Directories

Path Synopsis
cmd
sqlbgen command

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.