Migrations

SQL-first migrations with checksum verification and rollback support.

Overview

Postbase uses plain SQL files for migrations:

migrations/
├── 20260125090000_create_users.up.sql
├── 20260125090000_create_users.down.sql
├── 20260125090100_create_posts.up.sql
├── 20260125090100_create_posts.down.sql
└── 20260125090200_add_indexes.up.sql
└── 20260125090200_add_indexes.down.sql

.up.sql - Applied when migrating up
.down.sql - Applied when rolling back

Creating Migrations

Generate Files

postbase migrate new create_users

Creates:

Created migration files:
  migrations/20260125090000_create_users.up.sql
  migrations/20260125090000_create_users.down.sql

Custom Directory

postbase migrate new create_users --dir ./db/migrations

Write Migration SQL

Edit migrations/20260125090000_create_users.up.sql:

-- Create users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(50) DEFAULT 'user',
  email_verified BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Create indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
 
-- Create trigger for updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$ language 'plpgsql';
 
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Edit migrations/20260125090000_create_users.down.sql:

DROP TRIGGER IF EXISTS update_users_updated_at ON users;
DROP FUNCTION IF EXISTS update_updated_at_column();
DROP TABLE IF EXISTS users;

Applying Migrations

Apply All Pending

postbase migrate up

Output:

Applying migrations to myapp (public)...
 
  ↑ 20260125090000_create_users
    ✓ Applied in 45ms
 
  ↑ 20260125090100_create_posts
    ✓ Applied in 32ms
 
Applied 2 migrations in 77ms

Apply to Specific Database

postbase migrate up --database myapp

Apply to Specific Schema

postbase migrate up --schema auth

Dry Run

Preview without applying:

postbase migrate up --dry-run

Rolling Back

Rollback Last Migration

postbase migrate down

Output:

Rolling back last migration in myapp (public)...
 
  ↓ 20260125090100_create_posts
    ✓ Rolled back in 28ms

Rollback Multiple

postbase migrate down --count 3

Rollback to Specific Migration

postbase migrate down --to 20260125090000

Checking Status

postbase migrate status

Output:

Migration Status for myapp (public)
 
Applied (2):
  ✓ 20260125090000_create_users    Applied: 2026-01-25 10:30:00
  ✓ 20260125090100_create_posts    Applied: 2026-01-25 10:30:01
 
Pending (1):
  ○ 20260125090200_add_indexes
 
Total: 3 migrations, 2 applied, 1 pending

JSON Output

postbase migrate status --json

Multi-Schema Support

Separate Migration Directories

migrations/
├── public/
│   ├── 20260125_create_users.up.sql
│   └── 20260125_create_users.down.sql
└── auth/
    ├── 20260125_create_sessions.up.sql
    └── 20260125_create_sessions.down.sql

Apply per Schema

# Apply public schema migrations
postbase migrate up --schema public
 
# Apply auth schema migrations
postbase migrate up --schema auth

Status per Schema

postbase migrate status --schema auth

Migration Tracking

Postbase tracks migrations in the _postbase_migrations table:

SELECT * FROM _postbase_migrations;

id	name	checksum	applied_at
1	20260125_create_users	abc123...	2026-01-25 10:30:00
2	20260125_create_posts	def456...	2026-01-25 10:30:01

Checksum Verification

Postbase computes SHA-256 checksums of migration files. If a file changes after being applied, you'll see a warning:

⚠ Checksum mismatch for 20260125_create_users
  File may have been modified after migration was applied.

Best Practices

Naming Conventions

YYYYMMDDHHMMSS_descriptive_name.up.sql

Examples:

20260125090000_create_users.up.sql
20260125090100_add_email_verified_to_users.up.sql
20260125090200_create_orders_table.up.sql

Idempotent Migrations

Use IF NOT EXISTS and IF EXISTS:

-- Up migration
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL
);
 
-- Down migration
DROP TABLE IF EXISTS users;

Transaction Safety

Wrap complex migrations in transactions:

BEGIN;
 
ALTER TABLE users ADD COLUMN role VARCHAR(50);
UPDATE users SET role = 'user' WHERE role IS NULL;
ALTER TABLE users ALTER COLUMN role SET NOT NULL;
 
COMMIT;

Data Migrations

Separate schema and data migrations:

-- 20260125_add_role_to_users.up.sql (schema change)
ALTER TABLE users ADD COLUMN role VARCHAR(50);
 
-- 20260125_set_default_roles.up.sql (data migration)
UPDATE users SET role = 'user' WHERE role IS NULL;
ALTER TABLE users ALTER COLUMN role SET NOT NULL;

Testing Migrations

# Create test database
postbase db create myapp_test
 
# Apply migrations
postbase migrate up --database myapp_test
 
# Run tests
npm test
 
# Cleanup
postbase db drop myapp_test --force

Common Patterns

Adding a Column

-- Up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
-- Down
ALTER TABLE users DROP COLUMN phone;

Adding an Index

-- Up
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);
 
-- Down
DROP INDEX CONCURRENTLY IF EXISTS idx_users_phone;

Renaming a Column

-- Up
ALTER TABLE users RENAME COLUMN name TO full_name;
 
-- Down
ALTER TABLE users RENAME COLUMN full_name TO name;

Adding a Foreign Key

-- Up
ALTER TABLE posts
ADD CONSTRAINT fk_posts_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;
 
-- Down
ALTER TABLE posts DROP CONSTRAINT fk_posts_user;

Creating an Enum

-- Up
CREATE TYPE user_role AS ENUM ('user', 'admin', 'moderator');
ALTER TABLE users ADD COLUMN role user_role DEFAULT 'user';
 
-- Down
ALTER TABLE users DROP COLUMN role;
DROP TYPE user_role;