@samveys/database — Docs

Connections, a query builder, transactions, a schema builder, and migrations for Samveys (design §8 to).

Engine	Factory	Client library	Notes
SQLite (default)	`sqlite({ file })`	built-in `node:sqlite`	No server needed. `:memory:` for tests, WAL for files
MySQL (and MariaDB)	`mysql({ url })`	`mysql2` (install in the app)	One name for both servers (ADR 0012); conformance on MySQL 8.4 and MariaDB 11.4. No RETURNING, so inserts report `insertId`
PostgreSQL	`postgres({ url })`	`pg` (install in the app)	`lock_timeout` for migrations, `CONCURRENTLY` indexes

SQLite is the default engine (ADR 0011). It needs no server, no container and no client library. MySQL (also for MariaDB servers) and PostgreSQL are full alternatives. Each runs the same 48-case conformance suite on a real engine in CI. A driver is imported the first time its connection runs a query, so an app that only uses SQLite never loads pg or mysql2 (tested).

1. Configure connections

To pick the engine at deploy time, use databaseFromEnv(). It chooses in this order:

DB_CONNECTION: sqlite, mysql (MySQL or MariaDB) or postgres.
The scheme of DATABASE_URL (mysql://, postgres://), so a configured URL is never ignored.
SQLite, the default.

Server engines read DATABASE_URL or DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME and DB_PASSWORD. For SQLite, DB_DATABASE is the file, storage/app.db by default. An unknown engine fails with SVS_E070.

// config/database.ts — the engine follows the environment
import { databaseFromEnv, defineDatabaseConfig, mysql } from '@samveys/database'

export default defineDatabaseConfig({
  default: 'main',
  connections: {
    main: databaseFromEnv(), // SQLite unless DB_CONNECTION or DATABASE_URL says otherwise
    shop: mysql({ url: process.env.SHOP_DATABASE_URL! }), // a MySQL or MariaDB server
  },
})

# nothing set                                        → SQLite at storage/app.db (default)
DB_CONNECTION=sqlite   DB_DATABASE=storage/app.db
DB_CONNECTION=mysql    DATABASE_URL=mysql://app:secret@db:3306/app     # MySQL or MariaDB server
DATABASE_URL=postgres://app:secret@db:5432/app       # the scheme picks PostgreSQL

The tested examples below use in-memory SQLite, so they run without a database server:

import { defineDatabaseConfig, sqlite } from '@samveys/database'

export default defineDatabaseConfig({
  default: 'main',
  connections: {
    main: sqlite({ file: ':memory:' }),
    reports: sqlite({ file: ':memory:' }),
  },
})

Register the plugin in samveys.config.ts with database(config). It binds 'db' in the container. At shutdown the container closes every opened connection, after every plugin's shutdown() has run.

2. Query

import assert from 'node:assert/strict'
import { test } from 'node:test'
import { createDatabaseManager } from '@samveys/database'
import config from './config/database.ts'

test('query builder', async () => {
  const db = createDatabaseManager(config)
  await db.rawQuery('create table orders (id integer primary key, status text, total integer)')
  await db.from('orders').insert([
    { id: 1, status: 'paid', total: 120 },
    { id: 2, status: 'open', total: 80 },
    { id: 3, status: 'paid', total: 45 },
  ])

  const paid = await db.from('orders').select('id', 'total').where('status', 'paid').orderBy('total', 'desc').get()
  assert.deepEqual(paid.map((row) => row.id), [1, 3])

  const page = await db.from('orders').orderBy('id').paginate(1, 2)
  assert.equal(page.total, 3)

  // Keyset pagination: stable when rows are inserted between pages. End with a unique column.
  const first = await db.from('orders').orderBy('total').orderBy('id').cursorPaginate(2)
  const second = await db.from('orders').orderBy('total').orderBy('id').cursorPaginate(2, first.next)
  assert.deepEqual([...first.data, ...second.data].map((row) => row.id), [3, 2, 1])

  // The other connection is a separate database.
  await db.connection('reports').rawQuery('create table runs (id integer)')
  assert.equal(await db.connection('reports').from('runs').count(), 0)
  await db.close()
})

Identifiers are always quoted, and they must be plain names (users, users.id, *). Anything else fails with SVS_E075. Values are always bound, never interpolated. update() and delete() without a where() fail with SVS_E074.

3. Transactions

import assert from 'node:assert/strict'
import { test } from 'node:test'
import { createDatabaseManager } from '@samveys/database'
import config from './config/database.ts'

test('transactions', async () => {
  const db = createDatabaseManager(config)
  await db.rawQuery('create table ledger (id integer primary key, amount integer)')

  {
    await using trx = await db.transaction()
    await trx.from('ledger').insert({ id: 1, amount: 10 })
    // Leaving the scope without commit() rolls back.
  }
  assert.equal(await db.from('ledger').count(), 0)

  await db.transaction(async (trx) => {
    await trx.from('ledger').insert({ id: 2, amount: 20 })
    // A nested transaction is a savepoint; its failure rolls back only its own work.
    await trx.transaction(async (inner) => inner.from('ledger').insert({ id: 3, amount: 30 })).catch(() => undefined)
  })
  assert.equal(await db.from('ledger').count(), 2)
  await db.close()
})

When the callback throws, the transaction rolls back and the original error is rethrown. If the rollback also fails, its error is attached as cause. After commit() or rollback(), any further use fails with SVS_E076.

4. Migrations

import assert from 'node:assert/strict'
import { test } from 'node:test'
import { createDatabaseManager, createMigrator, defineMigration } from '@samveys/database'
import config from './config/database.ts'

const createPosts = defineMigration({
  up({ schema }) {
    schema.createTable('posts', (t) => {
      t.increments()
      t.string('title', 120)
      t.date('published_on').nullable()
      t.timestamps()
    })
  },
  down({ schema }) {
    schema.dropTable('posts')
  },
})

test('migrate and roll back', async () => {
  const db = createDatabaseManager(config)
  const migrator = createMigrator({ database: db, migrations: { '0001_create_posts': createPosts } })

  const dry = await migrator.migrate({ dryRun: true })
  assert.match(dry.sql[0]?.statements[0] ?? '', /^create table "posts"/)

  await migrator.migrate()
  await db.from('posts').insert({ title: 'Hello', published_on: Temporal.PlainDate.from('2026-11-04') })
  const post = await db.from('posts').firstOrFail()
  assert.ok(post.published_on instanceof Temporal.PlainDate)

  await migrator.rollback()
  assert.equal(await db.schema.hasTable('posts'), false)
  await db.close()
})

migrations can also be a directory path. Every .ts or .js file in it must default-export a defineMigration(), and the files run in name order. A batch is checked before anything runs:

Rule	Engines	Safe alternative
Index on an existing table without `concurrently`	PostgreSQL	`createIndex(table, cols, { concurrently: true })` in a migration with `transaction: false`
`drop column`	PostgreSQL, MySQL	Stop reading the column in one deploy, then drop it in a later one
`rename column`	PostgreSQL, MySQL	Add the new column, copy the data, switch reads, then drop the old one

A violation fails with SVS_E072 and nothing runs. Call safetyAssured(reason) inside the migration after reviewing it. On PostgreSQL every migration starts with SET lock_timeout = '5s'. On engines with transactional DDL (PostgreSQL, SQLite), a failed migration leaves nothing behind.

5. Dates and times

Column	PostgreSQL	MySQL	SQLite	Read as
`date()`	`date`	`date`	`date` (ISO text)	`Temporal.PlainDate`
`datetime()`	`timestamp(6)`	`datetime(6)`	`datetime` (ISO text)	`Temporal.PlainDateTime`
`timestamptz()`	`timestamptz(6)`	`timestamp(6)` (UTC)	`timestamptz` (ISO text)	`Temporal.Instant`

Microseconds survive the round trip on every engine (tested). MySQL TIMESTAMP ends at 2038-01-19. For later instants use datetime() and store UTC. Integers above 2^53 come back as bigint. JSON comes back as text, so call JSON.parse on it.

6. Conformance

@samveys/database/conformance runs 48 cases over connections, the query builder, pagination, transactions, types, constraints, migrations, streaming, and cancellation. The engines are real: SQLite in memory, mysql against MySQL 8.4 and MariaDB 11.4 servers, and PostgreSQL 17, in containers started with the docker CLI (ADR 0009). Without Docker the engine suites are skipped. With SVS_REQUIRE_DOCKER=1 they fail instead.

import { runDatabaseConformance } from '@samveys/database/conformance'
import { myDriverConfig } from './my-driver.ts'

runDatabaseConformance({ name: 'my-engine', connection: () => myDriverConfig({ url: process.env.DATABASE_URL! }) })

7. Error codes

Code	When
`SVS_E070`	A connection cannot be opened, or the name is not configured. Passwords are never shown
`SVS_E071`	The engine rejected a query. `meta.engineCode` holds the SQLSTATE or engine code, and bound values are not in the message
`SVS_E072`	Unsafe migration
`SVS_E073`	`firstOrFail()` found no row (HTTP 404)
`SVS_E074`	`update()` or `delete()` without `where()`
`SVS_E075`	Invalid identifier, operator, pagination value, or cursor
`SVS_E076`	Transaction used after commit or rollback

Commands

The plugin registers the database commands of. Point it at your files with the second argument of database(); the defaults are database/migrations and database/seeders, relative to the app root.

svs migrate                      # run pending migrations as one batch
svs migrate --dry-run            # print the SQL per migration, change nothing
svs migrate:rollback --steps 2   # revert the last two batches (default 1)
svs migrate:status               # name, batch and run time of every migration
svs db:seed --class 01_users     # run one seeder (default: all, in name order)
svs db:wipe                      # drop every table, after a confirmation

--connection <name> picks a connection; migrations run on writers only.
In production (APP_ENV=production) every command that writes needs --force. --dry-run is allowed without it.
db:wipe always asks for confirmation unless --force. It also drops samveys_migrations.
--json prints one result document. Errors (SVS_E070..SVS_E076) print their message and exit with code 1.

#1. Configure connections

#2. Query

#3. Transactions

#4. Migrations

#5. Dates and times

#6. Conformance

#7. Error codes

#Commands