npm package: @shannonarcher/db-migrate-sql
Description: Simple SQL migration tool with rollback support for PostgreSQL
Author: Shannon Archer
Latest version: 1.0.0

# 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

### `create [name]`

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

### `up`

Run all pending migrations:

```bash
db-migrate up
```

### `down [name]`

Rollback a migration (prompts for confirmation):

```bash
# Rollback latest migration
db-migrate down

# Rollback specific migration
db-migrate down 20240101120000_add_users_table
```

### `list`

Show applied and pending migrations:

```bash
db-migrate list
```

### `views`

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

```bash
db-migrate views
```

### `all`

Run pending migrations AND apply views:

```bash
db-migrate all
```

### `config`

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

### 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:**
```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