mirror of
https://github.com/webinstall/webi-installers.git
synced 2026-08-20 20:46:30 +00:00
119 lines
3.4 KiB
Markdown
119 lines
3.4 KiB
Markdown
---
|
|
title: sql-migrate
|
|
homepage: https://github.com/therootcompany/golib/tree/main/cmd/sql-migrate
|
|
tagline: |
|
|
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:
|
|
|
|
```text
|
|
~/.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`
|
|
|
|
```sh
|
|
# 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`:
|
|
|
|
```text
|
|
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:
|
|
|
|
```sql
|
|
-- 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:
|
|
|
|
```sh
|
|
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`.
|