Documentation
¶
Overview ¶
Package db provides a high-level API for interacting with SQLite databases using zombiezen.com/go/sqlite. It offers a type-safe way to map Go structs to SQL tables and columns, simplifying CRUD operations.
Struct Tagging and Scanning ¶
The package uses the "db" struct tag to map struct fields to database columns.
type User struct {
ID int `db:"id"`
Username string `db:"username"`
Meta Meta `db:"meta,json"` // Scanned as JSON
}
If the "db" tag is missing, the field name is converted to snake_case to infer the column name (e.g., "UserName" -> "user_name"). Use `db:"-"` to ignore a field.
Nested struct fields (and pointer-to-struct fields) are flattened with a "parent_child" prefix to form the column name — a field named "Foo" of type Inner expands to columns "foo_a", "foo_b" matching Inner's own column list. Use `db:"-"` to exclude a field from this expansion.
Column inference: the SELECT/INSERT asymmetry ¶
This package distinguishes SELECT-side and INSERT-side column lists because they have very different reorder safety:
- SELECT, RETURNING, UPDATE-RETURNING, DELETE-RETURNING columns are matched by NAME against the SQLite statement's output. Reordering the Go struct's fields cannot misroute a value to the wrong destination.
- INSERT column lists are POSITIONAL. The caller writes the `?` value tuple as `Exec(ctx, args...)`. If the package inferred the column list from struct-field declaration order, *reordering a struct field* would silently rebind every value to the wrong column — same types, still compiles, may not be caught by schema constraints.
The package therefore infers columns *only* for SELECT-side operations:
- Select[T](table) with no columns → inferred from T.
- [Selector.Returning] / Updater.Returning / Deleter.Returning called with no arguments → inferred from T.
- Declare[T](table) with no columns → inferred from T, available to downstream Declaration.Select, Declaration.Update, Declaration.Delete.
INSERT keeps explicit columns. Use Declare[T](table, "a", "b") to pin the insert-column order at package scope, independent of struct layout, then call .Insert(...) at every insert site. A side benefit: tag renames in T now produce a loud SQLite "no such column" error against the inferred SELECT list rather than a silent zero-scan.
To force a literal `SELECT *`, pass "*" as the only column: `db.Select[T](table, "*")`.
ColumnsOf[T] is exported for callers who want the inferred list without going through a builder.
Column inference requires T to be a struct (a single pointer level is dereferenced). Non-struct T — basic types, slices, maps — must use explicit columns; inference returns an error rather than silently emitting SELECT *.
Supported Types ¶
The scanner supports the following Go types for column mapping:
- Basic types: string, int, int64, uint64, bool, float64, []byte
- Pointers: Supported (scans into the value or sets to nil if NULL)
- Nested structs: Supported via the "db" tag or inferred names.
- JSON Columns: Use `db:"colname,json"` to scan a JSON text column into a struct, map, or slice field. This is useful for storing complex data types like []string or configuration maps.
Implicit JSON for Top-Level Scan Targets ¶
When the scan target itself is a slice (other than []byte) or a map, the column's text is decoded as JSON without requiring a struct or tag:
tags, err := db.Select[[]string]("posts", "tags").Where("id=?").Exec1(ctx, 1)
meta, err := db.Select[map[string]int]("info", "data").Exec1(ctx)
[]byte remains the BLOB escape hatch and is never JSON-decoded. For JSON fields nested inside a struct, use the `db:"col,json"` tag.
SQL Safety ¶
Table names, column names, WHERE clauses, JOIN conditions, and other structural SQL fragments are interpolated directly into the generated SQL string. Only values bound via ? placeholders are parameterized. Callers must ensure that all structural string arguments (table names, column names, WHERE/JOIN/ORDER BY expressions) are not derived from untrusted input.
Context Handling ¶
Database connections are managed via the context. Use db.With(ctx, conn) to bind a connection to a context, and then pass that context to db functions.
Iterators ¶
Several methods return iter.Seq iterators that report errors via a pointer:
var err error
for user := range users.Select().Iter(ctx, &err) {
fmt.Println(user.Name)
}
if err != nil {
return err
}
Index ¶
- func ColumnsOf[T any]() ([]string, error)
- func Exec(ctx context.Context, sql string, args ...any) (int, error)
- func From(ctx context.Context) *sqlite.Conn
- func Tx(ctx context.Context, fn func(context.Context) error) (rerr error)
- func TxV[T any](ctx context.Context, fn func(context.Context) (T, error)) (T, error)
- func With(ctx context.Context, conn *sqlite.Conn) context.Context
- type Declaration
- type Deleter
- func (cfg Deleter[T]) Exec(ctx context.Context, args ...any) (count int, err error)
- func (cfg Deleter[T]) Exec1(ctx context.Context, args ...any) (ret T, err error)
- func (cfg Deleter[T]) ExecN(ctx context.Context, args ...any) (ret []T, err error)
- func (cfg Deleter[T]) Iter(ctx context.Context, rerr *error, args ...any) iter.Seq[T]
- func (cfg Deleter[T]) Returning(columns ...string) Deleter[T]
- func (cfg Deleter[T]) SQL() string
- func (cfg Deleter[T]) Where(clauses ...string) Deleter[T]
- type Inserter
- func (cfg Inserter[T]) Exec(ctx context.Context, args ...any) (int, error)
- func (cfg Inserter[T]) Exec1(ctx context.Context, args ...any) (result T, err error)
- func (cfg Inserter[T]) ExecN(ctx context.Context, args ...any) (ret []T, err error)
- func (cfg Inserter[T]) Iter(ctx context.Context, rerr *error, args ...any) iter.Seq[T]
- func (cfg Inserter[T]) OnConflict(clause string) Inserter[T]
- func (cfg Inserter[T]) Returning(values ...string) Inserter[T]
- func (cfg Inserter[T]) SQL() string
- func (cfg Inserter[T]) Values(placeholders ...string) Inserter[T]
- type JSONArg
- type Selector
- func (cfg Selector[T]) Exec1(ctx context.Context, args ...any) (ret T, err error)
- func (cfg Selector[T]) ExecN(ctx context.Context, args ...any) (ret []T, err error)
- func (cfg Selector[T]) GroupBy(columns ...string) Selector[T]
- func (cfg Selector[T]) Having(clauses ...string) Selector[T]
- func (cfg Selector[T]) Iter(ctx context.Context, rerr *error, args ...any) iter.Seq[T]
- func (cfg Selector[T]) Join(table string, on ...string) Selector[T]
- func (cfg Selector[T]) LeftJoin(table string, on ...string) Selector[T]
- func (cfg Selector[T]) Limit(limit int) Selector[T]
- func (cfg Selector[T]) Offset(offset int) Selector[T]
- func (cfg Selector[T]) OrderBy(columns ...string) Selector[T]
- func (cfg Selector[T]) SQL() string
- func (cfg Selector[T]) Where(clauses ...string) Selector[T]
- type Updater
- func (cfg Updater[T]) Exec(ctx context.Context, args ...any) (count int, err error)
- func (cfg Updater[T]) Exec1(ctx context.Context, args ...any) (ret T, err error)
- func (cfg Updater[T]) ExecN(ctx context.Context, args ...any) (ret []T, err error)
- func (cfg Updater[T]) Iter(ctx context.Context, rerr *error, args ...any) iter.Seq[T]
- func (cfg Updater[T]) Returning(columns ...string) Updater[T]
- func (cfg Updater[T]) SQL() string
- func (cfg Updater[T]) Set(column string, value any) Updater[T]
- func (cfg Updater[T]) SetExpr(column string, expr string, args ...any) Updater[T]
- func (cfg Updater[T]) Where(clauses ...string) Updater[T]
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func ColumnsOf ¶
ColumnsOf returns the column names a value of T expands into when scanned, inferred from struct tags (or snake_case'd field names when untagged). See scanner.ColumnsOf for the full rules; this is a re-export so callers can stay inside this package.
func From ¶
From returns the database connection from the current context. Returns nil if no connection is bound.
func Tx ¶
Tx runs fn inside a SAVEPOINT transaction on the connection in ctx. If fn returns a non-nil error or panics, the savepoint is rolled back; otherwise it is released. Calls to Tx may be nested — each creates a new savepoint.
func TxV ¶
TxV is the value-returning form of Tx. The function runs inside a SAVEPOINT; on error or panic the savepoint is rolled back and the zero value of T is returned alongside the error. Use this instead of closing over a local variable from outside the transaction:
story, err := db.TxV(ctx, func(ctx context.Context) (*Story, error) {
return medea.CreateStory(ctx, userID, spec)
})
Types ¶
type Declaration ¶
type Declaration[T any] struct { // contains filtered or unexported fields }
A Declaration describes how a Go type is related to a table and a set of columns.
func Declare ¶
func Declare[T any](table string, columns ...string) Declaration[T]
Declare declares that there is a table with the specified columns in the database and associates it with a type, making it easier to select and insert.
When no columns are passed, the column list is inferred from T's struct tags via ColumnsOf. Inference failures (e.g. T is not a struct) are stored on the Declaration and surfaced from any derived builder's Exec call.
func (Declaration[T]) Delete ¶
func (cfg Declaration[T]) Delete() Deleter[T]
Delete returns a Deleter with the table determined by the declaration. The declaration's columns are carried as the default RETURNING list, used when Deleter.Returning is called with no arguments.
func (Declaration[T]) Insert ¶
func (cfg Declaration[T]) Insert(insertions ...string) Inserter[T]
Insert returns an Inserter with the table and returned columns determined by the declaration.
Calling Insert with no insertions on a Declaration that also has no columns (typically `Declare[T](table).Insert()` where T's column list was deferred to inference) is an error: there's nothing to insert and nothing to RETURNING. The error is surfaced at Exec time.
func (Declaration[T]) Select ¶
func (cfg Declaration[T]) Select() Selector[T]
Select returns a selector with the table and columns determined by the declaration.
func (Declaration[T]) Update ¶
func (cfg Declaration[T]) Update() Updater[T]
Update returns an Updater with the table determined by the declaration. The declaration's columns are carried as the default RETURNING list, used when Updater.Returning is called with no arguments.
type Deleter ¶
type Deleter[T any] struct { // contains filtered or unexported fields }
A Deleter constructs a DELETE statement.
func DeleteFrom ¶
DeleteFrom returns a builder for a DELETE statement without a type parameter. This is a convenience for Delete[struct{}] when RETURNING is not needed.
func (Deleter[T]) Exec ¶
Exec executes the delete statement and returns the number of rows affected.
func (Deleter[T]) Returning ¶
Returning specifies the returned values from the delete.
When no columns are passed, the list defaults to the Declaration's columns (if this Deleter was created via Declaration.Delete); otherwise it is inferred from T's struct tags via ColumnsOf. Inference failure (e.g. T is not a struct) is stored on the Deleter and surfaced at Exec time.
type Inserter ¶
type Inserter[T any] struct { // contains filtered or unexported fields }
An Inserter constructs an INSERT statement.
func InsertInto ¶
InsertInto returns a builder for an INSERT statement without a type parameter. This is a convenience for Insert[struct{}] when RETURNING is not needed.
func (Inserter[T]) Exec ¶
Exec executes the insert statement and returns the number of rows inserted. If Returning() was called, use Exec1, ExecN, or Iter instead to capture results.
func (Inserter[T]) Exec1 ¶
Exec1 executes the insert statement and returns a single result. Returns io.EOF if there was no result.
func (Inserter[T]) OnConflict ¶
OnConflict adds an ON CONFLICT clause to the insert statement.
func (Inserter[T]) Returning ¶
Returning specifies the returned values from the insert. You must use this if you want to scan results unless the inserter was created from a Declaration, since the Declaration specifies this for you.
type JSONArg ¶
type JSONArg struct {
// contains filtered or unexported fields
}
JSONArg is the opaque wrapper returned by JSON. The field is unexported so callers route everything through JSON; bindStatement detects the type and marshals.
func JSON ¶
JSON wraps a value so bindStatement encodes it as JSON text. This is the bind-side companion to the `db:"col,json"` scan tag, and lets callers pass structs, maps, or slices to Exec / Exec1 / ExecN without pre-marshaling:
db.Exec(ctx, `INSERT INTO t(info) VALUES (?)`, db.JSON(myStruct))
json.RawMessage values are bound as TEXT directly without re-marshaling.
type Selector ¶
type Selector[T any] struct { // contains filtered or unexported fields }
A Selector associates a Go type with a specification for how to select information from the database.
func Select ¶
Select returns a Selector associated with a Go type, a SQL table and a list of column expressions.
When no columns are specified, the column list is inferred from T's struct tags via ColumnsOf. Inference failure (e.g. T is not a struct) is stored on the Selector and surfaced at Exec time. To force a literal SELECT *, pass "*" as the only column.
func (Selector[T]) Exec1 ¶
Exec1 executes the query and returns a single result, or an error if there were no results.
func (Selector[T]) Having ¶
Having adds a HAVING clause to the selector. Each clause is logically AND.
func (Selector[T]) Iter ¶
Iter returns a Go iterator. If an error is encountered, *rerr will be updated and the iterator will stop.
type Updater ¶
type Updater[T any] struct { // contains filtered or unexported fields }
An Updater constructs an UPDATE statement.
func (Updater[T]) Exec ¶
Exec executes the update statement and returns the number of rows affected. If Returning() was called, use Exec1, ExecN, or Iter instead to capture results.
func (Updater[T]) Returning ¶
Returning specifies the returned values from the update.
When no columns are passed, the list defaults to the Declaration's columns (if this Updater was created via Declaration.Update); otherwise it is inferred from T's struct tags via ColumnsOf. Inference failure (e.g. T is not a struct) is stored on the Updater and surfaced at Exec time.
func (Updater[T]) Set ¶
Set adds a SET clause to the update statement that binds value as a parameter (column = ?). Use SetExpr when the right-hand side needs to reference other columns or call SQL functions.
func (Updater[T]) SetExpr ¶
SetExpr adds a SET clause using a raw SQL expression for the right-hand side. Use this when the value depends on the row's current state or another column, e.g. SET value = value + 1. Any ? placeholders in expr consume args in order, before any WHERE-clause args:
db.Update[struct{}]("clock").
SetExpr("value", "value + ?", 1).
Where("id = ?").
Exec(ctx, 1)
Source Files
Directories
¶
| Path | Synopsis |
|---|---|
|
internal
|
|
|
scanner
package scanner implements scanning Go types from a Sqlite statement, using reflection to analyze Go structures and assignment for basic Go types.
|
package scanner implements scanning Go types from a Sqlite statement, using reflection to analyze Go structures and assignment for basic Go types. |