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.