How to Use Schema Versioning

This guide covers schema version tracking, rollback-capable migration files, and programmatic access to migration history. Use it when you need to know which schema version is running in each environment, generate migrations that can be automatically reversed, or build custom tooling on top of the migration infrastructure.

When

Use this guide when you need to:

• Track which schema version is deployed to each environment
• Generate migration files with automatic rollback SQL
• Roll back a bad migration without manually writing reverse SQL
• Inspect migration history or check for drift between files and the database

Overview

db-semantic-planner tracks schema evolution through the _dbsp_migrations table, which records each applied migration with a monotonic schema_version integer and a destructive flag. Migration files contain both UP and DOWN SQL, generated automatically from the schema diff.

The full lifecycle:

schema diff → migration file (UP + DOWN) → apply → schema_version incremented
                                         → rollback → schema_version decremented

Key Files

File	Role
packages/adapter-pgsql/src/ddl/schema-diff.ts	compareSchemata(schema, db) — diff two ModelIR instances
packages/adapter-pgsql/src/ddl/migration-sql.ts	generateMigrationSQL() / generateDownSQL() — UP/DOWN SQL from diff
packages/adapter-pgsql/src/ddl/migration-file.ts	generateMigrationFile() / parseMigrationFile() — file format
packages/adapter-pgsql/src/ddl/migration-tracker.ts	_dbsp_migrations table: version tracking, advisory lock
packages/cli/src/commands/migrate.ts	migrate dev / apply / rollback / status CLI commands

Steps

1. Generate a migration with UP + DOWN SQL

dbsp migrate dev -s dbsp.schema.ts -d postgresql://localhost/mydb -n add_email_column

This:

1. Introspects the live database into a ModelIR
2. Calls compareSchemata(schemaModel, dbModel) to produce a SchemaDiff
3. Generates UP SQL via generateMigrationSQL(diff)
4. Generates DOWN SQL via generateDownSQL(diff) — automatically reversed, topologically ordered
5. Writes migrations/0004_add_email_column.sql with both sections

The generated file format:

-- Migration: 0004_add_email_column.sql
-- Generated by: dbsp migrate dev
-- Date: 2026-03-27T10:00:00.000Z

ALTER TABLE "users" ADD COLUMN "email" varchar(255) NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS "idx_users_email" ON "users" ("email");

-- DOWN

ALTER TABLE "users" DROP COLUMN "email" CASCADE;
-- WARNING: DROP INDEX "idx_users_email" auto-cascaded with column drop

Destructive changes (DROP TABLE, DROP COLUMN) generate -- WARNING: comments in the DOWN section and require --allow-destructive to include in UP SQL.

2. Apply migrations and track schema version

dbsp migrate apply -d postgresql://localhost/mydb

Each applied migration records in _dbsp_migrations:

• name — filename (e.g., 0004_add_email_column.sql)
• checksum — SHA-256 of file content (tamper detection)
• applied_at — timestamp
• schema_version — monotonic integer (max existing + 1, starts at 1)
• destructive — true if the migration contains DROP TABLE or DROP COLUMN

The first migration applied gets schema_version = 1. Each subsequent one increments by 1.

3. Inspect migration status

dbsp migrate status -d postgresql://localhost/mydb

Output:

Migration Status
   Database: postgresql://localhost/mydb
   Directory: migrations

  ✅ 0001_initial.sql — applied (applied: 2026-03-10T09:00:00.000Z)
  ✅ 0002_add_orders.sql — applied (applied: 2026-03-15T14:00:00.000Z)
  ✅ 0003_add_indexes.sql — applied (applied: 2026-03-20T11:00:00.000Z)
  ⏳ 0004_add_email_column.sql — pending

Total: 4 | Applied: 3 | Pending: 1

Status values:

Status	Meaning
applied	File applied, checksum matches
pending	File exists locally, not yet applied
checksum_mismatch	Applied but file modified since apply — investigate before proceeding
missing_file	Tracked in DB but file deleted from disk

For CI/scripting, use --json:

dbsp migrate status -d postgresql://... --json

4. Roll back the last N migrations

# Roll back 1 migration (default)
dbsp migrate rollback 1 -d postgresql://localhost/mydb

# Roll back 3 migrations
dbsp migrate rollback 3 -d postgresql://localhost/mydb

Rollback:

1. Reads the last N records from _dbsp_migrations
2. Verifies checksum matches the file on disk (fails with error if modified)
3. Executes DOWN SQL from the migration file (split on the -- DOWN separator)
4. Removes the record from _dbsp_migrations
5. The previous migration's schema_version becomes the current version

Destructive rollback (DOWN SQL contains DROP TABLE or DROP COLUMN) requires --force:

dbsp migrate rollback 1 -d postgresql://... --force

Error cases:

Error	Cause	Fix
"No DOWN section in migration X"	File has no -- DOWN separator	Edit file manually to add DOWN SQL
"Migration file modified since apply"	Checksum mismatch	Revert file or resolve manually
"Cannot rollback N, only M applied"	Requesting more rollbacks than applied	Reduce count
"Destructive rollback requires --force"	DOWN would DROP TABLE/COLUMN	Add --force after reviewing SQL

5. Use the programmatic API

The versioning infrastructure is also available as a library for custom tooling:

// doctest: skip — requires real PostgreSQL connection (getAppliedMigrations, getNextSchemaVersion, recordMigration use pool)
import { compareSchemata, generateDownSQL, generateMigrationSQL, getAppliedMigrations, getNextSchemaVersion, recordMigration } from '@dbsp/adapter-pgsql';

// Compare schema definition vs live DB
const diff = compareSchemata(schemaModel, introspectedModel);
console.log(`${diff.changes.length} changes, hasDestructive: ${diff.hasDestructive}`);
console.log(diff.summary);

// Generate SQL
const upSQL = generateMigrationSQL(diff, { schemaName: 'public' });
const downSQL = generateDownSQL(diff, { schemaName: 'public' });

// Check migration history
const applied = await getAppliedMigrations(pool);
const currentVersion = applied.at(-1)?.schemaVersion ?? 0;
console.log(`Current schema version: ${currentVersion}`);

// Record a custom migration
const nextVersion = await getNextSchemaVersion(pool);
await recordMigration(pool, 'custom_001.sql', checksum, nextVersion, diff.hasDestructive);

6. Reversibility reference

generateDownSQL() handles each change kind as follows:

Change	DOWN SQL	Reversible?
create_table	DROP TABLE IF EXISTS ... CASCADE	YES
add_column	ALTER TABLE ... DROP COLUMN ... CASCADE	YES (data lost)
alter_column_type	ALTER COLUMN ... TYPE <fromType>	PARTIAL (needs meta.fromType)
alter_column_nullable	Reverse SET/DROP NOT NULL	YES
alter_column_default	Restore old default	PARTIAL (needs meta.oldDefault)
add_primary_key	DROP CONSTRAINT	YES
add_foreign_key	DROP CONSTRAINT	YES
create_index	DROP INDEX	YES
drop_table	-- WARNING: cannot reconstruct	NO (data lost)
drop_column	-- WARNING: cannot reconstruct	NO (data lost)
drop_primary_key	-- WARNING: cannot reconstruct	NO
drop_foreign_key	-- WARNING: cannot reconstruct	NO
drop_index	-- WARNING: cannot reconstruct	NO

When meta.fromType or meta.oldDefault is missing (manual edit, older migration), the DOWN SQL falls back to a -- WARNING: comment.

Gotchas

• Advisory lock uses a dedicated client — withMigrationLock() calls pool.connect(), not pool.query(). The lock is held for the entire rollback transaction. Do not pass a raw query to pool — use the withMigrationLock(pool, fn) wrapper.

• schema_version is apply-time, not file-time — the version is assigned when migrate apply runs (max(schema_version) + 1), not when the file is generated. Two developers generating migrations locally will get the same version number resolved at apply-time, not at generation.

• Checksum mismatch is a hard error — if you edited a migration file after applying it, rollback will fail. Resolve by: (a) reverting the file, or (b) manually removing the tracking record and re-applying.

• -- DOWN separator is line-strict — the parser uses ^\s*-- DOWN\s*$. A SQL string containing '-- DOWN' inside a value does not trigger the split (SC-25).

• DOWN SQL for alter_column_type requires meta.fromType — compareSchemata() stores fromType in SchemaChange.meta only for columns where the type changed. Verify with diff.changes.find(c => c.kind === 'alter_column_type')?.meta?.fromType.

• Auto-migrate adds columns to existing _dbsp_migrations — if the table was created by an older version without schema_version or destructive columns, ensureMigrationsTable() adds them and backfills schema_version by ROW_NUMBER() OVER (ORDER BY applied_at).