SQL Migrate

sql-migrate (CLI)

The agent skill is embedded in the help output.

Run sql-migrate --help use its output to guide usage decisions.

The up, down, and sync subcommands produce POSIX shell scripts - pipe them to sh to run.

Migration 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'
    ├── 2021-02-03-001000_init-app.up.sql
    ├── 2021-02-03-001000_init-app.down.sql
    └── ...

The initial migration file contains configuration variables:

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

Migration file structure

The migration files contain their own management, including a randomly-generated id:

-- change_me (up)
SELECT 'place your UP migration here';

-- leave this as the last line
INSERT INTO _migrations (name, id) VALUES ('2026-05-27-002000_change_me', 'e22295e5');

Environment variables by database

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

SQL client extensibility

The --sql-command flag tells sql-migrate how to talk to your database. Known clients (psql, sqlite3, sqlcmd, mariadb/mysql) have correct options applied automatically. Since migrations run via shell commands, you can make sql-migrate compatible with any SQL client by setting sql_command in the init migration file.

sqlmigrate (Go module)

See go doc for each independent module (golib is a monorepo):

• github.com/therootcompany/golib/database/sqlmigrate/v2
  • github.com/therootcompany/golib/database/sqlmigrate/pgmigrate
  • github.com/therootcompany/golib/database/sqlmigrate/litemigrate
  • github.com/therootcompany/golib/database/sqlmigrate/msmigrate
  • github.com/therootcompany/golib/database/sqlmigrate/mymigrate

DO NOT search the parent golib module.