So, I create my app, ready for a first release, I go and hit the "create initial migration file" and first bug: for some reason it does not add the required configuration for prod anymore:
Now, I do my release, and it runs in prod, so inevitable, someone asks for a new feature, and I need to add columns to my model. At which point I deploy my release, and although Quarkus starts with this optimistic message:
2024-06-12 15:19:33,565 INFO [org.fly.cor.int.lic.VersionPrinter] (main) Flyway Community Edition 9.22.3 by Redgate
2024-06-12 15:19:33,567 INFO [org.fly.cor.int.lic.VersionPrinter] (main) See release notes here: https://rd.gt/416ObMi
2024-06-12 15:19:33,567 INFO [org.fly.cor.int.lic.VersionPrinter] (main)
2024-06-12 15:19:33,573 INFO [org.fly.cor.FlywayExecutor] (main) Database: jdbc:postgresql://localhost:5433/rivieradev_quarkus (PostgreSQL 14.12)
2024-06-12 15:19:33,595 INFO [org.fly.cor.int.com.DbValidate] (main) Successfully validated 2 migrations (execution time 00:00.012s)
2024-06-12 15:19:33,648 INFO [org.fly.cor.int.com.DbMigrate] (main) Current version of schema "public": 1
2024-06-12 15:19:33,650 INFO [org.fly.cor.int.com.DbMigrate] (main) Schema "public" is up to date. No migration necessary.
It will actually fail when users start using my app:
2024-06-12 15:25:38,049 WARN [org.hib.eng.jdb.spi.SqlExceptionHelper] (executor-thread-1) SQL Error: 0, SQLState: 42703
2024-06-12 15:25:38,049 ERROR [org.hib.eng.jdb.spi.SqlExceptionHelper] (executor-thread-1) ERROR: column s1_0.lastupdated does not exist
Position: 81
2024-06-12 15:25:38,050 INFO [org.hib.eve.int.DefaultLoadEventListener] (executor-thread-1) HHH000327: Error performing load command: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select s1_0.id,s1_0.about,s1_0.aboutEN,s1_0.company,s1_0.companyURL,s1_0.height,s1_0.lastUpdated,s1_0.level,s1_0.linkedInAccount,s1_0.logo,s1_0.otherURL,s1_0.twitterAccount,s1_0.width from Sponsor s1_0 where s1_0.id=?] [ERROR: column s1_0.lastupdated does not exist
Position: 81] [n/a]
Well, sure, I forgot to add a migration file. Because it's too complicated.
Enable Flyway for dev mode (which is not really what I want, since I want ORM to handle schema changes during DEV)
Go back to DEV UI for Flyway, it says that it's already migrated (same lie as for prod)
Remove my initial migration rm src/main/resources/db/migration/V1.0.0__website.sql
Go back DEV UI for Flyway, hit "create initial migration"
Do a diff: git diff src/main/resources/db/migration/V1.0.0__website.sql
Paste the diff into my migration file: src/main/resources/db/migration/V2.0.0__website.sql (I suppose)
Edit the diff into valid SQL for migration
Revert the changes to the initial migration: git checkout src/main/resources/db/migration/V1.0.0__website.sql
This is WAY too complicated.
We should add a button in the DEV UI (and/or a CLI command to do the same) that generates a n+1 sql migration file based on the previous migrations and the current schema. I'm pretty sure ORM has a tool for generating best-effort update scripts based on an initial DB schema.
Description
So, I create my app, ready for a first release, I go and hit the "create initial migration file" and first bug: for some reason it does not add the required configuration for prod anymore:
Also, adding this breaks dev mode, so I need to add this (which before adding flyway was implicit):
Now, I do my release, and it runs in prod, so inevitable, someone asks for a new feature, and I need to add columns to my model. At which point I deploy my release, and although Quarkus starts with this optimistic message:
It will actually fail when users start using my app:
Well, sure, I forgot to add a migration file. Because it's too complicated.
The docs don't even mention how to do this: https://quarkus.io/guides/flyway
Frankly, I don't know how, but here's my attempt:
rm src/main/resources/db/migration/V1.0.0__website.sql
git diff src/main/resources/db/migration/V1.0.0__website.sql
src/main/resources/db/migration/V2.0.0__website.sql
(I suppose)git checkout src/main/resources/db/migration/V1.0.0__website.sql
This is WAY too complicated.
We should add a button in the DEV UI (and/or a CLI command to do the same) that generates a n+1 sql migration file based on the previous migrations and the current schema. I'm pretty sure ORM has a tool for generating best-effort update scripts based on an initial DB schema.
Implementation ideas
No response