Database Migrations

Database Migrations with @schemavaults/dbh

@schemavaults/dbh provides Kysely migrations for PostgreSQL, applied through the dbh CLI. Migrations are opinionated: every file is a numbered module that exports an up() and a down() function. TypeScript source migrations are built to JavaScript first, then applied with the CLI.

Invoke the CLI with your package runner. Use bunx @schemavaults/dbh for validating and building migrations — build-db-migrations uses Bun's bundler and requires Bun anyway. Use npx @schemavaults/dbh for running / applying migrations (migrate and reverse): most PostgreSQL drivers are built for Node.js rather than Bun, so apply migrations on the Node runtime.

One-time setup (for consumers)

Migrations import the sql template tag from @/sql rather than directly from the package. This indirection is required by the build step (see the note under "Building migrations"), so configure it once:

1. Create a local sql module somewhere in your source tree, e.g. ./src/db/sql.ts, that re-exports the tag from the package:

   // src/db/sql.ts
   export { sql, sql as default } from "@schemavaults/dbh/sql";
   export type * from "@schemavaults/dbh/sql";
   

2. Configure the @/sql path alias in your tsconfig.json so migration sources typecheck and resolve:

   {
     "compilerOptions": {
       "baseUrl": ".",
       "paths": {
         "@/sql": ["./src/db/sql.ts"]
       }
     }
   }
   

3. Create a migrations directory, e.g. ./src/db/migrations/, and add your numbered migration files there.

Migration file format

Each migration is a single file in your migrations directory. The rules are:

1. The directory is non-empty.
2. Each file name is prefixed with a 5-digit migration number, followed by a short kebab-case description, e.g. 00000-template-migration.ts, 00001-create-users-table.ts. The number defines apply order.
3. Each module exports an up(db) and a down(db) function. up() applies the change; down() must reverse it exactly so migrations can be rolled back.
4. Migration numbers are unique — never reuse a number. If two branches both add 00040-*.ts, that collision must be resolved by renumbering one of them before merge.

Both up and down receive a Kysely<any> instance and return a Promise. Import the Kysely type from the package: import type { Kysely } from "@schemavaults/dbh".

Example: using the Kysely<any> query builder

Prefer the typed query builder for schema operations:

// 00001-create-users-table.ts
import type { Kysely } from "@schemavaults/dbh";

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable("users")
    .addColumn("user_id", "uuid", (col) => col.primaryKey())
    .addColumn("email", "text", (col) => col.notNull().unique())
    .addColumn("created_at", "bigint", (col) => col.notNull())
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("users").execute();
}

Example: using the sql template tag

For statements the builder can't express (or raw DDL), import sql from @/sql (your local module from setup, which re-exports Kysely's sql tag) and call .execute(db):

// 00002-create-squirrels-table.ts
import type { Kysely } from "@schemavaults/dbh";
import { sql } from "@/sql";

export async function up(db: Kysely<any>): Promise<void> {
  await sql`
    CREATE TABLE IF NOT EXISTS EXAMPLE_SQUIRRELS (
      squirrel_id UUID PRIMARY KEY,
      squirrel_name TEXT NOT NULL,
      created_at BIGINT NOT NULL
    );
  `.execute(db);

  // Always interpolate values via ${...}; the sql tag parameterizes them.
  await sql`CREATE INDEX squirrels_name_idx ON EXAMPLE_SQUIRRELS (squirrel_name);`.execute(
    db,
  );
}

export async function down(db: Kysely<any>): Promise<void> {
  await sql`DROP TABLE IF EXISTS EXAMPLE_SQUIRRELS;`.execute(db);
}

Important: migration files must always import sql from @/sql, never directly from @schemavaults/dbh/sql. The build-db-migrations step rewrites the literal @/sql import specifier to a relative path pointing at the built, standalone sql.js, so the import must be written exactly as @/sql for the build to work. (This is why the one-time setup configures the @/sql alias.)

Empty template migration

A no-op migration is valid (useful as a starting template):

// 00000-template-migration.ts
import type { Kysely } from "@schemavaults/dbh";

export async function up(
  db: Kysely<any>, // eslint-disable-line @typescript-eslint/no-unused-vars
): Promise<void> {}

export async function down(
  db: Kysely<any>, // eslint-disable-line @typescript-eslint/no-unused-vars
): Promise<void> {}

Validating migrations

Before building or applying, assert your source migrations directory is well-formed. The validate-migration-directory command checks all four rules above and exits 0 when valid, non-zero otherwise (good for CI / pre-commit):

bunx @schemavaults/dbh validate-migration-directory ./src/db/migrations

It reports each problem with an [ERROR]/[WARN] prefix:

• empty directory,
• a file missing the 5-digit prefix,
• a module missing up() or down(),
• duplicate migration numbers (branch collisions).

Treat duplicate numbers as warnings (non-fatal) with --duplicates-as-warnings.

Migrations importing through tsconfig path aliases (like @/sql from the one-time setup) validate correctly: the command discovers the nearest tsconfig.json declaring compilerOptions.paths by walking up from the migrations directory (following extends), and applies those aliases when importing each module. Use --tsconfig <path> to point at a specific config instead of relying on discovery.

Building migrations

TypeScript migrations must be compiled to JavaScript before they're applied (the migrate step runs on Node and imports .js). The build-db-migrations command uses Bun's bundler and also builds the standalone sql module the migrations depend on. Point --sql-module at the local sql.ts you created during setup:

bunx @schemavaults/dbh build-db-migrations ./src/db/migrations \
  --outdir ./dist/migrations \
  --sql-module ./src/db/sql.ts \
  --sql-outdir ./dist

Key options:

• <migrations-src> — directory of .ts migration sources (positional).
• --outdir <dir> — where compiled .js migrations are written (required).
• --sql-module <path> — path to your local sql.ts module to build alongside (required).
• --sql-outdir <dir> — where the built sql.js goes (defaults to the parent of --outdir).
• --external <pkg...> — packages to keep external (default: @schemavaults/dbh, kysely).

build-db-migrations requires bun to be installed and on the PATH.

Running migrations

Apply built migrations with migrate, and roll back with reverse. Both take the built migration folder and require an --environment; credentials come from process.env (or an --env-file). Run these with npx (Node.js): most PostgreSQL drivers target Node rather than Bun.

# Apply all pending migrations (to latest):
npx @schemavaults/dbh migrate ./dist/migrations --environment production --env-file ./.env.production

# Apply up to a specific version (the migration name w/o extension):
npx @schemavaults/dbh migrate ./dist/migrations 00001-create-users-table --environment staging

# Roll back down to a target version:
npx @schemavaults/dbh reverse ./dist/migrations 00000-template-migration --environment staging

Options for migrate / reverse:

• <folder> — path to the built migration folder (positional).
• [version] / <version> — target migration name; migrate defaults to latest, reverse requires it.
• -e, --environment <env> — development | test | staging | production (required).
• --ws-proxy-url <url> — custom Neon-compatible WebSocket proxy URL.
• --env-file <path> — load DB credentials from a .env file first.

Each result line prints as [Up|Down] <migrationName>: <Success|Error|NotExecuted>.

Programmatic API

The same operations are available from @schemavaults/dbh/migrate for tests or custom scripts, using the adapter's Kysely instance:

import { migrate, reverse } from "@schemavaults/dbh/migrate";

await migrate({ db: adapter.db, migrationFolder, version /* optional */ });
await reverse({ db: adapter.db, migrationFolder, version });

Typical end-to-end flow

# 1. Validate the source migrations directory.
bunx @schemavaults/dbh validate-migration-directory ./src/db/migrations

# 2. Build .ts migrations (+ sql module) to .js.
bunx @schemavaults/dbh build-db-migrations ./src/db/migrations \
  --outdir ./dist/migrations --sql-module ./src/db/sql.ts --sql-outdir ./dist

# 3. Apply the built migrations (npx / Node.js — pg drivers target Node).
npx @schemavaults/dbh migrate ./dist/migrations --environment production --env-file ./.env.production

Required environment variables (for migrate/reverse)

POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_URL, POSTGRES_HOST, POSTGRES_PORT, POSTGRES_DATABASE (and optional POSTGRES_URL_NON_POOLING). Set SCHEMAVAULTS_DBH_DEBUG=true for verbose debug logging.