Migrate: Enhancement: Wrap each migration file in an atomic transaction

[mcandre]

Could migration scripts be wrapped in db.BeginTx() ... db.Rollback/db.Commit(), to make it easier to resolve any errors that occur during migrations?

This could help keep migrations in a clean state, even when a migration fails! Rather than having to clean up the innards of the golang-migrate state, the user can simply fix the migrations and run them again until they pass.

https://golang.org/pkg/database/sql/#Conn.BeginTx

[dhui]

This would be an interesting change to support in the driver. (See: https://github.com/golang-migrate/migrate/issues/14)
However, not every RDBMS supports transactions for DDL/schema changes. e.g. MySQL I'm looking at you...
This is an issue since some migrations modify schema and others modify data.

For now, migrate errs on the side of flexibility.

[TVenuMadhav]

Golang Migrate Version 1 to Version 4

Are all migration files run in a transaction implicitly(from a black box perspective)?

if yes, then why do we have to explicitly mention (Begin/commit) in migration files with multiple sql statements?

Thanks in advance,

PS :- working with Postgres here.

[dhui]

Are all migration files run in a transaction implicitly(from a black box perspective)?

No. That's what this issue/feature request is about. See: https://github.com/golang-migrate/migrate/blob/master/MIGRATIONS.md#migration-content-format

if yes, then why do we have to explicitly mention (Begin/commit) in migration files with multiple sql statements?

It's a best practice. If your migration contains multiple statements, a failure in a single statement will rollback the whole migration which makes it easier to re-apply the whole migration after the issue has been resolved.

An issue w/ this feature request is that it's not backwards compatible. e.g. existing migrations using with BEGIN/COMMIT will break

[tv42]

I find it really hard to believe this is something the user is supposed to remember every single time they write a migration. I'm personally going to look for an alternate solution now, I really don't trust myself to remember that.

[dhui]

Other options:

1. Fork the DB driver and wrap your statement in a transaction in your fork
2. Implement this as an option for each database. As this issue is currently written, it implies this change would take affect for all migrations instead of being DB specific

See also: https://github.com/golang-migrate/migrate/pull/374

[u007]

i think it could be roled out progressively one by one driver