Change jdbc column names to snake case as default.

[vab2048]

Enhancement Description

This is really a convenience request more than an enhancement/feature. It is not needed (there are workarounds) so please feel free to close the issue if it is deemed not important. Since it is a potentially breaking change, I would not expect it until Axon 5 anyway.

Currently, there are three tables needed in a relational store for the default Axon application:

• TokenEntry
• SagaEntry
• AssociationValueEntry

The request of this issue is to change the default table and column definitions from camelCase/PascalCase to snake case to make it more in line with the default with databases.

Current Behaviour

The default value of these tables are defined in Axon using camelCase (and PascalCase for the table name). E.G. see TokenEntry;

        private String tokenTable = "TokenEntry";
        private String processorNameColumn = "processorName";
        private String segmentColumn = "segment";
        private String tokenColumn = "token";
        private String tokenTypeColumn = "tokenType";
        private String timestampColumn = "timestamp";
        private String ownerColumn = "owner";

When using JDBC (at least with Postgres, which is what I am using it with) this results in all lower case names of tables and columns e.g. (tokenentry, processorname, etc).

This is unexpected to me, because DB conventions are to use snake case (please correct me if I am wrong).

I'm using the JDBC integration for the first time (I used JPA before) and I received an error with my migration script which worked when using JPA: ERROR: relation "tokenentry" does not exist. This result was unexpected because the definition of the table token_entry worked with JPA.

Of course everything works fine by default if I change the migration script to use tokenentry, processorname, etc.

Wanted Behaviour

The request of this issue is to change the default table and column definitions from camelCase/PascalCase to snake case to make it more in line with the default with databases.

This is just to make the default behaviour more seamless.

When using JPA this is done automatically for you by Hibernate and so doing this would unify the behaviour between the two.

So in Axon 5 the default would change to be token_entry, processor_name, etc.

Possible Workarounds

Instead of defining our migration scripts using the normal standard snake case, we can just adopt the axon default.

Or alternatively we can instruct Axon to use our custom definitions.

---

In reality this is truly a minor issue, I just added it because getting the "default" right makes it much easier for beginners to get a seamless experience and enjoy using Axon.

[smcvb]

I think it's completely fine to keep this issue alive, @vab2048. However, it's indeed a breaking change, with an easy workaround at the moment.

As the adjustment is simple but not overly pressing, I've added the tags "Ideal for Contribution" and "Priority 4." For those looking to pick this up, note that we'll not merge this change for any Axon 4.x version due to the aforementioned breaking changes it would introduce. As soon as Axon 5 is coming around the corner, we can start making this change too.

[Blackdread]

A middle solution would be to do similar as TokenSchema but for SagaSchema. Currently SagaSchema only defines variables sagaEntryTable and associationValueEntryTable but we should be able to override the naming of columns like:

associationKey
associationValue
sagaId
sagaType
serializedSaga
...

The strange part is that https://start.axoniq.io/ generates the flyway migration with

-- this script is compatible with PostgreSQL only
-- to use in other databases please adapt the script

DROP TABLE IF EXISTS association_value_entry;
DROP TABLE IF EXISTS token_entry;
DROP TABLE IF EXISTS saga_entry;

CREATE TABLE IF NOT EXISTS association_value_entry
(
    id SERIAL,
    association_key VARCHAR(255) NOT NULL,
    association_value VARCHAR(255),
    saga_id VARCHAR(255) NOT NULL,
    saga_type VARCHAR(255),
    PRIMARY KEY (id)
    );

CREATE INDEX IF NOT EXISTS association_value_entry_saga_type_association_key_association_value_idx
    on association_value_entry (saga_type, association_key, association_value);

CREATE INDEX IF NOT EXISTS association_value_entry_saga_id_saga_type_idx
    on association_value_entry (saga_id, saga_type);

CREATE TABLE IF NOT EXISTS token_entry
(
    processor_name VARCHAR(255) NOT NULL,
    segment INTEGER NOT NULL,
    owner VARCHAR(255),
    timestamp VARCHAR(255) NOT NULL,
    token bytea,
    token_type VARCHAR(255),
    PRIMARY KEY (processor_name, segment)
    );

CREATE TABLE IF NOT EXISTS saga_entry
(
    saga_id VARCHAR(255) NOT NULL,
    revision VARCHAR(255),
    saga_type VARCHAR(255),
    serialized_saga bytea,
    PRIMARY KEY (saga_id)
    );

but if we just copy paste that, then we get the issue that is mentioned in this ticket for all tables and columns.

Something seems weird as we can do

@Bean
fun tokenSchema(): TokenSchema {
    return TokenSchema.builder()
        .setTokenTable("token_entry")
        .setProcessorNameColumn("processor_name")
        .setTokenTypeColumn("token_type")
        .build()
}
@Bean
fun sagaSchema(): SagaSchema {
    return SagaSchema("saga_entry", "association_value_entry")
}

but why bother trying to override the defaults and have a flyway migration script when actually we could inject JdbcSagaStore/JdbcTokenStore and call respective createSchema()/createSchema(...). What is the current approach proposed by axon usually? There seem to be 2 options where one is trying to create the tables without checking if it already exist. Also something worth knowing is that only the script for flyway does create the necessary indexes, whereas the other solution does not.

[smcvb]

A middle solution would be to do similar as TokenSchema but for SagaSchema.

Very good suggestion, @Blackdread, and it can be easily introduced. If anybody reading this feels like it, you're free to provide a PR for that!

The strange part is that https://start.axoniq.io/ generates the flyway migration with

I wager this is a bug with the starter atm, thanks for spotting it. I'll chat with the people managing the starter if they have a simple solution for it.

but why bother trying to override the defaults and have a flyway migration script when actually we could inject JdbcSagaStore/JdbcTokenStore and call respective createSchema()/createSchema(...). What is the current approach proposed by axon usually?

The createSchema method approach pre-dates the common usage of database migration tooling, like FlyWay. From that perspective, you're looking at legacy methods that are there for backward compatibility.

Having said that, from a pure Axon Framework perspective, using the schema classes would be the way to got. From a personal perspective, I think it makes sense to move towards FlyWay, however. Although I understand that doesn't necessarily help, I'd thus leave it up to your personal preference on this subject, since both will get you to a working solution.

The most important thing, I'd say, is to be consistent in your application. My personal FlyWay-recommendation stems from the point that I assume you use FlyWay for your (projection) database(s) too. Hence, it's consistent within your application to use that throughout.