Akka-persistence-jdbc is a plugin for akka-persistence that writes journal entries to a configured JDBC database. It supports writing journal messages and snapshots to two tables: the 'journal' table and the 'snapshot' table.
By setting the appropriate Journal and SnapshotStore classes in the application.conf, you can choose the following databases
- H2 (tested, works on 1.4.179)
- Postgresql (tested, works on v9.3.1)
- MySql (tested, works on 5.6.19 MySQL Community Server (GPL))
- Oracle XE (tested, works on Oracle XE 11g r2)
- IBM Informix (tested, works on v12.10)
Note: The plugin should work on other databases too. When you wish to have support for a database, contact me and we can work something out.
To include the JDBC plugin into your sbt project, add the following lines to your build.sbt file:
resolvers += "dnvriend at bintray" at "http://dl.bintray.com/dnvriend/maven"
libraryDependencies += "com.github.dnvriend" %% "akka-persistence-jdbc" % "1.0.8"
For Maven users, add the following to the pom.xml
<dependency>
<groupId>com.github.dnvriend</groupId>
<artifactId>akka-persistence-jdbc_2.10</artifactId>
<version>1.0.8</version>
</dependency>
<dependency>
<groupId>com.github.dnvriend</groupId>
<artifactId>akka-persistence-jdbc_2.11</artifactId>
<version>1.0.8</version>
</dependency>
Add the following to the repositories section of the pom:
<repository>
<snapshots><enabled>false</enabled></snapshots>
<id>central</id>
<name>bintray</name>
<url>http://dl.bintray.com/dnvriend/maven</url>
</repository>
- ScalikeJDBC 2.1.1 -> 2.1.2
- Moved to bintray
- Merged mwkohout fix using Oracle's MERGE on issue #3, thanks!
- Fixed - Issue3: Handling save attempts with duplicate snapshot ids and persistence ids
- Fixed - Issue5: Connection pool is being redefined when using journal and snapshot store
- Akka 2.3.5 -> 2.3.6
- ScalikeJDBC 2.1.0 -> 2.1.1
- Added schema name configuration for the journal and snapshot
- Added table name configuration for the journal and snapshot
- ScalikeJDBC 2.0.5 -> 2.1.0
- Akka 2.3.4 -> 2.3.5
- IBM Informix 12.10 supported
- Oracle XE 11g supported
- scalikejdbc 2.0.4 -> 2.0.5
- akka-persistence-testkit 0.3.3 -> 0.3.4
- Release to Maven Central
- Tested against MySQL/5.6.19 MySQL Community Server (GPL)
- Tested against H2/1.4.179
- Added the snapshot store
- Refactored the JdbcSyncWriteJournal so it supports the following databases:
- Using Martin Krasser's akka-persistence-testkit to test the akka-persistence-jdbc plugin.
- Update to Akka 2.3.4
- Using ScalikeJDBC as the JDBC access library instead of my home-brew one.
- Initial commit
In application.conf place the following:
akka {
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
}
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.PostgresqlSyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.PostgresqlSyncSnapshotStore"
}
jdbc-connection {
username ="admin"
password = "admin"
driverClassName = "org.postgresql.Driver"
url = "jdbc:postgresql://localhost:5432/mydb"
journalSchemaName = "public"
journalTableName = "journal"
snapshotSchemaName = "public"
snapshotTableName = "snapshot"
}
Note: You can drop the schema name (username/database name) part of the query by setting the schema names to "", the query that runs will not have the . format but only the tablename, like so:
journalSchemaName = ""
snapshotSchemaName = ""
The jdbc-journal and jdbc-snapshot-store sections are optional. The defaults are the PostgresqlSyncWriteJournal and the PostgresqlSyncSnapshotStore.
To alter the database dialect for the journal, change the class to:
class = "akka.persistence.jdbc.journal.PostgresqlSyncWriteJournal"
class = "akka.persistence.jdbc.journal.MysqlSyncWriteJournal"
class = "akka.persistence.jdbc.journal.H2SyncWriteJournal"
class = "akka.persistence.jdbc.journal.OracleSyncWriteJournal"
class = "akka.persistence.jdbc.journal.InformixSyncWriteJournal"
To alter the database dialect of the snapshot-store, change the class to:
class = "akka.persistence.jdbc.snapshot.PostgresqlSyncSnapshotStore"
class = "akka.persistence.jdbc.snapshot.MysqlSyncSnapshotStore"
class = "akka.persistence.jdbc.snapshot.H2SyncSnapshotStore"
class = "akka.persistence.jdbc.snapshot.OracleSyncSnapshotStore"
class = "akka.persistence.jdbc.snapshot.InformixSyncSnapshotStore"
The following schema works on H2
CREATE TABLE IF NOT EXISTS public.journal (
persistence_id VARCHAR(255) NOT NULL,
sequence_number BIGINT NOT NULL,
marker VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
created TIMESTAMP NOT NULL,
PRIMARY KEY(persistence_id, sequence_number)
);
CREATE TABLE IF NOT EXISTS public.snapshot (
persistence_id VARCHAR(255) NOT NULL,
sequence_nr BIGINT NOT NULL,
snapshot TEXT NOT NULL,
created BIGINT NOT NULL,
PRIMARY KEY (persistence_id, sequence_nr)
);
The following schema works on Postgresql
CREATE TABLE IF NOT EXISTS public.journal (
persistence_id VARCHAR(255) NOT NULL,
sequence_number BIGINT NOT NULL,
marker VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
created TIMESTAMP NOT NULL,
PRIMARY KEY(persistence_id, sequence_number)
);
CREATE TABLE IF NOT EXISTS public.snapshot (
persistence_id VARCHAR(255) NOT NULL,
sequence_nr BIGINT NOT NULL,
snapshot TEXT NOT NULL,
created BIGINT NOT NULL,
PRIMARY KEY (persistence_id, sequence_nr)
);
The following schema works on MySQL
CREATE TABLE IF NOT EXISTS journal (
persistence_id VARCHAR(255) NOT NULL,
sequence_number BIGINT NOT NULL,
marker VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
created TIMESTAMP NOT NULL,
PRIMARY KEY(persistence_id, sequence_number)
);
CREATE TABLE IF NOT EXISTS snapshot (
persistence_id VARCHAR(255) NOT NULL,
sequence_nr BIGINT NOT NULL,
snapshot TEXT NOT NULL,
created BIGINT NOT NULL,
PRIMARY KEY (persistence_id, sequence_nr)
);
Note: Please set the schema names to "" like so:
journalSchemaName = ""
journalTableName = "journal"
snapshotSchemaName = "public"
snapshotTableName = ""
When you wish to use the schemaName for the database name, then fill the schema names to be the database name
The following schema works on Oracle
CREATE TABLE public.journal (
persistence_id VARCHAR(255) NOT NULL,
sequence_number NUMERIC NOT NULL,
marker VARCHAR(255) NOT NULL,
message CLOB NOT NULL,
created TIMESTAMP NOT NULL,
PRIMARY KEY(persistence_id, sequence_number)
);
CREATE TABLE public.snapshot (
persistence_id VARCHAR(255) NOT NULL,
sequence_nr NUMERIC NOT NULL,
snapshot CLOB NOT NULL,
created NUMERIC NOT NULL,
PRIMARY KEY (persistence_id, sequence_nr)
);
Note: Oracle schemas are users really. To use a username for the schema name, set the configuration like:
journalSchemaName = "system"
journalTableName = "journal"
snapshotSchemaName = "system"
snapshotTableName = "snapshot"
To not use schema names, set the configuration to:
journalSchemaName = ""
journalTableName = "journal"
snapshotSchemaName = ""
snapshotTableName = "snapshot"
The following schema works on Informix 12.10
CREATE TABLE IF NOT EXISTS journal (
persistence_id VARCHAR(255) NOT NULL,
sequence_number NUMERIC NOT NULL,
marker VARCHAR(255) NOT NULL,
message CLOB NOT NULL,
created DATETIME YEAR TO FRACTION(5) NOT NULL,
PRIMARY KEY(persistence_id, sequence_number)
);
CREATE TABLE IF NOT EXISTS snapshot (
persistence_id VARCHAR(255) NOT NULL,
sequence_nr NUMERIC NOT NULL,
snapshot CLOB NOT NULL,
created NUMERIC NOT NULL,
PRIMARY KEY (persistence_id, sequence_nr)
);
The application.conf for Postgresql should be:
akka {
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
}
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.PostgresqlSyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.PostgresqlSyncSnapshotStore"
}
jdbc-connection {
username ="admin"
password = "admin"
driverClassName = "org.postgresql.Driver"
url = "jdbc:postgresql://localhost:5432/mydb"
journalSchemaName = "public"
journalTableName = "journal"
snapshotSchemaName = "public"
snapshotTableName = "snapshot"
}
The application.conf for MySQL should be:
akka {
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
}
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.MysqlSyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.MysqlSyncSnapshotStore"
}
jdbc-connection {
username ="admin"
password = "admin"
driverClassName = "com.mysql.jdbc.Driver"
url = "jdbc:mysql://localhost/test"
journalSchemaName = ""
journalTableName = "journal"
snapshotSchemaName = ""
snapshotTableName = "snapshot"
}
The application.conf for H2 should be:
akka {
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
}
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.H2SyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.H2SyncSnapshotStore"
}
jdbc-connection {
username ="sa"
password = ""
driverClassName = "org.h2.Driver"
url = "jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1"
journalSchemaName = "public"
journalTableName = "journal"
snapshotSchemaName = "public"
snapshotTableName = "snapshot"
}
The application.conf for Oracle should be:
akka {
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
}
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.OracleSyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.OracleSyncSnapshotStore"
}
jdbc-connection {
username ="system"
password = "oracle"
driverClassName = "oracle.jdbc.OracleDriver"
url = "jdbc:oracle:thin:@//192.168.99.99:49161/xe"
journalSchemaName = "system"
journalTableName = "journal"
snapshotSchemaName = "system"
snapshotTableName = "snapshot"
}
The application.conf for Informix should be:
akka {
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
}
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.InformixSyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.InformixSyncSnapshotStore"
}
jdbc-connection {
username ="informix"
password = "informix"
driverClassName = "com.informix.jdbc.IfxDriver"
url = "jdbc:informix-sqli://192.168.99.99:9088/test:INFORMIXSERVER=ol_cyklo"
journalSchemaName = "public"
journalTableName = "journal"
snapshotSchemaName = "public"
snapshotTableName = "snapshot"
}
For more information about the Postgres docker image, please view training/postgres
sudo docker run --name postgres -d -p 49432:5432 training/postgres
sudo docker run -ti -v /akka-persistence-jdbc:/akka-persistence-jdbc --link postgres:postgres --name test murad/java8 /bin/bash
Connect to the database with following settings and create the database schema:
hostname: 192.168.99.99
port: 49432
username: docker
password: docker
database: docker
The test application.conf should be:
akka {
loglevel = "DEBUG"
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
# we need event publishing for tests
publish-confirmations = on
publish-plugin-commands = on
# disable leveldb (default store impl)
journal.leveldb.native = off
}
log-dead-letters = 10
log-dead-letters-during-shutdown = on
}
jdbc-connection {
username ="docker"
password = "docker"
driverClassName = "org.postgresql.Driver"
url = "jdbc:postgresql://"${POSTGRES_PORT_5432_TCP_ADDR}":"${POSTGRES_PORT_5432_TCP_PORT}"/docker"
journalSchemaName = "public"
journalTableName = "journal"
snapshotSchemaName = "public"
snapshotTableName = "snapshot"
}
In the container named 'test', type:
cd /akka-persistence-jdbc
./activator "test-only akka.persistence.jdbc.journal.PostgresqlSyncJournalSpec"
./activator "test-only akka.persistence.jdbc.snapshot.PostgresqlSyncSnapshotStoreSpec"
For more information about the H2 docker image, please view viliusl / ubuntu-h2-server
sudo docker run --name h2 -d -p 1522:1521 -P viliusl/ubuntu-h2-server
sudo docker run -ti -v /akka-persistence-jdbc:/akka-persistence-jdbc --link h2:h2 --name test murad/java8 /bin/bash
Connect to the database with the following settings:
hostname: 192.168.99.99
port: 1522
username: sa
password:
database: test
The test application.conf should be:
akka {
loglevel = "DEBUG"
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
# we need event publishing for tests
publish-confirmations = on
publish-plugin-commands = on
# disable leveldb (default store impl)
journal.leveldb.native = off
}
log-dead-letters = 10
log-dead-letters-during-shutdown = on
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.H2SyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.H2SyncSnapshotStore"
}
h2 {
host = "192.168.99.99"
host = ${?H2_PORT_1521_TCP_ADDR}
port = "1522"
port = ${?H2_PORT_1521_TCP_PORT}
}
jdbc-connection {
username ="sa"
password = ""
driverClassName = "org.h2.Driver"
url = "jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1"
// url = "jdbc:h2:tcp://"${h2.host}":"${h2.port}"/test"
journalSchemaName = "public"
journalTableName = "journal"
snapshotSchemaName = "public"
snapshotTableName = "snapshot"
}
In the container named 'test', type:
cd /akka-persistence-jdbc
./activator "test-only akka.persistence.jdbc.journal.H2SyncJournalSpec"
./activator "test-only akka.persistence.jdbc.snapshot.H2SyncSnapshotStoreSpec"
For more information about the MySQL docker image, please view mysql
sudo docker run --name mysql -e MYSQL_ROOT_PASSWORD=root -d -p 49306:3306 mysql
sudo docker run -ti -v /akka-persistence-jdbc:/akka-persistence-jdbc --link mysql:mysql --name test murad/java8 /bin/bash
Connect to the database with following settings and create the database schema:
hostname: 192.168.99.99
port: 49306
username: root
password: root
database: mysql
The test application.conf should be:
akka {
loglevel = "DEBUG"
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
# we need event publishing for tests
publish-confirmations = on
publish-plugin-commands = on
# disable leveldb (default store impl)
journal.leveldb.native = off
}
log-dead-letters = 10
log-dead-letters-during-shutdown = on
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.MysqlSyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.MysqlSyncSnapshotStore"
}
jdbc-connection {
username ="root"
password = "root"
driverClassName = "com.mysql.jdbc.Driver"
url = "jdbc:mysql://"${MYSQL_PORT_3306_TCP_ADDR}":"${MYSQL_PORT_3306_TCP_PORT}"/mysql"
journalSchemaName = ""
journalTableName = "journal"
snapshotSchemaName = ""
snapshotTableName = "snapshot"
}
In the container named 'test', type:
cd /akka-persistence-jdbc
./activator "test-only akka.persistence.jdbc.journal.MysqlSyncJournalSpec"
./activator "test-only akka.persistence.jdbc.snapshot.MysqlSyncSnapshotStoreSpec"
For more information about the Oracle XE docker image, please view alexeiled / docker-oracle-xe-11g
sudo docker run -d --name oracle -p 49160:22 -p 49161:1521 -p 49162:8080 alexeiled/docker-oracle-xe-11g
sudo docker run -ti -v /akka-persistence-jdbc:/akka-persistence-jdbc --link oracle:oracle --name test murad/java8 /bin/bash
Connect database with following setting and create the database schema:
hostname: 192.168.99.99
port: 49161
sid: xe
username: system
password: oracle
Password for SYS
oracle
Connect to Oracle Application Express web management console with following settings:
url: http://localhost:49162/apex
workspace: INTERNAL
user: ADMIN
password: oracle
Login by SSH
ssh root@localhost -p 49160
password: admin
The test application.conf should be:
akka {
loglevel = "DEBUG"
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
# we need event publishing for tests
publish-confirmations = on
publish-plugin-commands = on
# disable leveldb (default store impl)
journal.leveldb.native = off
}
log-dead-letters = 10
log-dead-letters-during-shutdown = on
}
jdbc-journal {
class = "akka.persistence.jdbc.journal.OracleSyncWriteJournal"
}
jdbc-snapshot-store {
class = "akka.persistence.jdbc.snapshot.OracleSyncSnapshotStore"
}
jdbc-connection {
username ="system"
password = "oracle"
driverClassName = "oracle.jdbc.OracleDriver"
url = "jdbc:oracle:thin:@//"${ORACLE_PORT_1521_TCP_ADDR}":"${ORACLE_PORT_1521_TCP_PORT}"/xe"
journalSchemaName = "system"
journalTableName = "journal"
snapshotSchemaName = "system"
snapshotTableName = "snapshot"
}
In the container named 'test', type:
cd /akka-persistence-jdbc
./activator "test-only akka.persistence.jdbc.journal.OracleSyncJournalSpec"
./activator "test-only akka.persistence.jdbc.snapshot.OracleSyncSnapshotStoreSpec"
IBM does not yet have docker images for there products, so I have created one.
sudo docker run -ti --name informix -p 9088:9088 dnvriend/ibm-informix /bin/bash
In the informix container run:
su informix
oninit -v
onstat -l
The sbspace file has already been created, but this is what I did:
su informix
cd ~
touch sbspace
chmod 660 sbspace
onspaces -c -S sbsp -p /home/informix/sbspace -o 500 -s 20480
Now run the test container
sudo docker run -ti -v /akka-persistence-jdbc:/akka-persistence-jdbc --link informix:informix --name test murad/java8 /bin/bash
I used IntelliJ, added the Informix JDBC driver and used the following URL to connect:
jdbc:informix-sqli://192.168.99.99:9088/test:informixserver=ol_cyklo
Credentials like the following:
hostname: 192.168.99.99
port: 9088
username: informix
password: informix
database: test
The test application.conf should be:
akka {
loglevel = "DEBUG"
persistence {
journal.plugin = "jdbc-journal"
snapshot-store.plugin = "jdbc-snapshot-store"
# we need event publishing for tests
publish-confirmations = on
publish-plugin-commands = on
# disable leveldb (default store impl)
journal.leveldb.native = off
}
log-dead-letters = 10
log-dead-letters-during-shutdown = on
}
jdbc-connection {
username ="informix"
password = "informix"
driverClassName = "com.informix.jdbc.IfxDriver"
url = "jdbc:informix-sqli://"${INFORMIX_PORT_9088_TCP_ADDR}":"${INFORMIX_PORT_9088_TCP_PORT}"/test:INFORMIXSERVER=ol_cyklo"
journalSchemaName = ""
journalTableName = "journal"
snapshotSchemaName = ""
snapshotTableName = "snapshot"
}
In the container named 'test', type:
cd /akka-persistence-jdbc
./activator "test-only akka.persistence.jdbc.journal.InformixSyncJournalSpec"
./activator "test-only akka.persistence.jdbc.snapshot.InformixSyncSnapshotStoreSpec"
I use the vagrant configuration in the vagrant subdirectory:
vagrant up
vagrant ssh
Then I launch all the containers:
sudo docker run --name postgres -d -p 5432:5432 training/postgres
sudo docker run --name h2 -d -p 1522:1521 -P viliusl/ubuntu-h2-server
sudo docker run --name mysql -e MYSQL_ROOT_PASSWORD=root -d -p 3306:3306 mysql
sudo docker run --name oracle -d -p 49160:22 -p 1521:1521 -p 49162:8080 alexeiled/docker-oracle-xe-11g
sudo docker run --name informix -ti -p 9088:9088 dnvriend/ibm-informix /bin/bash
I configure the databases, launch the database service and create the schemas when necessary, then I just launch the test suite from IntelliJ IDEA and/or use Activator to test. All databases in one go without installing DB services on my OSX.
Gotta love Docker! :-)
Have fun!