Managing Database Evolutions

When you use a relational database, you need a-track and organize your database schema evolutions. Typically there is several situation where you need a more sophisticated the "to" track your database schema changes:

• When your work is within a team of developers, each person needs to know about the any schema change.
• When you deploy on a production server, you need to a robust-to upgrade your database schema.
• If you work on the several machines, you need to keep all database schemas synchronized.

If you work with JPA, Hibernate can handle database evolutions for you automatically. Evolutions is useful if you don't use JPA or if you prefer to manually take care of your database schema for finer tuning .

Evolutions scripts

Play tracks your database evolutions using several evolutions script. These scripts is written in plain old SQL and should BES located in the db/evolutions directory of your Applicati On.

The first script is named 1.sql, the second script 2.sql, and so on ...

Each script contains the parts:

• The Ups part the describe the required transformations.
• The Downs part, describe how to revert them.

For example, take a look at the This first evolution script that bootstrap a basic application:

# Users schema # --- !Ups CREATE TABLE User (    id bigint(20) NOT NULL AUTO_INCREMENT,    email varchar(255) NOT NULL,    password varchar(255) NOT NULL,    fullname varchar(255) NOT NULL,    isAdmin boolean NOT NULL,    

As you see you has to delimitate the both Ups and Downs sections by using comments in your SQL script.

When evolutions is activated, Play would check your database schema state before each request in DEV mode, or BEF Ore starting the application in PROD mode. In DEV mode, if your database schema was not up to date, an error page would suggest that synchronize your database s Chema by running the appropriate SQL script.

If you agree with the SQL script, you can apply it directly to clicking on the ' Apply Evolutions ' button.

If you use an In-memory database (db=mem), Play would automatically run all evolutions scripts if your database is Empty.

Synchronizing Concurrent Changes

Now let's imagine that we have the developers working on this project. Developer A would work on a feature that require a new database table. So it'll create the following 2.sql Evolution script:

# Add Post # --- !UpsCREATE TABLE Post (    id bigint(20) NOT NULL AUTO_INCREMENT,    title varchar(255) NOT NULL,    content text NOT NULL,    postedAt date NOT NULL,    author_id bigint(20) NOT NULL,    FOREIGN KEY (author_id) REFERENCES User(id),    PRIMARY KEY (id)); # --- !DownsDROP TABLE Post;

Play would apply this evolution script to Developer A database.

On the other hand, developer B would work on a feature this require to alter the User table. So it'll also create the following 2.sql Evolution script:

# Update User # --- !UpsALTER TABLE User ADD age INT; # --- !DownsALTER TABLE User DROP age;

Developer B finishes his feature and commits (let's say they are using Git). Now developer A have to merge the work of he colleague before continuing, so it run gitpull, and the merge have A Conflict, like:

Auto-merging db/evolutions/2.sqlCONFLICT (add/add): Merge conflict in db/evolutions/2.sqlAutomatic merge failed; fix conflicts and then commit the result.

Each developer have created a 2.sql evolution script. So developer A needs to merge the content of this file:

<<<<<<< HEAD# Add Post # --- !UpsCREATE TABLE Post (    id bigint(20) NOT NULL AUTO_INCREMENT,    title varchar(255) NOT NULL,    content text NOT NULL,    postedAt date NOT NULL,    author_id bigint(20) NOT NULL,    FOREIGN KEY (author_id) REFERENCES User(id),    PRIMARY KEY (id)); # --- !DownsDROP TABLE Post;=======# Update User # --- !UpsALTER TABLE User ADD age INT; # --- !DownsALTER TABLE User DROP age;>>>>>>> devB

The merge is really easy-to-do:

# Add Post and update User # --- !UpsALTER TABLE User ADD age INT; CREATE TABLE Post (    id bigint(20) NOT NULL AUTO_INCREMENT,    title varchar(255) NOT NULL,    content text NOT NULL,    postedAt date NOT NULL,    author_id bigint(20) NOT NULL,    FOREIGN KEY (author_id) REFERENCES User(id),    PRIMARY KEY (id)); # --- !DownsALTER TABLE User DROP age; DROP TABLE Post;

This evolution script represents the new Revision 2 of the database, which is different of the previous revision 2 That's developer A has already applied.

So Play would detect it and ask developer A to synchronize he database by first reverting the old revision 2 already appli Ed, and by applying the new Revision 2 script:

Inconsistent states

Sometimes a mistake in your evolution scripts, and they would fail. In this case, Play would mark your database schema as being in a inconsistent state and would ask you to manually resolve T He problem before continuing.

For example, the Ups script of this evolution have an error:

# Add another column to User  # --- !UpsALTER TABLE Userxxx ADD company varchar(255); # --- !DownsALTER TABLE User DROP company;

So trying to apply this evolution would fail, and Play would mark your database schema as inconsistent:

Now before continuing you have the fix this inconsistency. So you run the fixed SQL command:

ALTER TABLE User ADD company varchar(255);

... and then mark this problem as manually resolved by clicking on the button.

But because your evolution script has the error, you probably want to fix it. So you modify the 3.sql script:

# Add another column to User  # --- !UpsALTER TABLE User ADD company varchar(255); # --- !DownsALTER TABLE User DROP company;

Play detects this new evolution that replaces the previous 3 one, and would run the following script:

Now everything are fixed, and can continue to work.

In developement mode however it's often simpler to simply trash your developement database and reapply all evolutions fro M the beginning.

Evolutions commands

The evolutions run interactively in DEV mode. However in PROD mode you'll have to use theevolutions command to fix your database schema before running your a Pplication.

If you try to run a application in production mode on a database which is not up to date, the application would not start.

~        _            _ ~  _ __ | | __ _ _  _| |~ | ' _ \| |/_ ' | | |  |_|~ |            __/|_|\____|\__ (_)~ |_| |__/~ ~ play! Master-localbuild, http://www.playframework.org~ framework ID is prod~~ CTRL + C to stop~ 13:33:22 INFO ~ starting ~/test13 : 33:22 INFO ~ precompiling ... 13:33:24 INFO ~ Connected to jdbc:mysql://localhost13:33:24 WARN ~ 13:33:24 WARN ~ Your database was not up to Date.13:3 3:24 WARN ~ use ' play evolutions ' command to manage database evolutions.13:33:24 ERROR ~ @662c6n234can ' t start in PROD m Ode with errors Your database needs evolution! An SQL script would be is run on your database. Play.db.evolutions$invaliddatabaserevisionat play.db.Evolutions.checkEvolutionsState (evolutions.java:323) at Play.db.Evolutions.onApplicationStart (evolutions.java:197) at play. Play.start (play.java:452) at Play. Play.init (play.java:298) at Play.server.Server.main (server.java:141) Exception in thread "main" play.db.evolutions$ Invaliddatabaserevisionat Play.db.Evolutions.checkEvolutionsStatE (evolutions.java:323) at Play.db.Evolutions.onApplicationStart (evolutions.java:197) at play. Play.start (play.java:452) at Play. Play.init (play.java:298) at Play.server.Server.main (server.java:141)

The error message ask you to run the  play evolutions  command:

$ play Evolutions~ _ _ ~ _ __ | | __ _ _  _| |~ | ' _ \| |/_ ' | | |  |_|~ |            __/|_|\____|\__ (_)~ |_| |__/~ ~ play! Master-localbuild, http://www.playframework.org~ framework ID is gbo~~ Connected to jdbc:mysql://localhost~ application Revision is 3 [15ED3F5] and Database revision are 0 [da39a3e]~~ Your Database needs evolutions! #----------------------------------------------------------------------------#---rev:1,ups-6b21167 CREATE TABLE User (id bigint () NOT NULL auto_increment, email varchar (255) is not NULL, password varchar (255) is not NULL, ful lname varchar (255) NOT NULL, ISAdmin boolean NOT NULL, PRIMARY KEY (id)); #---rev:2,ups-9cf7e12 ALTER TABLE User ADD age INT;    CREATE TABLE Post (id bigint () NOT NULL auto_increment, title varchar (255) is not NULL, content text is not NULL, Postedat date not NULL, author_id bigint (a) not NULL, FOREIGN key (author_id) REFERENCES User (ID), PRIMARY key (ID)); #---Rev:3,Ups-15ed3f5 ALTER TABLE User ADD Company varchar (255); #----------------------------------------------------------------------------~ Run ' play evolutions:apply ' to Automatically apply this script to the db~ or apply it yourself and mark it do using ' play evolutions:markapplied ' ~

If you want Play to automatically run this evolution for you, then run:

$ play evolutions:apply

If you prefer running this script manually on your production database, you need to the tell Play ' that ' is up-to-d Ate by running:

$ play evolutions:markApplied

If there is any errors while automatically running the evolutions scripts, as in DEV mode, you need to manually resolve T Hem, and mark your database schema a fixed by running:

$ play evolutions:resolve

Continuing the discussion

Learn how to configure Logging.

Managing Database Evolutions