See https://github.com/sbowman/migrations/blob/master/v2 for migrations/v2.
The migrations package is designed to help manage database revisions in Go applications. It works in a similar fashion to Ruby on Rails ActiveRecord migrations: create a directory for your migrations, add numberically ordered SQL files with an "up" and "down" section, then write your "up" and "down" migrations. You may then call functions in the package to migrate the database up or down, based on the current "revision" of the database.
The migrations package is designed to be included in your product binary.
When the application deploys to the server, you can run your migrations from
the server, without manually having to setup the database.
Alternatively, you could build a small CLI binary or Docker container, include
the migrations package and your SQL files in the image, and run that
standalone.
In a cloud environment like Kubernetes, you might create a "maintenance" pod
to run migrations on startup (and periodically clean out old data from the
database). Implement an HTTP endpoint that holds on to any clients until
migrations complete. When a client starts, it requests that maintentance HTTP
endpoint before accepting its own requests. When the migrations finish in the
maintenance pod, the maintenance pod allows the HTTP endpoint to finally reply
(and simply reply with a 2XX immediately for any future requests). This ensures
your applications, on deployment, all wait for migrations to complete before
accepting requests, so they are using the latest and greatest version of your
database.
Migrations 1.4.0 is completely API-compatible with Migrations 1.0.0.
All the remote migration functionality and Cobra/Viper command support described below is completely optional. It is isolated in packages so as not to pull in either the Cobra, Viper, or AWS packages into your binary unless you use them.
The "sql" directory is used to test the migrations. Its contents should NOT be included in your applications.
The "cmd" directory contains some sample code describing how to setup
migrations functionaltiy in your applications, using the Cobra and Viper
packages. (This package will eventually be deprecated, and the examples moved
to proper Example code.)
In order to remove the PostgreSQL dependency from this package, as of 1.4 test
cases have been moved to the tests directory, and are treated as a standalone
package. This allows us to maintain test cases, but utilize the PostgreSQL
driver to test migrations against a real database server.
To run these test cases:
cd tests && make
The Makefile tears down any existing migrations_test database, creates a new
migrations_test database, runs the tests for migrations in a transaction,
followed by tests for migrations outside of a transaction.
Version 1.2 deprecates Cobra and Viper integrations. Obviously not everyone is using these packages, and it seems presumptuous to force others to include them.
The functionality will be completely removed in version 1.5.
There are a few primary functions to add to your applications to use the migrations package.
The migrations package can create empty migration files with the "up" and "down" sections inserted for you (see below).
migrations.Create("/path/to/migrations", "migration name")
This will create a migration file in the migrations path. The name will be
modified to remove the space, and a revision number will be inserted. If there
were five other SQL migrations in /path/to/migrations, You'd end up with a
file like so:
$ ls /path/to/migrations
total 16
-rw-r--r-- 1 sbowman staff 99 May 22 15:39 1-create-sample.sql
-rw-r--r-- 1 sbowman staff 168 May 22 15:40 2-add-email-to-sample.sql
-rw-r--r-- 1 sbowman staff 207 May 22 15:50 3-create-my-proc.sql
-rw-r--r-- 1 sbowman staff 96 May 22 16:32 4-add-username-to-sample.sql
-rw-r--r-- 1 sbowman staff 347 May 22 17:55 5-rename-sample-to-users.sql
-rw-r--r-- 1 sbowman staff 22 May 26 19:03 6-migration-name.sql
Once you've created your migration files and added the appropriate SQL to generate your database schema and seed any data, you'll want to apply the migrations to the database by calling:
migrations.Migrate(dbConn, "/path/to/migrations", revisionNum)
Where dbConn is a *sql.Conn database connection (from the database/sql Go
package), and revisionNum is the revision number.
The revision number allows you to apply just the migrations appropriate for the
current version of your application. Day to day, you'll likely just use the
value -1, which applies any and all existing migrations, in order of their
revision number.
But suppose you just want to apply the first four migrations to your database,
but not the last two yet? Set revisionNum to 4 and call migrations.Migrate.
The migrations package will apply just the SQL from the migration files from 1
to 4, in order.
What if you've already run all six migrations listed above? When you call
migrations.Migrate with revisionNum set to 4, the migrations package
applies the SQL from the "down" section in migration 6-migration-name.sql,
followed by the SQL in the "down" section of 5-rename-sample-to-users.sql.
We call this "rolling back" a migration, and it allows you to develop your
application, make some changes, apply them, then roll them back if they don't
work.
Note: make sure to load the database driver before applying migrations! For example:
import _ "github.com/jackc/pgx/v4/stdlib"
See tests/migrations_test.go for an example.
The migrations package used to include integration with Cobra and Viper. This
was deprecated in 1.4 and is now officially removed. In its place is a sample
application, migrate, leveraging Cobra and Viper to demonstrate using the
migrations package in an application.
For PostgreSQL users, this sample app may also be installed as a standalone app to run migrations and create SQL migration file templates.
The following command-line parameters/environment variables are supported by the
migrate application:
uri/DB_URI- the database URI, e.g.postgres://username:password@localhost:5432/database_namemigrations/MIGRATIONS- the path to the migrations files, defaults to "./sql"revision/REVISION- the revision number to run migrations, defaults to latestauto/AUTOMIGRATE- migrate or rollback the database to the version matching the available SQL migrations
With --auto, the migrate app will use the SQL migration files' latest version
as the version to migrate to. If the database is at a later version and supports
schema_rollbacks, a migrate --auto will rollback the database to that version.
Otherwise it will migrate to the latest SQL migration file revision.
The migrations package supports optionally including the SQL rollback (Down)
section of each migration in a new database table, schema_rollbacks. This
allows migrations to be rolled back without requiring the original SQL files.
This can be useful in environments such as Docker and Kubernetes when you need
to rollback a version to a previous image or pod, which means the SQL files to
rollback the database migrations won't be present. When present, these embedded
SQL commands can be used to rollback the database to the correct version for the
image now in use.
If this information is not in the database and the database requires a rollback, you may still use the SQL files to perform the rollback. You'll just have to do that manually.
Additionally, the migrations package now has a Check() function to check the
version of the SQL migration files versus what migrations have been applied to
the database. This function returns nil if the current SQL migrations have all
been applied to the database, ErrMigrationRequired if there are unapplied
migrations, or ErrRollbackRequired if there are migrations applied to the
database that are no longer present in the SQL migration files.
As of version 1.4, the migrations package adds support for running migrations
outside of a transaction. This package was originally designed to work with
PostgreSQL, which supports DDL commands inside of a transaction. But this
feature is not available in all databases, and running statements inside a
transaction on some of these databases can cause errors. As of version 1.4,
you may run migrations in transactionless mode.
Transactionless migrations are obviously less safe than running migrations in
a transaction. If you have migrations with multiple SQL commands, and a
command fails in the middle of the migration, your database will be in a
partially migrated state and you'll have to clean up the migration manually to
continue with the migration. If you use migrations like this, each individual
SQL migration file should contain the minimum number of SQL commands as is
reasonable, so that if a migration fails it's easier to resolve.
To run transactionless migrations, call MigrateUnsafe instead of Migrate.
As of version 1.3, the migrations package supports asynchronous migrations
(see the /async flag below). This allows longer running migrations to run
in the background, while allowning the main, synchronous migrations to complete.
If you use the Migrate function, asynchronous migrations are ignored and run
like normal migrations. This is useful in development. To run asynchronous
migrations in the background, use MigrateAsync
For example, if you're using something like the "maintenance" pod described in
the introduction, you may want to allow your migrations to run to completion,
let your other pods depending on those synchronous migrations know the database
is ready, while letting the longer running asynchronous migrations more time to
complete in the background. To do this, call MigrateAsync and handle the
migrations.ResultChannel yourself.
Here's some sample code illustrating MigrateAsync:
asyncResults, err := migrations.MigrateAsync(dbConn, pathToMigrations,
migrateToRevision)
if err != nil {
return fmt.Errorf("migrations failed: %s', err)
}
// Once migrations.MigrateAsync returns, all the synchronous migrations
// have completed...
NotifyClientsMigrationsCompleted();
// Blocks until the asynchronous requests complete; logs any errors in the
// asynchronous migrations
for result := range asyncResults {
if result.Err != nil {
if result.Command == "" {
Log.Infof("Asynchronous migration %s failed: %s",
result.Migration, result.Err)
continue
}
Log.Infof("Asynchronous migration %s failed on command %s: %s",
result.Migration, result.Command, result.Err)
}
}
// When asynchronous migrations finish, the asyncResults channel closes
The migrations package uses a simple Logger interface to expose migration
information to the user. By default, this goes to stdout. You're welcome to
implement your own logger and wire migrations logging into your own log output.
Here's the interface:
// Logger is a simple interface for logging in migrations.
type Logger interface {
Debugf(format string, args ...interface{})
Infof(format string, args ...interface{})
}
Just assign your logger to migrations.Log before running any migration
functions.
There's also a NilLogger available, if you'd like to hide all migrations
output.
migrations.Log = new(migrations.NilLogger)
Typically you'll run migrations locally from disk, and either remotely provision the database from your local machine, or copy the files with your application deployment and provision your database directly on deployment or runtime from your application server.
Create a directory in your application for the SQL migration files:
$ mkdir ./sql
$ cd sql
Now create a SQL migration. The filename must start with a number, followed by a dash, followed by some description of the migration. For example:
$ vi 1-create_users.sql
If you're using the Cobra commands, there's a "db create" function that creates the directory and numbers the migration file correctly for you (if migrations already exist, it'll figure out the next number):
$ ./myapp db create create_users
Created new migration ./sql/1-create_users.sql
An empty migration file looks like this:
# --- !Up
# --- !Down
Under the "up" section, add the changes you'd like to make to the database in this migration. You may insert as many database commands as you like, but ideally each migration carries out the simplest, smallest unit of work that makes for a useful database, e.g. create a database table and indexes; make a modification to a table; or create a stored procedure.
Note the above line formats are required, including the exclamation points, or
migrations won't be able to tell "up" from "down." As of version 1.3, the
# at the front of the up and down sections is optional, e.g. --- !Up is
equivalent to # --- !Up, to support a valid SQL syntax and syntax
highlighters.
The "down" section should contain the code necessary to rollback the "up" changes.
So our "create_users" migration may look something like this:
# --- !Up
create table users (
id serial primary key,
username varchar(64) not null,
email varchar(1024) not null,
password varchar(40) not null,
enabled bool not null default true
);
create unique index idx_enabled_users on users (username) where enabled;
create unique index idx_enabled_emails on users (email) where enabled;
# --- !Down
drop table users;
The migrations package simply passes the raw SQL in the appropriate section ("up" or "down"), to the database. The SQL calls are wrapped in a single transaction, so that if there's a problem with the SQL, the database can rollback the entire migration (see below to disable these transactions for special cases).
Some databases, such as PostgreSQL, support nearly all schema modification
commands (CREATE TABLE, ALTER TABLE, etc.) in a transaction. Databases like
MySQL have some support for this. Your mileage may vary, but if your database
doesn't support transactioned schema modifications, you may have to manually
repair your databases should a migration partially fail. This is another
reason to keep each migration modification small and targeted to a specific
change, and not put everything in one revision file: if the migration fails
for whatever reason, it's easier to clean up.
Migrations package version 1.1 adds support for "remote" migrations from the Amazon AWS S3. This allows you to upload your migrations to an S3 bucket, and have your application apply the modifications from the S3 bucket instead of a local disk. This makes it easier to deploy something like AWS Lambda serverless compute programs.
Currently remote migrations only support AWS S3. Additional remote storage systems may be supported in the future.
The remote functionality assumes your AWS credentials are located in a
$HOME/.aws/credentials file, similar to:
[default]
aws_access_key_id = <AWS access key>
aws_secret_access_key = <AWS secret key>
This may be customized to whatever security settings are necessary for your account. See the [https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-files.html](S3 documentation) for more details.
To configure migrations to work with S3, use the migrations package like normal,
but call remote.InitS3. If you're using spf13/cobra, you might put it in a
PersistentPreRun function:
rootCmd = &cobra.Command{
PersistentPreRun: func(cmd *cobra.Command, args []string) {
remote.InitS3(viper.GetString("s3.region"))
},
// ...
You may copy the SQL migrations to an S3 bucket manually, or use the S3 push function:
if err := remote.PushS3(viper.GetString("migrations),
viper.GetString("region"), viper.GetString("bucket")); err != nil {
fmt.Fprintf(os.Stderr, "Failed to push migrations to S3: %s\n", err)
os.Exit(1)
}
The default "db create" command creates migrations in a local directory, in expectation you'll use a "db push" command based on the above to push the migrations to S3.
After the InitS3 call, you can run the remote migrations the same way you run standard, disk-based migrations, but pass in the bucket name instead of a migrations directory:
conn, err := sql.Open("postgres", "postgres://....")
if err != nil {
return err
}
if err := migrations.Migrate(conn, viper.GetString("bucket"),
viper.GetInt("revision")); err != nil {
fmt.Fprintf(os.Stderr, "Failed to migrate: %s\n", err)
os.Exit(1)
}
See the remote/cmd package for examples (or feel free to use them in your own spf13/cobra and spf13/viper applications).
Similar to the Cobra/Viper support describe above for migration commands against local files, a set of remote S3-compatible commands exist that may be integrated into your application. These work nearly identially to the local versions, with the addition of some S3 settings.
To add the remote Cobra commands to your app, use something like the following:
import migrations "github.com/sbowman/migrations/remote/cmd"
func init() {
// ... other init code ...
migrations.AddRemoteTo(RootCmd)
}
If you do this, you don't have to mess with any of the main migrations or
migrations/remote packages. You'll automatically get a db create and a db migrate command, like the local commands, but you'll also get a third command,
db push, described below.
The following settings are supported with the S3 remote Cobra commands:
uri- the database URI, e.g. "postgres://postgres@localhost/myapp"driver- the name of the database driver; defaults to. "postgres"migrations- the path to the migrations files, defaults to "./sql"revision- the revision number to run migrations, defaults to -1region- the AWS region the bucket is in; defaults to "us-west-2"bucket- the name of the bucket holding the migration files
You should copy your migrations directly into the bucket; do not put them in a
subdirectory. The db push command will copy any new or updated files to the
S3 bucket for you:
$ ./myapp db push --bucket="myapo-migrations"
You may put other files or directories in your bucket. As long as they don't
end in .sql, the migrations will ignore any files or folders.
All other commands function in the same way as the local file versions, except
that you must supply a --bucket value:
$ ./myapp db migrate --bucket="myapp-migrations"
The supplied S3-based commands default to region us-west-2. If your bucket
isn't in this region, supply a --region=<name> value for any command, e.g.
--region=us-west-1.
If you do not supply a bucket to the S3-based commands, the migrations package applies the local migration files. This can be useful for local development.
Additionally, you can run your migrations from within your app when it starts up:
if err := migrations.Migrate(dbConn, "my-bucket-name", revisionNum); err != nil {
return err
}
Note that this is exactly the same as running on the local file system, except the migration path is assumed to be the bucket name, "my-bucket-name".
In version 1.3, migrations includes support for custom flags in the SQL scripts.
There is only one flag at present (/async), but additonal flags could be
added in the future.
To tweak how the migration is processed, include the flag at the end of the up or down line/comment in the migration. For example:
# --- !Up /async
Make sure to put a space between the direction value, e.g. Up, and each flag,
in this case, /async.
The /async flag will run the migration in a separate thread. This can be
useful for running migrations that can optionally fail, or long-running
migrations that can run in the background for a while after your application
starts.
Use this flag with caution. The migration will be marked as completed (recorded
in the schema_migrations table in the database) immediately after it is
handed off to the background process. If it fails, it will return an error on
a results channel (which your application may listen for), but it will still
appear to be successfully completed (because it's in the schema_migrations
table). This is designed this way on purpose, to be the least invasive
approach to asynchronous migrations. But that means if something goes wrong
in an asynchronous migration, it's your responsibility to manually resolve the
problem.
To use the /async function there is a new MigrateAsync function. See the
example above for a sample of how to use it. If you don't call MigrateAsync,
the /async flag is ignored and the migration run as a normal migration.