@thesmart/alter

Alter

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

Quick Start:

• Install Alter (more)
• Install PostgreSQL client tools (e.g. psql) (more)
• 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:

• While developing a feature, use alter create to generate SQL for your dev database.
• Commit the SQL files along with your code changes.
• 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:

• Create .alter.env using defaults (unless it already exists)
• Create a ./db directory (unless it already exists)
• 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
• 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.