mig

README

mig

mig is a database migration tool. Manage your database's evolution by creating incremental SQL files.

Goals of this fork

This is a restructured and modified fork of https://github.com/pressly/goose which includes many necessary bug fixes and general improvements to better suit the Go abcweb framework https://github.com/nullbio/abcweb -- although feel free to use this migration tool as a standalone or in your own projects even if you do not use abcweb, it by no means requires it.

Install

$ go get -u github.com/nullbio/mig/...

Usage

mig is a database migration tool for Postgres and MySQL.

Usage:
  mig [command]

Examples:
mig up postgres "user=postgres dbname=postgres sslmode=disable"
mig down mysql "user:password@/dbname"
mig create add_users

Available Commands:
  create      Create a blank migration template
  down        Roll back the version by one
  downall     Roll back all migrations
  help        Help about any command
  redo        Down then up the latest migration
  redoall     Down then up all migrations
  status      Dump the migration status for the database
  up          Migrate the database to the most recent version available
  upone       Migrate the database by one version
  version     Print the current version of the database

Flags:
      --version   Print the mig tool version

Use "mig [command] --help" for more information about a command.

Note: If you're using ABCWeb the mig commands are built into the abcweb tool. See abcweb --help for usage.

Supported Databases

mig supports MySQL and Postgres. The drivers used are:

https://github.com/go-sql-driver/mysql

https://github.com/lib/pq

See these drivers for details on the format of their connection strings.

Couple of example runs

create

Create a new SQL migration.

$ mig create add_users
$ Created db/migrations/20130106093224_add_users.sql

Edit the newly created script to define the behavior of your migration. Your SQL statements should go below the Up and Down comments.

An example command run:

up

Apply all available migrations.

$ mig up postgres "user=username password=password dbname=database"
$ Success   20170314220650_add_dogs.sql
$ Success   20170314221501_add_cats.sql
$ Success   2 migrations

Migrations

A sample SQL migration looks like:

-- +mig Up
CREATE TABLE post (
    id int NOT NULL,
    title text,
    body text,
    PRIMARY KEY(id)
);

-- +mig Down
DROP TABLE post;

Notice the annotations in the comments. Any statements following -- +mig Up will be executed as part of a forward migration, and any statements following -- +mig Down will be executed as part of a rollback.

By default, SQL statements are delimited by semicolons - in fact, query statements must end with a semicolon to be properly recognized by mig.

More complex statements (PL/pgSQL) that have semicolons within them must be annotated with -- +mig StatementBegin and -- +mig StatementEnd to be properly recognized. For example:

-- +mig Up
-- +mig StatementBegin
CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
returns void AS $$
DECLARE
  create_query text;
BEGIN
  FOR create_query IN SELECT
      'CREATE TABLE IF NOT EXISTS histories_'
      || TO_CHAR( d, 'YYYY_MM' )
      || ' ( CHECK( created_at >= timestamp '''
      || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
      || ''' AND created_at < timestamp '''
      || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
      || ''' ) ) inherits ( histories );'
    FROM generate_series( $1, $2, '1 month' ) AS d
  LOOP
    EXECUTE create_query;
  END LOOP;  -- LOOP END
END;         -- FUNCTION END
$$
language plpgsql;
-- +mig StatementEnd

Library functions

// Global io.Writer variable that can be changed to get incremental success 
// messages from function calls that process more than one migration,
// for example Up and DownAll. Defaults to ioutil.Discard.
var mig.Log

// Create a templated migration file in dir
mig.Create(name, dir string) (name string, err error)

// Down rolls back the version by one
mig.Down(driver, conn, dir string) (name string, err error)

// DownAll rolls back all migrations.
// Logs success messages to global writer variable Log.
mig.DownAll(driver, conn, dir string) (count int, err error)

// Up migrates to the highest version available
mig.Up(driver, conn, dir string) (count int, err error)

// UpOne migrates one version
mig.UpOne(driver, conn, dir string) (name string, err error)

// Redo re-runs the latest migration.
mig.Redo(driver, conn, dir string) (name string, err error)

// Return the status of each migration
mig.Status(driver, conn, dir string) (status, error)

// Return the current migration version
mig.Version(driver, conn string) (version int64, err error)

Documentation

Index

• Variables
• func Create(name, dir string) (string, error)
• func Down(driver, conn, dir string) (name string, err error)
• func DownAll(driver, conn, dir string) (int, error)
• func DownAllDB(db *sql.DB, dir string) (int, error)
• func DownDB(db *sql.DB, dir string) (name string, err error)
• func IsNoMigrationError(err error) bool
• func Redo(driver, conn, dir string) (string, error)
• func RedoDB(db *sql.DB, dir string) (string, error)
• func SetDialect(d string) error
• func Status(driver, conn, dir string) (status, error)
• func StatusDB(db *sql.DB, dir string) (status, error)
• func Up(driver, conn, dir string) (int, error)
• func UpDB(db *sql.DB, dir string) (int, error)
• func UpOne(driver, conn, dir string) (name string, err error)
• func UpOneDB(db *sql.DB, dir string) (name string, err error)
• func Version(driver, conn string) (int64, error)
• func VersionDB(db *sql.DB) (int64, error)

Variables

var (
	ErrNoCurrentVersion = errors.New("no current version found")
	ErrNoNextVersion    = errors.New("no next version found")
)

var Log io.Writer

Functions

func Create

func Create(name, dir string) (string, error)

Create a templated migration file in dir

func Down

func Down(driver, conn, dir string) (name string, err error)

Down rolls back the version by one

func DownAll

func DownAll(driver, conn, dir string) (int, error)

DownAll rolls back all migrations. Logs success messages to global writer variable Log.

func DownAllDB

func DownAllDB(db *sql.DB, dir string) (int, error)

DownAllDB rolls back all migrations. Logs success messages to global writer variable Log. Expects SetDialect to be called beforehand.

func DownDB

func DownDB(db *sql.DB, dir string) (name string, err error)

DownDB rolls back the version by one Expects SetDialect to be called beforehand.

func IsNoMigrationError

func IsNoMigrationError(err error) bool

IsNoMigrationError returns true if the error type is of errNoMigration, indicating that there is no migration to run

func Redo

func Redo(driver, conn, dir string) (string, error)

Redo re-runs the latest migration.

func RedoDB

func RedoDB(db *sql.DB, dir string) (string, error)

RedoDB re-runs the latest migration. Expects SetDialect to be called beforehand.

func SetDialect

func SetDialect(d string) error

SetDialect sets the current driver dialect for all future calls to the library.

func Status

func Status(driver, conn, dir string) (status, error)

Status returns the status of each migration

func StatusDB

func StatusDB(db *sql.DB, dir string) (status, error)

StatusDB returns the status of each migration Expects SetDialect to be called beforehand

func Up

func Up(driver, conn, dir string) (int, error)

Up migrates to the highest version available

func UpDB

func UpDB(db *sql.DB, dir string) (int, error)

UpDB migrates to the highest version available Expects SetDialect to be called beforehand.

func UpOne

func UpOne(driver, conn, dir string) (name string, err error)

UpOne migrates one version

func UpOneDB

func UpOneDB(db *sql.DB, dir string) (name string, err error)

UpOneDB migrates one version Expects SetDialect to be called beforehand.

func Version

func Version(driver, conn string) (int64, error)

Version returns the current migration version

func VersionDB

func VersionDB(db *sql.DB) (int64, error)

VersionDB returns the current migration version Expects SetDialect to be called beforehand