npm package: @loom_project/db-migrate
Description: Zero-dependency PostgreSQL migration tool for Bun
Author: Loom
Latest version: 1.0.0

# @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

```bash
bun add @loom_project/db-migrate
```

Or install globally:

```bash
bun add -g @loom_project/db-migrate
```

## Quick Start

### 1. Configure Database

Create a `.env` file in your project root:

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

### 2. Create Migration Directory

```bash
mkdir migrate
```

### 3. Create Your First Migration

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

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

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

```bash
db-migrate
db-migrate migrate
```

### Check Migration Status

```bash
db-migrate status
```

### Create New Migration

```bash
db-migrate create <name>
```

### Specify Migration Directory

```bash
db-migrate --dir ./my-migrations
```

### Show Help

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

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

### Adding Columns

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

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

### Creating Types

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

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

```bash
# Migrate public schema
DB_SCHEMA=public db-migrate

# Migrate app schema
DB_SCHEMA=app db-migrate
```

### Check What Will Run

```bash
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](https://www.npmjs.com/package/@loom_project/db-migrate)