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:

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";

Configure the
@/sql
path alias in your

tsconfig.json

so migration sources typecheck and resolve:

jsonc

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

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:

The directory is non-empty.
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.
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.
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

when valid, non-zero otherwise (good for CI / pre-commit):

bash

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:

bash

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.

bash

# 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

bash

# 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.

database-migrations

NPX Install

Tags

SKILL.md Content

Database Migrations with @schemavaults/dbh

One-time setup (for consumers)

Migration file format

Example: using the
`Kysely<any>`
query builder

Example: using the
`sql`
template tag

Empty template migration

Validating migrations

Building migrations

Running migrations

Programmatic API

Typical end-to-end flow

Required environment variables (for migrate/reverse)