sql-migrate

SQL Schema migration tool forGo. Based ongorp andgoose.

Features

• Usable as a CLI tool or as a library
• Supports SQLite, PostgreSQL, MySQL, MSSQL and Oracle databases (throughgorp)
• Can embed migrations into your application
• Migrations are defined with SQL for full flexibility
• Atomic migrations
• Up/down migrations to allow rollback
• Supports multiple database types in one project
• Works great with other libraries such assqlx
• Supported on go1.13+

Installation

To install the library and command line program, use the following:

go get -v github.com/rubenv/sql-migrate/...

For Go version from 1.18, use:

go install github.com/rubenv/sql-migrate/...@latest

Usage

As a standalone tool

$ sql-migrate --helpusage: sql-migrate [--version] [--help] <command> [<args>]Available commands are:    down      Undo a database migration    new       Create a new migration    redo      Reapply the last migration    status    Show migration status    up        Migrates the database to the most recent version available

Each command requires a configuration file (which defaults todbconfig.yml, but can be specified with the-config flag). This config file should specify one or more environments:

development:  dialect: sqlite3  datasource: test.db  dir: migrations/sqlite3production:  dialect: postgres  datasource: dbname=myapp sslmode=disable  dir: migrations/postgres  table: migrations

(See more examples for different set upshere)

Also one can obtain env variables in datasource field viaos.ExpandEnv embedded call for the field.This may be useful if one doesn't want to store credentials in file:

production:  dialect: postgres  datasource: host=prodhost dbname=proddb user=${DB_USER} password=${DB_PASSWORD} sslmode=require  dir: migrations  table: migrations

Thetable setting is optional and will default togorp_migrations.

The environment that will be used can be specified with the-env flag (defaults todevelopment).

Use the--help flag in combination with any of the commands to get an overview of its usage:

$ sql-migrate up --helpUsage: sql-migrate up [options] ...  Migrates the database to the most recent version available.Options:  -config=dbconfig.yml   Configuration file to use.  -env="development"     Environment.  -limit=0               Limit the number of migrations (0 = unlimited).  -version               Run migrate up to a specific version, eg: the version number of migration 1_initial.sql is 1.  -dryrun                Don't apply migrations, just print them.

Thenew command creates a new empty migration template using the following pattern<current time>-<name>.sql.

Theup command applies all available migrations. By contrast,down will only apply one migration by default. This behavior can be changed for both by using the-limit parameter, and the-version parameter. Note-version has higher priority than-limit if you try to use them both.

Theredo command will unapply the last migration and reapply it. This is useful during development, when you're writing migrations.

Use thestatus command to see the state of the applied migrations:

$ sql-migrate status+---------------+-----------------------------------------+|   MIGRATION   |                 APPLIED                 |+---------------+-----------------------------------------+| 1_initial.sql | 2014-09-13 08:19:06.788354925 +0000 UTC || 2_record.sql  | no                                      |+---------------+-----------------------------------------+

Running Test Integrations

You can see how to run setups for different setups by executing the.sh files intest-integration

# Run mysql-env.sh example (you need to be in the project root directory)./test-integration/mysql-env.sh

MySQL Caveat

If you are using MySQL, you must append?parseTime=true to thedatasource configuration. For example:

production:  dialect: mysql  datasource: root@/dbname?parseTime=true  dir: migrations/mysql  table: migrations

Seehere for more information.

Oracle (oci8)

Oracle Driver isoci8, it is not pure Go code and relies on Oracle Office Client (Instant Client), more detailed information is in theoci8 repo.

Install with Oracle support

To install the library and command line program, use the following:

go get -tags oracle -v github.com/rubenv/sql-migrate/...

development:  dialect: oci8  datasource: user/password@localhost:1521/sid  dir: migrations/oracle  table: migrations

Oracle (godror)

Oracle Driver isgodror, it is not pure Go code and relies on Oracle Office Client (Instant Client), more detailed information is in thegodror repository.

Install with Oracle support

To install the library and command line program, use the following:

1. Install sql-migrate

go get -tags godror -v github.com/rubenv/sql-migrate/...

1. Download Oracle Office Client(e.g. macos, clickInstant Client if you are other system)

wget https://download.oracle.com/otn_software/mac/instantclient/193000/instantclient-basic-macos.x64-19.3.0.0.0dbru.zip

1. Configure environment variablesLD_LIBRARY_PATH

export LD_LIBRARY_PATH=your_oracle_office_path/instantclient_19_3

development:  dialect: godror  datasource: user/password@localhost:1521/sid  dir: migrations/oracle  table: migrations

As a library

Import sql-migrate into your application:

import "github.com/rubenv/sql-migrate"

Set up a source of migrations, this can be from memory, from a set of files, from bindata (more on that later), or from any library that implementshttp.FileSystem:

// Hardcoded strings in memory:migrations := &migrate.MemoryMigrationSource{    Migrations: []*migrate.Migration{        &migrate.Migration{            Id:   "123",            Up:   []string{"CREATE TABLE people (id int)"},            Down: []string{"DROP TABLE people"},        },    },}// OR: Read migrations from a folder:migrations := &migrate.FileMigrationSource{    Dir: "db/migrations",}// OR: Use migrations from a packr box// Note: Packr is no longer supported, your best option these days is [embed](https://pkg.go.dev/embed)migrations := &migrate.PackrMigrationSource{    Box: packr.New("migrations", "./migrations"),}// OR: Use pkger which implements `http.FileSystem`migrationSource := &migrate.HttpFileSystemMigrationSource{    FileSystem: pkger.Dir("/db/migrations"),}// OR: Use migrations from bindata:migrations := &migrate.AssetMigrationSource{    Asset:    Asset,    AssetDir: AssetDir,    Dir:      "migrations",}// OR: Read migrations from a `http.FileSystem`migrationSource := &migrate.HttpFileSystemMigrationSource{    FileSystem: httpFS,}

Then use theExec function to upgrade your database:

db, err := sql.Open("sqlite3", filename)if err != nil {    // Handle errors!}n, err := migrate.Exec(db, "sqlite3", migrations, migrate.Up)if err != nil {    // Handle errors!}fmt.Printf("Applied %d migrations!\n", n)

Note thatn can be greater than0 even if there is an error: any migration that succeeded will remain applied even if a later one fails.

Checkthe GoDoc reference for the full documentation.

Writing migrations

Migrations are defined in SQL files, which contain a set of SQL statements. Special comments are used to distinguish up and down migrations.

-- +migrate Up-- SQL in section 'Up' is executed when this migration is appliedCREATE TABLE people (id int);-- +migrate Down-- SQL section 'Down' is executed when this migration is rolled backDROP TABLE people;

You can put multiple statements in each block, as long as you end them with a semicolon (;).

You can alternatively set up a separator string that matches an entire line by settingsqlparse.LineSeparator. Thiscan be used to imitate, for example, MS SQL Query Analyzer functionality where commands can be separated by a line withcontents ofGO. Ifsqlparse.LineSeparator is matched, it will not be included in the resulting migration scripts.

If you have complex statements which contain semicolons, useStatementBegin andStatementEnd to indicate boundaries:

-- +migrate UpCREATE TABLE people (id int);-- +migrate StatementBeginCREATE OR REPLACE FUNCTION do_something()returns void AS $$DECLARE  create_query text;BEGIN  -- Do something hereEND;$$language plpgsql;-- +migrate StatementEnd-- +migrate DownDROP FUNCTION do_something();DROP TABLE people;

The order in which migrations are applied is defined through the filename: sql-migrate will sort migrations based on their name. It's recommended to use an increasing version number or a timestamp as the first part of the filename.

Normally each migration is run within a transaction in order to guarantee that it is fully atomic. However some SQL commands (for example creating an index concurrently in PostgreSQL) cannot be executed inside a transaction. In order to execute such a command in a migration, the migration can be run using thenotransaction option:

-- +migrate Up notransactionCREATE UNIQUE INDEX CONCURRENTLY people_unique_id_idx ON people (id);-- +migrate DownDROP INDEX people_unique_id_idx;

Embedding migrations withembed

If you like your Go applications self-contained (that is: a single binary): useembed to embed the migration files.

Just write your migration files as usual, as a set of SQL files in a folder.

Import the embed package into your application and point it to your migrations:

import "embed"//go:embed migrations/*var dbMigrations embed.FS

Use theEmbedFileSystemMigrationSource in your application to find the migrations:

migrations := migrate.EmbedFileSystemMigrationSource{FileSystem: dbMigrations,Root:       "migrations",}

Other options such aspackr orgo-bindata are no longer recommended.

Embedding migrations with libraries that implementhttp.FileSystem

You can also embed migrations with any library that implementshttp.FileSystem, likevfsgen,parcello, orgo-resources.

migrationSource := &migrate.HttpFileSystemMigrationSource{    FileSystem: httpFS,}

Extending

Adding a new migration source means implementingMigrationSource.

type MigrationSource interface {    FindMigrations() ([]*Migration, error)}

The resulting slice of migrations will be executed in the given order, so it should usually be sorted by theId field.

Usage withsqlx

This library is compatible with sqlx. When calling migrate just dereference the DB from your*sqlx.DB:

n, err := migrate.Exec(db.DB, "sqlite3", migrations, migrate.Up)                    //   ^^^ <-- Here db is a *sqlx.DB, the db.DB field is the plain sql.DBif err != nil {    // Handle errors!}

Questions or Feedback?

You can use Github Issues for feedback or questions.

License

This library is distributed under theMIT license.

go get -v github.com/rubenv/sql-migrate/...

$ sql-migrate --helpusage: sql-migrate [--version] [--help] <command> [<args>]Available commands are:down      Undo a database migrationnew       Create a new migrationredo      Reapply the last migrationstatus    Show migration statusup        Migrates the database to the most recent version available

development:dialect: sqlite3datasource: test.dbdir: migrations/sqlite3production:dialect: postgresdatasource: dbname=myapp sslmode=disabledir: migrations/postgrestable: migrations

$ sql-migrate up --helpUsage: sql-migrate up [options] ...  Migrates the database to the most recent version available.Options:  -config=config.yml   Configuration file to use.  -env="development"   Environment.  -limit=0             Limit the number of migrations (0 = unlimited).  -dryrun              Don't apply migrations, just print them.

$ sql-migrate status+---------------+-----------------------------------------+|   MIGRATION   |                 APPLIED                 |+---------------+-----------------------------------------+| 1_initial.sql | 2014-09-13 08:19:06.788354925 +0000 UTC || 2_record.sql  | no                                      |+---------------+-----------------------------------------+

production:dialect: mysqldatasource: root@/dbname?parseTime=truedir: migrations/mysqltable: migrations

import "github.com/rubenv/sql-migrate"

// Hardcoded strings in memory:migrations := &migrate.MemoryMigrationSource{Migrations: []*migrate.Migration{&migrate.Migration{Id:   "123",Up:   []string{"CREATE TABLE people (id int)"},Down: []string{"DROP TABLE people"},},},}// OR: Read migrations from a folder:migrations := &migrate.FileMigrationSource{Dir: "db/migrations",}// OR: Use migrations from bindata:migrations := &migrate.AssetMigrationSource{Asset:    Asset,AssetDir: AssetDir,Dir:      "migrations",}

db, err := sql.Open("sqlite3", filename)if err != nil {// Handle errors!}n, err := migrate.Exec(db, "sqlite3", migrations, migrate.Up)if err != nil {// Handle errors!}fmt.Printf("Applied %d migrations!\n", n)

-- +migrate Up-- SQL in section 'Up' is executed when this migration is appliedCREATE TABLE people (id int);-- +migrate Down-- SQL section 'Down' is executed when this migration is rolled backDROP TABLE people;

-- +migrate UpCREATE TABLE people (id int);-- +migrate StatementBeginCREATE OR REPLACE FUNCTION do_something()returns void AS $$DECLARE  create_query text;BEGIN  -- Do something hereEND;$$language plpgsql;-- +migrate StatementEnd-- +migrate DownDROP FUNCTION do_something();DROP TABLE people;

-- +migrate Up notransactionCREATE UNIQUE INDEX people_unique_id_idx CONCURRENTLY ON people (id);-- +migrate DownDROP INDEX people_unique_id_idx;

migrations := &migrate.PackrMigrationSource{Box: packr.NewBox("./migrations"),}

migrations := &migrate.PackrMigrationSource{Box: myBox,Dir: "./migrations",}

go-bindata -pkg myapp -o bindata.go db/migrations/

migrations := &migrate.AssetMigrationSource{Asset:    Asset,AssetDir: AssetDir,Dir:      "db/migrations",}

type MigrationSource interface {FindMigrations() ([]*Migration, error)}

Constants¶

Variables¶

var MigrationDialects = map[string]gorp.Dialect{"sqlite3":   gorp.SqliteDialect{},"postgres":  gorp.PostgresDialect{},"mysql":     gorp.MySQLDialect{Engine: "InnoDB", Encoding: "UTF8"},"mssql":     gorp.SqlServerDialect{},"oci8":OracleDialect{},"godror":OracleDialect{},"snowflake": gorp.SnowflakeDialect{},}

Functions¶

func Exec(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection) (int,error)

func ExecContext(ctxcontext.Context, db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection) (int,error)

func ExecMax(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, maxint) (int,error)

func ExecMaxContext(ctxcontext.Context, db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, maxint) (int,error)

func ExecVersion(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, versionint64) (int,error)

func ExecVersionContext(ctxcontext.Context, db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, versionint64) (int,error)

func SetDisableCreateTable(disablebool)

func SetIgnoreUnknown(vbool)

func SetSchema(namestring)

func SetTable(namestring)

func SkipMax(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, maxint) (int,error)

Types¶

type AssetMigrationSource struct {// Asset should return content of file in path if existsAsset func(pathstring) ([]byte,error)// AssetDir should return list of files in the pathAssetDir func(pathstring) ([]string,error)// Path in the bindata to use.Dirstring}

func (aAssetMigrationSource) FindMigrations() ([]*Migration,error)

type EmbedFileSystemMigrationSource struct {FileSystemembed.FSRootstring}

func (fEmbedFileSystemMigrationSource) FindMigrations() ([]*Migration,error)

type FileMigrationSource struct {Dirstring}

func (fFileMigrationSource) FindMigrations() ([]*Migration,error)

type HttpFileSystemMigrationSource struct {FileSystemhttp.FileSystem}

func (fHttpFileSystemMigrationSource) FindMigrations() ([]*Migration,error)

type MemoryMigrationSource struct {Migrations []*Migration}

func (mMemoryMigrationSource) FindMigrations() ([]*Migration,error)

type Migration struct {IdstringUp   []stringDown []stringDisableTransactionUpboolDisableTransactionDownbool}

func ParseMigration(idstring, rio.ReadSeeker) (*Migration,error)

func ToApply(migrations []*Migration, currentstring, directionMigrationDirection) []*Migration

func (mMigration) Less(other *Migration)bool

func (mMigration) NumberPrefixMatches() []string

func (mMigration) VersionInt()int64

type MigrationDirectionint

const (UpMigrationDirection =iotaDown)

type MigrationRecord struct {Idstring    `db:"id"`AppliedAttime.Time `db:"applied_at"`}

func GetMigrationRecords(db *sql.DB, dialectstring) ([]*MigrationRecord,error)

type MigrationSet struct {// TableName name of the table used to store migration info.TableNamestring// SchemaName schema that the migration table be referenced.SchemaNamestring// IgnoreUnknown skips the check to see if there is a migration// ran in the database that is not in MigrationSource.//// This should be used sparingly as it is removing a safety check.IgnoreUnknownbool// DisableCreateTable disable the creation of the migration tableDisableCreateTablebool}

func (msMigrationSet) Exec(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection) (int,error)

func (msMigrationSet) ExecContext(ctxcontext.Context, db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection) (int,error)

func (msMigrationSet) ExecMax(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, maxint) (int,error)

func (msMigrationSet) ExecMaxContext(ctxcontext.Context, db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, maxint) (int,error)

func (msMigrationSet) ExecVersion(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, versionint64) (int,error)

func (msMigrationSet) ExecVersionContext(ctxcontext.Context, db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, versionint64) (int,error)

func (msMigrationSet) GetMigrationRecords(db *sql.DB, dialectstring) ([]*MigrationRecord,error)

func (msMigrationSet) PlanMigration(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, maxint) ([]*PlannedMigration, *gorp.DbMap,error)

func (msMigrationSet) PlanMigrationToVersion(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, versionint64) ([]*PlannedMigration, *gorp.DbMap,error)

type MigrationSource interface {// Finds the migrations.//// The resulting slice of migrations should be sorted by Id.FindMigrations() ([]*Migration,error)}

type OracleDialect struct {gorp.OracleDialect}

func (OracleDialect) IfSchemaNotExists(command, _string)string

func (OracleDialect) IfTableExists(command, _, _string)string

func (OracleDialect) IfTableNotExists(command, _, _string)string

type PackrBox interface {List() []stringFind(namestring) ([]byte,error)}

type PackrMigrationSource struct {BoxPackrBox// Path in the box to use.Dirstring}

func (pPackrMigrationSource) FindMigrations() ([]*Migration,error)

type PlanError struct {Migration    *MigrationErrorMessagestring}

func (p *PlanError) Error()string

type PlannedMigration struct {*MigrationDisableTransactionboolQueries            []string}

func PlanMigration(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, maxint) ([]*PlannedMigration, *gorp.DbMap,error)

func PlanMigrationToVersion(db *sql.DB, dialectstring, mMigrationSource, dirMigrationDirection, versionint64) ([]*PlannedMigration, *gorp.DbMap,error)

func ToCatchup(migrations, existingMigrations []*Migration, lastRun *Migration) []*PlannedMigration

type SqlExecutor interface {Exec(querystring, args ...interface{}) (sql.Result,error)Insert(list ...interface{})errorDelete(list ...interface{}) (int64,error)}

type TxError struct {Migration *MigrationErrerror}

func (e *TxError) Error()string