Database Migrations

BCP 0.2.3 extends framework-managed SQL migrations across MySQL, PostgreSQL and SQLite.

Supported providers

Migration bookkeeping uses the same provider selection as bcp/database:

mysql
postgresql
sqlite

Install the optional driver used by the application:

npm install mysql2
npm install pg
npm install better-sqlite3

Only one driver is required when the project uses one database provider.

Provider configuration

MySQL:

DB_DRIVER=mysql
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=
DB_NAME=bcp_app

PostgreSQL:

DATABASE_URL=postgresql://postgres:password@localhost:5432/bcp_app

SQLite:

DB_DRIVER=sqlite
DATABASE_URL=./data/bcp.sqlite

Database commands load the development environment before connecting, so migration commands and bcp dev use the same provider selection.

Create a migration

bcp db create create_users

BCP creates an ordered TypeScript file:

migrations/
  20260829090000_create_users.ts

Generated migrations export up() and down():

import type {
    TransactionDatabase,
} from "bcp/database";

export async function up(
    db: TransactionDatabase
): Promise<void> {
    await db.execute(`
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            email VARCHAR(255) NOT NULL UNIQUE
        )
    `);
}

export async function down(
    db: TransactionDatabase
): Promise<void> {
    await db.execute(
        "DROP TABLE users"
    );
}

Migration filenames use a UTC timestamp prefix for stable execution order.

SQL dialect responsibility

BCP keeps its own _bcp_migrations bookkeeping provider-aware, but it does not translate application migration SQL.

This means migrations should either:

• use SQL supported by every database target used by the application, or
• intentionally target one provider and use that provider's SQL syntax.

Parameter placeholders also follow the active provider:

MySQL       ?
SQLite      ?
PostgreSQL  $1, $2, ...

Run pending migrations

bcp db migrate

BCP creates _bcp_migrations with provider-specific DDL, detects pending migration files, and executes them in filename order.

All migrations applied by one command share the same batch number. Each migration runs inside its own transaction and its bookkeeping record is written in that same transaction.

Provider-specific bookkeeping includes:

• MySQL AUTO_INCREMENT,
• PostgreSQL BIGSERIAL,
• SQLite INTEGER PRIMARY KEY AUTOINCREMENT.

Check status

bcp db status

The command reports applied and pending migration files together with the batch number for applied migrations.

Roll back

bcp db rollback

Rollback reverses only the latest migration batch. Migrations run from newest to oldest and each down() executes in a transaction together with deletion of its migration record.

BCP refuses to roll back an applied migration when its migration file is missing.

Project root

All database commands support the normal project-root option:

bcp db status --root ./apps/admin

Database Platform v2 behavior

0.2.3 keeps migration commands consistent across the built-in SQL providers:

bcp db create
bcp db migrate
bcp db status
bcp db rollback

The command names and migration file contract stay the same regardless of whether the application uses MySQL, PostgreSQL or SQLite.