@arraypress/db-migrate

Race-safe one-shot schema bootstrap for Kysely on D1, SQLite, libSQL, and Postgres.

Designed for the "ship a single canonical schema, no per-step migrations" pattern that's right for early-stage apps and Cloudflare Worker deployments where multiple isolates can cold-start simultaneously.

If you're past 1.0 and need ordered, reversible migrations, use Kysely's official Migrator instead — this library is for the simpler case where you just want "install the schema if it isn't already, exactly once, even under cold-start race conditions."

Why this exists

On Cloudflare Workers (and any serverless runtime), two isolates can cold-start simultaneously and both pass an "is the schema installed?" check before either writes the marker. Without a claim-before-DDL pattern, both then run CREATE TABLE and partial-fail on duplicate-table errors — leaving you with half a schema and no clear recovery path.

The pattern this library encodes:

1. Create the tracking table (CREATE IF NOT EXISTS is itself safe).
2. Check if the version is already installed — fast path.
3. If not, claim the marker first via INSERT OR IGNORE (SQLite) / ON CONFLICT DO NOTHING (Postgres). Only one isolate's row insert succeeds.
4. The winner runs the DDL. The loser sees numAffectedRows === 0 and bails out — its next request will see the marker on the fast path.
5. If the DDL throws, roll the marker back so the next cold start retries.

It's ~80 LOC, but bugs here are catastrophic (orphan partial schemas, bricked deployments). One library + tested implementation > per-app rolls.

Install

npm install @arraypress/db-migrate kysely

Peer dependency: kysely ^0.27.0 || ^0.28.0.

Quick start

SQLite / D1 / libSQL

import { bootstrapSchema } from '@arraypress/db-migrate';
import { sql } from 'kysely';

await bootstrapSchema(db, {
  marker: 'myapp_migrations',
  version: '0001_initial_schema',
  ddl: async (db) => {
    await db.schema.createTable('users')
      .ifNotExists()
      .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())
      .addColumn('email', 'text', (col) => col.unique().notNull())
      .execute();

    await db.schema.createTable('sessions')
      .ifNotExists()
      .addColumn('id', 'text', (col) => col.primaryKey())
      .addColumn('user_id', 'integer', (col) => col.references('users.id'))
      .execute();
  },
  logger: console, // optional — emits "Installing schema..." messages
});

Postgres

await bootstrapSchema(db, {
  marker: 'myapp_migrations',
  version: '0001_initial_schema',
  dialect: 'postgres',
  ddl: schema.up,
});

Per-domain composed schemas

The DDL callback is just a function — compose however you like:

// schema/0001_initial.ts
import { createUsers } from './users.ts';
import { createOrders } from './orders.ts';

export async function up(db) {
  await createUsers(db);
  await createOrders(db);
}

// app entry:
await bootstrapSchema(db, {
  marker: 'myapp_migrations',
  version: '0001_initial_schema',
  ddl: schema.up,
});

Behaviour

Fast path (schema already installed)

Single indexed lookup. Returns { installed: false, wonClaim: false, version } and exits. Safe to call on every Worker invocation — that's the design.

Cold path (first install)

1. Tracking table created via CREATE TABLE IF NOT EXISTS (idempotent).
2. Marker claimed via INSERT OR IGNORE (SQLite) / ON CONFLICT DO NOTHING (Postgres). Only one isolate wins.
3. Winner runs the DDL. Returns { installed: true, wonClaim: true, version }.
4. Losers bail out. Return { installed: false, wonClaim: false, version } (same shape as the fast path — they're indistinguishable from the caller's perspective).

DDL failure (recovery)

If ddl(db) throws, the marker is deleted before the error rethrows, so the next cold start will retry instead of getting stuck with a half-installed schema.

If the marker delete itself fails (e.g. DB connection lost mid-install), the error from the DDL still rethrows — you'll see both errors in the logs. The next cold start will see the marker without the tables and may need manual recovery.

Configuration reference

BootstrapConfig<Db>

| Field | Required | Default | Description | |---|---|---|---| | version | yes | — | Logical name for this schema, e.g. '0001_initial_schema'. Bumping this string triggers a re-install on next startup. | | ddl | yes | — | Async callback that runs the schema. Receives the Kysely instance. | | marker | no | 'migrations' | Tracking table name. Must match [a-zA-Z_][a-zA-Z0-9_]*. Use an app-scoped name (e.g. 'myapp_migrations') when sharing a DB. | | dialect | no | 'sqlite' | 'sqlite' (D1 / libSQL / SQLite) or 'postgres'. | | logger | no | undefined (silent) | console or { log, error } partial. |

BootstrapResult

| Field | Description | |---|---| | installed | true only on the call that actually ran the DDL. | | wonClaim | true only when this caller won the marker race. Equivalent to installed in practice. | | version | Echo of the version checked. |

Patterns

Bumping the schema (pre-1.0)

Pre-1.0 apps that don't need historical migrations can just bump the version string and replace the DDL:

// Was '0001_initial_schema' — bump to install schema_v2 fresh.
await bootstrapSchema(db, {
  marker: 'myapp_migrations',
  version: '0002_added_invoices',
  ddl: schema_v2.up, // brand-new canonical schema
});

The library will see the v1 marker, miss the v2 marker, and run v2's DDL — but without dropping v1's tables first. If v2 includes new columns on existing tables, you need to drop the old ones in your DDL or coordinate the migration manually.

For real schema evolution post-1.0, switch to Kysely's Migrator which keeps an ordered ledger.

Multiple schema versions in one DB

Different apps can share a Postgres / libSQL DB by using distinct marker values:

await bootstrapSchema(db, { marker: 'app_a_migrations', version: '0001', ddl: appA.up });
await bootstrapSchema(db, { marker: 'app_b_migrations', version: '0001', ddl: appB.up });

Each app maintains its own tracking table and never sees the other's state.

Custom logger

Silence the install messages but keep error visibility:

await bootstrapSchema(db, {
  version: '0001',
  ddl: schema.up,
  logger: { log: () => {}, error: console.error },
});

Concurrency notes

The library uses one of two atomic primitives depending on dialect:

• SQLite / D1 / libSQL: INSERT OR IGNORE INTO ... (name) VALUES (?). Inside SQLite this is a single statement that holds the WAL lock briefly — the loser sees zero rows affected.
• Postgres: INSERT INTO ... (name) VALUES ($1) ON CONFLICT (name) DO NOTHING. Atomic at the row level. Loser sees numAffectedRows === 0.

Both detect the win/loss via the driver's reported affected-row count. Different drivers expose this under different keys (numAffectedRows, meta.changes, changes) — the library tries all three.

If your driver reports neither (you'd see the loser ALSO try to install and partial-fail on duplicate tables), open an issue and we'll add a fallback path.

Security notes

• marker is interpolated into SQL via sql.raw — but the library validates it against [a-zA-Z_][a-zA-Z0-9_]* and throws otherwise. This is a consumer-config field, not user input — never pass user-controlled values.
• version is parameterised — safe for any string.
• The DDL callback runs as your DB user — no extra privilege escalation. If your ddl is malicious, you've got bigger problems.

License

MIT