Managing Database Evolutions

Evolutions Scripts👨‍🎓

The play framework uses a variety of evolution scripts to keep track of your database evolutions. These scripts should be found in your application's conf/evolutions/{database name} directory and are written in standard SQL. This path should be conf/evolutions/default if the evolutions apply to your default database.

The first script is called 1.sql, followed by 2.sql, and so forth.

Each script consists of two sections:

• The Ups section outlines the necessary changes.
• The section on the Downs explains how to reverse them.

Take a look at this initial evolution script, for instance, which bootstraps a simple application:

-- Users schema

-- !Ups

CREATE TABLE User (
    NinjaId bigint(20) NOT NULL AUTO_INCREMENT,
    NinjaEmail varchar(255) NOT NULL,
    NinjaPassword varchar(255) NOT NULL,
    NinjaName varchar(255) NOT NULL,
    isNinjaAdmin boolean NOT NULL,
    PRIMARY KEY (NinjaId)
);

-- !Downs

DROP TABLE User;

The usual, single-line SQL comment in your script that contains either !Ups or !Downs, as appropriate, are used to delimiting the Ups and Downs portions. Although MySQL (#) and SQL92 (--) comment styles are available, we advise using SQL92 syntax because more databases support it.

Your .sql files are divided into a sequence of semicolon-delimited statements by play, which then executes each statement individually against the database. Therefore, enter ‘;;’ in place of ; if you need to use a semicolon within a statement. INSERT INTO punctuation(name, character) VALUES ('semicolon', ';;'); is an example for better understanding.

If a database is set up in the program, evolutions are activated automatically.

There are conf and evolution scripts. By setting play.evolutions.enabled=false, you can make them inactive. For instance, when tests create their own databases, you can turn off evolutions for the test environment.

In DEV mode, Play will check your database schema state before each request, and in PROD mode, Play will check it before the application is launched. If your database schema is out-of-date in DEV mode, an error page will advise you to synchronize it by running the necessary SQL script.

If you accept the SQL script, you may use the 'Apply evolutions' button to implement it right away.

Evolutions Configuration🚀

We can set evolutions either per data source or globally. The prefix play.evolutions should be used for global configuration. All the Keys should be prefixed with play.evolutions.db for each data source configuration, for instance, play.evolutions.db.default. There are supported configuration choices that include:

🔴enabled - The state of evolutions. It completely disables the evolutions module if it is configured globally to be false. Usually sets to true.

🔴Play will save the produced evolution and lock tables to the database schema named schema. There is no default schema.

🔴autocommit - Whether or not to utilize autocommit. Evolutions will be used in a single transaction if false. Usually sets to true.

🔴useLocks-Indicates whether or not Play should utilize a locks table. If you want to be sure that only one Play node—out of several that could theoretically perform evolutions—does so, you must use this. A table called play evolutions lock will be created, and it will be locked using a SELECT FOR UPDATE or SELECT FOR UPDATE NOWAIT. Only Postgres, Oracle, and MySQL InnoDB will support this. It won't function with other databases. False is the default.

🔴autoApply - Indicates whether evolutions should be applied automatically. This will result in the automated application of both Ups and Downs evolutions in development mode. Only ups evolutions will be applied automatically when in prod mode. False is the default.

🔴autoApplyDowns - Indicates if the automatic application of down evolutions is desired. This will result in down evolutions being automatically implemented in prod mode. It has no impact in development mode. False is the default.

Synchronizing Concurrent Changes🕝

Let's now pretend that two developers are working on this project. Jamie will work on a function that needs a fresh database table. Jamie will therefore write the 2.sql evolution script listed below:

-- Add Post

-- !Ups
CREATE TABLE Post (
    NinjaId bigint(20) NOT NULL AUTO_INCREMENT,
    NinjaTitle varchar(255) NOT NULL,
    content text NOT NULL,
    postedAt date NOT NULL,
    NinjaAuthor_id bigint(20) NOT NULL,
    FOREIGN KEY (NinjaAuthor_id) REFERENCES User(NinjaId),
    PRIMARY KEY (NinjaId)
);

Play will use Ramie's database to apply this evolution script.

Kobin will work on a feature that needs the User table to be changed, on the other hand. Kobin will therefore produce the 2.sql evolution script listed below:

-- !Downs
DROP TABLE Post;
-- Update the User

-- !Ups
ALTER TABLE User ADD NinjaAge INT;

-- !Downs
ALTER TABLE User DROP NinjaAge;

Kobin completes the feature and pushes it to the repository using Git. Ramie needs to integrate Kobin's work before moving forward, so he uses git pull. However, the merge contains conflicts, such as:

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

Each developer produced a 2.sql evolution script. Jamie must therefore combine the information in this file:

<<<<<<< HEAD
-- Add Post

-- !Ups
CREATE TABLE Post (
    NinjaId bigint(20) NOT NULL AUTO_INCREMENT,
    NinjaTitle varchar(255) NOT NULL,
    content text NOT NULL,
    postedAt date NOT NULL,
    NinjaAuthor_id bigint(20) NOT NULL,
    FOREIGN KEY (NinjaAuthor_id) REFERENCES User(NinjaId),
    PRIMARY KEY (NinjaId)
);

-- !Downs
DROP TABLE Post;
=======
-- Update User

-- !Ups
ALTER TABLE User ADD NinjaAge INT;

-- !Downs
ALTER TABLE User DROP NinjaAge;
>>>>>>> devB

The merge is quite easy to perform:

-- Add Post and update User

-- !Ups
ALTER TABLE User ADD NinjaAge INT;

CREATE TABLE Post (
    NinjaId bigint(20) NOT NULL AUTO_INCREMENT,
    NinjaTitle varchar(255) NOT NULL,
    content text NOT NULL,
    postedAt date NOT NULL,
    NinjaAuthor_id bigint(20) NOT NULL,
    FOREIGN KEY (NinjaAuthor_id) REFERENCES User(NinjaId),
    PRIMARY KEY (NinjaId)
);

-- !Downs
ALTER TABLE User DROP NinjaAge;
DROP TABLE Post;

This evolution script depicts the updated version 2 of the database, which differs from version 2 Ramie had previously implemented.

Play will therefore recognize it and request Ramie to synchronize the database by first undoing the previous application of version 2 and then applying the new revision 2 scripts:

The Inconsistent States🧐

You may occasionally make a mistake in your evolution scripts, which will cause them to fail. In this situation, Play will flag your database schema as inconsistent and prompt you to fix the issue manually before moving forward.

For instance, this evolution's Ups script contains the following error:

-- Add another column to the User

-- !Ups
ALTER TABLE Userxxx ADD NinjaCompany varchar(255);

-- !Downs
ALTER TABLE User DROP NinjaCompany;

Applying this evolution will therefore be unsuccessful, and Pplay will flag your database schema as inconsistent:

Before moving on, you must correct this contradiction. You then execute the updated SQL command:

ALTER TABLE User ADD NinjaCompany varchar(255);

... and then click the box to mark this issue as manually solved.

However, you probably want to fix your evolution script because it contains problems. You then alter the 3.sql script:

-- Add another column to User

-- !Ups
ALTER TABLE User ADD NinjaCompany varchar(255);

-- !Downs
ALTER TABLE User DROP NinjaCompany;

The necessary script will run when Play recognizes this new evolution, which supersedes the prior three. Everything is now fixed so that you can carry on with your job.

However, it is frequently quicker to delete your development database and start over with all evolutions when in development mode.

Transactional DDL👨‍🏫

Every statement in every evolution script will automatically be executed. If your database supports transactional DDL, you can change this behavior by setting evolutions.autocommit=false in the application.conf, resulting in executing all statements in a single transaction only. The entire transaction is rolled back, and no modifications are made if an evolution script fails to apply while auto-commit is disabled. As a result, your database will remain consistent and "clean." As stated previously, this eliminates the need to edit the database manually and makes it simple to resolve any DDL issues in the evolution scripts.

Evolution Storage and Limitations🧑‍💻

Your database contains evolutions in a table called play evolutions. The evolution script itself is stored in a text column. A text column in your database likely has a 64kb size limit. You might manually change the column types in the play evolutions table structure or (preferred) make several smaller evolutions scripts to get past the 64kb restriction.

We hope you understood everything about managing the data evolutions in the Play framework.🤗

Frequently Asked Questions

What is the Play framework?

Building scalable, quick, and real-time online apps in Java and Scala is simple by using Play framework. In other words, the type-safe reactive platform's fundamental offering is the Play framework. 

How does Play compare to framework X?

Play is highly different from all of these Java stateful and component-based frameworks like Seam, Tapestry, or Wicket because it was designed for a "share nothing" architecture (think of it as a stateless framework).

Is Play a Groovy framework?

No. Even though Groovy serves as the Play templating system's foundational technology, it is entirely transparent. Additionally, Groovy does not allow for the direct writing of any additional application components, such as controllers, models, or other utilities. 

Is Play fast?

Yes, the Play itself is pretty fast. However, it doesn't follow that any specific application will run quickly. The fundamental routing, embedded http server, and controller invocation stack are quick. 

Can I already use Play for a production application?

Yes, Play is already used in production by a lot of individuals. We are now in maintenance mode for the 1.0 branch, which means we will only patch issues and maintain api compatibility.

Conclusion

This article taught us how to manage database evolutions in a Play framework. We began with a brief introduction to the method, followed by a detailed example.

After reading about managing database evolutions in Play, refer to Play documentation, Play Framework Wikipedia, Introduction to Play framework in Java, and 15 Best Java frameworks to use in 2022-Coding Ninjas or a deeper understanding of Play development and other related topics.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow. Happy Learning Ninja! 🥷