migrate

Drawbridge Migrate Tool, Version 2

The latest version of the migrate app works with a new package, github.com/sbowman/drawbridge, which combines github.com/sbowman/hermes and github.com/sbowman/migrations into a single package. Currently drawbridge only supports PostgreSQL, but it is designed and will support additional databases like SQLite3 and MySQL soon.

Drawbridge simplifies the migration requirements and processesn and provides some new features, so it's not compatible with github.com/sbowman/migrations. If you're using github.com/sbowman/migrations, make sure to use v1 of the migrate tool.

Installing Migration

To install the binary globally:

go install github.com/sbowman/migrate@latest

Note again that this version of the binary only supports migrations managed by Drawbridge. If you are still using github.com/sbowman/migrations, please use version 1 of this app.

Creating the Next Migration Script

Drawbridge migrations work off a set of SQL files in a directory to manage versions of your database schema. These files are numbered and deployed in order, which makes it easy to manage the version of your database schema alongside the version of your application.

To quickly create a new empty SQL migration file with the correct number, you can use the create command:

migrate create create-users

If there are no migrations in the default ./sql directory, this will create a file, ./sql/1-create-users.sql with the Up and Down sections in the file. If there are already three migrations, it'll create a ./sql/4-create-users.sql.

Note there is no reason you can't create these migration files manually. They're pretty simple. But the create command can be helpful.

Migrating the Database

Applying the latest migrations to the database is straightforward:

migrate --uri=postgres://postgres@localhost/myapp_dev?sslmode=disable

This will look for the files in the ./sql directory, see which have been applied to the current database, and apply any missing migrations in numerical order.

The following options are available:

• --migrations - point to a different directory of SQL migration files (DB_MIGRATIONS)
• --revision - migrate or rollback the database to this version (in line with the version number of the SQL file)
• --metadata - use an alternate metadata table in the database to store and compare what migrations have been applied; defaults to drawbridge.schema_migrations (DB_METADATA)
• --timeout - change the default timeout for applying migrations (defaults to 10 minutes; DB_TIMEOUT)
• --uri - the Go SQL driver URI to the database, e.g. postgres://localhost/myapp_dev?ssl_mode=disable (DB_URI)

I recommend leveraging something like direnv and setting the DB_URI environment variable when you cd into your project's directory. In this way, you may simply run migrate and have everything work.

Writing migrations

A SQL migration file is simply a .sql file with an Up and Down section. Here's an example:

# --- !Up
create table users
(
    id            uuid primary key      default gen_random_uuid(),
    email         varchar(128) not null,
    password_hash varchar(128),
);

create unique index idx_users_email on users (email);

# --- !Down
drop table users;

When applying this migration, drawbridge will send the Up section to the database, in a transaction if possible. If any part of the migration fails, it should rollback the entire migration.

If you want to remove this migration from your database schema (rollback your change after the fact), migrate (via drawbridge) will run the Down section, which in the above example simply drops the users table (in PostgreSQL, if you drop the table associated with the index, the index is dropped as well).

Not every migration must have a Down section; if there's nothing to do or you don't want the change to be rolled back, simply don't include or leave Down blank. However, this is atypical: for nearly every change, you should include a way to remove it!

Also, it's recommended you keep your migrations small and to the point. Don't have a single migration create 20 tables and a dozen indexes. Over the years of using this patter, I've found it's better to keep the migrations small, and instead of 20 tables in one migration, create 20 migrations, one for each table. This makes it much easier to read, maintain, and debug if there are problems in your SQL.

Finally, don't make large changes to the data in your database using migrations. This is allowed, and it's tempting, given the migration is pure SQL. But again, years of using this and similar frameworks have demonstrated that it's risky to manipulate your data too much in a migration. It can lead to failed deployments (maybe you didn't realize exactly how much data you were changing in production?) or mysterious bugs. If you're modifying your database tables in a migration and need to move data around to accommodate or you need to load some enum-like data, by all means manipulate that data in the migration. Just be careful and reasonable with the size of your migrations.

Database revisions

Every database migration file must have a number. For example, the files in your ./sql directory may look like:

$ ls
ls -1
1-pgcrypto-extension.sql
10-forgot-password.sql
2-create-users.sql
3-create-signing-keys.sql
3-create-refresh-tokens.sql

Some things to note about the above list:

• Drawbridge migrations are applied in sequential order, so the above changes will be applied in this order: 1, 2, 3, 3, 10.
• It's ok if there are gaps in the numbers; drawbridge will happily skip those numbers.
• It's ok if there are duplicate numbers, for example, if two different developers committed database changes in the same release, and both generated the same 3 migration. That's perfectly fine, with the caveat the ordering is not guaranteed. If two migrations share the same number but depend on one running before the other, this may fail.

Migrations may also be applied to a specific version. For example, say you don't want migration 10 applied. You may tell migrate to run to revision 3 (or 5 or 7 or anything between 3 and 9):

migrate -v 3

This will apply migrations 1, 2, and both 3's, but not 10. If your database is already at version 10, this will apply the Down migration in 10-forgot-password.sql and roll those changes out of the database.

Rollbacks

You can reverse a migration by setting the revision number to the previous migration. Using the above list of migrations, say for example migration 10 is incorrect. Per the example above, you can set the revision to 3 to roll out those changes:

migrate -v 3

Alternatively, there's a shortcut command that can be helpful in the most common use case: you're writing a new migration, and you bungled it. To quickly roll back that migration, you can use the rollback command:

migrate rollback

This will rollback the very last migration applied to the database. You can also specify the number of steps to rollback:

migrate rollback -s 2

This will rollback the last two migrations.

For More Information

• https://github.com/sbowman/drawbridge