@dokebi/migrate 0.4.0

An optional plugin that runs migrations (table and index changes) and seeds for dokebi-db connections (D-0040). Node.js >=24.0.0, core ^0.4.0, @dokebi/db ^0.4.0. AI agents start with AI.md.

• It knows only history and locking per DB kind: PostgreSQL, MySQL, MariaDB, MongoDB. Migration bodies use the driver the app chose (pg, mysql2, mariadb, mongodb) as is. There is no query builder or ORM.
• The app never runs DDL at start. Call dokebi-migrate up at deployment. The plugin inside the app reports pending migrations (onPending: warn, default) or refuses to start (fail); migrations marked phase = 'post-deploy' (a backfill, a contract step) never stop it, so expand/contract deploys keep fail on.
• When several servers call up at once, a DB lock lets only one proceed. When the process holding the lock dies, the next run takes over.
• An applied file that was edited is noticed: up records each file's SHA-256 and refuses to run (CHECKSUM_MISMATCH) when an applied one no longer matches; --accept-changed records a harmless edit, onChanged: warn only warns (AI.md §2.3).

Install

npx dokebi add @dokebi/migrate --set connections='{"main":{"dialect":"postgres"}}'

npx dokebi is the @dokebi/cli installed in the project (dokebi init adds it to devDependencies). The npm package named dokebi is not this CLI, so never run npx -y dokebi. Without a local install use npx -y @dokebi/cli ….

It creates the folders src/infra/db/migrations/ and src/infra/db/seeds/, and installs @dokebi/db along when it is missing.

Configuration

dokebi-migrate:
  connections:              # dokebi-db connection name → history and lock style
    main: { dialect: postgres }      # postgres | mysql | mariadb | mongodb
    docs: { dialect: mongodb }
  directory: src/infra/db   # default. migrations/<connection>/, seeds/<connection>/
  onPending: warn           # warn (default) | fail | ignore; pending post-deploy files never stop the start
  onChanged: fail           # up with an applied file edited since it ran: fail (default) | warn
  lockTimeoutMs: 60000      # how long to wait for another run's lock
  lockTtlMs: 30000          # MongoDB lock expiry (a lock whose renewal stopped is taken over)
  statementTimeoutMs: 3600000  # each migration statement, on the migration session (D-0112)
  queryTimeoutMs: 3660000      # PostgreSQL client limit on that session (>= statementTimeoutMs)
  lockWaitTimeoutMs: 2000      # a migration statement's wait for a table or row lock
  lockRetries: 5               # a step that ran out of the lock wait runs again from its start
  lockRetryDelayMs: 1000       # first pause before a retry; doubles, with jitter

| Key | Type | Default | Description | |---|---|---|---| | connections.<name>.dialect | enum | — (required) | postgres, mysql, mariadb or mongodb | | directory | string | src/infra/db | Migration and seed root | | onPending | enum | warn | warn, fail or ignore (pending post-deploy files are logged at info only) | | lockTimeoutMs | integer ms | 60000 | Lock wait | | lockTtlMs | integer ms | 30000 | MongoDB lock expiry | | statementTimeoutMs | integer ms | 3600000 | Each migration and seed statement on the migration session, instead of the connection's request limit; over it is MIGRATION_TIMEOUT (AI.md §1.1) | | queryTimeoutMs | integer ms | 3660000 | PostgreSQL: the session client's query_timeout (at least statementTimeoutMs) | | lockWaitTimeoutMs | integer ms | 2000 | A migration statement's wait for a table or row lock (lock_timeout, lock_wait_timeout) | | lockRetries | integer | 5 | Retries of a step that ran out of lockWaitTimeoutMs, each from its start (AI.md §1.2) | | lockRetryDelayMs | integer ms | 1000 | Pause before the first retry; doubles per retry, with jitter |

Files

src/infra/db/migrations/<connection>/20261014120000_create_users.ts   # <UTC yyyymmddHHMMss>_<snake_case>
src/infra/db/seeds/<connection>/demo-users.ts

// migration: both up and down are required (refused before running otherwise)
import type { PoolClient as Db } from 'pg';
export async function up(db: Db): Promise<void> {
  await db.query('CREATE TABLE users (id serial PRIMARY KEY, email text NOT NULL UNIQUE)');
}
export async function down(db: Db): Promise<void> {
  await db.query('DROP TABLE users');
}

// seed
export async function run(db: Db): Promise<void> {
  await db.query("INSERT INTO users (email) VALUES ('[email protected]') ON CONFLICT DO NOTHING");
}

npx dokebi-migrate make create_users --connection main creates an empty file for the dialect (with the type import).

What db is:

| Dialect | db | Transactions | |---|---|---| | postgres | the run's pg client (PoolClient) | a transaction per file; a failure rolls back the whole file. A file that exports transaction = false runs without it (below) | | mysql | mysql2 PoolConnection | DDL commits at once. A failed file is not recorded, but statements that ran before it stay | | mariadb | mariadb PoolConnection | as mysql | | mongodb | the connection's default database (Db) | none |

Outside the transaction: export const transaction = false

CREATE INDEX CONCURRENTLY (and DROP INDEX CONCURRENTLY) cannot run inside a transaction block. Give such a statement its own file that exports transaction = false:

import type { PoolClient as Db } from 'pg';

export const transaction = false;

export async function up(db: Db): Promise<void> {
  await db.query('DROP INDEX CONCURRENTLY IF EXISTS users_email_idx'); // a leftover invalid index
  await db.query('CREATE INDEX CONCURRENTLY IF NOT EXISTS users_email_idx ON users (email)');
}
export async function down(db: Db): Promise<void> {
  await db.query('DROP INDEX CONCURRENTLY IF EXISTS users_email_idx');
}

On PostgreSQL the body then runs on the run's session without BEGIN/COMMIT, each statement committing on its own; the migration lock is held as for any file, and the history row is written only after the body succeeded. A failure leaves the file unrecorded, so the next up runs it again, while what already committed stays (a failed CREATE INDEX CONCURRENTLY leaves an INVALID index). Write such a file to be run again: IF NOT EXISTS / IF EXISTS, drop a leftover invalid index first, DROP INDEX CONCURRENTLY IF EXISTS in down, one statement per db.query call (pg runs a multi-statement string as one implicit transaction) and only this one change in the file. Only true (the default) and false are accepted; any other value is INVALID_FILE before anything runs. MySQL, MariaDB and MongoDB accept the flag and change nothing: their files never run in a transaction. status marks these files (no transaction) ("transaction": false with --json).

After the deploy: export const phase = 'post-deploy'

A change on a live table is expand → deploy → backfill → contract (D-0104). The backfill and the contract run once the new code is live, so they export phase = 'post-deploy' (the default is 'pre-deploy'; any other value is INVALID_FILE). The deployment runs npx dokebi-migrate up --phase pre-deploy (pending files in name order, stopping before the first pending post-deploy one), starts the new code, then runs npx dokebi-migrate up. At start the plugin loads only the pending files to read their phase: pending post-deploy files are logged at info (MIGRATIONS_POST_DEPLOY_PENDING) and never stop the start; a pending pre-deploy file still fails onPending: fail. A pre-deploy file named after a pending post-deploy file stays pending too (it is never jumped over), and the start says so: finish the previous deploy with up first. status marks these files (post-deploy) ("phase": "post-deploy" with --json). Example: examples/50-migration-phases.

Commands

npx dokebi-migrate up [--to <name>] [--phase pre-deploy] [--connection <name>]   # pending ones as one batch
npx dokebi-migrate down [--steps N | --to <name>]             # default: the last batch; N >= 1
npx dokebi-migrate status                                     # applied | pending | missing (recorded but no file); (no transaction), (post-deploy)
npx dokebi-migrate seed [--only <name>]...
npx dokebi-migrate make <snake_case> --connection <name>
npx dokebi-migrate schema [--connection <name>]                # the database shape, read only
# common: --config dokebi.yaml --entry src/infra/db/connections.ts --json

The executable opens only the connections it needs, from the app's connection definitions (--entry) and the dokebi-db settings in dokebi.yaml. Failure exits with code 1; with --json it prints { ok: false, error: { code, message, connection, file } }.

stdout holds only the result (one JSON document with --json) and logs go to stderr. The log level is the same as the app's: DOKEBI_LOG_LEVEL > dokebi.yaml options.logging.level > info (D-0047). For example, with DOKEBI_LOG_LEVEL=debug npx dokebi-migrate up --json > result.json the debug lines for locks, plans and steps go to the terminal (stderr) and the result to the file.

Error codes have no package prefix since 0.4.0 (MIGRATE_FAILED → MIGRATION_FAILED, MIGRATE_PENDING → MIGRATIONS_PENDING, others just drop MIGRATE_; D-0071). Errors are core DokebiErrors with a fix; the list is in AI.md.

The database shape (schema)

npx dokebi-migrate schema --json (and Migrator.schema(), MigrateApi.schema(connection)) reads what each database has, without writing or reading a row: tables with columns (type as the database prints it, nullable, default, identity or auto increment, generated), primary key, unique constraints, indexes (columns, unique, method, partial predicate), foreign keys (columns, referenced table and columns, on delete and on update) and checks, plus engine and collation on MySQL and MariaDB and comments where set. MongoDB gives collections with their indexes and $jsonSchema validator. The migration history (dokebi_migrations, MongoDB also dokebi_migrations_lock) is marked isMigrationHistory: true. The order is fixed, so the same database prints the same JSON. The URL, user and password are never printed. The result format is the JSON Schema schema.schema.json in this package; the fields are listed in AI.md §4.1. npx dokebi describe --with-db (@dokebi/cli) shows the same shape for every connection.

History and locking

| Dialect | History | Lock | |---|---|---| | postgres | the dokebi_migrations table of the current schema (with each file's checksum and token_checksum, D-0145) | pg_try_advisory_lock (a key derived from the schema name); released when the session ends | | mysql, mariadb | the dokebi_migrations table | GET_LOCK (a name derived from the DB name); released when the session ends | | mongodb | the dokebi_migrations collection | the dokebi_migrations_lock document; expires by server time ($$NOW) and is renewed while held, one renewal at a time, lockTtlMs/3 after the previous answer; no answer within the lease = lost |

History holds the name, the batch number, the applied time and the two checksums of the file (its bytes, and its tokens without formatting and comments: a file only a formatter changed is warned about as MIGRATE_REFORMATTED, not refused; AI.md §2.3). The lock is taken before the history table is created (to avoid concurrent creation conflicts).

If the MongoDB lock expires or is taken over during a run, the run stops with LOCK_LOST: ownership is renewed and checked on the server before each step and again before its result is recorded, so the interrupted step is not recorded and no further step runs. What the interrupted body already changed stays (no transactional DDL, no fencing of the body): keep MongoDB migrations safe to run again and shorter than lockTtlMs. The SQL locks belong to the session that runs the step and writes the history, so they cannot be lost without the step failing too.

The plugin inside the app

The generated registration code registers it like any other plugin. At start it reads the status of each configured connection (without a lock). When migrations are pending it logs the warning MIGRATIONS_PENDING (connection, pending, first), or with onPending: fail fails the start with MIGRATIONS_PENDING. Pending post-deploy files are read for their phase (only pending files are loaded) and logged at info as MIGRATIONS_POST_DEPLOY_PENDING; they never stop the start. When the status cannot be read it warns MIGRATIONS_UNKNOWN (fails with fail). The API is migrator(connection) (up, down, status, seed) and status().

Verification

npm test -w @dokebi/migrate: 19 unit tests (options, file rules, the transaction export (only booleans), the phase export and up --phase pre-deploy with a store double, the make command, the command's logs, cleanup and lost-lock logs, down steps validation, and the MongoDB heartbeat with store doubles: one renewal at a time, lost when a renewal gets no answer within the lease, stopped by release) and 16 tests against four real Docker DBs. Per DB: up, status, down (batch, steps, to), seed, a failed file not recorded (postgres rollback), each file applied once when two runs call up at once, a mongodb run whose lock expires (blocked event loop) or is taken over during a step stops with LOCK_LOST and records nothing, and takeover after SIGKILL of the lock-holding process (measured: postgres 19 ms, mysql 5 ms, mariadb 11 ms, mongodb 3.1 s = lockTtlMs 3 s). PostgreSQL transaction = false (D-0092): without the flag CREATE INDEX CONCURRENTLY is refused in the file's transaction and not recorded; with it a unique index whose build fails on a duplicate stays unrecorded (an invalid index left behind) and, once the data is fixed, the same file applies while a writer inserts every 5 ms behind an open write transaction (measured: build 1.55 s, 250 inserts meanwhile, slowest 6 ms; a plain CREATE INDEX in the same situation made an insert wait 1.03 s) and down drops it concurrently. On all four DBs the flag is accepted and shown by status, and a non-boolean value stops up before any file runs. The app plugin's warning and refused start, and the executable running up, status (with (no transaction)), seed and down on four connections at once. tests/schema.test.ts (5 tests on the same four Docker DBs): after migrations with a primary key, a composite unique constraint, indexes (MongoDB: TTL, sparse unique, partial), a foreign key with ON DELETE CASCADE, a check, defaults, nullable, identity / auto increment and a generated column, schema gives exactly the expected result twice, valid against schema.schema.json; the executable prints it for all four connections and as text, and never the URL or password (debug logs on, also when the login is refused). D-0104 (phases), on all four DBs: a start under onPending: fail refused while a pre-deploy file is pending and allowed (an info log) with only post-deploy ones pending, up --phase pre-deploy stopping before the first post-deploy file and never jumping over it, and the executable's --phase and status phases; unit tests for the phase values and a start check that never imports an applied file. Example 50 runs expand/contract on PostgreSQL with onPending: fail. The playground (example 25) creates its four DB tables with these migrations and starts with onPending: fail.