Generate Database Migration from hibernate model

[rmanibus]

Description

Today, there is no real way to generate db migrations.

Current Process: When creating a database migration, the workflow is quite cumbersome:

• Modify the entity.
• Check the update script in the dev UI (when accessible—sometimes the app doesn't even start, making it unavailable).
• Manually create a new migration file.
• Customize the update script as needed.

New Workflow: the process is simplified to:

• Modify the entity.
• Customize the update script as needed.

This could then be executed either from the dev UI, or by registering a new maven / gradle task, that way it is accessible even if the app does not start.

I remember when I first started coding in PHP, doctrine was doing this very well: https://symfony.com/bundles/DoctrineMigrationsBundle/current/index.html

Implementation ideas

Liquidbase provide a diff command that compute the difference between two database, and a generate changelog command that generate migrations from this diff.

Instead of comparing real databases, we could compare a virtual database created from hibernate persistence model and the local dev db.

There is already some work around a hibernate virtual db, but it is not working with quarkus:
https://github.com/liquibase/liquibase-hibernate

See linked PR

[quarkus-bot]

/cc @andrejpetras (liquibase), @geoand (liquibase), @gsmet (hibernate-orm,liquibase), @yrodiere (hibernate-orm)

[rmanibus]

The way it has been done in liquidbase-hibernate is by leveraging the org.hibernate.boot.Metadata.
There is a way to access the metadata object using the Integrator spi, I did some experiment around that in the attached PR.

[siddharthapd]

@rmanibus Thanks for raising this here, I did created an issue in liquibase-hibernate project liquibase/liquibase-hibernate#626 last year.

Secondly there is a way to integrate liquibase-hibernate which can generate diff. The only catch / hack is that in your gradle goals for liquibaseRuntime you need to import spring dependency for that. i have provided a working example here -->
liquibase/liquibase-hibernate#436 (comment)

Please check Issue comment - 1869420287

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
    liquibase
    liquibaseRuntime.extendsFrom runtime
}

ext {
    diffChangeLogVersion = "CHANGE-0002"
    rollbackTagVersion = "CHANGE-0002"
    diffChangeLogFile = "src/main/resources/XXXX/db-changelog-${diffChangeLogVersion}.oracle.sql"
    entitiesPackage =  XXX.XXX.XXX.XXX"
    hibernateGenericDialect = "org.hibernate.dialect.OracleDialect"
    springCoreVersion = "6.1.2"
    springDataVersion = "3.2.1"
}

dependencies {
    // Liquibase
    implementation "io.quarkus:quarkus-liquibase"
    implementation "org.liquibase:liquibase-core:4.25.1"
    liquibaseRuntime "org.liquibase:liquibase-core:4.25.1"
    liquibaseRuntime "org.liquibase.ext:liquibase-hibernate6:4.25.1"
    //liquibaseRuntime "org.liquibase:liquibase-groovy-dsl:3.0.2"
    liquibaseRuntime "info.picocli:picocli:4.7.5"
    liquibaseRuntime "com.oracle.database.jdbc:ojdbc11-production:23.2.0.0"
    liquibaseRuntime "javax.xml.bind:jaxb-api:2.3.1"
    liquibaseRuntime "ch.qos.logback:logback-core:1.2.9"
    liquibaseRuntime "ch.qos.logback:logback-classic:1.2.9"

    liquibaseRuntime "org.springframework:spring-core:${springCoreVersion}"
    liquibaseRuntime "org.springframework.data:spring-data-jpa:${springDataVersion}"
    liquibaseRuntime "org.springframework.data:spring-data-envers:${springDataVersion}"
    liquibaseRuntime sourceSets.main.output
}

task deleteDiffChangeLog(type: Delete) {
    delete diffChangeLogFile
}

task liquibaseEntitiesToDbDiffChangelog(type: JavaExec) {
    dependsOn deleteDiffChangeLog
    group = "liquibase"
    classpath sourceSets.main.runtimeClasspath
    classpath configurations.liquibaseRuntime
    mainClass = "liquibase.integration.commandline.LiquibaseCommandLine"
    args "--logLevel=FINE"
    args "--changeLogFile=${diffChangeLogFile}"
    args "--url=${dbURL}"
    args "--username=${dbUser}"
    args "--password=${dbPassword}"
    args "--defaultSchemaName=${dbSchema}"
    args "--driver=${dbDriver}"
    args "--referenceUrl=hibernate:spring:${entitiesPackage}?dialect=${hibernateGenericDialect}"
    args "diffChangeLog"
}

[siddharthapd]

Also there is a problem with current liquibase-hibernate plugin version 4.29.2 i.e it does not generate changelog against empty local database, which is kind of absurd IMHO. we definately need something quarkus native diff plugin to generate the diff. current liquibase-hibernate plugin is very much broken with latest hibernate versions.

[yrodiere]

If you want the migration script to be generated automatically, why not use the Hibernate feature?

For context, Hibernate ORM is able to create initial DDL scripts, and we already have something that allows turning that into the initial DDL script for Flyway, using a button in the Dev UI:

quarkus/extensions/flyway/runtime/src/main/java/io/quarkus/flyway/runtime/devui/FlywayJsonRpcService.java

Lines 97 to 111 in f308816

	public FlywayActionResponse create(String ds) {
	this.getDatasources(); // Make sure we populated the datasources
	Supplier<String> found = initialSqlSuppliers.get(ds);
	if (found == null) {
	return new FlywayActionResponse("error", "Unable to find SQL generator");
	}
	String script = found.get();
	Flyway flyway = getFlyway(ds);
	if (flyway != null) {
	if (script != null) {
	Map<String, String> params = Map.of("ds", ds, "script", script, "artifactId", artifactId);
	try {

quarkus/extensions/hibernate-orm/deployment/src/main/java/io/quarkus/hibernate/orm/deployment/dev/HibernateOrmDevUIProcessor.java

Line 68 in 63edc9f

.produce(new JdbcInitialSQLGeneratorBuildItem(dsName, new HibernateOrmDevInfoCreateDDLSupplier(puName)));

We could most likely do that for Liquibase as well.

Back to the point, Hibernate ORM is also able to create "update" DDL, to migrate your schema from what it currently is to what Hibernate ORM expects. So... we could have something similar, another button on the Dev UI to add a Flyway/Liquibase migration?

And that wouldn't require thousands of lines of code like in #43724 .

EDIT: Note that I'm not personally recommending such automatic DDL updates, as they can miss critical details and lead to loss of data (e.g. if you rename a column). But I guess if a human has a look at the script after it's generated and before it's executed, that could work.

[rmanibus]

I didn't know that hibernate was able to create "diff" scripts, that's a super interesting lead !

@yrodiere to your point, the goal here is not to let hibernate blindly manage migrations, but rather to create a base to simplify developer's life

[rmanibus]

one more consideration:
Generally speaking running from the dev UI is mostly fine, but in some cases, the app would not start without the migration applied, in this case it is impossible to trigger the migration from the dev UI, so we should have an alternate way of running it.

[rmanibus]

I will provide a PR for this one

[siddharthapd]

@rmanibus are you not creating anything for liquibase ?

[rmanibus]

I think both are fairly easy to implement, I am just going to start by flyway because that's what my project is using.