Moving away from gobuffalo/fizz SQL migration DSL and constraining MySQL to 8.0+

[aeneasr]

Is your feature request related to a problem? Please describe.

We're currently using gobuffalo/fizz which is a a common DSL for migrating SQL databases. Unfortunately it turned out that fizz has quite a few bugs and unfortunate design decisions which make it impossible to rely on the project as migrations can change unexpectedly over time.

I have opened two issues which should help clarify what the problems are - if you're interested in the technical aspects and reasons I recommend giving them a read: gobuffalo/fizz#91, gobuffalo/fizz#92

The gobuffalo framework is awesome, so please don't send any negative energy towards them. I think fizz is also a great product, it's just not as matured as I hoped it to be. This is also an oversight on my end which I am sorry for!

Describe the solution you'd like

We will need to move away from fizz and back to raw SQL migrations. Because the schema is currently broken (see gobuffalo/fizz#91) and because it is very difficult to figure out what database deployment has what bugs (see gobuffalo/fizz#92) it probably makes sense to start the migrations from fresh and have the whole schema created from scratch.

This of course implies that existing databases will not be migratable and that it might not be possible to move from ORY Kratos v0.3 to v0.4 without losing data.

I don't think that there are currently any prod systems running, so I hope that this isn't a big deal, but if there are I'd like to assure you that we will try to figure out a migration path for you. If you're affected by this, please comment on this issue!

Describe alternatives you've considered

We could invest a significant amount of time trying to figure out how to migrate an existing database to the new migration schema, but we'd much rather work on features instead. It really depends on you - the community!

Additional Context

I will start working on this next week so if you have any feedback please share it beforehand!

[aeneasr]

As part of this change we also want to move away from MySQL 5.6/5.7 to MySQL 8.0 because it handles json types, has timestamp precision, and is generally faster (MySQL tests take the longest in ORY Kratos). MySQL is GA since 2018 and is supported on all major cloud platforms except for GCP.

[romcok]

Hi,
we are currently working on the integration of Kratos into our system. In about a month, we would like to use it for production. Do you think it will be resolved by then?
We are using Postgres database.

[aeneasr]

It should be done by then

[kiwicopple]

I'm also interested in the Postgres integration 🐘

[aeneasr]

What do you mean by that?

[kiwicopple]

I'm echoing romcok's comment. I'd love to give this a try, and we have a production dependency on Postgres.

Small aside - it looks like you already have a Postgres quickstart in the repo? I'm trying to do a POC, but it seems kratos-migrate in 0.0.3 only works with sqlite?

docker-compose -f quickstart.yml -f quickstart-postgres.yml up --build --force-recreate

[aeneasr]

You're in the wrong issue it appears. Please report bugs in the appropriate issue or create a new issue. It's also preferred to bump issues that you also have with a "+1" emoji, to keep notification load to a minimum for maintainers. Thank you.

[aeneasr]

Good news, I was able to fix migrations without affecting existing databases. The only database dialect that will have issues is SQLite, but given that we consider SQLite to be temporary/thoraway anyways we won't have a proper fix here!

If you want to check out the upstream work we did:

• Handle foreign keys and indices properly in SQLite and add e2e tests gobuffalo/fizz#94
• Only run compatible down migrations gobuffalo/pop#564
• Return error when migration version does not exist gobuffalo/pop#563
• Move to maintained jackc/pgx driver gobuffalo/pop#562

This means that there will be no breaking changes for existing deployments that use PostgreSQL, MySQL, or CockroachDB!

I am thus closing this issue.