Migrations with SQL-files

[AlekSi]

The first step is to create Go library (in separate package gopkg.in/reform.v1/migrator) and CLI (in reform-db) to apply SQL migrations.

Basics

Migration is a sequence of SQL DDL statements. Migrations are versioned. A version is an opaque string. Versions are sorted by sort.Strings.

Applied migrations are tracked in database table schema_migrations. In PostgreSQL syntax:

CREATE TABLE schema_migrations (
  version varchar PRIMARY KEY
);

When applying migrations, all not-applied migrations should be run, independent on a version. For example, given migrations A, B, C, D, E, and B and D are already applied, A, C, and E still should be run.

Example migration file: 2006-01-02-15-04-create-people.sql

-- +migration 2006-01-02-15-04
CREATE TABLE people (
  …
);

-- +migration VERSION is magic version comment. VERSION should match filename prefix.

Go Package API

TBD.

• It should support graceful cancellation (with context?).
• Each migration should be implicitly wrapped in a transaction.

CLI

1. Run migrations from files.

reform-db migrate migrations/*.sql

• read all files;
• check that versions in file names match versions in magic comments in files;
• sort migrations by version;
• remove already applied versions;
• apply the rest.

2. Run migrations from stdin.

cat migrations/*.sql | reform-db migrate

• read all data until EOF;
• split migrations by magic comments;
• sort migrations by version;
• remove already applied versions;
• apply the rest.

3. Create new migration file.

reform-db create-migration create-people.sql

It should create file <2006-01-02-15-04>-create-people.sql with

-- +migration 2006-01-02-15-04

-- place your code here

2006-01-02-15-04 is a time format for current UTC time.

Random notes

• Let's not add down/redo, at least for now: https://flywaydb.org/documentation/faq#downgrade.
• It's totally ok to use Go 1.8 database/sql features, context cancellation in particular. We can then make alternative implementation for previous Go versions, or drop support for them.

[rumyantseva]

I'll take it!

[rumyantseva]

How I understand task description:

• migration files must support defined name format 'YYYY-MM-DD-HH-mm-[something]'
• 'migration version' and 'file name prefix' must be equal

Please, explain if it is possible to define custom versioning style (or at least prefix) - numbers, letters or somethings else?

[AlekSi]

YYYY-MM-DD-HH-mm prefix should be enforced only by reform-db create-migration CLI command. Go package should work with any versioning scheme as long as the version in file matches filename prefix, there are no duplicates, etc. So if you really want your files to have names 1.sql, 2.sql, etc. reform-db create-migration will not help you, but also nothing else should stop you.

Alternatively, CLI may detect current versioning scheme (dates, sequential numbers, etc.) and play along. But I'm not sure we want to implement this feature now.

[evanmcclure]

Just curious if you even want to tackle this given that there are several enterprise-ready, mature schema migration tools already available. DRY.

[dgellow]

@AlekSi Have you already try out migrate? I think it does what you want. I've used it for the past ~3 years to manage migrations and that has been quite good so far. I'm not sure that's something worth implementing in reform directly.