golib/cmd/sql-migrate

sql-migrate

A feature-branch-friendly SQL migrator

# sql-migrate [-d sqldir] <command> [args]
sql-migrate -d ./sql/migrations/ init --sql-command psql
sql-migrate -d ./sql/migrations/ up 3
sql-migrate -d ./sql/migrations/ down 2

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:

migrations/
├── _migrations.sql
├── 0001-01-01-001000_init-migrations.sql
├── 2021-02-03-001000_init-app.up.sql
├── 2021-02-03-001000_init-app.down.sql
├── 2021-02-03-001000_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:

0001-01-01-001000_init-migrations
2021-02-03-001000_init-app
2021-02-03-002000_add-products
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:

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

-- add-products (down)
DROP TABLE IF EXISTS "products";

Usage

sql-migrate -d ./sql/migrations/ init --sql-command <psql|mariadb|mysql> --migrations-log ./sql/migrations.log
sql-migrate -d ./sql/migrations/ create <kebab-case-description>
sql-migrate -d ./sql/migrations/ status
sql-migrate -d ./sql/migrations/ up 99
sql-migrate -d ./sql/migrations/ down 1
sql-migrate -d ./sql/migrations/ list

See sql-migrate help for details.

COMMANDS
   init          - creates migrations directory, initial migration, log file,
	                and query for migrations
   create        - creates a new, canonically-named up/down file pair in the
                   migrations directory, with corresponding insert
   status        - shows the same output as if processing a forward-migration
   up [n]        - create a script to run pending migrations (ALL by default)
   down [n]      - create a script to roll back migrations (ONE by default)
   list          - lists migrations

OPTIONS
   -d <migrations directory>  default: ./sql/migrations/
   --help                     show command-specific help

NOTES
   Migrations files are in the following format:
      <yyyy-mm-dd>-<number>_<name>.<up|down>.sql
      2020-01-01-1000_init-app.up.sql

	The initial migration file contains configuration variables:
		-- migrations_log: ./sql/migrations.log
		-- sql_command: psql "$PG_URL" -v ON_ERROR_STOP=on --no-align --file %s

	The log is generated on each migration file contains a list of all migrations:
      0001-01-01-001000_migrations.up.sql
      2020-12-31-001000_init-app.up.sql
      2020-12-31-001100_add-customer-tables.up.sql
      2020-12-31-002000_add-ALL-THE-TABLES.up.sql

   The 'create' generates an up/down pair of files using the current date and
      the number 1000. If either file exists, the number is incremented by 1000 and
      tried again.