Database Access with Play

1. Overview

Almost all applications these days use some sort of database to store data. These databases can range from SQL databases such as MySQL, PostgreSQL, or NoSQL databases such as MongoDB or DynamoDB.

As developers, we need to know exactly how to configure and integrate with the database of our choice from our code.

In this tutorial, we’ll see how to integrate with a database using the Play Framework.

2. Database Configuration

To connect to our database, we’ll use Slick, which is a database driver that gives us the benefit of static checking, compile-time safety, and compositionality of Scala alongside plain SQL.

To use Slick, we need to add the dependency to our build.sbt:

libraryDependencies ++= Seq(
  "com.typesafe.play" %% "play-slick" % "6.0.0",
)

This basically adds the Slick library as well as other dependencies.

2.1. PostgreSQL

To connect to PostgreSQL, we need to add the postgresql JDBC driver to our build.sbt as Slick will use it to establish a connection to PostgreSQL.

Our build.sbt should then look like:

libraryDependencies ++= Seq(
  "com.typesafe.play" %% "play-slick" % "6.0.0",
  "org.postgresql" % "postgresql" % "42.5.1"
)

Now that we have our dependencies all set up, we need to configure Slick. To do that, we need to add our database configuration to our application.conf.

By convention, the default Slick database must have the name default:

slick.dbs.default {
  profile = "slick.jdbc.PostgresProfile$"
  db {
    driver = "org.postgresql.Driver"
    url="jdbc:postgres://localhost:5432/{database_name}"
    user=root
    password=password
    numThreads=20
    maxConnections=20
  }
}

Slick 3.0 added numThreads and maxConnections in version 3.0 and upwards to provide a thread and connection pool for optimal asynchronous execution of database actions.

2.2. MySQL

To connect to MySQL, we need to add the MySQL JDBC driver to our build.sbt. Slick will use this to connect to MySql:

libraryDependencies ++= Seq(
  "com.typesafe.play" %% "play-slick" % "6.0.0",
  "mysql" % "mysql-connector-java" % "8.0.15"
)

Now, similar to how we did it for PostgreSQL, we set up our database configuration in application.conf:

slick.dbs.default {
  profile = "slick.jdbc.MySQLProfile$"
  db {
    driver = "com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/mysql"
    user=mysql
    password=mysql
    numThreads=20
    maxConnections=20
  }
}

3. Querying the Database

Now that we’ve configured our database, we need to use it in our code. To use the database, we just need to add our database configuration provider to our controller or DAO and also extend HasDatabaseConfigProvider[JdbcProfile]:

@Singleton
class UserDAO @Inject()(protected val dbConfigProvider: DatabaseConfigProvider)(implicit ec: ExecutionContext)
  extends HasDatabaseConfigProvider[JdbcProfile] {
  import profile.api._
}

By adding the dbConfigProvider and extending HasDatabaseConfigProvider[JdbcProfile], we get access to the db variable, which represents our database against which we can run our queries. The profile.api._ import gives us the database I/O Action implementation for the relevant driver and database, the sql string interpolator amongst others.

Slick provides us with the ability to interact with our database as normally would with collections and also enables us to run plain SQL queries.

Our DAO could contain a method like:

def findEmployeeNameById(id : Int) : Future[Option[String]] = {
  val query = sql"select name from employees where id = ${id};".as[String]
  val queryResult : Future[Vector[String]] = db.run(query)
  queryResult.map(_.headOption)
}

The Slick documentation contains a lot of examples of queries and other extra methods we might find useful.

4. Querying Multiple Databases

As we’ve seen, the default Slick database configuration is called default, but what if we want to give ours a different name or use multiple databases?

We can do that by defining both database configurations in our application.conf:

slick.dbs.default {
  profile = "slick.jdbc.PostgresProfile$"
  db {
    driver = "org.postgresql.Driver"
    url="jdbc:postgres://localhost:5432/postgres"
    user=postgres
    password=postgres
    numThreads=20
    maxConnections=20
  }
}

slick.dbs.second {
  profile = "slick.jdbc.MySQLProfile$"
  db {
    driver = "com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/mysql"
    user=mysql
    password=mysql
    numThreads=20
    maxConnections=20
  }

Now, in our controller or DAO, we can refer to the second database by using the NamedDatabase annotation to access our other database named second:

import play.db.NamedDatabase
@Singleton
class UserDAO @Inject()(@NamedDatabase("second") protected val dbConfigProvider: DatabaseConfigProvider)(implicit ec: ExecutionContext)
  extends HasDatabaseConfigProvider[JdbcProfile] {
  import profile.api._
}

We can run the same query as before and it will be run against the second database.

5. Conclusion

In this article, we’ve seen how to set up database configurations, connect to our database from our code, and run queries against the database. When setting database connections, it’s important to understand how the database is configured as well as how it’s used.

As usual, the source code can be found over on GitHub.