vim-ale/sql-migrate

title, homepage, tagline

title	homepage	tagline
sql-migrate	https://github.com/therootcompany/golib/tree/main/cmd/sql-migrate	sql-migrate: A lightweight, feature-branch-friendly SQL migrator.

To update or switch versions, run webi sql-migrate@stable (or @v2, @beta, etc).

Files

These are the files that are created and/or modified with this installer:

~/.config/envman/PATH.env
~/.local/bin/sql-migrate
~/.local/opt/sql-migrate-VERSION/bin/sql-migrate

<PROJECT-DIR>/migrations.log
<PROJECT-DIR>/sql/migrations/<yyyy-mm-dd>-<number>_<name>.<up|down>.sql

Cheat Sheet

sql-migrate is a lightweight migration tool that gets out of your way - it works with your existing SQL tools and allows working with distinct sets of migrations, such as is typical in feature branches.

How to use sql-migrate

Migration commands output a POSIX shell script which should be run with sh.

init, sync, up, down, list, status

# Initialize a migration directory
sql-migrate -d ./sql/migrations/ init --sql-command psql

# Export ENVs for your database
export PG_URL='postgres://user:pass@example.com/dbname?sslmode=require&sslnegotiation=direct'

# Create a new migration (auto-generates up/down pair with incrementing number)
sql-migrate -d ./sql/migrations/ create add-users-table

# SELECT and log existing migrations
sql-migrate -d ./sql/migrations/ sync | sh

# Run all pending migrations up
sql-migrate -d ./sql/migrations/ up | sh

# Run 3 migrations up
sql-migrate -d ./sql/migrations/ up 3 | sh

# Run 1 migration down (roll back)
sql-migrate -d ./sql/migrations/ down | sh

# Run 2 migrations down
sql-migrate -d ./sql/migrations/ down 2 | sh

# List all migrations
sql-migrate -d ./sql/migrations/ list

# See which migrations have been applied
sql-migrate -d ./sql/migrations/ status

Migration directory layout

Migrations follow the naming format <yyyy-mm-dd>-<number>_<name>.<up|down>.sql:

sql/
├── migrations.log                                # transaction log (auto-managed)
└── migrations/
    ├── 0001-01-01-001000_init-migrations.up.sql  # generated by 'init' (has config vars)
    ├── 2021-02-03-001000_init-app.up.sql
    ├── 2021-02-03-001000_init-app.down.sql
    ├── 2021-02-03-002000_add-products.up.sql
    ├── 2021-02-03-002000_add-products.down.sql
    └── 2021-02-03-003000_add-customers.up.sql

The initial 0001-01-01-001000_init-migrations.up.sql migration contains configuration variables:

-- migrations_log: ./sql/migrations.log
-- sql_command: psql "$PG_URL" -v ON_ERROR_STOP=on --no-align --tuples-only --file %s

Environment variables by database:

• PostgreSQL: PG_URL (auth url), PGOPTIONS (to set schema and other specific options)
• SQLite3: SQLITE_PATH
• SQL Server: SQLCMDSERVER, SQLCMDDATABASE, SQLCMDUSER, SQLCMDPASSWORD
• MySQL / MariaDB: MY_CNF (path to my.cnf, containing credentials)

Database client compatibility

The --sql-command flag tells sql-migrate how to talk to your database:

sql-migrate -d ./sql/migrations/ init --sql-command psql

The following clients are known and will have the correct options applied:

• psql (PostgreSQL)
• sqlite3
• sqlcmd (mssql / Microsoft SQL Server)
• mariadb / mysql

Since the migrations run via shell commands, you can make sql-migrate compatible with any SQL client by setting sql_command in migrations/0001-01-01-001000_init-migrations.up.sql.