SQL import script not loaded in neither profile (prod, dev, test)

[kolorobot]

Neither of the scenarios works for me as it goes to loading the SQL file:

• import.sql file in src/main/resources in profile DEV, TEST, PROD
• import.sql file in src/test/resources in profile DEV, TEST, PROD

I also tried all the properties:

• %dev.quarkus.hibernate-orm.sql-load-script = db/data-test.sql
• %test.quarkus.hibernate-orm.sql-load-script = db/data-test.sql

When no file is present I get the exception that the property is not pointing to the actual file:,

Caused by: io.quarkus.deployment.configuration.ConfigurationError: Unable to find file referenced in 'quarkus.hibernate-orm.sql-load-script=db/data-test-bad.sql'. Remove property or add file to your path.

but when I fix the path the file is not loaded anyways. I even added some no-SQL statement hoping to see errors, but nothing happens.

Environment (please complete the following information):

• Output of java -version: JDK 11
• GraalVM version (if different from Java): JDK11
• Quarkus version or git rev: 1.2.1.Final
• Postgres

Additional context

• Flyway migrations get executed properly during the startup.

Related to:

#3661
#6545
#7290

[gsmet]

Could you provide a reproducer? Thanks!

[kolorobot]

@gsmet: I prepared the sample so you can test every scenario I described. The only thing: run the postgres server (docker command in project's README) before starting the application.

https://github.com/kolorobot/quarkus-postgres-sample

[geoand]

Thanks @kolorobot

I'll take a look tonight

[geoand]

Looking at this real quick, it seems like ParsedPersistenceXmlDescriptor get's populated with the proper file.

Also the runtime logs for Hibernate do show that hibernate.hbm2ddl.import_files uses the proper value. I could dig more but it probably makes more sense for someone with Hibernate internals knowledge to check, they will definitely be able to make faster progress than me :)

[radcortez]

I did find the same issue, until I've realized that Hibernate only runs the import file if the database.generation is set to create. Not sure if this is the issue of the @kolorobot, but maybe it is.

[jonathanvila]

Same experience here...... had 1 file with tables creation and inserts , and I wanted to use the hibernate.creation=none and execute the import.sql ( I'm migrating SpringPetclinic into Quarkus spring extensions ) ..... and it doesnt read the file.....
Changing to hibernate.creation=create has worked.

[acasanova99]

Still having this issue with Quarkus 2.5.1, all works fine iff %xxxx.quarkus.hibernate-orm.database.generation=create

[Proximator]

I am also having the same issue, any update about this issue?

[gsmet]

Well, it's not really a bug. In Hibernate ORM (and it has always been the case, I think), the import script is only executed if you create the schema (which makes sense as this script is supposed to import data, not do anything else).

Now maybe we should have another feature to allow having a schema creation script that is executed always, where you could also have insert statements.

@Sanne @yrodiere WDYT?

[Proximator]

Thanks for your quick response, what I am trying to do for my integration tests, is to execute a script after Hibernate ORM has set up all the database objects, according to this article: https://www.morling.dev/blog/quarkus-and-testcontainers, it seems to be supported, I tried both options create and drop-and-create, none of them worked.
Any idea how I can fix it? or a workaround?

[gsmet]

Yes, it should work. If it doesn't, you should probably put together a reproducer so that we can have a look. We have thorough tests for that as it kept getting broken so we really need a reproducer to have a look.

[acasanova99]

The thing is that, if we have a DB with data, we cannot asume to drop the entire DB and populate it again, it would be interesting to just execute a .sql script that make some modifications although the generation is equal to "none".

I agree, it is not a bug. But it would be interesting to decide when to run the import sql script

[yrodiere]

Now maybe we should have another feature to allow having a schema creation script that is executed always, where you could also have insert statements.

@Sanne @yrodiere WDYT?

I'm not sure what the purpose of this feature is, so that makes me uncomfortable. I think it warrants some discussion:

• If the point is to create tables manually, or fix the schema on an existing database, why not use flyway/liquibase/etc.? That's a best practice, and I wouldn't want to encourage anything else.
• If the point is to populate data on an existing database that has a schema but no data, then why not just run the data initialization script along with the scripts that created the schema, manually? Also, you'd run the risk of populating the data twice if you start the application twice...
• If the point is always execute some SQL on startup, not just once, then I'd like to understand what the scripts does exactly... ?

[Sanne]

In case someone wants to contribute something trivial in this area to ORM we can take it in consideration (I haven't fully understood the details), but for anything beyond we should indeed encourage using flyway or liquibase.

We certainly don't want to expand features in ORM to the point of competing with other libraries which already do a fine job at addressing such goals.

+1 to know more about the use case :)

In general I'd recommned: use Hibernate ORM's schema management for quick experiments in development cycles on an empty database; as soon as the DB content actually becomes important to the point you don't want to drop it constantly, it also means your scripts need to be written with greater care. The other tools offer better management abilities for multiple, more complex scripts and safeguards.

[Diom]

My Use Case: I am using Flyway with Quarkus to manage the DB schema migration. I have that working and the tables are correctly instantiated at startup.

I would like the import.sql to execute only in the dev and test profiles (per the defaults). This should not be considered part of the schema proper, simply a handy way to pre-populate integration-test data. I have been considering hacks like adding a different migrations path for those profiles with a V999__Load_Test_Data.sql file in there, but I would prefer not to... I'd have to allow out of order migrations potentially.

I'll probably press on with changing how I was planning to the tests as this is not a major issue for me, but thought I would see if my use-case might help with the rationale for a change. 👍🏻

[yrodiere]

Ok, it seems everyone who commented here just wants to be able to import data on startup in dev/test mode, even with ORM schema management disabled, and independently of other tools such as Flyway.

This makes sense, but that's what #21866 is about, and #21866 explains the use case more clearly, right in the issue description.

So I'll close this ticket as a duplicate of #21866. If you think that's wrong, ping me and we'll see what needs to be done.

[yrodiere]

Duplicates #21866