Documentation

You are viewing the documentation for the 2.8.0-RC5 development release. The latest stable release series is 3.0.x.

§Accessing an SQL database

NOTE: JDBC is a blocking operation that will cause threads to wait. You can negatively impact the performance of your Play application by running JDBC queries directly in your controller! Please see the “Configuring a CustomExecutionContext” section.

§Configuring JDBC connection pools

Play provides a plugin for managing JDBC connection pools. You can configure as many databases as you need.

To enable the database plugin add the build dependencies:

Java
libraryDependencies ++= Seq(
  javaJdbc
)
Scala
libraryDependencies ++= Seq(
  jdbc
)

§Configuring the JDBC Driver dependency

Play does not provide any database drivers. Consequently, to deploy in production you will have to add your database driver as an application dependency.

For example, if you use MySQL5, you need to add a dependency for the connector:

libraryDependencies ++= Seq(
  "mysql" % "mysql-connector-java" % "5.1.41"
)

§Databases configuration

Then you must configure a connection pool in the conf/application.conf file. By convention, the default JDBC data source must be called default and the corresponding configuration properties are db.default.driver and db.default.url.

# Default database configuration
db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play"

If something isn’t properly configured, you will be notified directly in your browser:

You can also change the default name by setting play.db.default, for example:

play.db.default = "primary"

db.primary.driver=org.h2.Driver
db.primary.url="jdbc:h2:mem:play"

§How to configure several data sources

To configure several data sources:

# Orders database
db.orders.driver=org.h2.Driver
db.orders.url="jdbc:h2:mem:orders"

# Customers database
db.customers.driver=org.h2.Driver
db.customers.url="jdbc:h2:mem:customers"

§H2 database engine connection properties

In memory database:

# Default database configuration using H2 database engine in an in-memory mode
db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play"

File based database:

# Default database configuration using H2 database engine in a persistent mode
db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:/path/to/db-file"

The details of the H2 database URLs are found from H2 Database Engine Cheat Sheet.

§SQLite database engine connection properties

# Default database configuration using SQLite database engine
db.default.driver=org.sqlite.JDBC
db.default.url="jdbc:sqlite:/path/to/db-file"

§PostgreSQL database engine connection properties

# Default database configuration using PostgreSQL database engine
db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://database.example.com/playdb"

§MySQL database engine connection properties

# Default database configuration using MySQL database engine
# Connect to playdb as playdbuser
db.default.driver=com.mysql.jdbc.Driver
db.default.url="jdbc:mysql://localhost/playdb"
db.default.username=playdbuser
db.default.password="a strong password"

§Exposing the datasource through JNDI

Some libraries expect to retrieve the Datasource reference from JNDI. You can expose any Play managed datasource via JDNI by adding this configuration in conf/application.conf:

db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play"
db.default.jndiName=DefaultDS

§How to configure SQL log statement

Not all connection pools offer (out of the box) a way to log SQL statements. HikariCP, per instance, suggests that you use the log capacities of your database vendor. From HikariCP docs:

Log Statement Text / Slow Query Logging

Like Statement caching, most major database vendors support statement logging through properties of their own driver. This includes Oracle, MySQL, Derby, MSSQL, and others. Some even support slow query logging. We consider this a “development-time” feature. For those few databases that do not support it, jdbcdslog-exp is a good option. Great stuff during development and pre-Production.

Because of that, Play uses jdbcdslog-exp to enable consistent SQL log statement support for supported pools. The SQL log statement can be configured by database, using logSql property:

# Default database configuration using PostgreSQL database engine
db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://database.example.com/playdb"
db.default.logSql=true

After that, you can configure the jdbcdslog-exp log level as explained in their manual. Basically, you need to configure your root logger to INFO and then decide what jdbcdslog-exp will log (connections, statements and result sets). Here is an example using logback.xml to configure the logs:

<!--
   Copyright (C) 2009-2019 Lightbend Inc. <https://www.lightbend.com>
-->

<configuration>

  <conversionRule conversionWord="coloredLevel" converterClass="play.api.libs.logback.ColoredLevel" />

  <appender name="FILE" class="ch.qos.logback.core.FileAppender">
     <file>${application.home:-.}/logs/application.log</file>
     <encoder>
       <pattern>%date [%level] from %logger in %thread - %message%n%xException</pattern>
     </encoder>
  </appender>

  <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
      <pattern>%coloredLevel %logger{15} - %message%n%xException{10}</pattern>
    </encoder>
  </appender>

  <appender name="ASYNCFILE" class="ch.qos.logback.classic.AsyncAppender">
    <appender-ref ref="FILE" />
  </appender>

  <appender name="ASYNCSTDOUT" class="ch.qos.logback.classic.AsyncAppender">
    <appender-ref ref="STDOUT" />
  </appender>

  <logger name="play" level="INFO" />

  <logger name="org.jdbcdslog.ConnectionLogger" level="OFF"  /> <!-- Won' log connections -->
  <logger name="org.jdbcdslog.StatementLogger"  level="INFO" /> <!-- Will log all statements -->
  <logger name="org.jdbcdslog.ResultSetLogger"  level="OFF"  /> <!-- Won' log result sets -->

  <root level="WARN">
    <appender-ref ref="ASYNCFILE" />
    <appender-ref ref="ASYNCSTDOUT" />
  </root>

  <shutdownHook class="ch.qos.logback.core.hook.DelayingShutdownHook"/> 
  
</configuration>

Warning: Keep in mind that this is intended to be used just in development environments and you should not configure it in production, since there is a performance degradation and it will pollute your logs.

§Accessing the JDBC datasource

Play database packages provides access to the default datasource, primarily through the Database (see docs for Java and Scala) class.

Java
import javax.inject.*;

import play.db.*;

import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CompletionStage;

@Singleton
class JavaApplicationDatabase {

  private Database db;
  private DatabaseExecutionContext executionContext;

  @Inject
  public JavaApplicationDatabase(Database db, DatabaseExecutionContext context) {
    this.db = db;
    this.executionContext = executionContext;
  }

  public CompletionStage<Integer> updateSomething() {
    return CompletableFuture.supplyAsync(
        () -> {
          return db.withConnection(
              connection -> {
                // do whatever you need with the db connection
                return 1;
              });
        },
        executionContext);
  }
}
Scala
import javax.inject.Inject
import play.api.db.Database

import scala.concurrent.Future

class ScalaApplicationDatabase @Inject() (db: Database, databaseExecutionContext: DatabaseExecutionContext) {
  def updateSomething(): Unit = {
    Future {
      db.withConnection { conn =>
        // do whatever you need with the db connection
      }
    }(databaseExecutionContext)
  }
}

For a database other than the default:

Java

import javax.inject.Inject;
import javax.inject.Singleton;

import play.db.NamedDatabase;
import play.db.Database;

import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CompletionStage;

@Singleton
class JavaNamedDatabase {
  private Database db;
  private DatabaseExecutionContext executionContext;

  @Inject
  public JavaNamedDatabase(
      // inject "orders" database instead of "default"
      @NamedDatabase("orders") Database db, DatabaseExecutionContext executionContext) {
    this.db = db;
    this.executionContext = executionContext;
  }

  public CompletionStage<Integer> updateSomething() {
    return CompletableFuture.supplyAsync(
        () ->
            db.withConnection(
                connection -> {
                  // do whatever you need with the db connection
                  return 1;
                }),
        executionContext);
  }
}
Scala
import javax.inject.Inject
import play.api.db.Database
import play.db.NamedDatabase

import scala.concurrent.Future

class ScalaNamedDatabase @Inject() (
    @NamedDatabase("orders") ordersDatabase: Database,
    databaseExecutionContext: DatabaseExecutionContext
) {
  def updateSomething(): Unit = {
    Future {
      ordersDatabase.withConnection { conn =>
        // do whatever you need with the db connection
      }
    }(databaseExecutionContext)
  }
}

In both cases, when using withConnection, the connection will be automatically closed at the end of the block.

§Obtaining a JDBC connection

You can retrieve a JDBC connection the same way:

Java
import java.sql.Connection;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CompletionStage;
import javax.inject.Inject;

import play.db.Database;

class JavaJdbcConnection {
  private Database db;
  private DatabaseExecutionContext executionContext;

  @Inject
  public JavaJdbcConnection(Database db, DatabaseExecutionContext executionContext) {
    this.db = db;
    this.executionContext = executionContext;
  }

  public CompletionStage<Void> updateSomething() {
    return CompletableFuture.runAsync(
        () -> {
          // get jdbc connection
          Connection connection = db.getConnection();

          // do whatever you need with the db connection
          return;
        },
        executionContext);
  }
}
Scala

import javax.inject.Inject
import play.api.db.Database

import scala.concurrent.Future

class ScalaJdbcConnection @Inject() (db: Database, databaseExecutionContext: DatabaseExecutionContext) {
  def updateSomething(): Unit = {
    Future {
      // get jdbc connection
      val connection = db.getConnection()

      // do whatever you need with the db connection

      // remember to close the connection
      connection.close()
    }(databaseExecutionContext)
  }
}

It is important to note that resulting Connections are not automatically disposed at the end of the request cycle. In other words, you are responsible for calling their close() method somewhere in your code so that they can be immediately returned to the pool.

§Using a CustomExecutionContext

You should always use a custom execution context when using JDBC, to ensure that Play’s rendering thread pool is completely focused on rendering results and using cores to their full extent. You can use Play’s CustomExecutionContext (see docs for Java and Scala) class to configure a custom execution context dedicated to serving JDBC operations. See JavaAsync/ScalaAsync and ThreadPools for more details.

All of the Play example templates on Play’s download page that use blocking APIs (i.e. Anorm, JPA) have been updated to use custom execution contexts where appropriate. For example:

  1. Scala: going to playframework/play-scala-anorm-example/ shows that the CompanyRepository class takes a DatabaseExecutionContext that wraps all the database operations.
  2. Java: going to playframework/play-java-jpa-example shows that the JPAPersonRepository class takes a DatabaseExecutionContext that wraps all the database operations.

For thread pool sizing involving JDBC connection pools, you want a fixed thread pool size matching the connection pool, using a thread pool executor. Following the advice in HikariCP’s pool sizing page, you should configure your JDBC connection pool to double the number of physical cores, plus the number of disk spindles, i.e. if you have a four core CPU and one disk, you have a total of 9 JDBC connections in the pool:

# db connections = ((physical_core_count * 2) + effective_spindle_count)
fixedConnectionPool = 9

database.dispatcher {
  executor = "thread-pool-executor"
  throughput = 1
  thread-pool-executor {
    fixed-pool-size = ${fixedConnectionPool}
  }
}

§Configuring the connection pool

Out of the box, Play uses HikariCP as the default database connection pool implementation. Also, you can use your own pool that implements play.api.db.ConnectionPool by specifying the fully-qualified class name:

play.db.pool=your.own.ConnectionPool

The full range of configuration options for connection pools can be found by inspecting the play.db.prototype property in Play’s JDBC reference.conf.

§Testing

For information on testing with databases, including how to setup in-memory databases and, see :

§Enabling Play database evolutions

Read Evolutions to find out what Play database evolutions are useful for, and follow the instructions for using it.

Next: Using an in memory H2 database