[peterldowns]

I've been writing Golang for years now, and I heavily endorse everything written here.

Only exception is you should use my migration library [0] instead of tern — you don't need down migrations, and you can stop worrying about migration number conflicts.

One other suggestion I'll make is you probably at some point should write a translation layer between your API endpoints and the http.Handler interface, so that your endpoints return `(result *T, error)` and your tests can avoid worrying about serde/typeasserting the results.

[0] https://github.com/peterldowns/pgmigrate

[glenjamin]

This looks excellent!

The go tools for managing DB schema migrations have always felt lacking to me, and it seems like your tool ticks all of the boxes I had.

Except for one: lack of support for CREATE INDEX CONCURRENTLY (usually done by detecting that and skipping the transaction for that migration). How do you handle creating indexes without this?

[peterldowns]

Thanks for taking a look!

Long-running index creation is a problem for pgmigrate and anyone else doing “on-app-startup” or “before-app-deploys” migrations.

Even at moderate scale (normal webapp stuff, not megaco size) building indexes can take a long time — especially for the tables where it’s most important to have indexes.

But if you’re doing long-running index building in your migrations step, you can’t deploy a new version of your app until the migration step finishes. (Big problem for lots of reasons.)

The way I’ve dealt with this in the past is:

- the database connection used to perform migrations has a low statement timeout of 10seconds.

- a long-running index creation statement gets its own migration file and is written as: “CREATE INDEX … IF NOT EXISTS”. This definition does not include the “CONCURRENTLY” directive. When migrations run on a local dev server or during tests, the table being indexed is small so this happens quickly.

- Manually, before merging the migration in and deploying so that it’s applied in production, you open a psql terminal to prod and run “CREATE INDEX … CONCURRENTLY”. This may take a long time; it can even fail and need to be retried after hours of waiting. Eventually, it’s complete.

- Merge your migration and deploy your app. The “CREATE INDEX … IF NOT EXISTS” migration runs and immediately succeeds because the index exists.

I’m curious what you think about this answer. If you have any suggestions for how pgmigrate should handle this better, I’d seriously appreciate it!

[glenjamin]

I think that’s the safest approach, but it’s inconvenient for the common case of an index that’ll be quick enough in practice.

The approach I’ve seen flyway take is to allow detecting / opting out of transactions on specific migrations

As long as you always apply migrations before deploying and abort the deploy if they time out or fail, then this approach is perfectly safe.

On the whole I think flyway does a decent job of making the easy things easy and the harder things possible - it just unfortunately comes with a bunch JVM baggage - so a Go based tool seems like a good alternative

[peterldowns]

Makes sense — Flyway is so good, copying their behavior is usually a smart choice. Thanks for the feedback!

[jrockway]

I can definitely get behind using some other migration library! Thank you for writing and sharing this!

[peterldowns]

Thanks :) if you have the time, I’d sincerely appreciate feedback on it and especially on its docs/readme, even a simple github issue for “this is confusing” or “this typo is weird” would be really helpful.

[domano]

Bold of you to flat out drop down migrations.

I guess having a new up migration to cover the case is better, but its nice to have a documented way of rolling back (which would be the down migration) - without applying it programmatically. But it helps if other team members can see how a change should be rolled back ideally.

[peterldowns]

Glenjamin gave a great answer. I’ll just add that in my experience (being responsible for the team’s database, at a few companies over the years), down migrations are NEVER helpful when a migration goes wrong. Roll-forward is the only correct model. Besides, there are plenty of migrations that cant be safely rolled back, so most “down migrations” either destroy data or don’t work (or both.)

[glenjamin]

The key here is that in production it's almost always not safe to actually apply a down migration - so it's better to make that clear than to pretend there's some way to reverse an irreversible operation.