Managing database with Play Carbonate module

Managing the database is important aspect of modern application development, typically done with database migrations. I used migrations before I started working with play so naturally the first module I wrote for it was a database migrations module with my favorite migrations library, Carbon Five Migrations.

The initial version of the module was really basic and after a twenty or so models created and equal amount of copy-pasted create table statements I got this idea of updating my module with the Hibernate schema update. And the result of that is the Play Carbonate module.

Remember that the schema update is just a tool. Always check the SQL it generates!

Configuration

To use the module add the module to your application.conf file

module.carbonate=${play.path}/modules/carbonate-{version}

Next configure the database and add the carbonate path in to the application.conf file, in this example we are using id local-mysql

%local-mysql.db=
%local-mysql.db.url=jdbc:mysql://localhost:3306/play-test
%local-mysql.db.driver=com.mysql.jdbc.Driver
%local-mysql.db.user=root
%local-mysql.db.pass=
%local-mysql.jpa.ddl=none
%local-mysql.carbonate.path=conf/migrations
Now our carbonate module is configured and ready to use.

Usage example

Now lets consider a simple model shown below We will run the carbonate:new command to generate a new database migration file

play carbonate:new --%local-mysql
We open the file and format it a bit and add a semicolon to the end, in general we make sure that it is what we want.

To run the migration we just start our play application

play run --%local-mysql
22:08:20,709 INFO  ~ Running migrations from path conf/migrations
22:08:20,757 INFO  ~ Migrating database... applying 1 migration.
22:08:20,758 INFO  ~ Running migration 20110404220338_simple_model.sql.
22:08:20,812 INFO  ~ Migrated database in 0:00:00.250.

Now we need to change our model a bit, we want to add a category for our entity. So after the change we run the carbonate:new again

play carbonate:new --%local-mysql

And again after some formatting and most importantly adding the semicolons our migration looks like this

In general you should split every SQL statement to its own migration to avoid trouble in the case they fail. Here we keep them in single file to keep this example short and readable.

To apply the migration we run our application again

play run --%local-mysql
22:19:28,016 INFO  ~ Running migrations from path conf/migrations
22:19:28,058 INFO  ~ Migrating database... applying 1 migration.
22:19:28,059 INFO  ~ Running migration 20110404221245_added_category_for_the_entity.sql.
22:19:28,439 INFO  ~ Migrated database in 0:00:00.380.