[Suggestion] Populating database in a read only application that can't use database devservices init script

[agreedSkiing]

Anyone got an idea how I could populate an application that only reads data from the database when doing blackbox testing (@QuarkusIntegrationTest) and when running the application in quarkus:dev mode?

I don't have access to flyway, but hibernate reactive and the reactive pgclient. The application has custom made postgresql views entities that contain our own uuid array logic to handle postegsql arrays (don't have access to code whilst writing this, and I think this now handled in hibernate, but yeah wasn't before), so running a hibernate create does not work since it errors out on the view entities :(.

I'm currently thinking about implementing the (https://quarkus.io/guides/reactive-sql-clients#database-schema-and-seed-data)[https://quarkus.io/guides/reactive-sql-clients#database-schema-and-seed-data], but I don't feel safe deploying that kind of logic in a read only application to a production enviroment.

Just echoing this again I still want it to populate the database in case of manual testing i quarkus dev :/, if it wasn't for that requirement i could probably just use a testprofile or the other thing to create the database.

Right right almost forgot, sorry for my rambling, our build pipeline does not support quarkus devservices /cry so no using the init script of the database devservice

[quarkus-bot[bot]]

/cc @geoand (devservices), @stuartwdouglas (devservices)

[geoand]

cc @yrodiere

[agreedSkiing]

Right shouldn't have mentioned devservices in the title :/ Sorry for the ping Geoand and Stuartwdouglas

[geoand]

No need to apologize :)

[yrodiere]

Hello,

To sum up:

• Dev services init scripts would work, but you can't use them because containers are not supported in your environment.
• Flyway/Liquibase would work, but you can't use them because... company policy would be my guess?
• Hibernate Reactive's schema/data init would work, but you can't use it because of legacy code/schemas.
• You're not comfortable adding custom code to write to the DB in your app, for fear it will accidentally write in (theoretically read-only) production.

At this point I'm glad I'm not in your shoes 😁

More seriously, if your testing DB already has a schema initialized, I think you're after #21866. Which would be great, if someone had the time to implement it -- and deal with the inevitable consequences of related breaking changes. For now nobody had that kind of time, hopefully someone will.

---

What can you do right now?

Well, you could maybe rely on Hibernate's DB init to run custom SQL, while disabling schema initialization for the parts that don't work for you, using a filter. It's not technically supported (read: tested) in Quarkus, but might work anyway. Something like this:

application.properties:

# Use a different Quarkus profile for integration tests than prod
quarkus.test.integration-test-profile=integrationtest
# Extend the "test" profile for integration tests
# NOTE: Integration tests will only pick *runtime* properties, build-time properties will still be those of prod.
%integrationtest.quarkus.config.profile.parent=test

# Init schema on test startup
%test.quarkus.hibernate-orm.database.generation=drop-and-create 
%test.quarkus.hibernate-orm.sql-load-script=import-test.sql
# Exclude from schema initialization the parts of the schema that are too specific for Hibernate
# NOTE: Try removing `%test.` if this gets ignored.
%test.quarkus.hibernate-orm.unsupported-properties."hibernate.hbm2ddl.schema_filter_provider"=com.acme.MySchemaFilterProvider

src/main/java/com/acme/MySchemaFilterProvider.java: (must be in main sources to work with integration tests)

package com.acme;

// ... imports ...

@RegisterForReflection // Necessary for native compilation
public class MySchemaFilterProvider extends DefaultSchemaFilterProvider {

   @Override
   public SchemaFilter getCreateFilter() {
      return new DefaultSchemaFilter() {
         @Override
         public boolean includeTable(Table table) {
              return !"my_table_I_dont_want_created".equals(table.getName());
         };
      };
   }
}

src/main/resources/import-test.sql: (must be in main sources to work with integration tests)

-- Initialize schema that Hibernate couldn't
<put the schema init for your custom tables/views here>

-- Initialize data
<put your data init here>

... and use a custom import.sql that would only initialize the data

---

Failing that, yes I think rolling out your own init is your only solution.

If you're not comfortable including that init the (production) app, even if it's diabled outside of integration tests, then maybe just define a test resource in your @QuarkusIntegrationTest that is responsible for setting up an SQL client, and sending SQL to the DB before/after tests to init/reset the DB. You could rely on test dependencies to Flyway/Liquibase or some other tool. But it'll be like building an SQL stack from the ground up, Quarkus won't help you much (since you'll be doing all this outside of the Quarkus app).

[agreedSkiing]

Thanks for the ideas, which helped me to formulate the last comment (#44753 (comment)) in the thread. So next workday is going to put them all to the test, even the one using a test resource.

[agreedSkiing]

Flyaway is not strictly forbidden but something our group does not want to have in a application that should not modify data and since the dependency is not needed in the application in prod I don't really want to have it myself included and to my knowledge the test scope is something that can't be used for quarkus:dev

[yrodiere]

I would argue that "something in your group" should give your application a DB user that only has read access, rather than rely on good faith.
But, my experience tells me "something in our group" often cannot be reasoned with, so I understand :)

[agreedSkiing]

Well I cloud just use flyway with the test scope and for local development use the same script (maybe) as an devserice init script. Also stupid policy that goes kinda like this do not use dependencies that are not used in prod if they aren't used in test scope.

[yrodiere]

Please answer in threads, that will be easier to follow...

[agreedSkiing]

Ah sorry 😔 was using my email client to reply