Feature request: baseline/ignore migrations

[depombo]

We perform data migrations in IaSQL which is built on PostgreSQL and we have the need to ignore certain migrations as databases sometimes are recreated and the migration table gets blown away. This can be done either with an explicit blacklist, but ideally by defining a baseline based on a timestamp that ignores migrations before the provided date similar to Flyway. The interface should be as simple as an additional, optional configuration value called BASELINE_TIMESTAMP.

This is a high priority for us and we are considering building our own migration system as we have not found one that fits all of our needs. If guidance can be provided someone from our team can contribute as soon as next week :)

[amacneil]

Can you explain the workflow where you would need this?

Generally, when restoring schema from a backup, you should insert rows into the schema_migrations table for any migrations that are already applied. Dbmate will insert these automatically for you when generating the schema.sql file.

I have never heard of IaSQL before, but I read your linked issue:

Data migrations are needed for IaSQL, more so than the traditional schema migrations, and require baselining migrations to avoid duplicate record violations when IaSQL databases are recreated from scratch (e.g. connected and disconnected to the same cloud account)...

I guess this is similar to terraform import? What stops you from populating the schema_migrations table with any migrations you already consider applied, when connecting to an existing cloud account. That doesn't strike me as any more difficult than keeping track of a "baseline date".

...or when changes happen outside of IaSQL and are “synced” from the cloud by the cron job.

That seems to defeat the point of using a migration system.

[depombo]

Can you explain the workflow where you would need this?

IaSQL maintains a two-way connection between a PostgreSQL database and the cloud. When connecting a database to your cloud account, IaSQL treats the cloud as the source of truth and backfills the database with what is in the cloud account. It is common to disconnect a database from your cloud account and delete the database since it is so easy to create/connect a brand new database to your cloud account again. This mechanism is used by IaSQL internally when you upgrade to the latest schema version of tables in a simple fashion. However, after a database-to-cloud-account disconnect and subsequent connect as used during upgrade/debugging/etc running migrate in the migration system over the data migrations would produce duplicate key errors so for it to work once again you need to do one of the following:

• set up a migration system anew / delete all the data migrations files
• baseline migrations to today in the migration system
• manually populate the schema_migrations table for every single data migration defined

I guess this is similar to terraform import? What stops you from populating the schema_migrations table with any migrations you already consider applied, when connecting to an existing cloud account. That doesn't strike me as any more difficult than keeping track of a "baseline date".

Nothing stops you from populating the schema_migrations table manually but depending on the number of required columns it might be unnecessarily tedious especially if the workflow is being run in CI/CD. I haven't looked at the schema_migrations table for dbmate, but certain migrations systems take care to store the md5 / file signature to make sure that migrations don't get modified once they have been run. Setting a "baseline date" which can be as simple as today's date is much simpler in this particular workflow than understanding the internals of the schema_migrations table and doing as many INSERTs as you have data migrations which could be a high number. This is definitely an atypical workflow since most migration systems are used for schema migrations in the first place and some migration systems are very opinionated about only running schema migrations. Baselining is a pretty common use case for data migrations even if data migrations themselves are rare. dbmate strikes me as less opinionated than other migration systems which is what brought me here. Would you want to support a "baseline date" feature?

[amacneil]

Dbmate's schema_migrations table is very simple, it is just a single column containing each applied migration version number. We don't persist the migration name or applied date etc. We don't distinguish between schema migrations and data migrations, and it should work equally fine for both.

So far I don't think the "baseline date" feature would make sense for us to add. You can already do this by simply inserting one row for each existing migration you want to "baseline", and that is more reliable (e.g. if someone added a new migration with an earlier date/version, we would correctly apply it). Getting a list of all migrations preceding a given date and inserting them into the database is very easy. To natively support baseline dates we would need to add new complexity, either by storing the date in a new table (not really any benefit over just listing the migrations), or requiring you to pass it as a CLI flag every time you run dbmate (error prone / easy to accidentally apply old migrations).

[depombo]

I didn't realize the schema_migrations table was so simple. I can see why a "baseline date" wouldn't make sense. Got it, thanks!

[depombo]

What would be the suggested way of initing the schema_migrations table and running inserts on it before running migrate such that it doesn't fail though?

[amacneil]

Here is where we create the migrations table. The sql is:

create table if not exists schema_migrations (version varchar(255) primary key);