§Storing Persistent Events in a Relational Database

This page describes how to configure a relational database for use with Persistent Entity API or Akka Typed Persistence in a Lagom service .

§Project dependencies

To use a relational database add the following in your project’s build:

In Maven:

<dependency>
    <groupId>com.lightbend.lagom</groupId>
    <artifactId>lagom-scaladsl-persistence-jdbc_${scala.binary.version}</artifactId>
    <version>${lagom.version}</version>
</dependency>

In sbt:

libraryDependencies += lagomScaladslPersistenceJdbc

You will also need to add the jar for your JDBC database driver.

To enable the RDBMS Persistence on your Application you will have to mix in | JdbcPersistenceComponents and HikariCPComponents as described in Dependency Injection.

§Configuration

Lagom uses the akka-persistence-jdbc plugin. This supports the following relational databases:

• PostgreSQL
• MySQL
• Oracle
• H2
• SQL Server

We advise against using H2 in production, however, it is suitable for use in development and testing.

In Lagom’s default configuration, Lagom will use Play’s JDBC support to configure and create a connection pool. Details on how to configure it can be found here. Play should be configured to provide a JNDI binding for the datasource, by default Lagom binds it to DefaultDS.

Lagom then configures a Slick Database to use that datasource in combination with a AsyncExecutor that manages the thread pool for asynchronous execution of Database I/O Actions. Lagom will also take care that the connection pool is configured correctly according to the AsyncExecutor settings. The Slick Database is then bound to the JNDI name DefaultDB and it’s used to configure the akka-persistence-jdbc plugin.

The akka-persistence-jdbc plugin uses Slick to map tables and manage asynchronous execution of JDBC calls. This means Lagom internally configures it to use the right Slick profile for your database.

So for example, to configure a PostgreSQL database, you can add the following to your application.conf:

db.default {
  driver = "org.postgresql.Driver"
  url = "jdbc:postgresql://database.example.com/lagom-db"
}

jdbc-defaults.slick.profile = "slick.jdbc.PostgresProfile$"

§Table creation

By default, Lagom will automatically create the tables it needs for you if they don’t already exist. This is great for development and testing, but in some circumstances may not be appropriate for production. The table auto creation feature can be disabled by using the following configuration:

lagom.persistence.jdbc.create-tables.auto = false

The database schemas needed for the tables can be found here.

The full configuration options that Lagom provides for managing the creation of tables is here:

# Defaults to use for each Akka persistence plugin
jdbc-defaults.slick {

  # The Slick profile to use
  # set to one of: slick.jdbc.PostgresProfile$, slick.jdbc.MySQLProfile$, slick.jdbc.OracleProfile$ or slick.jdbc.H2Profile$
  # profile = "slick.jdbc.PostgresProfile$"

  # The JNDI name for the Slick pre-configured DB
  # By default, this value will be used by all akka-persistence-jdbc plugin components (journal, read-journal and snapshot).
  # you may configure each plugin component to use different DB settings
  jndiDbName=DefaultDB
}


db.default {

  # The JNDI name for this DataSource
  # Play, and therefore Lagom, will automatically register this DataSource as a JNDI resource using this name.
  # This DataSource will be used to build a pre-configured Slick DB
  jndiName=DefaultDS

  # Lagom will configure a Slick Database, using the async-executor settings below
  # and register it as a JNDI resource using this name.
  # By default, all akka-persistence-jdbc plugin components will use this JDNI name
  # to lookup for this pre-configured Slick DB
  jndiDbName=DefaultDB

  async-executor {
    # number of objects that can be queued by the async executor
    queueSize = 10000

    # 5 * number of cores
    numThreads = 20

    # same as number of threads
    minConnections = 20

    # same as number of threads
    maxConnections = 20

    # if true, a Mbean for AsyncExecutor will be registered
    registerMbeans = false
  }

  # Hikari is the default connection pool and it's fine-tuned to use the same
  # values for minimum and maximum connections as defined for the async-executor above
  hikaricp {
    minimumIdle = ${db.default.async-executor.minConnections}
    maximumPoolSize = ${db.default.async-executor.maxConnections}
  }

  # Alternatively, BoneCP can be used instead of Hikari.
  # More information on how to switch to BoneCP can be found here:
  # https://www.playframework.com/documentation/2.6.x/ScalaDatabase#Selecting-and-configuring-the-connection-pool
  #
  # The settings below configured it to use the same
  # values for minimum and maximum connections as defined for the async-executor above
  bonecp {
    # the pool partition count
    partitionCount = 1

    # the value below is dependent on the partitionCount
    # it must be equal or less than async-executor.minConnections / partitionCount
    minConnectionsPerPartition = ${db.default.async-executor.minConnections}

    # the value below is dependent on the partitionCount
    # it must be equal or less than async-executor.maxConnections / partitionCount
    maxConnectionsPerPartition = ${db.default.async-executor.maxConnections}
  }
}


lagom.persistence.jdbc {

  # Configuration for creating tables
  create-tables {

    # Whether tables should be created automatically as needed
    auto = true

    # How long to wait for tables to be created, before failing
    timeout = 20s

    # The cluster role to create tables from
    run-on-role = ""

    # Exponential backoff for failures configuration for creating tables
    failure-exponential-backoff {

      # minimum (initial) duration until processor is started again
      # after failure
      min = 3s

      # the exponential back-off is capped to this duration
      max = 30s

      # additional random delay is based on this factor
      random-factor = 0.2
    }
  }
}

§Application Loader

The Lagom Application Loader needs to load the correct persistence components based on whether you’re working with Cassandra or JDBC. For an RDBMS not only the JDBC persistence components must be loaded but also a connection pool must be configured. By default the Hikari connection pool is available in Play and can be configured as follows:

abstract class BlogApplication(context: LagomApplicationContext)
    extends LagomApplication(context)
    with JdbcPersistenceComponents
    with HikariCPComponents
    with AhcWSComponents {