mirror of
https://github.com/therootcompany/golib.git
synced 2026-01-27 23:18:05 +00:00
125 lines
3.3 KiB
Markdown
125 lines
3.3 KiB
Markdown
# [sql-migrate](https://github.com/therootcompany/golib/tree/main/cmd/sql-migrate)
|
|
|
|
> A feature-branch-friendly SQL migrator
|
|
|
|
```sh
|
|
sql-migrate <command> [-d sqldir] [-f logfile] [args]
|
|
```
|
|
|
|
## Features
|
|
|
|
- Locally-stored Migrations (not in DB)
|
|
- Migration log can be hand edited to easily roll forwards or backwards
|
|
- Migrations are simple shell scripts
|
|
- Works with any database (just change the command)
|
|
|
|
## Overview
|
|
|
|
- Migration Directory
|
|
- Migration Log
|
|
- Migrations Files (up, down)
|
|
|
|
### Migration Directory
|
|
|
|
Lexicographically-sortable files in the format `<sequence>_<description>.<up|down>.sql`:
|
|
|
|
```text
|
|
migrations/
|
|
├── 2021-02-03-001000_init.up.sql
|
|
├── 2021-02-03-001000_init.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
|
|
└── 2021-02-03-003000_add-customers.down.sql
|
|
```
|
|
|
|
### Migration Log
|
|
|
|
A simple list of migration names.
|
|
|
|
`./sql/migrations.log`:
|
|
|
|
```text
|
|
# command: psql "$PG_URL" < %s
|
|
# batch: 1
|
|
2021-02-03-001000_init
|
|
2021-02-03-002000_add-products
|
|
|
|
# batch: 2
|
|
2021-02-03-003000_add-customers
|
|
```
|
|
|
|
Change `command` to work with any database.
|
|
|
|
### Migration Files
|
|
|
|
Simply SQL. Comments are generated for easy finding with `grep`.
|
|
|
|
`2021-02-03-002000_add-products.up.sql`:
|
|
|
|
```sh
|
|
-- add-products (up)
|
|
CREATE TABLE "products" (
|
|
slug VARCHAR(127) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
price INTEGER NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
revoked_at TIMESTAMP,
|
|
PRIMARY KEY ("slug")
|
|
);
|
|
```
|
|
|
|
`2021-02-03-002000_add-products.down.sql`:
|
|
|
|
```sh
|
|
-- add-products (down)
|
|
DROP TABLE IF EXISTS "products";
|
|
```
|
|
|
|
## Usage
|
|
|
|
```sh
|
|
sql-migrate init -d ./sql/migrations/ -f ./sql/migrations.log
|
|
sql-migrate create <kebab-case-description>
|
|
sql-migrate status
|
|
sql-migrate up
|
|
sql-migrate down
|
|
sql-migrate list
|
|
```
|
|
|
|
See `sql-migrate help` for details.
|
|
|
|
<!-- Generated by running `go run . --help >> README.md` and then trimming a bit -->
|
|
|
|
```text
|
|
COMMANDS
|
|
init - inits sql dir and migration file, adding or updating the
|
|
default command
|
|
create - creates a new, canonically-named up/down file pair in the
|
|
migrations directory
|
|
status - shows the same output as if processing a forward-migration
|
|
for the most recent batch
|
|
up - processes the first 'up' migration file missing from the
|
|
migration state
|
|
down - rolls back the latest entry of the latest migration batch
|
|
(the whole batch if just one)
|
|
list - lists migrations
|
|
|
|
OPTIONS
|
|
-d <migrations directory> default: ./sql/migrations/
|
|
-f <migration state file> default: ./sql/migrations.log
|
|
|
|
The migration state file contains the client command template (defaults to
|
|
'psql "$PG_URL" < %s'), followed by a list of batches identified by a batch
|
|
number comment and a list of migration file basenames and optional user
|
|
comments, such as:
|
|
# command: psql "$PG_URL" < %s
|
|
# batch: 1
|
|
2020-01-01-1000_init.up.sql # does a lot
|
|
2020-01-01-1100_add-customer-tables.up.sql
|
|
# batch: 2
|
|
# We did id! Finally!
|
|
2020-01-01-2000_add-ALL-THE-TABLES.up.sql
|
|
```
|