Let’s Play! - My Play Framework Learning Journey (6) - Play Database Connectivity

In this post I will cover aspects of Play database connectivity:

  • Swappable database connection
  • Database password encryption/decryption

Slick is used for database connectivity. For how to use Slick API, please refer to Slick documentation.

Swappable Database Connection

The objective here is to allow using of different databases for different purpose: in-memory database for local unit testing, and Oracle database for integration testing, acceptance testing and production. Furthermore, for different environments, different database connection string, user name and password should be used.

Slick provides a DatabaseConfig class that allows you to configure a Slick driver plus a matching Database together. This makes it easy to abstract over different kinds of database systems by simply changing a configuration file. The idea is to inject the right DatabaseConfig instance for different environment and needs. One way is to bind based on an environment variable.

@Provides @Singleton
def provideDatabaseConfig(lifecycle: ApplicationLifecycle)(implicit ec: ExecutionContext): DatabaseConfig[JdbcProfile] = {
  val env = Option(System.getProperty("env")).getOrElse("localtest")
  val dbConf = env match {
    case "dev" => DbConfigHelper(conf).getDecryptedConfig("dev_db")
    case x => DbConfigHelper(conf).getDecryptedConfig(x)
  }
  lifecycle.addStopHook { () =>
    Future {
      Try(dbConf.db.close()) match {
        case Success(_) => log.info("Database successfully closed.")
        case Failure(t) => log.warn("Error occurred while closing database.", t)
      }
    }
  }
  dbConf
}

This will bind one of the database configuration defined in Play’s application.conf, depending on the value of the “env” environment variable:

localtest {
  profile = "slick.jdbc.H2Profile$"
  db {
    url = "jdbc:h2:mem:test;INIT=runscript from 'conf/create-schema.sql'"
    driver = org.h2.Driver
    connectionPool = disabled
    keepAliveConnection = true
  }
}

dev_db {
  profile = "slick.jdbc.OracleProfile$"
  db {
    url = "jdbc:oracle:thin:@//<server>:<port>/<service>"
    driver = "oracle.jdbc.driver.OracleDriver"
  }
}
  • If env = “dev”, the database “dev_db” will be injected.
  • If env = “localtest” or is absent, the in-memory database “localtest” will be injected.
  • More environments can be added.
  • If env is specified but does not match all the above, it will attempt to inject a database with the same name as env’s value.

Next, you can inject DatabaseConfig using the standard @Inject annotation, and get the Database object from DatabaseConfig:

@Singleton
class SomeRepo @Inject() (val dbConfig: DatabaseConfig[JdbcProfile], val tables: Tables, cacheManager: CacheManager) {

  import dbConfig.profile.api._

  val db = dbConfig.db

  @CacheResult(cacheName="default")
  def findAll: Future[Seq[SomeTable]] = db.run(tables.someTable.result)

}

Here “Tables” is the class that defines all the Slick “entities”. Ignore the cache related stuff for now - they will be covered in a separate blog.

Finally, you can pass in a value for the “env” environment variable. This can be done in various ways, such as using the -D switch of sbt:

sbt -Denv=dev run

Why not Play-Slick

Play has a Play-Slick integration module but it does not handle the swapping of database connections based on run-time configuration such as environment variables.

The difference between using Play-Slick and using Slick directly is:

  • Play-Slick provides database evolution feature, which is similar to what Flyway does.
  • Play-Slick takes care of the life-cycle management to create and shut down the connection or connection pool together with your application.

So if Slick is used directly, one needs to handle the application life-cycle explicitly and shut down the connections when the application is shut down.

The above is confirmed with Lightbend’s support team.

Using HikariCP as Connection Pool with Slick

It is pretty easy to use HikariCP for connection pooling as Slick supports HikariCP as the default pooling implementation. Just define the following dependency:

libraryDependencies += "com.typesafe.slick" %% "slick-hikaricp" % "3.1.1"

By default pooling will be enabled. To disable pooling, set:

    connectionPool = disabled

Configuration of HikariCP can be done in the db configuration. Refer to this API Doc for all the configuration options.

Database Password Decryption

In some cases your organization will require database passwords stored in files to be encrypted. This means when the application need to use the password to connect to database, it need to decrypt it first.

This post will not talk about the actual encryption and decryption logic. It will just cover how to use Play’s externalized configuration mechanism to handle decryption of password.

First we need to be able to read the credentials information (encrypted password, key and maybe IV from some externalized settings. Play provides a Configuration object to get the configuration settings put in its configuration files (default is application.conf).

It might be good to keep the credentials information separate from other configurations. Typesafe Config allows file inclusion, so we can then put the them in another file (say “credentials.conf”), and include it in application.conf.

include "credentials"

Next we want to get the Slick DatabaseConfig object loaded from configuration, decrypt the encrypted password text in the DatabaseConfig object, and replace its password attribute with the decrypted password. Below is an example:

case class DbConfigHelper(conf: Configuration) {

  def getDecryptedConfig(db: String): DatabaseConfig[JdbcProfile] = {

    val dbConfig = DatabaseConfig.forConfig[JdbcProfile](db)
    val decryptor: Option[Decryptor] = ??? // create decryptor

    decryptor map { decryptor =>
      val decryptedConfig = dbConfig.config.
        withValue("db.password", ConfigValueFactory.fromAnyRef(decryptor.decrypt(dbConfig.config.getConfig("db").getString("password"))))
      DatabaseConfig.forConfig[JdbcProfile]("", decryptedConfig)
    } getOrElse dbConfig
  }
}

Note that your own decryptor API will probably be different. The above example is just to illustrate how you can replace the password attribute in the DatabaseConfig object.

Additional References

Go Top
comments powered by Disqus