goose

README

goose

Goose is a database migration tool. Manage your database's evolution by creating incremental SQL files or Go functions.

Goals of this fork

github.com/cloudwan/goose is a fork of github.com/pressly/goose (indirectly bitbucket.org/liamstask/goose) with the following changes:

• No config files
• Default goose binary can migrate SQL files only
• Go migrations:
  • We dropped building Go migrations on-the-fly from .go source files
  • Instead, you can create your own goose binary, import github.com/cloudwan/goose package and run complex Go migrations with your own *sql.DB connection
  • Each Go migration function is called with *sql.Tx argument - within its own transaction
  • If a deployment model does not allow go compiler to be available from PATH it is possible to load go migrations from one or more precompiled plugins (*.so)
• The goose pkg is decoupled from the default binary:
  • goose pkg doesn't register any SQL drivers anymore (no driver panic() conflict within your codebase!)
  • goose pkg doesn't have any vendor dependencies anymore

Install

$ go get -u github.com/cloudwan/goose/cmd/goose

This will install the goose binary to your $GOPATH/bin directory.

Usage

Usage: goose [OPTIONS] DRIVER DBSTRING COMMAND

Examples:
    goose postgres "user=postgres dbname=postgres sslmode=disable" up
    goose mysql "user:password@/dbname" down
    goose sqlite3 ./foo.db status

Options:
  -dir string
    	directory with migration files (default ".")

Commands:
    up          Migrate the DB to the most recent version available
    down        Roll back the version by 1
    redo        Re-run the latest migration
    status      Dump the migration status for the current DB
    dbversion   Print the current version of the database
    create      Creates a blank migration template
    create-next Creates a blank migration template with next sequential version

create

Create a new Go migration.

$ goose create AddSomeColumns
$ goose: created db/migrations/20130106093224_AddSomeColumns.go

Edit the newly created script to define the behavior of your migration.

You can also create an SQL migration:

$ goose create AddSomeColumns sql
$ goose: created db/migrations/20130106093224_AddSomeColumns.sql

up

Apply all available migrations.

$ goose up
$ goose: migrating db environment 'development', current version: 0, target: 3
$ OK    001_basics.sql
$ OK    002_next.sql
$ OK    003_and_again.go

down

Roll back a single migration from the current version.

$ goose down
$ goose: migrating db environment 'development', current version: 3, target: 2
$ OK    003_and_again.go

redo

Roll back the most recently applied migration, then run it again.

$ goose redo
$ goose: migrating db environment 'development', current version: 3, target: 2
$ OK    003_and_again.go
$ goose: migrating db environment 'development', current version: 2, target: 3
$ OK    003_and_again.go

status

Print the status of all migrations:

$ goose status
$ goose: status for environment 'development'
$   Applied At                  Migration
$   =======================================
$   Sun Jan  6 11:25:03 2013 -- 001_basics.sql
$   Sun Jan  6 11:25:03 2013 -- 002_next.sql
$   Pending                  -- 003_and_again.go

Note: for MySQL parseTime flag must be enabled.

dbversion

Print the current version of the database:

$ goose dbversion
$ goose: dbversion 002

Migrations

goose supports migrations written in SQL or in Go.

SQL Migrations

A sample SQL migration looks like:

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

-- +goose Down
DROP TABLE post;

Notice the annotations in the comments. Any statements following -- +goose Up will be executed as part of a forward migration, and any statements following -- +goose 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 goose.

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

-- +goose Up
-- +goose 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;
-- +goose StatementEnd

Go Migrations

1. Create your own goose binary, see example
2. Import github.com/cloudwan/goose
3. Register your migration functions
4. Run goose command, ie. goose.Up(db *sql.DB, dir string)

A sample Go migration 00002_users_add_email.go file looks like:

package main

import (
	"database/sql"

	"github.com/cloudwan/goose"
)

func init() {
	goose.AddMigration(Up, Down)
}

func Up(tx *sql.Tx) error {
	_, err := tx.Exec("UPDATE users SET username='admin' WHERE username='root';")
	if err != nil {
		return err
	}
	return nil
}

func Down(tx *sql.Tx) error {
	_, err := tx.Exec("UPDATE users SET username='root' WHERE username='admin';")
	if err != nil {
		return err
	}
	return nil
}

Go Migrations from a precompiled plugins

1. Import github.com/cloudwan/goose
2. Load migration plugins with LoadMigrationPlugins
3. Run goose command, ie. goose.Up(db *sql.DB, dir string)

A migration plugin can be created from a go template generated by goose. Golang plugin system requires that the plugin's namespace is main. To compile a plugin one can use the following command:

go build -buildmode=plugin -o 002_next.so 002_next.sql

License

Licensed under MIT License

Documentation

Index

• Variables
• func AddMigration(up func(*sql.Tx) error, down func(*sql.Tx) error)
• func AddNamedMigration(filename string, up func(*sql.Tx) error, down func(*sql.Tx) error)
• func Create(db *sql.DB, dir, name, migrationType string, sequential bool) error
• func CreateMigration(name, migrationType, dir string, version string) (path string, err error)
• func Down(db *sql.DB, dir string) error
• func DownTo(db *sql.DB, dir string, version int64) error
• func EnsureDBVersion(db *sql.DB) (int64, error)
• func FinalizeMigration(tx *sql.Tx, direction bool, v int64) error
• func GetDBVersion(db *sql.DB) (int64, error)
• func LoadMigrationPlugins(dirpath string) error
• func NumericComponent(name string) (int64, error)
• func Redo(db *sql.DB, dir string) error
• func Run(command string, db *sql.DB, dir string, args ...string) error
• func SetDialect(d string) error
• func Status(db *sql.DB, dir string) error
• func Up(db *sql.DB, dir string) error
• func UpByOne(db *sql.DB, dir string) error
• func UpTo(db *sql.DB, dir string, version int64) error
• func Version(db *sql.DB, dir string) error
• type Migration
  • func LastMigration(dirpath string) (*Migration, error)
  • func (m *Migration) Down(db *sql.DB) error
  • func (m *Migration) String() string
  • func (m *Migration) Up(db *sql.DB) error
• type MigrationRecord
• type Migrations
  • func CollectMigrations(dirpath string, current, target int64) (Migrations, error)
  • func (ms Migrations) Current(current int64) (*Migration, error)
  • func (ms Migrations) Last() (*Migration, error)
  • func (ms Migrations) Len() int
  • func (ms Migrations) Less(i, j int) bool
  • func (ms Migrations) Next(current int64) (*Migration, error)
  • func (ms Migrations) Previous(current int64) (*Migration, error)
  • func (ms Migrations) String() string
  • func (ms Migrations) Swap(i, j int)
• type MySqlDialect
• type PostgresDialect
• type SqlDialect
  • func GetDialect() SqlDialect
• type Sqlite3Dialect
• type TemplateParams

Variables

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

	MaxVersion int64 = 9223372036854775807 // max(int64)

)

Functions

func AddMigration

func AddMigration(up func(*sql.Tx) error, down func(*sql.Tx) error)

func AddNamedMigration

func AddNamedMigration(filename string, up func(*sql.Tx) error, down func(*sql.Tx) error)

func Create

func Create(db *sql.DB, dir, name, migrationType string, sequential bool) error

Create writes a new blank migration file.

func CreateMigration

func CreateMigration(name, migrationType, dir string, version string) (path string, err error)

func Down

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

func DownTo

func DownTo(db *sql.DB, dir string, version int64) error

func EnsureDBVersion

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

retrieve the current version for this DB. Create and initialize the DB version table if it doesn't exist.

func FinalizeMigration

func FinalizeMigration(tx *sql.Tx, direction bool, v int64) error

Update the version table for the given migration, and finalize the transaction.

func GetDBVersion

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

wrapper for EnsureDBVersion for callers that don't already have their own DB instance

func LoadMigrationPlugins

func LoadMigrationPlugins(dirpath string) error

LoadMigrationPlugins loads all the valid looking precompiled migration libraries in the migrations folder and go func registry, and key them by version. Registration is done automatically by shared library init func.

func NumericComponent

func NumericComponent(name string) (int64, error)

look for migration scripts with names in the form:

XXX_descriptivename.ext

where XXX specifies the version number and ext specifies the type of migration

func Redo

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

func Run

func Run(command string, db *sql.DB, dir string, args ...string) error

func SetDialect

func SetDialect(d string) error

func Status

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

func Up

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

func UpByOne

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

func UpTo

func UpTo(db *sql.DB, dir string, version int64) error

func Version

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

Types

type Migration

type Migration struct {
	Version  int64
	Next     int64               // next version, or -1 if none
	Previous int64               // previous version, -1 if none
	Source   string              // path to .sql script
	UpFn     func(*sql.Tx) error // Up go migration function
	DownFn   func(*sql.Tx) error // Down go migration function
}

func LastMigration

func LastMigration(dirpath string) (*Migration, error)

lastVersion returns version of last migration present in dirpath

func (*Migration) Down

func (m *Migration) Down(db *sql.DB) error

func (*Migration) String

func (m *Migration) String() string

func (*Migration) Up

func (m *Migration) Up(db *sql.DB) error

type MigrationRecord

type MigrationRecord struct {
	VersionId int64
	TStamp    time.Time
	IsApplied bool // was this a result of up() or down()
}

type Migrations

type Migrations []*Migration

func CollectMigrations

func CollectMigrations(dirpath string, current, target int64) (Migrations, error)

CollectMigrations returns all the valid looking migration scripts in the migrations folder and go func registry, and key them by version.

func (Migrations) Current

func (ms Migrations) Current(current int64) (*Migration, error)

func (Migrations) Last

func (ms Migrations) Last() (*Migration, error)

func (Migrations) Len

func (ms Migrations) Len() int

helpers so we can use pkg sort

func (Migrations) Less

func (ms Migrations) Less(i, j int) bool

func (Migrations) Next

func (ms Migrations) Next(current int64) (*Migration, error)

func (Migrations) Previous

func (ms Migrations) Previous(current int64) (*Migration, error)

func (Migrations) String

func (ms Migrations) String() string

func (Migrations) Swap

func (ms Migrations) Swap(i, j int)

type MySqlDialect

type MySqlDialect struct{}

type PostgresDialect

type PostgresDialect struct{}

type SqlDialect

type SqlDialect interface {
	// contains filtered or unexported methods
}

SqlDialect abstracts the details of specific SQL dialects for goose's few SQL specific statements

func GetDialect

func GetDialect() SqlDialect

type Sqlite3Dialect

type Sqlite3Dialect struct{}

type TemplateParams

type TemplateParams struct {
	Version, Name string
}