@loom_project/db-migrate

Zero-dependency PostgreSQL migration tool for Bun with built-in idempotency support.

Features

• Zero Dependencies: Uses Bun's native SQL client
• Idempotent Migrations: Write safe, repeatable migrations
• Transaction Safety: Each migration runs in its own transaction
• Checksum Verification: Detects modified migration files
• Simple CLI: Easy-to-use command-line interface
• Schema Isolation: Migrations tracked per schema

Installation

bun add @loom_project/db-migrate

Or install globally:

bun add -g @loom_project/db-migrate

Quick Start

1. Configure Database

Create a .env file in your project root:

DATABASE_URL=postgres://user:password@localhost:5432/mydb
DB_SCHEMA=public

2. Create Migration Directory

mkdir migrate

3. Create Your First Migration

db-migrate create create_users_table

This creates a file like migrate/001__update__create_users_table.sql

4. Write Migration SQL

Edit the generated file with idempotent SQL:

-- Create table with IF NOT EXISTS
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Add column conditionally
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'users' AND column_name = 'role'
  ) THEN
    ALTER TABLE users ADD COLUMN role VARCHAR(50);
  END IF;
END $$;

5. Run Migrations

db-migrate

Migration File Format

Migration files must follow this naming pattern:

<version>__<type>__<description>.sql

• version: 3-digit number (001, 002, 003, ...)
• type: Category (init, update, fix, etc.)
• description: Brief description using underscores

Examples:

• 001__init__create_users_table.sql
• 002__update__add_email_index.sql
• 003__fix__remove_duplicate_users.sql

CLI Commands

Run Migrations (Default)

db-migrate
db-migrate migrate

Check Migration Status

db-migrate status

Create New Migration

db-migrate create <name>

Specify Migration Directory

db-migrate --dir ./my-migrations

Show Help

db-migrate help

Environment Variables

| Variable | Description | Default | |----------|-------------|---------| | DATABASE_URL | PostgreSQL connection URL | - | | DB_HOST | Database host | - | | DB_PORT | Database port | 5432 | | DB_NAME | Database name | - | | DB_USER | Database username | - | | DB_PASSWORD | Database password | - | | DB_SCHEMA | Target schema | public |

Migration Tracking

Migrations are tracked in the loom_migrations schema:

loom_migrations.migrate_<schema_name>

Examples:

• For public schema → loom_migrations.migrate_public
• For app schema → loom_migrations.migrate_app

Each table stores:

• Version number
• Migration name and description
• File checksum (SHA256)
• Execution timestamp
• Execution time in milliseconds

Writing Idempotent Migrations

Table Creation

CREATE TABLE IF NOT EXISTS my_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

Adding Columns

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'my_table' AND column_name = 'new_column'
  ) THEN
    ALTER TABLE my_table ADD COLUMN new_column TEXT;
  END IF;
END $$;

Creating Indexes

CREATE INDEX IF NOT EXISTS idx_name ON my_table(name);

Creating Types

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'my_enum') THEN
    CREATE TYPE my_enum AS ENUM ('value1', 'value2');
  END IF;
END $$;

Programmatic API

You can also use the library programmatically:

import { loadConfig, createConnection, runMigrations } from '@loom_project/db-migrate';

const config = loadConfig();
createConnection(config);

const result = await runMigrations(config.targetSchema, './migrate');

console.log(`Executed ${result.executed.length} migrations`);

Best Practices

1. Always use idempotent SQL: Migrations should be safe to run multiple times
2. One logical change per migration: Keep migrations focused and atomic
3. Never modify applied migrations: Create new migrations to fix issues
4. Test migrations: Run them against a test database first
5. Use transactions: Each migration runs in a transaction and rolls back on error
6. Sequential versioning: Keep version numbers in order (001, 002, 003...)

Error Handling

• Migrations run in transactions and rollback automatically on failure
• Execution stops at the first failed migration
• Check logs for detailed error messages
• Fix the migration and run again

Examples

Multi-Schema Setup

# Migrate public schema
DB_SCHEMA=public db-migrate

# Migrate app schema
DB_SCHEMA=app db-migrate

Check What Will Run

db-migrate status

Output:

Total migrations: 5
Applied: 3
Pending: 2

Migrations:
  ✓ 001 - 001__init__create_users.sql (2025-01-07T10:30:00Z)
  ✓ 002 - 002__update__add_roles.sql (2025-01-07T10:30:05Z)
  ✓ 003 - 003__fix__email_index.sql (2025-01-07T10:30:10Z)
  ○ 004 - 004__update__add_timestamps.sql
  ○ 005 - 005__init__create_posts.sql

Requirements

• Bun >= 1.2.0 (for native PostgreSQL support)
• PostgreSQL >= 9.6

License

MIT

Contributing

Issues and pull requests are welcome!

Links

• NPM Package