Product Catalog Application using Play and Scala Part 4 – Defining Database Schema

Fri, Jul 25, 2014

Play Programming #Play #programming #scala

In this article we will be looking at how to define and create models. I will spend more time on this since there are few parts I want to cover here including evolutions, Anorm, object mapping, object relationship and transaction.

 

Evolutions

For application like this, we will need a data store like MySQL or H2 to store our data. Initially we will have a few database tables created. But as we progress, we might make some changes to the table structure such as adding a new table or drop an existing column, and we will have to be very cautious when deploying to test or production server, and probably need to synchronize with other team mates on the changes. Play provides a mechanism, called “evolutions”, which detects whether a database upgrade is needed and will ask for our permission before doing so. This could come very handy when we have team mates that working remotely or not sharing the same database instance.

Evolutions script consists of 2 parts, part that upgrades the schema and part to downgrade the schema by reverting the changes. All the scripts should be placed in conf/evolutions/default and named 1.sql for the first revision and so on. In DEV mode, the application will prompt you for upgrade whenever you visit the page. And in PRODUCTION mode, you will be prompted before starting the application.

Before we start writing our first evolution script, we will need to create a new database for this application.

[thor@localhost product-catalog]$ mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 9
Server version: 5.5.38-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> create database play_product_catalog;
Query OK, 1 row affected (0.00 sec)

Please remember to update the application.conf file with the database name we just created.(refer here). Here is the evolutions script we will be using in this application.

# Initial DB schema
 
# --- !Ups
 
CREATE TABLE Products (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    category_id bigint(20) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE Categories (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    PRIMARY KEY (id)
);
 
# --- !Downs
 
DROP TABLE Products;
DROP TABLE Categories;

And we save this script as 1.sql in conf/evolutions/default. Then we starts our application with activator and we will see something like below.

Play - Prompt to update db

Play – Prompt to update db

Click on the “Apply the script now!” button to complete the schema update. Once we are done we will be seeing new tables created in our database.

MariaDB [play_product_catalog]> show tables;
+--------------------------------+
| Tables_in_play_product_catalog |
+--------------------------------+
| Categories                     |
| Products                       |
| play_evolutions                |
+--------------------------------+
3 rows in set (0.00 sec)

And we are done. In next article we will be looking on how to define models in this application.