Support import.sql or similar facility for data population when not using Hibernate to create the schema

[josephearl]

Description

If I am using JPA to create my schema through Hibernate, I can easily add test data by creating an import.sql file in my resources.

However if I switch to using Flyway with PostgreSQL dev services, there doesn't seem to be an easy to do this except creating some extra migrations which are just used for test.

In Spring Boot I can create a data.sql file in my resources, and I can configure Spring Boot to execute this regardless of how my schema was created or what database I am using.

I am aware of the TC_INITSCRIPT option for test containers - but this would not work for our CI builds on k8s where TestContainers is not used (and in addition there doesn't seem to be an easy way to configure this when using dev services - quarkus.datasource.devservices.properties.TC_INIT_SCRIPT is documented not to work).

Implementation ideas

No response

[quarkus-bot]

/cc @Sanne, @gsmet, @yrodiere

[Sanne]

Good idea!
I'd be happy to review related PRs.

[yrodiere]

We keep getting similar bug reports (latest: #23821) from users who are suprised that sql-load-script is fixed at build time. And, quite frankly, it's hard to argue. I mean, wouldn't you expect sql-*load*-script to be used for... I don't know... loading data?

Anyway, I'm getting more and more convinced that the problem is, beyond the missing feature, that our configuration is confusing.

And the problem is made worse by the fact that configuration properties involved in schema generation don't even have "schema" in their name. For example quarkus.hibernate-orm.database.generation: it's obviously about "generating a database", but what does that mean? The schema? The data? Both? Currently, it means both, and that's a problem (see issue description, and #7358, and #23821, and probably more to come).

At the very least, I think we should get rid of quarkus.hibernate-orm.sql-load-script and find better names for both this existing property, and the new one we'll introduce for the data import script. Maybe quarkus.hibernate-orm.database.generation.schema-init-script and quarkus.hibernate-orm.database.generation.data-init-script? Or quarkus.hibernate-orm.database.generation.sql-init-script and quarkus.hibernate-orm.database.generation.sql-import-script? Or quarkus.hibernate-orm.database.generation.sql-schema-script and quarkus.hibernate-orm.database.generation.sql-data-script?

If I dared, I would suggest to go further and:

• rename quarkus.hibernate-orm.database.generation to quarkus.hibernate-orm.schema-management.strategy. Because, you know, it's not just about "generation"! There's drop, too. Alternatively, I suppose quarkus.hibernate-orm.schema-management.action would work, too.
• introduce a completely separate category with quarkus.hibernate-orm.data-management: quarkus.hibernate-orm.data-management.import-script, but also quarkus.hibernate-orm.data-management.strategy. The strategies would be none and import, the default being none in prod and import otherwise.

While I believe it's for the best, I suspect this won't be a popular change given that most Quarkus applications out there probably use quarkus.hibernate-orm.database.generation and will suffer from such a breaking change.

@gsmet , @Sanne , WDYT?

[Serkan80]

Is there any progress on this issue ? I'd really like to have an option to use TC_INIT_SCRIPT.

And may I ask what the reason is for not supporting the Testcontainer's jdbc url ?

[yrodiere]

Is there any progress on this issue ? I'd really like to have an option to use TC_INIT_SCRIPT.

Not yet. Personally this didn't make it to the top of my todo-list. I don't know about other contributors.

And may I ask what the reason is for not supporting the Testcontainer's jdbc url ?

I'm not sure what you're referring to. We didn't talk about a JDBC URL at all in this ticket? If this question is unrelated to this ticket, please ask it on a relevant ticket and/or start a Discussion.

[Serkan80]

I'm not sure what you're referring to. We didn't talk about a JDBC URL at all in this ticket? If this question is unrelated to this ticket, please ask it on a relevant ticket and/or start a Discussion.

The Quarkus Guide Dev Services for Databases says that Quarkus is not using the Testcontainer's jdbc driver, so therefore we can't pass extra arguments to the connection url of Testcontainers, like mentioned here in the documentation.

If this would have been possible, then this issue would not exist.

Currently, I don't see any other option then injecting the AgroalDatasource and executing sql scripts in my test cases (like creating the tables and filling them with some data), which is quite cumbersome and not elegant.

It would have been nice, like the OP mentioned, to have the option to configure it:

quarkus.datasource.devservices.properties.TC_INIT_SCRIPT=my-init-script.sql

In my case, I'm working with Debezium Embedded, and I need to connect to two databases that I want to startup via dev-services and they need to have tables and some initial data before the tests start.

[gsmet]

@Serkan80 If you are using Testcontainers, #30455 will fit your needs.

It's in the main branch only for now, but given it's small, I will try to backport it for the 2.16.3.Final planned for next Wednesday.

[rsvoboda]

Can be #30455 treated as sufficient solution for this issue? (So it can be closed)

[yrodiere]

Can be #30455 treated as sufficient solution for this issue? (So it can be closed)

Unfortunately it cannot. This issue is not limited to dev services.

From the initial description:

but this would not work for our CI builds on k8s where TestContainers is not used

Also, some people want this feature in production (e.g. #23821).

[rsvoboda]

OK, thanks Yoann.

[cmasantos]

I've been reading this and the other related issues and I'm unable to understand which work arounds are being used?

I want to use flyway to manage the schema creation, but the tests and local dev I want to be able have an import.sql file to insert to some data.

• If i use the testcontainers init script, the flyway has not been run at that point.
• Putting the insert statements at along with the flyway scritps sounds terrible wrong.
• This gist(https://gist.github.com/mwalliczek/f8f952dd9a472ff27f38bc6d84e829ee) points to a dependency without commits in the last 5 years (which might be ok).

I understand that this might not be a priority but in case someone find a good approach to this please let me know.

Thank you

[yrodiere]

Putting the insert statements at along with the flyway scritps sounds terrible wrong.

That's the only viable workaround ATM in my opinion.

Something like this could work, and not even be that bad, assuming you put your test migration scripts in src/test-data:

%test,dev.quarkus.flyway.locations=db/migration,filesystem:src/test-data/

You may have to adjust the filesystem path to match the current working directory (I assumed it's just the project's directory).

If you run integration tests, this won't work though, because quarkus.flyway.locations will be set to its default (the prod config). In that case you may want to try this, and hopefully the extension will just ignore the missing src/test-data in production...

quarkus.flyway.locations=db/migration,filesystem:src/test-data/

[cmasantos]

Putting the insert statements at along with the flyway scritps sounds terrible wrong.

That's the only viable workaround ATM in my opinion.

Something like this could work, and not even be that bad, assuming you put your test migration scripts in src/test-data:

%test,dev.quarkus.flyway.locations=db/migration,filesystem:src/test-data/

You may have to adjust the filesystem path to match the current working directory (I assumed it's just the project's directory).

If you run integration tests, this won't work though, because quarkus.flyway.locations will be set to its default (the prod config). In that case you may want to try this, and hopefully the extension will just ignore the missing src/test-data in production...

quarkus.flyway.locations=db/migration,filesystem:src/test-data/

Ok, I think I made it work with an extra dir called db/test-data with a file named V999.0.0_test_data.sql and with %test,dev.quarkus.flyway.locations=db/migration , db/test-data/ in the application.properties.

Some more examples here: https://github.com/quarkusio/quarkus/blob/main/integration-tests/flyway/src/main/resources/application.properties

Thank you

[yrodiere]

Related development in Hibernate ORM itself: https://hibernate.atlassian.net/browse/HHH-19103