Configuration

The plugin relies on Slick to do create the SQL dialect for the database in use, therefore the following must be configured in application.conf

Configure pekko-persistence:

  • instruct Apache Pekko persistence to use the jdbc-journal plugin,
  • instruct Apache Pekko persistence to use the jdbc-snapshot-store plugin,
  • instruct Apache Pekko persistence to use the jdbc-durable-state-store plugin (Postgres and H2 only)

Configure slick:

  • The following slick profiles are supported:
  • slick.jdbc.PostgresProfile$
  • slick.jdbc.MySQLProfile$
  • slick.jdbc.H2Profile$
  • slick.jdbc.OracleProfile$
  • slick.jdbc.SQLServerProfile$

Database Schema

Note

Please note that the H2 database is not recommended to be used as a production database, and support for H2 is primarily for testing purposes.

For testing purposes the journal and snapshot tables can be created programmatically using the provided SchemaUtils.

Scala
source
implicit val system: ActorSystem = ActorSystem("example") val done: Future[Done] = SchemaUtils.createIfNotExists()
Java
sourceimport org.apache.pekko.persistence.jdbc.testkit.javadsl.SchemaUtils;

ActorSystem actorSystem = ActorSystem.create("example");
CompletionStage<Done> done = SchemaUtils.createIfNotExists(actorSystem);

A dropIfExists variant is also available.

Note: SchemaUtils was introduced in version 5.0.0.

Reference Configuration

pekko-persistence-jdbc provides the defaults as part of the reference.conf. This file documents all the values which can be configured.

There are several possible ways to configure loading your database connections. Options will be explained below.

One database connection pool per journal type

There is the possibility to create a separate database connection pool per journal-type (one pool for the write-journal, one pool for the snapshot-journal, and one pool for the read-journal). This is the default and the following example configuration shows how this is configured:

Postgres
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

// general.conf is included only for shared settings used for the pekko-persistence-jdbc tests
include "general.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
    state {
      plugin = "jdbc-durable-state-store"
    }
  }
}

jdbc-journal {
  slick = ${slick}
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  slick = ${slick}
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  slick = ${slick}
}

# the pekko-persistence-jdbc provider in use for durable state store
jdbc-durable-state-store {
  slick = ${slick}
}

slick {
  profile = "slick.jdbc.PostgresProfile$"
  db {
    host = "localhost"
    host = ${?DB_HOST}
    url = "jdbc:postgresql://"${slick.db.host}":5432/docker?reWriteBatchedInserts=true"
    user = "docker"
    password = "docker"
    driver = "org.postgresql.Driver"
    numThreads = 5
    maxConnections = 5
    minConnections = 1
  }
}
MySQL
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

// general.conf is included only for shared settings used for the pekko-persistence-jdbc tests
include "general.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
  }
}

jdbc-journal {
  slick = ${slick}
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  slick = ${slick}
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  slick = ${slick}
}

# TODO https://github.com/apache/pekko-persistence-jdbc/issues/174
# the pekko-persistence-jdbc provider in use for durable state store
#jdbc-durable-state-store {
#  slick = ${slick}
#}

slick {
  profile = "slick.jdbc.MySQLProfile$"
  db {
    host = ${docker.host}
    host = ${?DB_HOST}
    url = "jdbc:mysql://"${slick.db.host}":3306/mysql?cachePrepStmts=true&cacheCallableStmts=true&cacheServerConfiguration=true&useLocalSessionState=true&elideSetAutoCommits=true&alwaysSendSetIsolation=false&enableQueryTimeouts=false&connectionAttributes=none&verifyServerCertificate=false&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&useLegacyDatetimeCode=false&serverTimezone=UTC&rewriteBatchedStatements=true"
    user = "root"
    password = "root"
    driver = "com.mysql.cj.jdbc.Driver"
    numThreads = 5
    maxConnections = 5
    minConnections = 1
  }
}
H2
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

// general.conf is included only for shared settings used for the pekko-persistence-jdbc tests
include "general.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
    state {
      plugin = "jdbc-durable-state-store"
    }
  }
}

jdbc-journal {
  slick = ${slick}
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  slick = ${slick}
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  slick = ${slick}
}

# the pekko-persistence-jdbc provider in use for durable state store
jdbc-durable-state-store {
  slick = ${slick}
}

slick {
  profile = "slick.jdbc.H2Profile$"
  db {
    url = "jdbc:h2:mem:test-database;DATABASE_TO_UPPER=false;"
    user = "root"
    password = "root"
    driver = "org.h2.Driver"
    numThreads = 5
    maxConnections = 5
    minConnections = 1
  }
}
Oracle
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

// general.conf is included only for shared settings used for the pekko-persistence-jdbc tests
include "general.conf"
include "oracle-schema-overrides.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
  }
}


jdbc-journal {
  slick = ${slick}
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  slick = ${slick}
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  slick = ${slick}
}

# the pekko-persistence-jdbc provider in use for durable state store
jdbc-durable-state-store {
  slick = ${slick}
}

slick {
  profile = "slick.jdbc.OracleProfile$"
  db {
    host = ${docker.host}
    host = ${?DB_HOST}
    url = "jdbc:oracle:thin:@//"${slick.db.host}":1521/xe"
    user = "system"
    password = "oracle"
    driver = "oracle.jdbc.OracleDriver"
    numThreads = 5
    maxConnections = 5
    minConnections = 1
  }
}
SQL Server
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

include "general.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
  }
}

jdbc-journal {
  tables {
    journal {
      schemaName = "dbo"
    }
  }

  slick = ${slick}
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  tables {
    snapshot {
      schemaName = "dbo"
    }
  }

  slick = ${slick}
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  tables {
    journal {
      schemaName = "dbo"
    }
  }

  slick = ${slick}
}

# the pekko-persistence-jdbc provider in use for durable state store
jdbc-durable-state-store {
  tables {
    durable_state {
      schemaName = "dbo"
    }
  }

  slick = ${slick}
}

slick {
  profile = "slick.jdbc.SQLServerProfile$"
  db {
    host = ${docker.host}
    host = ${?DB_HOST}
    url = "jdbc:sqlserver://"${slick.db.host}":1433;databaseName=docker;integratedSecurity=false;encrypt=false;"
    user = "docker"
    password = "docker"
    driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    numThreads = 5
    maxConnections = 5
    minConnections = 1
  }
}

Sharing the database connection pool between the journals

In order to create only one connection pool which is shared between all journals the following configuration can be used:

Postgres
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

include "general.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
    state {
      plugin = "jdbc-durable-state-store"
    }
  }
}

pekko-persistence-jdbc {
  shared-databases {
    slick {
      profile = "slick.jdbc.PostgresProfile$"
      db {
        host = "localhost"
        host = ${?DB_HOST}
        url = "jdbc:postgresql://"${pekko-persistence-jdbc.shared-databases.slick.db.host}":5432/docker?reWriteBatchedInserts=true"
        user = "docker"
        password = "docker"
        driver = "org.postgresql.Driver"
        numThreads = 5
        maxConnections = 5
        minConnections = 1
      }
    }
  }
}

jdbc-journal {
  use-shared-db = "slick"
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  use-shared-db = "slick"
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  use-shared-db = "slick"
}

# the pekko-persistence-jdbc provider in use for durable state store
jdbc-durable-state-store {
  use-shared-db = "slick"
}
MySQL
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

include "general.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
  }
}

pekko-persistence-jdbc {
  shared-databases {
    slick {
      profile = "slick.jdbc.MySQLProfile$"
      db {
        host = ${docker.host}
        host = ${?DB_HOST}
        url = "jdbc:mysql://"${pekko-persistence-jdbc.shared-databases.slick.db.host}":3306/mysql?cachePrepStmts=true&cacheCallableStmts=true&cacheServerConfiguration=true&useLocalSessionState=true&elideSetAutoCommits=true&alwaysSendSetIsolation=false&enableQueryTimeouts=false&connectionAttributes=none&verifyServerCertificate=false&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&useLegacyDatetimeCode=false&serverTimezone=UTC&rewriteBatchedStatements=true"
        user = "root"
        password = "root"
        driver = "com.mysql.cj.jdbc.Driver"
        numThreads = 5
        maxConnections = 5
        minConnections = 1
      }
    }
  }
}

jdbc-journal {
  use-shared-db = "slick"
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  use-shared-db = "slick"
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  use-shared-db = "slick"
}

# TODO https://github.com/apache/pekko-persistence-jdbc/issues/174
# the pekko-persistence-jdbc provider in use for durable state store
#jdbc-durable-state-store {
#  use-shared-db = "slick"
#}
H2
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

include "general.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
    state {
      plugin = "jdbc-durable-state-store"
    }
  }
}

pekko-persistence-jdbc {
  shared-databases {
    slick {
      profile = "slick.jdbc.H2Profile$"
      db {
        url = "jdbc:h2:mem:test-database;DATABASE_TO_UPPER=false;"
        user = "root"
        password = "root"
        driver = "org.h2.Driver"
        numThreads = 5
        maxConnections = 5
        minConnections = 1
      }
    }
  }
}

jdbc-journal {
  use-shared-db = "slick"
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  use-shared-db = "slick"
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  use-shared-db = "slick"
}
Oracle
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

include "general.conf"
include "oracle-schema-overrides.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
    state {
      plugin = "jdbc-durable-state-store"
    }
  }
}

pekko-persistence-jdbc {
  shared-databases {
    slick {
      profile = "slick.jdbc.OracleProfile$"
      db {
        host = ${docker.host}
        host = ${?DB_HOST}
        url = "jdbc:oracle:thin:@//"${pekko-persistence-jdbc.shared-databases.slick.db.host}":1521/xe"
        user = "system"
        password = "oracle"
        driver = "oracle.jdbc.OracleDriver"
        numThreads = 5
        maxConnections = 5
        minConnections = 1
      }
    }
  }
}

jdbc-journal {
  use-shared-db = "slick"
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  use-shared-db = "slick"
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  use-shared-db = "slick"
}

# the pekko-persistence-jdbc provider in use for durable state store
jdbc-durable-state-store {
  use-shared-db = "slick"
}
SQL Server
source# Copyright 2016 Dennis Vriend
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

include "general.conf"

pekko {
  persistence {
    journal {
      plugin = "jdbc-journal"
      // Enable the line below to automatically start the journal when the actorsystem is started
      // auto-start-journals = ["jdbc-journal"]
    }
    snapshot-store {
      plugin = "jdbc-snapshot-store"
      // Enable the line below to automatically start the snapshot-store when the actorsystem is started
      // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
    }
    state {
      plugin = "jdbc-durable-state-store"
    }
  }
}

pekko-persistence-jdbc {
  shared-databases {
    slick {
      profile = "slick.jdbc.SQLServerProfile$"
      db {
        host = ${docker.host}
        host = ${?DB_HOST}
        url = "jdbc:sqlserver://"${pekko-persistence-jdbc.shared-databases.slick.db.host}":1433;databaseName=docker;integratedSecurity=false;encrypt=false;"
        user = "docker"
        password = "docker"
        driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        numThreads = 5
        maxConnections = 5
        minConnections = 1
      }
    }
  }
}

jdbc-journal {
  use-shared-db = "slick"
}

# the pekko-persistence-snapshot-store in use
jdbc-snapshot-store {
  use-shared-db = "slick"
}

# the pekko-persistence-query provider in use
jdbc-read-journal {
  use-shared-db = "slick"
}

# the pekko-persistence-jdbc provider in use for durable state store
jdbc-durable-state-store {
  use-shared-db = "slick"
}

Customized loading of the db connection

It is also possible to load a custom database connection. In order to do so a custom implementation of SlickDatabaseProvider needs to be created. The methods that need to be implemented supply the Slick Database and Profile to the journals.

To enable your custom SlickDatabaseProvider, the fully qualified class name of the SlickDatabaseProvider needs to be configured in the application.conf. In addition, you might want to consider whether you want the database to be closed automatically:

pekko-persistence-jdbc {
  database-provider-fqcn = "com.mypackage.CustomSlickDatabaseProvider"
}
jdbc-journal {
  use-shared-db = "enabled" // setting this to any non-empty string prevents the journal from closing the database on shutdown
}
jdbc-snapshot-store {
  use-shared-db = "enabled" // setting this to any non-empty string prevents the snapshot-journal from closing the database on shutdown
}

DataSource lookup by JNDI name

The plugin uses Slick as the database access library. Slick supports jndi for looking up DataSources.

To enable the JNDI lookup, you must add the following to your application.conf:

jdbc-journal {
  slick {
    profile = "slick.jdbc.PostgresProfile$"
    jndiName = "java:jboss/datasources/PostgresDS"
  }
}

When using the use-shared-db = slick setting, the follow configuration can serve as an example:

pekko-persistence-jdbc {
  shared-databases {
    slick {
      profile = "slick.jdbc.PostgresProfile$"
      jndiName = "java:/jboss/datasources/bla"
    }
  }
}

Explicitly shutting down the database connections

The plugin automatically shuts down the HikariCP connection pool when the ActorSystem is terminated. This is done using ActorSystem.registerOnTerminationActorSystem.registerOnTermination.