While it is possible to write functional tests using ScalaTest or specs2 that test database access code by starting up a full application including the database, starting up a full application is not often desirable, due to the complexity of having many more components started and running just to test one small part of your application.

Play provides a number of utilities for helping to test database access code that allow it to be tested with a database but in isolation from the rest of your app. These utilities can easily be used with either ScalaTest or specs2, and can make your database tests much closer to lightweight and fast running unit tests than heavy weight and slow functional tests.

To connect to a database, at a minimum, you just need database driver name and the url of the database, using the Databases companion object. For example, to connect to MySQL, you might use the following:

This will create a database connection pool for the MySQL test database running on localhost, with the name default. The name of the database is only used internally by Play, for example, by other features such as evolutions, to load resources associated with that database.

You may want to specify other configuration for the database, including a custom name, or configuration properties such as usernames, passwords and the various connection pool configuration items that Play supports, by supplying a custom name parameter and/or a custom config parameter:

After using a database, since the database is typically backed by a connection pool that holds open connections and may also have running threads, you need to shut it down. This is done by calling the shutdown method:

database.shutdown()

Manually creating the database and shutting it down is useful if you’re using a test framework that runs startup/shutdown code around each test or suite. Otherwise it’s recommended that you let Play manage the connection pool for you.

Play also provides a withDatabase helper that allows you to supply a block of code to execute with a database connection pool managed by Play. Play will ensure that it is correctly shutdown after the block of code finishes executing:

Like the Database.apply factory method, withDatabase also allows you to pass a custom name and config map if you please.

Typically, using withDatabase directly from every test is an excessive amount of boilerplate code. It is recommended that you create your own helper to remove this boiler plate that your test uses. For example:

Tip: You can use this to externalize your test database configuration, using environment variables or system properties to configure what database to use and how to connect to it. This allows for maximum flexibility for developers to have their own environments set up the way they please, as well as for CI systems that provide particular environments that may differ to development.

Some people prefer not to require infrastructure such as databases to be installed in order to run tests. Play provides simple helpers to create an H2 in-memory database for these purposes:

import play.api.db.Databases
val database = Databases.inMemory()

The in-memory database can be configured, by supplying a custom name, custom URL arguments, and custom connection pool configuration. The following shows supplying the MODE argument to tell H2 to emulate MySQL, as well as configuring the connection pool to log all statements:

When running tests, you will typically want your database schema managed for your database. If you’re already using evolutions, it will often make sense to reuse the same evolutions that you use in development and production in your tests. You may also want to create custom evolutions just for testing. Play provides some convenient helpers to apply and manage evolutions without having to run a whole Play application.

To apply evolutions, you can use applyEvolutions from the Evolutions companion object:

import play.api.db.evolutions._
Evolutions.applyEvolutions(database)

This will load the evolutions from the classpath in the evolutions/<databasename> directory, and apply them.

After a test has run, you may want to reset the database to its original state. If you have implemented your evolutions down scripts in such a way that they will drop all the database tables, you can do this simply by calling the cleanupEvolutions method:

In some situations you may want to run some custom evolutions in your tests. Custom evolutions can be used by using a custom EvolutionsReader. The simplest of these is the SimpleEvolutionsReader, which is an evolutions reader that takes a preconfigured map of database names to sequences of Evolution scripts, and can be constructed using the convenient methods on the SimpleEvolutionsReader companion object. For example:

Cleaning up custom evolutions is done in the same way as cleaning up regular evolutions, using the cleanupEvolutions method:

Evolutions.cleanupEvolutions(database)

Note though that you don’t need to pass the custom evolutions reader here, this is because the state of the evolutions is stored in the database, including the down scripts which will be used to tear down the database.

Sometimes it will be impractical to put your custom evolution scripts in code. If this is the case, you can put them in the test resources directory, under a custom path using the ClassLoaderEvolutionsReader. For example:

The applyEvolutions and cleanupEvolutions methods are useful if you’re using a test framework to manage running the evolutions before and after a test. Play also provides a convenient withEvolutions method to manage it for you, if this lighter weight approach is desired:

Naturally, withEvolutions can be combined with withDatabase or withInMemory to reduce boilerplate code, allowing you to define a function that both instantiates the database and runs evolutions for you: