Alter

Alter is a CLI tool for managing incremental changes to PostgreSQL.

Quick Start:

1. Install Alter (more)
2. Install PostgreSQL client tools (e.g. psql) (more)
3. Run alter init from your project root

Why Use Alter?

Database migrations are essential for:

• Version Control: Track database changes alongside code changes
• Collaboration: Multiple developers can safely evolve the schema
• Deployment Safety: Apply changes incrementally across environments
• Rollback Capability: Safely revert problematic changes
• Audit Trail: Maintain a complete history of database evolution (ISO/IEC 27001/27002, SOX, etc.)
• Environment Consistency: Ensure dev, staging, and production schemas stay in sync

Alter's migration system is designed around these principles:

• Pure SQL: Migrations are written in standard SQL, not abstracted DSLs
• Incremental: Changes are applied one migration at a time
• Atomic: Each migration succeeds completely or fails completely
• Reversible: Every migration can be rolled back
• Zero Dependency: Uses native psql, not some janky language-specific driver
• Committed: Migrations are version controlled with your application code

This approach ensures that your database schema is:

• Predictable - Same migrations produce same results
• Reviewable - Database changes go through code review
• Deployable - Automated deployment can apply pending migrations
• Recoverable - Problems can be rolled back safely

Developer Workflow

Here's how you can work with Alter:

1. While developing a feature, use alter create to generate SQL for your dev database.
2. Commit the SQL files along with your code changes.
3. Once merged to main, use alter up or psql to apply all pending migrations to staging/production databases.

Install Alter

# This will install the `alter` binary to the npm global bin path.
npm install -g @thesmart/alter
which alter && echo "Alter has been installed here: $(npm prefix -g)/bin/alter"

Install PostgreSQL Client Tools

Alter depends on psql and pg_dump that ship with PostgreSQL. You should try to match versions with your production DB.

MacOS

I prefer to use Postgres.app, which has an installer and tray app.

echo 'export PG_INSTALL="/Applications/Postgres.app/Contents/Versions/latest"' >> ~/.zshrc
echo 'export PATH="$PG_INSTALL/bin:$PATH"' >> ~/.zshrc

You can also use brew:

# For Apple Silicon (M1/M2/M3/M4 etc.)
brew install libpq
echo 'export LIBPQ_INSTALL="/opt/homebrew/opt/libpq/bin"' >> ~/.zshrc
echo 'export PATH="$LIBPQ_INSTALL/bin:$PATH"' >> ~/.zshrc

Debian/Ubuntu

sudo apt-get update
sudo apt-get install -y postgresql-client

Red Hat/CentOS/Fedora

# RHEL/CentOS 8 or later, x86
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql-client

Arch Linux

sudo pacman -S postgresql-libs

Configuration (.alter.env)

Configuration is set using environment variables or (recommended) via configuration file in the current working directory called .alter.env. A default file is created for you by running alter init.

Here are the most important variables:

• PGDATABASE - Optional (Default: $USER). Database name to connect to.
• PGHOST - Optional (Default: localhost). Database server hostname.
• PGPORT - Optional (Default: 5432). Database server port.
• PGUSER - Optional (Default: $USER). Username for authentication.
• PGPASSWORD - Optional. Plain-text password for the PGUSER and PGDATABASE.
• PGPASSFILE - Optional. Path to password file.

Here are some additional variables:

• PGAPPNAME - Optional (Default: alter-cli). Application name for debugging queries via pg_stat_activity.
• PGSSLMODE - Optional (Default: disable). SSL connection control. See also PGSSLCERT, PGSSLKEY.
• PGCONNECT_TIMEOUT - Optional (Default: 0). Connection timeout in seconds.
• ALTER_ENV_PATH - Optional (Default $PWD/.alter.env) Target a different .env file.
• ALTER_SQL_PATH - Optional (Default: db). Directory for SQL migration files, either absolute or relative to current working directory.
• ALTER_TABLE - Optional (Default: alter_schema_history). The table name for recording applied migrations.
• NO_COLOR - Optional (Default: 0). Set to "1" to disable ANSI color output.
• VERBOSE - Optional (Default: 0). Set to "1" to enable verbose debug output.

Commands

Alter provides multiple commands for schema management:

COMMANDS:
    init                 Setup the project folder and database to work with Alter.
    create DESCRIPTION   Creates new migration with a UTC time-based id.
    up                   Migrate the DB to the most recent version available.
    up-one               Migrate the DB up by 1.
    up-to VERSION        Migrate the DB to a specific VERSION (inclusive).
    down                 Roll back that last applied version by 1.
    down-to VERSION      Roll back to a specific VERSION (inclusive).
    history              Print the database's change history.
    history-json         Print the database's change history in JSON.
    pending              Print all pending migrations, exit 0 if there are none or exit 70 if there any are pending.
    pending-json         Print all pending migrations in JSON, exit 0 if there are none or exit 70 if there any are pending.

OPTIONS:
  -h
  --help
        print help
  -v    enable verbose mode
  --version
        print version

init

Setup the project folder and database to work with Alter. This is designed to work with both new and existing database. Here's what it does:

1. Create .alter.env using defaults (unless it already exists)
2. Create a ./db directory (unless it already exists)
3. Create the database (unless it already exists)
   • If the database was created, apply ./db/_baseline.sql (if it exists)
   • If no _baseline.sql file exists, create one using the database's schema
4. Create the alter_schema_history table (i.e. ALTER_TABLE) (if it doesn't already exist)

$ alter init
Success 🎉
  Database:              my-database
  History table:         alter_schema_history
  Baseline schema file:  $USER/project/db/_baseline.sql

create

Create a new SQL migration.

$ alter create Add some column
Created new migration: db/2025-01-01-1159-add-some-column

up

Apply all available migrations.

$ alter up
OK    2025-01-01-1159-adds-user-authentication-feature/up.sql
OK    2025-01-03-1821-adds-phone-to-user-authentication/up.sql
OK    2025-03-18-0807-adds-tfa-to-user-authentication/up.sql

up-to

Migrate up to a specific version (inclusive).

$ alter up-to 2025-01-03
OK    2025-01-01-1159-adds-user-authentication-feature/up.sql
OK    2025-01-03-1821-adds-phone-to-user-authentication/up.sql
SKIP  2025-03-18-0807-adds-tfa-to-user-authentication/up.sql

up-by-one

Migrate up a single migration from the current version

$ alter up-by-one
OK    2025-01-01-1159-adds-user-authentication-feature/up.sql
SKIP  2025-01-03-1821-adds-phone-to-user-authentication/up.sql
SKIP  2025-03-18-0807-adds-tfa-to-user-authentication/up.sql

down

Roll back a single migration from the current version.

$ alter down
REV   2025-03-18-0807-adds-tfa-to-user-authentication/down.sql

down-to

Roll back migrations to a specific version (inclusive).

$ alter down-to 2025-01-03
REV   2025-03-18-0807-adds-tfa-to-user-authentication/down.sql
REV   2025-01-03-1821-adds-phone-to-user-authentication/down.sql
SKIP  2025-01-01-1159-adds-user-authentication-feature/down.sql

history

Print the history of previously applied migrations.

$ alter history
Wed Jan 01 2025      01:32:01.123
    2025-01-01-1159-adds-user-authentication-feature
Fri Jan 03 2025      19:12:00.736
    2025-01-03-1821-adds-phone-to-user-authentication
Thu Mar 21 2025      03:18:23.023
    2025-03-18-0807-adds-tfa-to-user-authentication

pending

Print all pending migrations. A pending migration is any local sql file not applied to the database. This will exit code 0 if there are no pending migrations or exit code 70 if there are any pending migrations needing to be run.

$ alter pending
2025-01-01-1159-adds-user-authentication-feature
2025-01-03-1821-adds-phone-to-user-authentication
2025-03-18-0807-adds-tfa-to-user-authentication

$ echo $?
70

$ alter up 1> /dev/null
$ alter pending
$ echo $?
0

License

Licensed under a custom Source Available License.

Here is a TL;DR summary:

• You can use, copy, modify, and distribute.
• No offering as a product or SaaS.
• Keep license and copyright in all copies.
• No warranty; license ends on violation.
• Project contributions constitute as an agreement, you grant a license back to the project.