Database Evolutions with Jooq ?

[Bernd Huber]

i like to use the following frameworks for database evolutions (migrations)

- liquibase

- flyway

- play framework evolutions

but they all have some drawbacks:

- liquibase: 

  - complicated rollbacks and tedious XML/SQL files that are very maintenance intensive

  - only transaction safe within each changeset (no full transactional safety for all changesets at once)

flyway:

  - no rollbacks (without commercial)

play framework evolutions:

  - no good support outside of the play framework world.

---

i somewhere read that (i dont know exactly where), that jooq also plans to provide an own database evolutions framework with jooq. 

Wanted to ask if there is something in the making... :)

It could be nice to write typesafe Database Evolutions with Jooq, make rollbacks, convert them to SQL if needed outside of the framework, etc.

[Lukas Eder]

Hi Bernd,

Thanks for your message.

Indeed, there's a jOOQ-migrations draft module, which is currently on hold, but not abandoned. The main reason for it being on hold is that there are a lot of much simpler things to do in jOOQ itself before tackling such a huge project like database migrations. If you look at jOOQ 3.19:

https://blog.jooq.org/jooq-3-19s-new-explicit-and-implicit-to-many-path-joins/

https://blog.jooq.org/jooq-3-19-0-released-with-duckdb-trino-oracle-23c-support-join-path-improvements-an-official-gradle-plugin-commercial-maven-repositories-policies-udt-paths-trigger-meta-data-hierarchies-and/

- Explicit path joins

- To-many (including many-to-many) path joins

- Path correlations (such a game changer for MULTISET and other correlated subqueries!)

- The above will spin off into even more exciting features around nesting that are constantly recurring, see e.g. this discussion: https://groups.google.com/g/jooq-user/c/MbKxVxyI2Bk

- Gradle plugin (another gift that keeps on giving)

- Commercial maven repository

- Policies (quite a few new customers just because of this!)

- UDT paths

All of these things were small and limited in scope, though still challenging. Database migrations is a huge project in comparison. When it started, it created tons of spin-off projects, which have been implemented already, including:

- Tons of parser improvements

- Tons of DDL features and emulations

- The DDL interpreter

- The diff functionality (check it out here: https://www.jooq.org/diff)

These things will by themselves lead to other improvements that people can already use today, including:

- Better DDL translator support (see https://www.jooq.org/translate)

- A hopefully soon improved DDLDatabase that doesn't rely on H2 anymore

- Database schema versioning (being able to work with two versions of a schema at the same time)

- etc.

The idea here is that with all the little prerequisites implemented, supporting database migrations will eventually just be a small API that combines tons of little, battle tested (in production) features that have been around for a long time. Perhaps, for jOOQ-migrations, things are a bit more tricky than for Liquibase/Flyway, because it will have to work effortlessly with jOOQ and jOOQ's code generator, CI/CD pipelines, etc. *in addition to* being able to be used standalone with other ORMs, for example.

But you can help! :)

While there's not much to say about Flyway (it's "just" a SQL script management API), I'm curious what you like / dislike about Liquibase. Why are the XML files maintenance intensive?

Also, what's so great about Play Framework Evolutions? What particular things should jOOQ-migrations take inspiration from?

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d636129e-df4f-4d2f-a518-dc31c2ea2013n%40googlegroups.com.

[Bernd Huber]

Hello Lukas,

thanks for the nice explanations!

Im currently reading the book "jooq masterclass" by anghel leonard and its a great read.

jOOQ has so many features, and the two blog articles you linked here are also very interesting, i will try to make use of them.

I even searched for a jOOQ sticker online to put on my work laptop, but thats something still missing :)

---

what i like about liquibase:

---

1. Contexts

Liquibase allows changesets to be associated with context(s).

I need to be able to associate my changesets in two differents contexts: "ddl", "dml".

Because then i can provide following workflow:

- Execute all "ddl" changesets, but only mark "dml" changesets as executed (do not execute them).

This way i can always create an empty schema that does not contain data. 

Please see my blog-post (its still a draft) about this topic:

- https://funkrusher.de/2024/05/20/database-operations/

---

2. Developers can work on different changesets at the same time (with Help of Tags, Rollbacks and free filenames)

Because in liquibase changeset-files can get free filenames ("featurexyz.xml") there are less conflicts.

- Peter can work on "featurexyz.xml" in his Git-Branch "featurexyz"

- Maria can work on "featureabc.xml" in her Git-Branch "featureabc"

Maria is going to Vacation for 1 Week. Now Peter can manually Rollback his "featurexyz.xml" because he gave the changeset contained in this XML-File

a specific Tag ("featurexyz"). He executes Liquibase Rollback manually (via a gradle task for example) and rollbacks this changeset locally (Rollback-Sqls are executed)

Now he checkout Marias Git-Branch, and starts his App. Now Marias Changeset "featureabc" is executed.

In other Migration-Tools like Flyway or Play-Evolutions the Filenames often need to have ascending order, like: "001.sql", "002.sql".

For the described use-case, Maria and Peter would have both worked on the File "002.sql", and this File would need to contain Rollback-Sql also.

When Maria merges her Branch into the Dev, and Peter merges the Dev into his Branch he will need to resolve a Git-Conflict first. He would need to rename his 002.sql

to 003.sql and override his 002.sql with Marias 002.sql.

---

What i dislike about Liquibase.

Liquibase has no option to execute all Changesets in one! Transaction.

Each Changeset is executed in a Transaction.

When one of the Changesets fail for any reason (maybe even during Live-Deploy) the Developer / Dev-Ops Person must fix this State where some changesets

have already been applied successfully, while the rest could not be applied because of an error.

If all changesets could be forced to be executed in One! Transaction (some Databases support this), it would be much safer.

The XML-Files are maintenance intensive because they are very "speaky". I need to provide multiple Identifiers for the changeset:

- Author

- Id

- Tag

- Context

- Ids need to be references sometimes again in the chageset..

Intellij does not show the <sql> in an XML-Changeset nicely, but mostly i would want to use plain SQL and not the Liquibase XML-Syntax (even though it is typesafe),

because it should be possible to always get the plain SQL of those changesets, because sometimes a release-deployer or dev-ops person should be able (in case of error)

to manually apply them safely to the database. Im not really sure if liquibase can also generate SQL out of the XML-Format, and if i should use this.

---

Play-Framework:

The Play-Evolutions during startup automatically detect, if the local Migration-Files are before/behind the applied migrations,

and if there is a discrepancy (diff).

When Play-Evolutions detects that there is a Diff at some evolution:

- It rollbacks all evolutions in backward ordering until it reaches this diff evolution and also rollbacks this.

- Afterwards it forward-migrates this evolution again and also all evolutions coming after this one.

This perfectly supports the typical Workflow of Developers, when they need to:

- switch branches

- work on different feature-branches that contain different new migrations (be able to fastly rollback and apply evolutions in different development branches)

Play-Evolutions has a very nice Local Developer Workflow going on, which mainly consists of a Small UI in the Webbrowser, that has an "Apply Evolutions" Button.

When there are errors, one could easily see the problematic code part in the browser and could manually fix his Db and afterwards click on the "Apply Evolutions" Button again,

to continue with his business without much interference.

--------------------------

It would be cool if jooq-migrations:

- would also be able to automatically! rollback changesets until the Diff (like play evolutions). Liquibase can not rollback automatically, only manually.

- has contexts like liquibase to put changesets into different categories.

- has unique changeset ids like liquibase that do not produce git conflicts (not like play-evolutions).

just some input :)

best regards,

Bernd

[Bernd Huber]

what i also dont like about liquibase. Many different ids:

• Ids
  
• Filenames
• Tags
• Labels

while this is powerful, it makes it much harders for the easier use-cases, because for most use-cases an Id would be good enough.

For example: Rollbacks only work with Tags, so if you want to use Rollbacks in Liquibase you must also provides Tags. Tags are only Supported in the XML-Format, and not in the SQL-Format of Liquibase, so as soon as you need Rollbacks you have no choice but the XML-Format (you can put <sql> into it of course to hack your way to still use sql instead of the XML-Format).

[Bernd Huber]

what i also dont like about liquibase. Many different ids:

• Ids
  
• Filenames
• Tags
• Labels

while this is powerful, it makes it much harders for the easier use-cases, because for most use-cases an Id would be good enough.

For example: Rollbacks only work with Tags, so if you want to use Rollbacks in Liquibase you must also provides Tags. Tags are only Supported in the XML-Format, and not in the SQL-Format of Liquibase, so as soon as you need Rollbacks you have no choice but the XML-Format (you can put <sql> into it of course to hack your way to still use sql instead of the XML-Format).

Sicher versendet mit Proton Mail.

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/B8SCnqb9c-E/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9243b730-00cb-4676-9246-9da1ddb02bc4n%40googlegroups.com.

[Mark Derricutt]

We went with Flyway as an independent migration framework (our app has a mix of legacy Hibernate and Jooq), and we run the migrations as a separate process outside of the application itself.

The lack of rollbacks was an initial concern - eventually, we found the fact that migrations would rollback via transaction isolation on error (SQL or otherwise) mitigated A LOT of that.

There are still issues of migrations altering, or removing data that can't be rolled back - or more, prevent rolling back software versions - but thru judicious planning, and having backups if needed we’ve not yet really had a scenario where that's been an issue. touch wood.

Mark

--
"Great artists are extremely selfish and arrogant things" — Steven Wilson, Porcupine Tree

[Lukas Eder]

Thanks a lot for your feedback, Bernd, greatly appreciated!

On Mon, May 20, 2024 at 1:08 PM 'Bernd Huber' via jOOQ User Group <jooq...@googlegroups.com> wrote:

I even searched for a jOOQ sticker online to put on my work laptop, but thats something still missing :)

You get stickers if you talk at conferences / user groups! :)

 

1. Contexts

Liquibase allows changesets to be associated with context(s).

I need to be able to associate my changesets in two differents contexts: "ddl", "dml".

Because then i can provide following workflow:

- Execute all "ddl" changesets, but only mark "dml" changesets as executed (do not execute them).

This way i can always create an empty schema that does not contain data. 

Please see my blog-post (its still a draft) about this topic:

- https://funkrusher.de/2024/05/20/database-operations/

I can see how this adds some value for development, but in my experience, it's a lot more messy than that. A "changeset" (i.e. something that should ideally deploy atomically) often involves creating objects (even temporary one), preparing data, creating more objects, creating objects dynamically based on data, deleting temporary data again, dropping temporary objects. Perhaps, this isn't what everyone was doing, but we certainly did this a lot of times where I worked before.

Anyway, perhaps for more simple cases, it's already good enough to have this simple view of things being strictly DDL or DML.

 

2. Developers can work on different changesets at the same time (with Help of Tags, Rollbacks and free filenames)

Because in liquibase changeset-files can get free filenames ("featurexyz.xml") there are less conflicts.

- Peter can work on "featurexyz.xml" in his Git-Branch "featurexyz"

- Maria can work on "featureabc.xml" in her Git-Branch "featureabc"

Maria is going to Vacation for 1 Week. Now Peter can manually Rollback his "featurexyz.xml" because he gave the changeset contained in this XML-File

a specific Tag ("featurexyz"). He executes Liquibase Rollback manually (via a gradle task for example) and rollbacks this changeset locally (Rollback-Sqls are executed)

Now he checkout Marias Git-Branch, and starts his App. Now Marias Changeset "featureabc" is executed.

In other Migration-Tools like Flyway or Play-Evolutions the Filenames often need to have ascending order, like: "001.sql", "002.sql".

For the described use-case, Maria and Peter would have both worked on the File "002.sql", and this File would need to contain Rollback-Sql also.

When Maria merges her Branch into the Dev, and Peter merges the Dev into his Branch he will need to resolve a Git-Conflict first. He would need to rename his 002.sql

to 003.sql and override his 002.sql with Marias 002.sql.

Yes, I never liked Flyway's strict ordering of files based on manually assigned numbers. That makes merging branches very painful. I have something even better in mind, but am not confident enough to talk about it in detail yet. Just think about it this way, you've already completely versioned all branches and merges in git, so in theory, everything could be derived automatically from this history (along with jOOQ's diff and DDL interpretation capabilities, etc.).

 

What i dislike about Liquibase.

Liquibase has no option to execute all Changesets in one! Transaction.

Each Changeset is executed in a Transaction.

When one of the Changesets fail for any reason (maybe even during Live-Deploy) the Developer / Dev-Ops Person must fix this State where some changesets

have already been applied successfully, while the rest could not be applied because of an error.

If all changesets could be forced to be executed in One! Transaction (some Databases support this), it would be much safer.

That's the ideal, of course. Though it's already good that a unit can be executed atomically.

 

The XML-Files are maintenance intensive because they are very "speaky". I need to provide multiple Identifiers for the changeset:

- Author

- Id

- Tag

- Context

- Ids need to be references sometimes again in the chageset..

Intellij does not show the <sql> in an XML-Changeset nicely, but mostly i would want to use plain SQL and not the Liquibase XML-Syntax (even though it is typesafe),

because it should be possible to always get the plain SQL of those changesets, because sometimes a release-deployer or dev-ops person should be able (in case of error)

to manually apply them safely to the database. Im not really sure if liquibase can also generate SQL out of the XML-Format, and if i should use this.

I would believe that Liquibase can export the generated SQL through logs, dry runs, no? It's certainly something jOOQ will be capable to do, through various APIs, irrespective of the storage format for the migration scripts (both SQL and XML will certainly be supported, I doubt that I'll support YAML or JSON, they're both worse formats than XML for structured data, in my opinion)

 

Play-Framework:

The Play-Evolutions during startup automatically detect, if the local Migration-Files are before/behind the applied migrations,

and if there is a discrepancy (diff).

When Play-Evolutions detects that there is a Diff at some evolution:

- It rollbacks all evolutions in backward ordering until it reaches this diff evolution and also rollbacks this.

- Afterwards it forward-migrates this evolution again and also all evolutions coming after this one.

This perfectly supports the typical Workflow of Developers, when they need to:

- switch branches

- work on different feature-branches that contain different new migrations (be able to fastly rollback and apply evolutions in different development branches)

Play-Evolutions has a very nice Local Developer Workflow going on, which mainly consists of a Small UI in the Webbrowser, that has an "Apply Evolutions" Button.

When there are errors, one could easily see the problematic code part in the browser and could manually fix his Db and afterwards click on the "Apply Evolutions" Button again,

to continue with his business without much interference.

I see, this is also prototyped in jOOQ-migrations as well, already. Branch switching should always be possible very easily in dev, maybe even simpler than what you describe here. Though I haven't done the exercise of looking into all the tons of edge cases yet, so it might be that jOOQ-migrations will work almost like what you described here after all.

 

It would be cool if jooq-migrations:

- would also be able to automatically! rollback changesets until the Diff (like play evolutions). Liquibase can not rollback automatically, only manually.

- has contexts like liquibase to put changesets into different categories.

- has unique changeset ids like liquibase that do not produce git conflicts (not like play-evolutions).

Yeah, all those boxes will be checked.

[Bernd Huber]

Hello Lukas,

> You get stickers if you talk at conferences / user groups! :)

ah, thats a bit of a hard task :D i think i need to pass here ;)

---

i find the idea of jooq migrations surely interesting,

thanks for sharing the ideas.

one thing keeps my head spinning there, which is:

how could jooq-migrations detect, when a changeset has changed.

Other migration-frameworks would compare the SQL in the Changeset/File with the SQL in the "Protocol-Table" (in liquibase this is the table DATABASECHANGELOG, or in play-framework "play_evolutions")

But because the generated SQL of jooq could also change with versions-upgrade of jooq (when you found a better way to build some SQL),

i think that you can not use the SQL for detecting the change. Maybe you even need to save the Codeparts in the Java-Class that contains the Jooq-Code for migrating.

So if anything is changed in this Java-File the migration needs to be marked as changed, or something like that.

But i will be excited to see how this would work, when it will sometimes come then :)

greetings, Bernd

[Lukas Eder]

On Fri, May 31, 2024 at 3:48 PM 'Bernd Huber' via jOOQ User Group <jooq...@googlegroups.com> wrote:

how could jooq-migrations detect, when a changeset has changed.

Other migration-frameworks would compare the SQL in the Changeset/File with the SQL in the "Protocol-Table" (in liquibase this is the table DATABASECHANGELOG, or in play-framework "play_evolutions")

But because the generated SQL of jooq could also change with versions-upgrade of jooq (when you found a better way to build some SQL),

i think that you can not use the SQL for detecting the change. Maybe you even need to save the Codeparts in the Java-Class that contains the Jooq-Code for migrating.

So if anything is changed in this Java-File the migration needs to be marked as changed, or something like that.

There's always a bit of "input source" for any change, e.g. SQL that is parsed by jOOQ but written by you, git hash for the relevant file, etc. It doesn't matter what jOOQ renders for your input SQL (including you changing settings, such as case sensitivity settings, etc.)

[Tamás Cservenák]

Did someone mention jooq stickers?

:D

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4LP5S87rBA9xyHJ%2BNxdiEHMA7Jrm6YH9EnFrPoGpBPBQ%40mail.gmail.com.