Documentation
¶
Overview ¶
Package database manages SQL connections for Vukem: dialects, SQLite defaults (WAL, busy timeout, foreign keys, a single-connection writer pool beside a reader pool), PostgreSQL and MySQL through driver modules, and transactions that roll back on error or panic.
Open turns a DATABASE_URL into a *DB; DB.Tx and RunInTx run a function in a *Tx. Both satisfy Executor, the interface that the orm and schema packages accept, so the same code runs inside or outside a transaction. A Manager holds named connections. Apps get the default connection, opened from configuration, with app.DB().
See https://github.com/AlexGalhardo/vukem/blob/main/docs/database.md.
Index ¶
- Variables
- func Rebind(d Dialect, query string) string
- func RebindChecked(d Dialect, query string) (string, error)
- func RunInTx(ctx context.Context, ex Executor, fn func(tx *Tx) error) error
- func ScanTime[D TimeDest](dest D) sql.Scanner
- type Config
- type DB
- func (db *DB) AddQueryObserver(obs QueryObserver)
- func (db *DB) Close() error
- func (db *DB) Dialect() Dialect
- func (db *DB) Encrypter() Encrypter
- func (db *DB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (db *DB) Ping(ctx context.Context) error
- func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
- func (db *DB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
- func (db *DB) SQL() *sql.DB
- func (db *DB) SetQueryObserver(obs QueryObserver)
- func (db *DB) Tx(ctx context.Context, fn func(tx *Tx) error) (err error)
- type Dialect
- type Driver
- type Encrypter
- type Executor
- type Manager
- type Option
- type Query
- type QueryObserver
- type TimeDest
- type Tx
- func (tx *Tx) AfterCommit(fn func(ctx context.Context))
- func (tx *Tx) Dialect() Dialect
- func (tx *Tx) Encrypter() Encrypter
- func (tx *Tx) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (tx *Tx) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
- func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
- func (tx *Tx) SQL() *sql.Tx
Examples ¶
Constants ¶
This section is empty.
Variables ¶
var ErrMixedPlaceholders = errors.New("database: query mixes ? placeholders with $n parameters")
ErrMixedPlaceholders is returned by RebindChecked when a PostgreSQL query mixes "?" placeholders with explicit "$n" parameters: renumbering the "?" would bind the wrong values.
var ErrUnterminatedSQL = errors.New("database: query ends inside an unterminated literal, identifier or comment")
ErrUnterminatedSQL is returned by RebindChecked when the query ends inside a string literal, a quoted identifier, a comment (a trailing "--" comment needs its newline) or a dollar-quoted body.
Functions ¶
func Rebind ¶ added in v0.25.0
Rebind rewrites the "?" placeholders of a hand-written query to d's style, so raw SQL shared between SQLite, MySQL and PostgreSQL is written once with "?": on PostgreSQL the n-th "?" becomes "$n", on the other dialects the query is returned unchanged.
"?" inside string literals ('…', E'…'), quoted identifiers ("…"), dollar-quoted bodies ($$…$$, $tag$…$tag$) and comments (-- and nested /* */) is left alone. On PostgreSQL "??" is a literal "?", for the jsonb operators (data ?? 'key' runs as data ? 'key'). Rebind only renumbers placeholders: values still go through args, never into the query text.
Rebind does not report malformed input: an unterminated literal is returned as scanned and "$n" mixed with "?" is renumbered anyway. Use RebindChecked for queries assembled at runtime or from fragments.
func RebindChecked ¶ added in v0.26.0
RebindChecked is Rebind that fails closed: it returns ErrUnterminatedSQL when the query (in any dialect) ends inside a literal, quoted identifier, comment or dollar-quoted body, and ErrMixedPlaceholders when a PostgreSQL query has both "?" placeholders and "$n" parameters. A query with only "$n" parameters is returned unchanged.
Prefer it over Rebind whenever the query is built from pieces (string concatenation, optional clauses), where an unbalanced quote would swallow the SQL appended after it; Rebind stays for constant queries.
The error quotes at most the first 80 bytes of the query, so a literal holding a secret is not copied whole into it; the caller decides what of the query, if anything, to log.
func RunInTx ¶
RunInTx runs fn inside ex's transaction when ex is already a *Tx, or in a new transaction when ex is a *DB.
func ScanTime ¶ added in v0.27.0
ScanTime wraps a time destination so Scan also accepts a timestamp stored as text. SQLite reports no column type for MIN, MAX, COALESCE or a subquery over a timestamp column, so its driver returns the stored text and database/sql cannot assign it to a time.Time:
var oldest sql.NullTime err := db.QueryRowContext(ctx, "SELECT MIN(created_at) FROM orders"). Scan(database.ScanTime(&oldest))
For an orm.NullTime field pass its embedded sql.Null:
var closed orm.NullTime err := row.Scan(database.ScanTime(&closed.Null))
A time.Time value (PostgreSQL, MySQL with parseTime, plain SQLite columns) is stored as is; NULL clears a pointer or a nullable destination and is an error for a plain time.Time, as in database/sql. Text with an offset comes back in a fixed zone of that offset and text without one in UTC, so compare with time.Time.Equal, not ==. Time-only text ("15:04:05"), unix timestamps stored as integers and any other text are errors.
Types ¶
type Config ¶
type Config struct {
MaxOpen int
MaxIdle int
MaxLifetime time.Duration
MaxIdleTime time.Duration
Log *slog.Logger
}
Config tunes connection pools.
type DB ¶
type DB struct {
// contains filtered or unexported fields
}
DB is a connection with its dialect. For SQLite, writes and transactions go to a single-connection writer pool and reads to a reader pool, which avoids SQLITE_BUSY under concurrency.
func Open ¶
Open connects to url:
- SQLite: "file:storage/app.db", "sqlite:storage/app.db", ":memory:";
- other schemes need their Driver (e.g. postgres.Driver()).
Example ¶
package main
import (
"context"
"fmt"
"github.com/AlexGalhardo/vukem/database"
)
func main() {
ctx := context.Background()
// Apps call app.DB(), which reads DATABASE_URL; ":memory:" is SQLite.
db, err := database.Open(ctx, ":memory:")
if err != nil {
fmt.Println(err)
return
}
defer db.Close()
_, _ = db.ExecContext(ctx, "CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)")
_, _ = db.ExecContext(ctx, "INSERT INTO notes (body) VALUES (?)", "hello")
var body string
_ = db.QueryRowContext(ctx, "SELECT body FROM notes").Scan(&body)
fmt.Println(db.Dialect().Name(), body)
}
Output: sqlite hello
func (*DB) AddQueryObserver ¶ added in v0.25.0
func (db *DB) AddQueryObserver(obs QueryObserver)
AddQueryObserver adds obs after the DB's current observers (devtools installed by App.DB keeps working next to a tracing observer). Call it before the DB is shared between goroutines.
func (*DB) Close ¶
Close closes the pools. Always close databases before deleting their files (required on Windows).
func (*DB) Encrypter ¶ added in v0.18.0
Encrypter returns the encrypter set with WithEncrypter, or nil.
func (*DB) ExecContext ¶
ExecContext runs a statement on the writer pool.
func (*DB) QueryContext ¶
QueryContext runs a query on the reader pool.
func (*DB) QueryRowContext ¶
QueryRowContext runs a single-row query on the reader pool, except for statements with RETURNING, which write and go to the writer pool.
func (*DB) SetQueryObserver ¶ added in v0.19.0
func (db *DB) SetQueryObserver(obs QueryObserver)
SetQueryObserver replaces every observer of a DB built with FromSQL or Open with obs (nil, or a typed nil pointer, removes them). Call it before the DB is shared between goroutines; AddQueryObserver keeps the existing ones.
func (*DB) Tx ¶
Tx runs fn in a transaction on the writer pool: it commits when fn returns nil and rolls back on error or panic (the panic is re-raised). SQLite write transactions use BEGIN IMMEDIATE.
Example ¶
package main
import (
"context"
"errors"
"fmt"
"github.com/AlexGalhardo/vukem/database"
)
func main() {
ctx := context.Background()
db, _ := database.Open(ctx, ":memory:")
defer db.Close()
_, _ = db.ExecContext(ctx, "CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)")
_, _ = db.ExecContext(ctx, "INSERT INTO accounts (balance) VALUES (100)")
// Returning an error rolls the transaction back.
err := db.Tx(ctx, func(tx *database.Tx) error {
if _, err := tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - 150"); err != nil {
return err
}
return errors.New("insufficient funds")
})
var balance int
_ = db.QueryRowContext(ctx, "SELECT balance FROM accounts").Scan(&balance)
fmt.Println(err, balance)
}
Output: insufficient funds 100
type Dialect ¶
type Dialect interface {
// Name is "sqlite", "postgres" or "mysql".
Name() string
// Placeholder returns the n-th (1-based) bind placeholder.
Placeholder(n int) string
// Quote quotes an identifier (already validated by callers).
Quote(ident string) string
}
Dialect describes SQL differences between databases.
type Driver ¶
type Driver struct {
// SQLName is the database/sql driver name ("pgx").
SQLName string
// Dialect is the SQL dialect.
Dialect Dialect
// Schemes are the URL schemes the driver accepts ("postgres").
Schemes []string
// DSN, when set, turns the URL into the database/sql driver's data
// source name (MySQL's driver does not take URLs). Its errors must not
// echo the URL, which may hold a password.
DSN func(rawURL string) (string, error)
}
Driver connects a dialect to a database/sql driver. PostgreSQL's comes from the drivers/postgres module: database.Open(ctx, url, postgres.Driver()).
type Encrypter ¶ added in v0.18.0
type Encrypter interface {
Seal(plaintext, ad []byte) string
Open(token string, ad []byte) ([]byte, error)
}
Encrypter seals and opens column values for orm.Encrypted; *crypt.Encrypter implements it. ad (additional data) binds a value to its table and column.
type Executor ¶
type Executor interface {
ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
Dialect() Dialect
}
Executor runs SQL; *DB and *Tx implement it, so ORM and schema code works inside and outside transactions.
type Manager ¶
type Manager struct {
// contains filtered or unexported fields
}
Manager holds named connections. The default connection is "default".
func NewManager ¶
NewManager returns a Manager with db as the default connection.
type Option ¶
type Option func(*openOptions)
Option configures Open.
func WithDriver ¶
WithDriver registers a non-SQLite driver for this Open call.
func WithEncrypter ¶ added in v0.18.0
WithEncrypter makes orm.Encrypted columns readable and writable through this connection (apps pass app.Crypt). Without it, writing or reading an Encrypted column fails.
func WithQueryObserver ¶ added in v0.19.0
func WithQueryObserver(obs QueryObserver) Option
WithQueryObserver reports every statement to obs (development tooling such as devtools, tracing). It can be passed several times: every observer is notified, in the order given. Without one the hot path costs one nil check.
type Query ¶ added in v0.19.0
type Query struct {
// SQL is the statement text as passed by the caller.
SQL string
// Args are the bound values; observers must not log or display them
// (they hold user data and secrets).
Args []any
// Duration is the time until the driver returned (for queries: until
// the rows were ready, not until they were read).
Duration time.Duration
// Err is the statement error, if any.
Err error
}
Query is one statement run through a DB or Tx, reported to a QueryObserver after it returns.
type QueryObserver ¶ added in v0.19.0
QueryObserver is notified of every statement run through a DB opened with WithQueryObserver (and its transactions). It runs on the caller's goroutine, so it must be fast and safe for concurrent use. Statements run directly on DB.SQL() bypass it.
type Tx ¶
type Tx struct {
// contains filtered or unexported fields
}
Tx is a transaction; it implements Executor.
func (*Tx) AfterCommit ¶ added in v0.24.0
AfterCommit runs fn once the transaction commits, after DB.Tx's fn returned (hooks run in registration order, with DB.Tx's context). A rollback or panic discards the hooks; a hook registered after DB.Tx's fn returned never runs. A panicking hook is logged and the remaining hooks still run (the commit already happened, so DB.Tx returns nil). It panics on a Tx not started by DB.Tx (a programmer error). Queues use it to enqueue jobs that must not exist for rolled-back work (queue.DispatchTx on drivers without transactional storage).
func (*Tx) Encrypter ¶ added in v0.18.0
Encrypter returns the encrypter of the DB that started the transaction.
func (*Tx) ExecContext ¶
ExecContext implements Executor.
func (*Tx) QueryContext ¶
QueryContext implements Executor.
func (*Tx) QueryRowContext ¶
QueryRowContext implements Executor.
Source Files
¶
Directories
¶
| Path | Synopsis |
|---|---|
|
Package dbtest is the database conformance suite: the same schema, ORM, transaction and migration checks run against every dialect (SQLite in core, Postgres and others in their driver modules).
|
Package dbtest is the database conformance suite: the same schema, ORM, transaction and migration checks run against every dialect (SQLite in core, Postgres and others in their driver modules). |