Unable to set default PostgreSQL DB using Dev Services & "devservices.jdbc.additional-jdbc-properties" config setting

[jehrenzweig-leagueapps]

I'm using Dev Services + Flyway to automatically provision a PostgreSQL 14.5 database in a Quarkus 2.16.5.Final project, with IntelliJ (2023.1, Ultimate Edition) as my IDE. Here's my application.yml file:

application.yml

quarkus:
  http:
    port: 8675
  smallrye-openapi:
    enable: true
    operation-id-strategy: 'class-method'
  container-image:
    group: 'starchy/poc'
    registry: 'us-docker.pkg.dev'
    name: potato-service
    additional-tags: latest
    build: true
    push: false
  datasource:
    devservices:
      enabled: false
mp:
  openapi:
    extensions:
      smallrye:
        openapi: 3.1.0

"%localhost":
  quarkus:
    swagger-ui:
      always-include: true
      enable: true
    datasource:
      localstorage:
        devservices:
          enabled: true
          image-name: postgres:14.5
          username: root
          password: verysecure
          db-name: potato_service
          port: 87654
    flyway:
      localstorage:
        baseline-on-migrate: true
        clean-at-start: false
        default-schema: potato_service
        migrate-at-start: true
        out-of-order: false
        validate-migration-naming: true

When I startup the project in IntelliJ, Dev Services successfully provisions a PostgreSQL 14.5 database instance in a Docker container & runs my Flyway migration script (V1680622059__CreateTables.sql), which creates & populates several tables:

INFO  [org.fly.cor.int.dat.bas.BaseDatabaseType] (Quarkus Main Thread) Database: jdbc:postgresql://localhost:87654/potato_service (PostgreSQL 14.5)
INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "potato_service" ...
INFO  [org.fly.cor.int.sch.JdbcTableSchemaHistory] (Quarkus Main Thread) Creating Schema History table "potato_service"."flyway_schema_history" ...
INFO  [org.fly.cor.int.com.DbMigrate] (Quarkus Main Thread) Current version of schema "potato_service": null
INFO  [org.fly.cor.int.com.DbMigrate] (Quarkus Main Thread) Migrating schema "potato_service" to version "1680622059 - CreateTables"
INFO  [org.fly.cor.int.com.DbMigrate] (Quarkus Main Thread) Successfully applied 1 migration to schema "potato_service", now at version v1680622059 (execution time 00:00.066s)

When I connect to the database using DataGrip as my IDE, I see a schema named potato_service & a database inside it named potato_service -- so far, so good! However, at this point, I'm unsure as to whether Quarkus is using potato_service as both the datasource schema & database.

What I'd like to do is explicitly set the PostgreSQL search_path, so that there's no question about what database is being used. It seems like this may be possible, by using an effective JDBC URL of jdbc:postgresql://localhost:87654/potato_service?search_path=potato_service, instead of the jdbc:postgresql://localhost:87654/potato_service URL that Dev Services uses. There was a PR (#15749) that appears to have added support for this in March 2021, and there is code in the main branch's PostgresqlDevServicesProcessor.java file that seems like it should do the trick. But I can't figure out what configuration setting I should be using for a custom Dev Services datasource in application.yml.

I took a look in the "All Configuration Settings" document and it seemed like quarkus.datasource."datasource-name".jdbc.additional-jdbc-properties would do the trick. However, when I update my application.yml file like so:

"%localhost":
  quarkus:
    swagger-ui:
      always-include: true
      enable: true
    datasource:
      localstorage:
        devservices:
          enabled: true
          image-name: postgres:14.5
          username: root
          password: verysecure
          db-name: potato_service
          port: 87654
        jdbc:
            additional-jdbc-properties: 
              search_path: potato_service

I get the following warning when Quarkus starts up:

WARN  [io.qua.config] (Quarkus Main Thread) Unrecognized configuration key "quarkus.datasource.localstorage.devservices.jdbc.additional-jdbc-properties.search_path" was provided; it will be ignored; verify that the dependency extension for this configuration is set or that you did not make a typo

Am I doing something obviously wrong here, in how I'm trying to set the quarkus.datasource.localstorage.jdbc.additional-jdbc-properties value in application.yml? Or am I setting the value correctly & Quarkus isn't seeing it?

[quarkus-bot[bot]]

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

[jehrenzweig-leagueapps]

I just made a slight change to how I'm representing my config setting, by adding a dash before search_path: potato_service, and now I no longer get a warning in my logs. Is this how I should have been doing it all along?

"%localhost":
  quarkus:
    swagger-ui:
      always-include: true
      enable: true
    datasource:
      localstorage:
        devservices:
          enabled: true
          image-name: postgres:14.5
          username: root
          password: verysecure
          db-name: potato_service
          port: 87654
        jdbc:
            additional-jdbc-properties: 
              - search_path: potato_service

If so, then here's the last question I have: how do I confirm the effective JDBC URL that Quarkus is using? Because after making the aforementioned change, this is still all I see in IntelliJ's "Console" tab, when Quarkus starts up:

INFO  [org.fly.cor.int.dat.bas.BaseDatabaseType] (Quarkus Main Thread) Database: jdbc:postgresql://localhost:54421/cart_service (PostgreSQL 14.5)

If Quarkus is actually using my setting, I'd expect to see this instead (?search_path=cart_service as the URL suffix):

INFO  [org.fly.cor.int.dat.bas.BaseDatabaseType] (Quarkus Main Thread) Database: jdbc:postgresql://localhost:54421/cart_service?search_path=cart_service (PostgreSQL 14.5)

[geoand]

The initial error suggests you had some kind of identation issue in your YAML.

[jehrenzweig-leagueapps]

Yeah, I suspect you're right. I updated my YAML syntax & the warning disappeared; see my comment above.

Now I'm trying to confirm that Quarkus is actually using the setting. I took a look in the main branch's PostgresqlDevServicesProcessor.getEffectiveJdbcUrl() method & it appears that the additional settings only get applied when useSharedNetwork == true (link):

public String getEffectiveJdbcUrl() {
    if (useSharedNetwork) {
        // in this case we expose the URL using the network alias we created in 'configure'
        // and the container port since the application communicating with this container
        // won't be doing port mapping
        String additionalUrlParams = constructUrlParameters("?", "&");
        return "jdbc:postgresql://" + hostName + ":" + POSTGRESQL_PORT
                + "/" + getDatabaseName() + additionalUrlParams;
    } else {
        return super.getJdbcUrl();
    }
}

I'm not sure how to check the value of useSharedNetwork and/or alue of getEffectiveJdbcUrl().

[jehrenzweig-leagueapps]

I'm pretty sure that Quarkus isn't using my application.yml file's key-value pair to set the value of the PostgreSQL search_path variable, because I need to manually add the schema name to each SQL table that I reference in my Quarkus project's Kotlin code, like so:

select * from potato_service.potato_types

If Dev Services was setting the search_path value to potato_service as intended, I should be able to write SQL queries like this:

select * from potato_types