Community contributed extensions

Multiple Database module

This module allows you to scale your Play! apps to use multiple databases of the same schema. One use of this module might be to reduce the load on a single database server, by storing data for separate users in separate databases.

Currently, this module only supports databases with identical schemas (i.e., each database has to have tables with the same name and structure).

Enable the migrate module for the application

In the /conf/application.conf file, you need a line to add the module to your project:

# The multiple database module
module.multidb=${play.path}/modules/multidb

Then, you need to add your connection parameters to your databases. Here is an example using two databases:

# Multiple Database Configuration
# ~~~~~
#
# db1 
mdb.key.1=host1
mdb.url.1=jdbc:mysql://localhost/db1
mdb.driver.1=com.mysql.jdbc.Driver
mdb.user.1=test
mdb.pass.1=test
#
# db2 
mdb.key.2=host2
mdb.url.2=jdbc:mysql://localhost/db2
mdb.driver.2=com.mysql.jdbc.Driver
mdb.user.2=test
mdb.pass.2=test
Finally, you need to comment-out / delete configuration entries for Play’s default single-database (starting with ‘db.’ or ‘db=’).

Database Keys and Parameters

Looking at the previous example configuration you’ll probably notice some similarities to Play’s native database parameters; db.XXXX have been replaced with mdb.XXXXX.{group}. Because your application has multiple databases, their parameters are associated with one another using the .{group} suffix. In the example, the groups are numbers, but any alpha-numeric identifier may be used.

One important new parameter is mdb.key.{group}. This parameter identifies the key to finding the correct database to use when each HTTP request comes into your Play application. By default, the Multiple Database module uses the URL’s domain name as the key to identify a database to connect to. So, to test this example configuration, we would give the machine running your Play! application the names of ‘host1’ and ‘host2’. Requests at ‘host1’ would use ‘db1’, where requests at ‘host2’ would use ‘db2’.

Request Processing

As mentioned above, the module uses the domain of the request by default as the key to determine which database to connect to. Before describing how to customize this default behavior, it will help to understand how the Multiple Database module integrates into the request. Here is the step-by-step breakdown:

  1. A request is made to Play!, which creates Play’s Http.Request object
  2. The database key extractor examines the Http.Request object and extracts the database key from it.
  3. The MJPAPlugin (multiple-JPA plugin) uses the database key to start a transaction on the associated database. (If no database is found an error is thrown).
  4. The request is processed by the associated Play! controller. All instances of Model and JPASupport objects use the entity manager and database of the transaction. Calls to JPA.em() honor the connection as well.
  5. The response is sent and the transaction opened in step 3 is closed.

Customizing Database Key Extraction

The default database key extractor implements the RequestDBKeyExtractor interface. This interface has a single method:

String extractKey(Http.Request request);

This method simply operates on the incoming request, extracting and returning the database key from it. The default implementation is thus very simple:

package play.db.jpa;
 
import play.mvc.Http.Request;
 
public class DomainDBKeyExtractor implements RequestDBKeyExtractor
{
	public String extractKey(Request request)
	{
		return (request == null ? null : request.domain);
	}
}
 
To override this method, simply create a single class in your Play application that implements the *RequestDBKeyExtractor* interface and the Multiple Database module will use that extractor instead.  (Make sure you only have one class that implements this interface, or the module will get confused).  

NOTE: If the extractor returns null, the action method will still be invoked, so be cautious in returning null. The null case is used when running a play.jobs.Job, for which no automatic transaction will be available. If an invalid non-null database key is returned, the module will throw an InvalidDatabaseException when it attempts to start the transaction.

Shared Database Parameters

Sometimes your multiple databases might share multiple common parameters. For example, if all of your databases use the MySQL database driver, each would have a configuration entry like: mdb.driver.{group}=com.mysql.jdbc.Driver (This is true for the example configuration as well). To avoid needless duplication, the Multiple Database module recognizes a group of ‘all’ as the common group for all databases. Thus, we could reduce our default configuration (from above) to the following:

# Multiple Database Configuration
# ~~~~~
# all
mdb.driver.all=com.mysql.jdbc.Driver
mdb.user.all=test
mdb.pass.all=test
#
# db1 
mdb.key.1=host1
mdb.url.1=jdbc:mysql://localhost/db1
#
# db2 
mdb.key.2=host2
mdb.url.2=jdbc:mysql://localhost/db2

Runtime Update

In some applications you may want to add a new database to the application ‘on-the-fly’. There is a convenience method that makes this easier: MDBPlugin.addDatabase(Map propertyMap). The passed propertyMap mimics the values from the application.conf file (e.g., mdb.key.new=newhost, etc). Note that the convenience method honors the ‘all’ group mentioned in the previous section.

A Few Caveats

It’s important to remember that this is a new module, and, although it tries to integrate as smoothly as possible with Play!, using this module may disrupt some aspects of existing Play! applications. Here is just a short list of things to watch out for:

  1. Compatibility: For now, this has only been implemented and tested on the 1.0 branch. If you need it for 1.1 and it doesn’t work, please consider contributing!
  2. Transactions: The module essentially overrides the JPAPlugin class and modifies how the JPA class gets its entity manager for the request. All direct programmatic transaction processing needs to use the MJPAPlugin.startTx(String dbKey, boolean readOnly) method, instead of JPAPlugin.startTx(boolean readOnly)
  3. Jobs: Since jobs have no associated Http.Request, Play jobs don’t run within an automatic transaction. Therefore, transaction management will have to be done by hand using the MJPAPlugin.startTx(String dbKey, boolean readOnly) method.