- 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+
To install the library and command line program, use the following:
go get -v github /rubenv/sql-migrate/...
For Go version from 1.18, use:
go install github /rubenv/sql-migrate/...@latest
$ sql-migrate --help
usage: 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/sqlite3
production:
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 --help
Usage: 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|
+---------------+-----------------------------------------+
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
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
Seeherefor more information.
Oracle Driver isoci8,it is not pure Go code and relies on Oracle Office Client (Instant Client), more detailed information is in theoci8 repo.
To install the library and command line program, use the following:
go get -tags oracle -v github /rubenv/sql-migrate/...
development:
dialect:oci8
datasource:user/password@localhost:1521/sid
dir:migrations/oracle
table:migrations
Oracle Driver isgodror,it is not pure Go code and relies on Oracle Office Client (Instant Client), more detailed information is in thegodror repository.
To install the library and command line program, use the following:
- Install sql-migrate
go get -tags godror -v github /rubenv/sql-migrate/...
- Download Oracle Office Client(e.g. macos, clickInstant Clientif you are other system)
wget https://download.oracle /otn_software/mac/instantclient/193000/instantclient-basic-macos.x64-19.3.0.0.0dbru.zip
- Configure environment variables
LD_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
Import sql-migrate into your application:
import"github /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)
iferr!=nil{
// Handle errors!
}
n,err:=migrate.Exec(db,"sqlite3",migrations,migrate.Up)
iferr!=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 referencefor the full documentation.
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 applied
CREATETABLEpeople(idint);
--+migrate Down
--SQL section 'Down' is executed when this migration is rolled back
DROPTABLEpeople;
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
.This
can be used to imitate, for example, MS SQL Query Analyzer functionality where commands can be separated by a line with
contents 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 Up
CREATETABLEpeople(idint);
--+migrate StatementBegin
CREATE OR REPLACEFUNCTIONdo_something()
returns voidAS$$
DECLARE
create_querytext;
BEGIN
--Do something here
END;
$$
language plpgsql;
--+migrate StatementEnd
--+migrate Down
DROPFUNCTIONdo_something();
DROPTABLEpeople;
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 notransaction
CREATEUNIQUE INDEXCONCURRENTLYpeople_unique_id_idxONpeople (id);
--+migrate Down
DROPINDEXpeople_unique_id_idx;
Embedding migrations withembed
If you like your Go applications self-contained (that is: a single binary): useembedto 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/*
vardbMigrationsembed.FS
Use theEmbedFileSystemMigrationSource
in your application to find the migrations:
migrations:=migrate.EmbedFileSystemMigrationSource{
FileSystem:dbMigrations,
Root:"migrations",
}
Other options such aspackrorgo-bindataare no longer recommended.
You can also embed migrations with any library that implementshttp.FileSystem
,likevfsgen
,parcello
,orgo-resources
.
migrationSource:=&migrate.HttpFileSystemMigrationSource{
FileSystem:httpFS,
}
Adding a new migration source means implementingMigrationSource
.
typeMigrationSourceinterface{
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.DB
if err!= nil {
// Handle errors!
}
You can use Github Issues for feedback or questions.
This library is distributed under theMITlicense.