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

This is a fork of https://bitbucket.org/liamstask/goose with the following changes:

• No config files
• Default goose binary can migrate SQL files only
• We dropped building .go files on-the-fly in favor of the below
• Import github.com/pressly/goose package
  • To run complex Go migrations with your own *sql.DB connection via *sql.Tx transactions
  • The pkg doesn't register any SQL drivers anymore (no panic() driver conflicts with your codebase!)
  • The pkg doesn't have any vendor dependencies anymore

Install

$ go get -u github.com/pressly/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

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

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

Import github.com/pressly/goose from your own project (see example), register migration functions and run goose command (ie. goose.Up(db *sql.DB, dir string)).

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

package migrations

import (
	"database/sql"

	"github.com/pressly/goose"
)

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

func Up(tx *sql.Tx) error {
	_, err := tx.Query("ALTER TABLE users ADD COLUMN email text DEFAULT '' NOT NULL;")
	if err != nil {
		return err
	}
	return nil
}

func Down(tx *sql.Tx) error {
	_, err := tx.Query("ALTER TABLE users DROP COLUMN email;")
	if err != nil {
		return err
	}
	return nil
}

License

Licensed under MIT License

Documentation

Index

• Variables
• func AddMigration(up func(*sql.Tx) error, down func(*sql.Tx) error)
• func Create(db *sql.DB, dir, name, migrationType string) error
• func CreateMigration(name, migrationType, dir string, t time.Time) (path string, err error)
• func NumericComponent(name string) (int64, error)
• func Run(command string, db *sql.DB, dir string, args ...string) error
• func SetDialect(d string) error
• type Client
  • func New(tableName string, dialect SqlDialect) *Client
  • func (c *Client) AddMigration(up func(*sql.Tx) error, down func(*sql.Tx) error)
  • func (c *Client) Down(db *sql.DB, dir string) error
  • func (c *Client) FinalizeMigration(tx *sql.Tx, direction bool, v int64) error
  • func (c *Client) GetDBVersion(db *sql.DB) (int64, error)
  • func (c *Client) Redo(db *sql.DB, dir string) error
  • func (c *Client) SetDialect(d string) error
  • func (c *Client) Status(db *sql.DB, dir string) error
  • func (c *Client) Up(db *sql.DB, dir string) error
  • func (c *Client) UpByOne(db *sql.DB, dir string) error
  • func (c *Client) Version(db *sql.DB, dir string) error
• type Migration
  • func (m *Migration) String() string
• type MigrationRecord
• type Migrations
  • 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) String() string
  • func (ms Migrations) Swap(i, j int)
• type MySqlDialect
• type PostgresDialect
• type SqlDialect
  • func GetDialect() SqlDialect
• type Sqlite3Dialect

Variables

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

Functions

func AddMigration

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

AddMigration exists for legacy support of the package global use of Goose. Calling the AddMigration method on a Goose struct is the preferred method.

func Create

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

Create writes a new blank migration file.

func CreateMigration

func CreateMigration(name, migrationType, dir string, t time.Time) (path string, err error)

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 Run

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

func SetDialect

func SetDialect(d string) error

Types

type Client

type Client struct {
	// TableName is the name of the table used to store migration status
	// for this client.
	TableName string
	// Dialect is the SqlDialect to use.
	Dialect SqlDialect
	// Migrations is the list of migrations.
	Migrations Migrations
}

Client stores the migration state and preferences. Prefer interacting with the Goose API through a Client struct created with New rather than using the global Client and functions.

func New

func New(tableName string, dialect SqlDialect) *Client

func (*Client) AddMigration

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

AddMigration adds a new go migration to the goose struct.

func (*Client) Down

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

func (*Client) FinalizeMigration

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

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

func (*Client) GetDBVersion

func (c *Client) GetDBVersion(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 (*Client) Redo

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

func (*Client) SetDialect

func (c *Client) SetDialect(d string) error

func (*Client) Status

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

func (*Client) Up

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

func (*Client) UpByOne

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

func (*Client) Version

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

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 (*Migration) String

func (m *Migration) String() string

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 (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) 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{}