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