Multiple import.sql files

[rvansa]

Description
Currently quarkus.hibernate-orm.sql-load-script accepts only single file. It would be very nice to support a list of files. In my use case I want to keep import.sql for test data but at the same time I need to create some extra tables, register triggers etc. in both test and production setup. I don't want to keep these in both production-profile file and import.sql, but this needs to be invoked after the drop-and-create in dev mode.

[rvansa]

I have also considered using persistence.xml, but Quarkus does not allow me to use custom persistence.xml for each profile (and in production setup I can't run the auxiliary script from Quarkus as I am running it with different db role).
So it seems I just have to code running it from one of the @ApplicationScoped services, and parse SQL (which is not trivial when it defines stored functions, so it's not just a matter of splitting that up by semicolons).

[Sanne]

Hi @rvansa , we could add this, I suppose we could accept a list of resource names (comma separated?) to then import them all sequentially.

But this would be very similar to you just pasting it all in one script, did you consider that?

Also I'm wondering if you shouldn't use one of the Flyway or Liquibase extensions. In general, the Hibernate implementation of sql-load-script is meant to be the simple option - for more complex needs we direct people to use those other libraries; not least as you mention we need to parse the statements and I'm not sure how the ORM parser will deal with triggers and functions.

[Sanne]

Have a look at this integration test, it's using both Flyway and ORM, and as you can see setting up the integration is almost zero effort (code would be even simpler if it didn't have to test the migrations):

• https://github.com/quarkusio/quarkus/tree/33b7c30da6239fb3c4407532c8bdecd8e5c7cebb/integration-tests/flyway

But let me know if I'm missing something and if you still think we should add the support for lists...

[rvansa]

Thanks for the suggestions, @Sanne - as I've mentioned keeping it all in single file is not suitable as some parts are suitable for production and the test data is not.

Yes, Flyway or Liquibase could probably do the trick - I was considering using Liquibase before but did not realize that it can be used for the initial setup as well - my bad Flyway is even menioned in https://quarkus.io/guides/hibernate-orm#hibernate-orm-in-development-mode.

[Sanne]

nice, thanks @rvansa .

[rvansa]

So I've been playing with Liquibase a bit and it's nice, however it does not work well in dev setup - Hibernate's drop & create policy. All Liquibase changes are applied before Hibernate kicks in (which makes sense), however as the tables are dropped we lose triggers in the tables (defined by Liquibase).

Is it possible to define auxiliary database objects for Hibernate in Quarkus?

Since the triggers creation is not that complex I can probably repeat them in Liquibase AND import.sql, though I wonder if there's a better systematic solution.

[Sanne]

Why would you let Hibernate drop the tables if you're using Liquibase?

The way I see it, the Hibernate schema tooling is meant for either:

• validating the schema is matching the ORM expectations
• quick prototyping of model changes, e.g. while you're tuning your domain objects and want it to "suggest" how the schema is going to be affected
• the import.sql is useful when testing the newly creted model changes; also useful for demos..

Beyond these contexts, I wouldn't expect you to let Hibernate drop the schema - but please let me know :)

[rvansa]

There's difference between dev and production: In dev, I'd use Liquibase to define the auxiliary objects (tables and triggers that populate these tables based on the primary model). Drop&create is useful (and mandated by some team members) to quickly prototype changes. Once the feature is completed, the developer would check out the model and update Liquibase changelog so that it's in sync in production.
In staging/production Liquibase would be normally used to setup & migrate the database, including the auxiliary objects. Hibernate is set to validate only.

[Sanne]

Ok, thanks! Let's try to implement this then.

Do you need this urgently? It's not complex but it requires changes across multiple projects.

[rvansa]

No, it's not urgent at all. Thank you!

[agoncal]

BTW, is there a way to have import.sql including other SQL files ? I can't find any syntax for that, but I was hoping to be able to write something like that in my import.sql

include file1.sql;
include file2.sql;
include file3.sql;

That would be a first easy step to be able to execute several SQL files at startup.

[Sanne]

hi @agoncal , no there isn't a way currently. Please open an HHH issue? cc/ @beikov @sebersole might like this and I suppose it wouldn't be too hard.

• https://hibernate.atlassian.net/browse/HHH

N.B. this would only be possible in the format in which we actually parse the file (which happens to be what is being used in Quarkus)

[beikov]

The implementation would be pretty simple and I'd suggest we reuse the JPA load script property javax.persistence.sql-load-script-source. The method org.hibernate.tool.schema.internal.Helper#interpretScriptSourceSetting would only have to split the value by File.pathSeparatorChar and create some kind of aggregated ScriptSourceInput.

[gsmet]

Yeah, I think it will be far less fragile to allow defining several (ordered) files rather than implementing some sort of inclusion in the SQL parser.

@beikov I think a comma would be good enough and more in line with what we do in Quarkus. If you don't already have anything like that in ORM, I guess it would be a good choice.

[agoncal]

@Sanne Done : https://hibernate.atlassian.net/browse/HHH-14618

[beikov]

I think this issue should be closed. I created a PR for this btw: hibernate/hibernate-orm#3999

[Sanne]

We normally close an issue when it's actually fixed in Quarkus - in this case when an ORM release is made available and when the upgrade PR is merged in Quarkus.

[antoniomacri]

Hi all, the issue is closed, but I'm trying to include multiple import.sql files and cannot succeed. Also I don't see any mention of this in docs.

Shouldn't be as simple as quarkus.hibernate-orm.sql-load-script=import-1.sql, import-2.sql?

[Sanne]

Hi @antoniomacri , yes I would have expected that to work as well; it does in Hibernate ORM.

I wonder if the Quarkus configuration parsing requires quotes to deal with the ", " separator. Could you try quoting it, or just avoid the space?

We should definitely clarify this in the docs!

[antoniomacri]

Hi Sanne. It doesn't work either:

quarkus.hibernate-orm.sql-load-script="import-1.sql","import-2.sql"

Gives:

Unable to find file referenced in 'quarkus.hibernate-orm.sql-load-script="import-1.sql","import-2.sql"'. Remove property or add file to your path.

I'm using hibernate-orm-quickstart as base.

[antoniomacri]

Any news on this?

Should I open another issue?

[Sanne]

hi @antoniomacri , yes please open a new issue, I definitely risk losing track of it otherwise :)

[antoniomacri]

@Sanne Done, thanks!