SchemaUtils.createIfNotExists disregards configured schema name

[PerWiklander]

Versions used

Akka version: 2.6.16
akka-persistence-jdbc version: 5.0.4

database: Postgresql

Expected Behavior

1. Create a schema named journal

2. application.config:

   journal-schema-name = "journal"
   
   jdbc-journal {
     tables.event_journal.schemaName = ${journal-schema-name}
     tables.event_tag.schemaName     = ${journal-schema-name}
   }
   
   jdbc-snapshot-store {
     tables.snapshot.schemaName = ${journal-schema-name}
   }
   

3. Run SchemaUtils.createIfNotExists()

4. Tables are created in schema journal

Actual Behavior

1. Create a schema named journal
2. application.config as above
3. Run SchemaUtils.createIfNotExists()
4. Tables are created in schema public

Comments

Looking a the implementation it is obvious that this fails since the schema is created from a static file:
schema/postgres/postgres-create-schema.sql

Why not use Slick to generate the schema from
akka.persistence.jdbc.journal.dao.JournalTables instead?

FYI: I'm doing this from an SBT task to provide a simpler local dev setup. I have no plans on running SchemaUtils in prod.

EDIT: I was only concerned with the schema here, but this of course applies to all settings like table and column names as well.

[octonato]

hi @PerWiklander

There were two reasons for us to not use the Slick table generation.

The most important one is that it's easier to read plain SQL and we use the same SQL in our docs. So we need to write the SQL anyway and we want to have code that runs it so we can prove it's correct.

Another reason, is that we had plans to remove Slick and use plain old JDBC. For the usage here, Slick is overkill. Unfortunately, we never got into refactoring it to plain old JDBC.

Concerning the usage of the SQL script, we also wanted it to be as simple as possible. We are aware that you can't tweak the table. It's by design.

Instead, you should use SchemaUtils.applyScript. This method accepts a String (any SQL statement) and runs it against the configured database. That should give you enough flexibility to create and drop any table or index.

[octonato]

Duplicate of #500

[PerWiklander]

Ok, good to have the background info.

I ended up using SchemaUtils.applyScript with a copy of the schema with variables for the schema and table names (from application.conf).

But if I knew about this from the beginning I would have used an easier way to send a SQL query to my database 😃

[octonato]

Maybe we should update the documentation to make it clear what are the limitations and how to work it around.

Would you be willing to send a PR?
This is the page we need to update: https://doc.akka.io/docs/akka-persistence-jdbc/current/configuration.html