db-migrate-sql

Simple, lightweight SQL migration tool for PostgreSQL with rollback support. No ORM required - just plain SQL files.

Features

• ✅ Pure SQL migrations (no ORM lock-in)
• ✅ Rollback support with down.sql files
• ✅ Database views management
• ✅ Configurable paths and table names
• ✅ Interactive prompts for safety
• ✅ Migration status tracking
• ✅ Timestamp-based naming

Installation

# Install in your project
npm install @shannonarcher/db-migrate-sql
# or
pnpm add @shannonarcher/db-migrate-sql

# Or install globally
npm install -g @shannonarcher/db-migrate-sql

Quick Start

# 1. Set your database URL
export DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

# 2. Create a migration
npx db-migrate create add_users_table

# 3. Edit the generated SQL files
# - db/migrations/20240101120000_add_users_table/up.sql
# - db/migrations/20240101120000_add_users_table/down.sql

# 4. Run migrations
npx db-migrate up

# 5. View migration status
npx db-migrate list

Commands

create [name]

Create a new migration with up/down SQL files:

db-migrate create add_users_table

This creates:

db/migrations/20240101120000_add_users_table/
├── up.sql      # Apply migration
└── down.sql    # Rollback migration

up

Run all pending migrations:

db-migrate up

down [name]

Rollback a migration (prompts for confirmation):

# Rollback latest migration
db-migrate down

# Rollback specific migration
db-migrate down 20240101120000_add_users_table

list

Show applied and pending migrations:

db-migrate list

views

Apply all database views from db/views/*.sql:

db-migrate views

all

Run pending migrations AND apply views:

db-migrate all

config

Show current configuration:

db-migrate config

Configuration

Configure via environment variables:

# Required
DATABASE_URL=postgresql://user:password@localhost:5432/mydb

# Optional (with defaults)
MIGRATIONS_DIR=db/migrations          # Where migration folders live
VIEWS_DIR=db/views                     # Where view SQL files live
MIGRATIONS_TABLE=_migrations           # Table to track applied migrations

Migration Structure

Directory Layout

your-project/
├── db/
│   ├── migrations/
│   │   ├── 20240101120000_add_users_table/
│   │   │   ├── up.sql
│   │   │   └── down.sql
│   │   └── 20240102130000_add_posts_table/
│   │       ├── up.sql
│   │       └── down.sql
│   └── views/
│       ├── user_stats.sql
│       └── post_counts.sql
└── package.json

Example Migration

up.sql:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

down.sql:

DROP INDEX IF EXISTS idx_users_email;
DROP TABLE IF EXISTS users;

Package.json Scripts

Add shortcuts to your package.json:

{
  "scripts": {
    "migrate": "db-migrate up",
    "migrate:down": "db-migrate down",
    "migrate:list": "db-migrate list",
    "migrate:create": "db-migrate create",
    "migrate:all": "db-migrate all"
  }
}

License

MIT

db-migrate-sql

db-migrate-sql