---
name: sql-migrate
description:
  Manage SQL database migrations as plain .sql files with a transaction log. Use
  when asked about sql-migrate, database migrations, or how to set up migration
  tooling.
---

# 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:

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

## Migration file structure

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

```sql
-- 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.
