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.
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:
libraryDependencies ++= Seq( javaJdbc)libraryDependencies ++= Seq( jdbc)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 adependency for the connector:
libraryDependencies ++= Seq( "com.mysql" % "mysql-connector-j" % "8.0.33")Then you must configure a connection pool in theconf/application.conf file. By convention, the default JDBC data source must be calleddefault and the corresponding configuration properties aredb.default.driver anddb.default.url.
# Default database configurationdb.default.driver=org.h2.Driverdb.default.url="jdbc:h2:mem:play"If something isn’t properly configured, you will be notified directly in your browser:
You can also change thedefault name by settingplay.db.default, for example:
play.db.default = "primary"db.primary.driver=org.h2.Driverdb.primary.url="jdbc:h2:mem:play"To configure several data sources:
# Orders databasedb.orders.driver=org.h2.Driverdb.orders.url="jdbc:h2:mem:orders"# Customers databasedb.customers.driver=org.h2.Driverdb.customers.url="jdbc:h2:mem:customers"In memory database:
# Default database configuration using H2 database engine in an in-memory modedb.default.driver=org.h2.Driverdb.default.url="jdbc:h2:mem:play"File based database:
# Default database configuration using H2 database engine in a persistent modedb.default.driver=org.h2.Driverdb.default.url="jdbc:h2:/path/to/db-file"The details of the H2 database URLs are found fromH2 Database Engine Cheat Sheet.
# Default database configuration using SQLite database enginedb.default.driver=org.sqlite.JDBCdb.default.url="jdbc:sqlite:/path/to/db-file"# Default database configuration using PostgreSQL database enginedb.default.driver=org.postgresql.Driverdb.default.url="jdbc:postgresql://database.example.com/playdb"# Default database configuration using MySQL database engine# Connect to playdb as playdbuserdb.default.driver=com.mysql.jdbc.Driverdb.default.url="jdbc:mysql://localhost/playdb"db.default.username=playdbuserdb.default.password="a strong password"Some libraries expect to retrieve theDatasource reference fromJNDI. You can expose any Play managed datasource via JNDI by adding this configuration inconf/application.conf:
db.default.driver=org.h2.Driverdb.default.url="jdbc:h2:mem:play"db.default.jndiName=DefaultDSNot 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. FromHikariCP 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 usesjdbcdslog-exp to enable consistent SQL log statement support for supported pools. The SQL log statement can be configured by database, usinglogSql property:
# Default database configuration using PostgreSQL database enginedb.default.driver=org.postgresql.Driverdb.default.url="jdbc:postgresql://database.example.com/playdb"db.default.logSql=trueAfter that, you can configure the jdbcdslog-explog level as explained in their manual. Basically, you need to configure your root logger toINFO and then decide what jdbcdslog-exp will log (connections, statements and result sets). Here is an example usinglogback.xml to configure the logs:
<?xml version="1.0" encoding="UTF-8" ?><!-- Copyright (C) from 2022 The Play Framework Contributors <https://github.com/playframework>, 2011-2021 Lightbend Inc. <https://www.lightbend.com>--><!DOCTYPE configuration><configuration> <import class="ch.qos.logback.classic.encoder.PatternLayoutEncoder"/> <import class="ch.qos.logback.classic.AsyncAppender"/> <import class="ch.qos.logback.core.FileAppender"/> <import class="ch.qos.logback.core.ConsoleAppender"/> <appender name="FILE" class="FileAppender"> <file>${application.home:-.}/logs/application.log</file> <encoder class="PatternLayoutEncoder"> <pattern>%date [%level] from %logger in %thread - %message%n%xException</pattern> </encoder> </appender> <appender name="STDOUT" class="ConsoleAppender"> <encoder class="PatternLayoutEncoder"> <pattern>%highlight(%-5level) %logger{15} - %message%n%xException{10}</pattern> </encoder> </appender> <appender name="ASYNCFILE" class="AsyncAppender"> <appender-ref ref="FILE"/> </appender> <appender name="ASYNCSTDOUT" class="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></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.
Play database packages provides access to the default datasource, primarily through theDatabase (see docs forJava andScala) class.
import java.util.concurrent.CompletableFuture;import java.util.concurrent.CompletionStage;import javax.inject.*;import play.db.*;@Singletonclass 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); }}import javax.inject.Injectimport scala.concurrent.Futureimport play.api.db.Databaseclass 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:
import java.util.concurrent.CompletableFuture;import java.util.concurrent.CompletionStage;import javax.inject.Inject;import javax.inject.Singleton;import play.db.Database;import play.db.NamedDatabase;@Singletonclass 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); }}import javax.inject.Injectimport scala.concurrent.Futureimport play.api.db.Databaseimport play.db.NamedDatabaseclass 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 usingwithConnection, the connection will be automatically closed at the end of the block.
You can retrieve a JDBC connection the same way:
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); }}
import javax.inject.Injectimport scala.concurrent.Futureimport play.api.db.Databaseclass 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 theirclose() method somewhere in your code so that they can be immediately returned to the pool.
CustomExecutionContextYou 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’sCustomExecutionContext (see docs forJava andScala) class to configure a custom execution context dedicated to serving JDBC operations. SeeJavaAsync/ScalaAsync andThreadPools for more details.
All of the Play example templates onPlay’s download page that use blocking APIs (i.e. Anorm, JPA) have been updated to use custom execution contexts where appropriate. For example:
DatabaseExecutionContext that wraps all the database operations.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 inHikariCP’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 = 9database.dispatcher { executor = "thread-pool-executor" throughput = 1 thread-pool-executor { fixed-pool-size = ${fixedConnectionPool} }}Out of the box, Play usesHikariCP as the default database connection pool implementation. Also, you can use your own pool that implementsplay.api.db.ConnectionPool by specifying the fully-qualified class name:
play.db.pool=your.own.ConnectionPoolThe full range of configuration options for connection pools can be found by inspecting theplay.db.prototype property in Play’s JDBCreference.conf.
For information on testing with databases, including how to setup in-memory databases and, see :
ReadEvolutions to find out what Play database evolutions are useful for, and follow the instructions for using it.
Found an error in this documentation? The source code for this page can be foundhere. After reading thedocumentation guidelines, please feel free to contribute a pull request. Have questions or advice to share? Go toour community forums to start a conversation with the community.