Managing database with play carbonate

04 April 2011

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 is the play-carbonate module.

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

Setting up

To add this module as dependency of your application, add it to the dependencies.yml file:

require:
    - play -> carbonate {version}

Next configure the database and add the carbonate path in to the application.conf file, I am using play id local-mysql and database play-test:

%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 run the the following play command with our play id local-mysql to generate a new migration file under the conf/migrations folder:

play carbonate:new --%local-mysql
Please give description for you migration:
simple model
21:00:18,136 WARN  ~ Changes from schema update:
create table SimpleEntity (id bigint not null auto_increment, age integer not null, isEternal bit not null, name varchar(255), primary key (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
21:00:18,138 WARN  ~ New migration file created ~/code/test-app/conf/migrations/20110404220338_simple_model.sql

Now we can check the generated file

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 want to change our model and add a SimpleCategory to our model:

So after the change we run the command again

play carbonate:new --%local-mysql

ending up with a migration looking like this:

Note that errors in migrations are easier to manage if you split every SQL statement to its own migration file.

To apply the migration we just 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.

playframework 3