@aragornhq/pg-migration

> ⚔️ Production-grade CLI for managing PostgreSQL schema migrations with raw SQL, rollback, integrity tracking, strict mode, and GitHub automation.

---

🚀 Features

- ✅ Native PostgreSQL support using pg
- ✅ Fully typed CLI (TypeScript)
- ✅ Supports migration:create, migration:up, migration:down
- ✅ Rollback support using -- ROLLBACK BELOW -- separator
- ✅ SHA-256 hash tracking for applied migrations
- ✅ Multiple statements per migration (up and rollback) in a single file
- ✅ Optional config via pg-migration.json
- ✅ Migrations run inside a single transaction for atomicity

---

📦 Installation

``bash
npm install --save-dev @aragornhq/pg-migration
`

🔧 Setup

1. Set the PostgreSQL connection using environment variables:

`bash
PG_HOST=localhost
PG_PORT=5432
PG_DB=postgres
PG_USER=postgres
PG_PASSWORD=password
PG_USE_SSL=false
`

2. Specify where your migration files live via a pg-migration.json file:

`json
{
"path": "db/migrations"
}
`

Create the folder if it does not already exist.

🛠️ Usage

Run the CLI with npx or via an npm script. The executable name is pg-migrate:

`bash
npx pg-migrate [options]
`

$3

- migration:create --path= – create a timestamped migration file. The --path option is optional when the path is defined in pg-migration.json.
- migration:up --path= – apply all pending migrations.
- migration:dry-run --path= – run migrations in a transaction and roll back for validation.
- migration:down --file= --path= – roll back a single migration.

Each file should contain your SQL up section followed by -- ROLLBACK BELOW -- and the down section. Multiple statements are allowed in both sections and will be run sequentially.

`sql
-- 20250101_create_table.sql
CREATE TABLE example (id SERIAL PRIMARY KEY);
CREATE INDEX idx_example_id ON example (id);

-- ROLLBACK BELOW --
DROP INDEX idx_example_id;
DROP TABLE example;
`

Applied migrations are recorded in a migrations` table together with a SHA‑256 hash. If a hash changes, the run fails to prevent drift.