mirror of
https://github.com/webinstall/webi-installers.git
synced 2026-08-21 13:06:37 +00:00
79 lines
2.4 KiB
Markdown
79 lines
2.4 KiB
Markdown
---
|
|
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.
|