Migrations

SQL migrations

boxdb migrate new create_accounts
# Edit the SQL file printed by new, then:
boxdb migrate up data.db
boxdb migrate status data.db
boxdb migrate down data.db

The default directory is db/migrations; use --dir to choose another one. new creates a template with a unique, ordered 14-digit UTC version and the provided name. For example, its contents can be:

-- migrate:up
CREATE TABLE accounts (
    id INT64 PRIMARY KEY,
    name TEXT NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at INSTANT NOT NULL
);

-- migrate:down
DROP TABLE accounts;

up creates the database if needed and applies pending files in version order. Use --format readable to create YAML storage; existing files are detected automatically. Each version commits its SQL, logical type metadata and tracking record together. A failed version rolls back, while previous versions stay applied. Binary writers use SQLite locking and its five-second busy timeout; readable files retain their single-writer lock and report contention. After a readable publication failure, reopen the database before retrying.

down rolls back exactly one version. An empty history is a successful no-op. An empty down section marks a migration as irreversible: attempting its rollback fails without changing the database. Up sections must contain SQL; use SELECT 1; for an intentional no-op. Both section markers are required.

Do not edit, rename or remove applied files. BoxDB checks their names and SHA-256 checksums, with CRLF line endings normalized to LF. Duplicate versions and new versions preceding an applied version are rejected before pending SQL runs. Transaction control, PRAGMA, VACUUM, ATTACH and DETACH are not allowed in either section; BoxDB owns the connection and transaction. DBMate directive options such as transaction:false are unsupported.

For scripts and CI:

boxdb migrate status data.db --json --check
boxdb migrate up data.db --json
boxdb migrate down data.db --json

status inspects committed state without modifying the file or taking a readable writer lock. Status and down require an existing database. --check returns exit status 1 when migrations are pending. Successful operations return 0; operation errors return 1, and invalid CLI arguments return 2. JSON output is an array: status entries have version, name and state (applied or pending); up/down entries contain the versions and names changed by that invocation. Diagnostics go to stderr. See boxdb migrate --help and each command’s help.

Migrations from application code

All clients use the same Rust migration runner and __boxdb_migrations history as the CLI. Keep the complete set, including applied versions, so every entry can be verified. A set is immutable after construction and can be reused with different databases. Files and embedded documents have identical checksum rules.

TypeScript

import { BoxDB, MigrationSet } from "@boxdb/client";

await using db = await BoxDB.open("data.db");
const migrations = await MigrationSet.read("db/migrations");
const applied = await migrations.up(db); // [{ version, name }]
const statuses = await migrations.status(db); // [{ version, name, state }]
const reverted = await migrations.down(db); // { version, name } or null

For bundled SQL, use MigrationSet.fromFiles([{ filename, sql }]); filename is a bare VERSION_NAME.sql name and sql is the full document with both section markers. Construction validates all documents before returning. Directory reads are asynchronous and ignore entries without a .sql extension.

Execution methods accept { signal } and require a root BoxDB, rather than a transaction handle. Each call occupies one queue operation and waits for any active transaction; finish the transaction before awaiting root migration work. SQL and lock waits run on the native worker. Ordinary requests on the same connection wait until the migration call, including rollback cleanup, finishes. The usual queue limits and draining/aborting close behavior apply.

Go

migrations, err := boxdb.ReadMigrations(os.DirFS("."), "db/migrations")
if err != nil { return err }
applied, err := migrations.Up(ctx, db)
if err != nil { return err }
_ = applied // []boxdb.MigrationInfo

ReadMigrations accepts any fs.FS, including embed.FS. NewMigrationSet([]boxdb.MigrationFile{{Filename: "20261015000000_create_accounts.sql", SQL: sql}}) validates and copies documents from memory. Status(ctx, db) returns []MigrationStatus; Down(ctx, db) returns *MigrationInfo, or nil for an empty history. Methods accept a BoxDB *sql.DB and pin one connection for the complete call. Contexts cancel waiting for the pool, writer locks and SQL execution.

Rust

use boxdb::migrations::{MigrationFile, MigrationSet};

let migrations = MigrationSet::from_files([MigrationFile {
    filename: "20261015000000_create_accounts.sql".into(),
    sql: include_str!("../db/migrations/20261015000000_create_accounts.sql").into(),
}])?;
let applied = migrations.up(&mut db)?;

MigrationSet::read(directory) loads files instead. Call up, down or status against an idle Database; status_file inspects a file without opening a writer.

An error or cancellation rolls back the active version; earlier committed versions remain applied. Inspect status and retry up to continue. Cancellation cannot undo a completed commit. Cleanup does not use the cancelled signal or context. Errors retain BoxDB codes and engine details, with migration version and direction added to execution diagnostics. Reopen after a readable publication failure, as with other writes. Drizzle’s migration helper retains its own format and journal; these methods operate on BoxDB SQL migrations.

Schema-only export

boxdb schema dump data.db > db/schema.sql
boxdb schema dump data.db --format=sql > db/schema.sql
boxdb schema dump data.db --format=yaml > db/schema.yaml

SQL is the default. Both formats retain user tables, indexes, views, triggers, constraints and logical types. Internal tables, application rows and AUTOINCREMENT sequence values are excluded. Dumping reads a consistent committed snapshot without modifying the source; virtual tables are unsupported.

SQL contains logical BoxDB declarations without metadata INSERT statements. Restore it through BoxDB into a new database:

boxdb restored.db < db/schema.sql
boxdb restored.db -e 'SELECT * FROM accounts'

SQL dumps omit migration history, so restored databases report the original migrations as pending. YAML retains applied migration records in its migrations section.

YAML exports a single boxdb-schema document with logical BoxDB declarations:

format: boxdb-schema
version: 1
tables:
  accounts:
    columns:
      id: INTEGER PRIMARY KEY
      email: TEXT
      nickname?: TEXT
      active: BOOLEAN DEFAULT TRUE
      status: ENUM('pending', 'active', 'disabled') DEFAULT 'pending'
      created_at: INSTANT
    constraints:
      - check: length(email) > 0
indexes:
  accounts_active_email:
    type: unique
    table: accounts
    expressions:
      - text_lower(email)
    where: active = TRUE

All tables are STRICT; YAML schema export rejects non-strict tables. Column mapping order preserves SQL column order. Objects are sorted by name, and empty optional sections are omitted. A column key ending in ? allows NULL; an unmarked key is required, including primary-key columns. Real table column names cannot end in ?. Ordinary SQL NULL and NOT NULL clauses are omitted; named constraints and null constraints with ON CONFLICT remain explicit to preserve their additional semantics. Defaults, checks and references retain their SQL expressions and use the real column names. Read-only columns end with READONLY, for example created_at: INSTANT READONLY. Index type is regular or unique; expressions can include COLLATE, ASC and DESC. Views contain a SQL query; triggers contain table, timing, event, optional update_of/when, and a SQL body without its enclosing BEGIN/END.

Nonzero application_id and user_version appear in pragmas. Applied migrations are an ordered list of records with version, name and checksum. Internal tables, generated type checks and physical codec metadata are omitted. See the complete example for logical types, generated columns, views, triggers and migration records.

YAML schema dumps are export-only documents for inspection and version-control diffs. They cannot be opened as databases or imported through boxdb convert. The readable database stream is a separate format containing schemas and rows; boxdb convert converts complete databases between binary and readable storage. SQL dumps can bootstrap an empty database. The library exposes Database::dump_schema and Database::dump_schema_file with SchemaFormat.

Why stock DBMate is insufficient

DBMate’s SQLite driver executes SQL through ordinary SQLite, bypassing BoxDB’s type translation and metadata updates. Its dump uses SQLite’s .schema command, which omits the metadata rows needed to recover logical types. BoxDB reuses its SQL section markers, but executes migrations through its own typed connection. The shell’s existing .schema command remains physical schema inspection.