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

``bash


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

`bash


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

$3

Create a new migration with up/down SQL files:

`bash
db-migrate create add_users_table
`

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

$3

Run all pending migrations:

`bash
db-migrate up
`

$3

Rollback a migration (prompts for confirmation):

`bash


Rollback latest migration

db-migrate down

Rollback specific migration

db-migrate down 20240101120000_add_users_table
`

$3

Show applied and pending migrations:

`bash
db-migrate list
`

$3

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

`bash
db-migrate views
`

$3

Run pending migrations AND apply views:

`bash
db-migrate all
`

$3

Show current configuration:

`bash
db-migrate config
`

Configuration

Configure via environment variables:

`bash


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

$3

`
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
`

$3

up.sql:
`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:
`sql
DROP INDEX IF EXISTS idx_users_email;
DROP TABLE IF EXISTS users;
`

Package.json Scripts

Add shortcuts to your package.json:

`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