Feature request: idempotent schema migrations

[dvasdekis]

Hi folks, I love this library, and I'm trying to get it running in a project that uses Flyway for schema migrations.

I've managed to get it to work by migrating the DB to procrastinate, and then using Flyway to baseline and then migrate the schema over the top of it.

However, for the app, the Flyway migrations run every time the app starts, to ensure that the DB is always aligned to the app's code. To force Procrastinate to work in the say way, I'm running procrastinate --app=tutorial.app schema --apply 2>&1 || true in my startup command to force success, even when it errors with:

Applying schema
Error: 
    Database error.
type "procrastinate_job_status" already exists

Ideally I'd love procrastinate schema --apply (or a different command) to succeed if the procrastinate tables already exist as-expected in the Postgres schema, and migrate them if not. Is that possible?

Thanks again!

[ewjoachim]

Hm, doesn't Flyway include a mechanism to mark which migration ran and which didn't ? Even if they're functionnaly idempotent, they could be costly to rerun... I'm surprised Flyway puts that on the migration writer.

[ewjoachim]

I could be wrong but the Flyway doc seems to say migrations can be either versionned (in which case they run only once) or repeatable (in which case they run every time at startup). Would that be a lead that helps you sort it out ? https://flywaydb.org/documentation/concepts/migrations.html#versioned-migrations

[dvasdekis]

HI @ewjoachim thanks so much for replying!

Flyway has a very robust migration versioning mechanism - it compares a hash of the SQL file to a new table in the database that it creates, and for it to be satisfied that the database is up to date, the hash in the DB must match the hash of the migrations file.

• If the database doesn't have the migrations table, all migrations occur.
• If the database has only some migrations applied, it applies the rest.
• If the database has all migrations applied, nothing happens.

So when using Flyway, we run it every time before the app server starts, so that the migrations are always in line with the source code version. Flyway succeeds as long as one of the 3 above cases are true.

To combine it with procrastinate migrations, it gets tricky. My current process is:

1. Apply the procrastinate migrations first up
2. 'Baseline' Flyway (tell it to ignore the stuff already in the DB that procrastinate made)
3. Apply the Flyway migrations

If we do nothing, Pain will occur when procrastinate changes its initial SQL, because our production database already has a set of migrations applied, and I'll have to figure out what you do with each new set of SQL you write for your tool, and incorporate it.

I raised this ticket because procrastinate doens't work the same way as Flyway - when you run procrastinate schema --apply over and over, it fails if the migrations are as procrastinate expects (i.e. they are correct). I want to run it over and over so that I know they're always there, and when you update procrastinate, it can just apply the new migrations.

I see some other options too:

1. Switch to using Flyway to manage all the migrations (much cleaner, but depends on how willing you guys are to change your migration format to Flyway's approach) - I could probably fork it to handle this?
2. Switch to using a Python-native SQL migrations tool for Procrastinate (yoyo looks good and handles multiple projects side-by-side very nicely). I could help with this if you guys like, but it seems pretty simple tbh, just some import and rework of this function. I would then use yoyo too.
3. I stop using any migrations tools, but I don't like this option!

[ewjoachim]

Switch to using Flyway to manage all the migrations (much cleaner, but depends on how willing you guys are to change your migration format to Flyway's approach) - I could probably fork it to handle this?

We've been using septentrion for a while, then we kinda switched to pum, then migra (mainly for the test, I believe they're not advertised in the doc) and we also support Django migrations. I'm not sure we would change to yet another migration tool, but if there are simple changes we can do to our migrations to help various tools keep track of them, sure.

yoyo looks good

I believe that the only thing we'd be missing to make our migrations yoyo-compatible would be dependency comments?

If so, I'm not opposed to adding those to our migration files.

[dvasdekis]

Oh! I didn't know you used a proper migraitons tool. I can use Migra - let me switch to that.

Probably then the thing that's needed is adding documentation about how to use your migrations alongside user-developed migrations to the docs. Let me get back to you with a PR once I figure it out.

Thanks!

[dvasdekis]

Just checking - it seems that in the sql/migrations/ folder you've got a bunch of migrations, which are then replicated in schema.sql.

Is sql/migrations just the versioned edition of the schema.sql file?

Thanks!

[ewjoachim]

In a word, yes.

schema.sql describes the final schema. The migration files describe, version by version, how to go from the schema of the previous version to the schema of the current version. They won't give you the exact schema because the migrations also need to handle details like backwards compat, but it's mostly the same.

https://procrastinate.readthedocs.io/en/stable/howto/migrations.html

[dvasdekis]

Thanks for letting me know. I got it all working by integrating this file with my user-defined Flyway migrations, and running it as one process. Cheers!

[ewjoachim]

Cheers to you too :)