Database

Support matrix#

Drivers are separate Go modules, so importing the core never downloads a heavy driver.

Connecting#

app.DB() opens the default connection on first use from DB_* variables, registers a db health check (/up?checks=1) and closes it on shutdown:

db, err := app.DB() // *database.DB
if err != nil {
	return err
}
r.Resource("posts", controllers.NewPostController(db))

SQLite URLs: file:storage/app.db, sqlite:storage/app.db or :memory:. Other schemes need their driver, added with vukem.WithDatabaseDrivers(...).

Outside an app, database.Open(ctx, url, database.WithConfig(cfg)) returns the same *database.DB; database.FromSQL(conn, dialect) wraps an existing *sql.DB.

SQLite defaults#

Every SQLite connection gets WAL journaling, busy_timeout, foreign_keys=ON and BEGIN IMMEDIATE for write transactions. Writes and transactions go to a single-connection writer pool, reads to a read-only reader pool, so concurrent requests do not fail with SQLITE_BUSY.

Apps that never open a SQLite database can leave the driver out with the vukem_nosqlite build tag (go build -tags vukem_nosqlite, or vukem_nosqlite in the project's .buildtags, which vukem new --minimal writes). database.Open then refuses SQLite URLs with an error naming the tag. Tests (vukemtest) keep using in-memory SQLite, so run them without the tag.

Raw SQL#

*database.DB and *database.Tx implement database.Executor:

var n int64
err := db.QueryRowContext(ctx, "SELECT COUNT(*) FROM posts WHERE published = ?", true).Scan(&n)

Always pass values as arguments; never concatenate input into SQL. db.Dialect() gives Quote and Placeholder for portable statements, and db.SQL() exposes the underlying *sql.DB.

PostgreSQL uses $1, $2 placeholders where SQLite and MySQL use ?. Write raw SQL once with ? and rebind it for the connection's dialect:

q := database.Rebind(db.Dialect(), "SELECT id FROM posts WHERE author_id = ? AND status = ?")
rows, err := db.QueryContext(ctx, q, authorID, "published") // $1, $2 on PostgreSQL

Rebind leaves ? inside string literals, quoted identifiers, dollar-quoted bodies and comments alone. On PostgreSQL ?? is a literal ?, for jsonb operators (data ?? 'key'). ExecContext and QueryContext never rebind on their own, so queries already written with $n keep working. Write one style per query: a $n mixed with ? is not renumbered and would bind the wrong value. database.RebindChecked(d, query) is the fail-closed variant: it returns ErrMixedPlaceholders for that mix and ErrUnterminatedSQL when the query ends inside a literal, quoted identifier, comment (a trailing -- comment needs its newline) or dollar-quoted body. Use it for queries assembled at runtime, where an unbalanced quote would swallow the SQL appended after it; Rebind is enough for constant queries. The errors quote at most the first 80 bytes of the query; what of it to log is the caller's call. The lexer assumes the servers' default settings: standard_conforming_strings on (PostgreSQL), no ANSI_QUOTES or NO_BACKSLASH_ESCAPES (MySQL). MySQL executes the contents of /*! */ and /*+ */ comments, but they are skipped like any comment, so placeholders inside them are not supported.

Query observers#

A database.QueryObserver sees every statement run through a DB and its transactions (SQL, duration, error; Args hold user data, so never log them). Add one to app.DB() with an option; it runs next to devtools instead of replacing it:

app, err := vukem.New(vukem.WithQueryObservers(sqlTracer))

On a DB you open yourself, pass database.WithQueryObserver once per observer or call db.AddQueryObserver before sharing the DB. db.SetQueryObserver replaces every observer. A panicking observer is logged and skipped, so it never fails the query or stops the other observers.

Transactions#

err := db.Tx(ctx, func(tx *database.Tx) error {
	if err := orm.Create(ctx, tx, &order); err != nil {
		return err
	}

	return orm.Create(ctx, tx, &item)
}) // commit on nil, rollback on error or panic (the panic is re-raised)

database.RunInTx(ctx, ex, fn) joins the caller's transaction when ex is already a *Tx, or starts one when it is a *DB, which keeps service functions composable.

tx.AfterCommit(func(ctx context.Context) { ... }) runs work once the transaction commits, in registration order, and drops it on rollback or panic. A hook registered after the db.Tx function returned never runs; a panicking hook is logged and the others still run (db.Tx returns nil, since the commit happened). queue.DispatchTx uses it on drivers without transactional storage.

UUIDs#

Package uuid (stdlib only) makes RFC 9562 UUIDs for public ids, without a third-party dependency:

type Order struct {
	orm.Model
	PublicID uuid.UUID // t.UUID("public_id").Unique() in the migration
}

o := Order{PublicID: uuid.NewV7()}      // time-ordered; uuid.NewV4() is fully random
u, err := uuid.Parse(r.PathValue("id")) // errors.Is(err, uuid.ErrInvalid) → 404

Both versions draw their random bits from crypto/rand. A v7 UUID sorts by its creation millisecond (random order inside one) and reveals that time (u.Time()). Parse accepts only the canonical 36-character form, in either case. A uuid.UUID is JSON and query-string text, is written to SQL as text (PostgreSQL uuid, text columns elsewhere) and scans text or 16-byte binary columns; scan nullable columns into a *uuid.UUID.

Next#

• ORM: typed queries and models.
• Migrations: schema builder and vukem migrate.

PostgreSQL#

go get github.com/AlexGalhardo/vukem/drivers/postgres

vukem.New(append([]vukem.Option{
	vukem.WithDatabaseDrivers(postgres.Driver()), // postgres:// and postgresql:// URLs
	// ...
}, opts...)...)

DATABASE_URL=postgres://app:secret@localhost:5432/app?sslmode=disable

Models, queries, migrations, relations, the database cache store and SESSION_DRIVER=database work unchanged on SQLite, PostgreSQL and MySQL.

MySQL and MariaDB#

go get github.com/AlexGalhardo/vukem/drivers/mysql

vukem.New(append([]vukem.Option{
	vukem.WithDatabaseDrivers(mysql.Driver()), // mysql:// and mariadb:// URLs
	// ...
}, opts...)...)

DATABASE_URL=mysql://app:secret@localhost:3306/app

The URL becomes a go-sql-driver DSN with parseTime, UTC times, utf8mb4 and a 10s dial timeout; query parameters override them. Hosts other than localhost require TLS with a verified certificate unless the URL sets tls= (for example tls=preferred). Default MySQL collations compare text case-insensitively, so Where and unique indexes on strings ignore case.

Conformance suite#

database/dbtest.Run(t, db) is the suite every dialect passes: migrations, schema changes, ORM round trips (JSON, nullable, booleans, times), unique violations as 409, foreign keys, soft deletes, cursor pagination, relations and transactions (including rollback on panic). drivers/postgres runs it against postgres:17 and drivers/mysql against mysql:8.4 and mariadb:11.8 with go test -tags=integration. Each top-level subtest gets a context cancelled after dbtest.SubtestTimeout (2 minutes), so a stuck lock or a slow server fails that subtest instead of hanging the run.

Transactions and SQLite#

Inside db.Tx, pass tx to every query. SQLite has a single writer connection, so a write through db while the transaction holds it waits forever. database.RunInTx(ctx, ex, fn) joins the caller's transaction when ex is already a *database.Tx.