Akka Persistence JDBC: Adding columns to the journal table

Akka Persistence / Event Sourcing
1

I am working with Akka Persistence JDBC version 3.5.2 and have a use case where I need to add a column to the “journal” table, especially a column to save the timestamp when the event was persisted. For now I have the timestamp in the event which is stored as a blob in the database making it difficult to write queries based on the timestamp of the event persistence.

I understand there will be a difference in the time of creation of the event and the time it gets persisted and it might be ok with my usecase.

Any help on how can I implement this?
Much Thanks.

2

Hi @krnkhanna1989,

The JDBC plugin works with DAOs and you can develop your own DAO and configure the plugin to use it. You need to dig a little bit on the source code to find out how to implement your own. There is no documentation for it.

There are a few gotchas:

  • We don’t consider the DAOs a public API. That means that we may change it in the future without notice. That would force you to adapt your code when updating to future releases.
  • Querying won’t be possible with the akka persistence query, but I guess you know about. You will need to have your own query method. In that case, a custom DAO that exposes the query you need will help.
  • You will still have to add the timestamp to your event and inside the DAO, read it and add to the timestamp column. The plugin has not notion of event timestamp, so you will need to use the event as a carrier. However, I would simply let the DAO define the timestamp or even the DB itself.
  • Using timestamp won’t work in a clustered environment. Each node may have different times due to clock skew.

I hope this helps.

Cheers,

Renato

3

hi @krnkhanna1989,

in parallel to @octonato’s answer, may I ask what’s the use case for you to add that timestamp? Also, how do you plan to read those timestamps? Are you developing new queries over the Journal?

Querying by time sounds a bit uncommon for an event-sourcing model so I’m trying to understand your particular case.

Cheers,

4

Hi @octonato, @ignasi35,

Thank you so much for your response on this.
I am saving some events to the “journal” table and have a requirement to delete the events which are older than 2 years. I have the date of the events in the event itself but it gets saved as a blob which will make it diffucult to query for the deletion.

I was planning to prepare a seperate function to query and delete those events.

Thank You,

Regards
Karan Khanna