postgres-shift

A simple, forward-only migration tool for PostgreSQL ported from postgres.js to TypeScript and extended to have more features, and ported to @alcyone-labs/arg-parser as a CLI handler, making it MCP-compatible out-of-the-box.

Features

• Forward-only migrations (no rollbacks)
• Schema-based organization
• Both SQL and JavaScript migrations
• CLI tool and programmatic API
• Built specifically for postgres.js
• TypeScript support
• MCP support

Installation

pnpm add @alcyone-labs/postgres-shift-ts
# or
npm install @alcyone-labs/postgres-shift-ts
# or
yarn add @alcyone-labs/postgres-shift-ts
# or
bun add @alcyone-labs/postgres-shift-ts
# or
deno add npm:@alcyone-labs/postgres-shift-ts

Quick Start

CLI Usage

0. Check options

npx migrate --help
# or
pnpx migrate --help

1. Set your database connection string:

export DB_CONNECTION_STRING="postgres://username:password@localhost:5432/database"

2. Create migration directories:

src/db/migrations/
├── public/
│   ├── 00001_create_users_table/
│   │   └── index.sql
│   └── 00002_add_email_index/
│       └── index.sql
└── analytics/
    └── 00001_create_events_table/
        └── index.sql

3. Run migrations:

npx migrate --path src/db/migrations
# or
pnpx migrate --path src/db/migrations

Programmatic Usage

import postgres from "postgres";
import shift from "@ophiuchus/postgres-shift";

const sql = postgres("postgres://username:password@localhost:5432/database");

await shift({
  sql,
  path: "./migrations/public",
  schema: "public",
  before: (migration) => console.log(`Running: ${migration.name}`),
  after: (migration) => console.log(`Completed: ${migration.name}`),
});

Migration Structure

Directory Organization

Migrations are organized by schema, with each migration in its own numbered directory:

migrations/
├── public/                    # Schema name
│   ├── 00001_initial_schema/  # Migration directory (5-digit prefix)
│   │   └── index.sql         # SQL migration
│   ├── 00002_add_users/
│   │   └── index.sql
│   └── 00003_complex_migration/
│       └── index.js          # JavaScript migration
└── analytics/
    └── 00001_create_tables/
        └── index.sql

Naming Convention

• Migration directories must start with a 5-digit number: 00001_, 00002_, etc.
• Numbers must be consecutive (no gaps)
• Use descriptive names after the number: 00001_create_users_table
• Underscores in names are converted to spaces in the migration log

SQL Migrations

Create an index.sql file in your migration directory:

-- 00001_create_users_table/index.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

JavaScript Migrations

Create an index.js file that exports a default function:

// 00002_seed_data/index.js
export default async function (sql) {
  await sql`
    INSERT INTO users (email) VALUES
    ('admin@example.com'),
    ('user@example.com')
  `;

  // You can perform complex logic here
  const users = await sql`SELECT * FROM users`;
  console.log(`Seeded ${users.length} users`);
}

CLI Reference

migrate

Run database migrations for all schemas in the specified directory.

npx migrate [options]

Options

• --path, -p, --migrations <path> - Path to migrations directory (default: src/db/migrations)

Environment Variables

• DB_CONNECTION_STRING - PostgreSQL connection string (required)

Examples

# Use default path
npx migrate

# Specify custom path
npx migrate --path ./db/migrations

# Using environment file
DB_CONNECTION_STRING="postgres://localhost/mydb" npx migrate

API Reference

shift(options)

Main migration function.

Parameters

• sql (Sql) - postgres.js database connection
• path (string) - Path to migration files for a specific schema
• schema (string) - PostgreSQL schema name (default: 'public')
• before (function, optional) - Callback called before each migration
• after (function, optional) - Callback called after each migration

Returns

Promise that resolves when all migrations are complete.

Example

import postgres from "postgres";
import shift from "@ophiuchus/postgres-shift";

const sql = postgres(process.env.DATABASE_URL);

try {
  await shift({
    sql,
    path: "./migrations/public",
    schema: "public",
    before: ({ migration_id, name, path }) => {
      console.log(`Starting migration ${migration_id}: ${name}`);
    },
    after: ({ migration_id, name, path }) => {
      console.log(`Completed migration ${migration_id}: ${name}`);
    },
  });
  console.log("All migrations completed successfully");
} catch (error) {
  console.error("Migration failed:", error);
  process.exit(1);
}

Migration Object

The migration object passed to before and after callbacks:

type TMigration = {
  path: string; // Full path to migration directory
  migration_id: number; // Numeric ID from directory name
  name: string; // Migration name (underscores converted to spaces)
};

How It Works

1. Discovery: Scans the specified directory for migration folders matching the pattern /^[0-9]{5}_/
2. Validation: Ensures migration numbers are consecutive with no gaps
3. Tracking: Creates a migrations table in the target schema to track completed migrations
4. Execution: Runs migrations in order, skipping those already completed
5. Recording: Records each successful migration in the tracking table

Migration Table Schema

CREATE TABLE migrations (
  migration_id SERIAL PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  name TEXT
);

Error Handling

• Missing consecutive numbers: Throws error if migration numbers have gaps
• Schema creation: Automatically creates the target schema if it doesn't exist
• Transaction safety: Each migration runs in its own transaction
• Rollback: Failed migrations are automatically rolled back

Best Practices

1. Never modify completed migrations - Always create new migrations for changes
2. Use descriptive names - Make migration purposes clear from the directory name
3. Keep migrations small - One logical change per migration
4. Test migrations - Run against a copy of production data
5. Backup before running - Always backup production databases first

Development

Running Tests

pnpm test:run

Building

pnpm build:tsup

License

MIT

Contributing

Contributions are welcome! Please read our contributing guidelines before submitting PRs.

Credits

Originally forked from postgres-shift by Rasmus Porsager.