Database access options

Most of your applications will need to access to a database. This page describe options you have to manage an SQL database from a Play Scala application.

Using Anorm

The Scala module includes a brand new data access layer called Anorm that uses plain SQL to make your database request and provides several API to parse and transform the resulting dataset.

We believe that it is the best way to access your relational databases from Scala and this component will be encouraged and fully integrated with the rest of the Play Scala stack.

Please check the complete manual for more information.

Integrating other existing Database access librairies

Perhaps you already use another existing Database access library for Scala and you want to keep using it from your Play application. Basically a Play application manage the JDBC connection for you, and provide your application with a simple java.sql.Connection object that you can use to integrate any other existing framework you want.

For example, here are the few steps need to integrate ScalaQuery with your Play application.

1. Add ScalaQuery to your dependencies.yml file

ScalaQuery is available from the Scala Tools repository. So open your application conf/depenencies.yml file, and add the following content:

# Application dependencies
 
require:
    - play
    - play -> scala 0.9
    - org.scalaquery -> scalaquery_2.8.1 0.9.1:
        transitive:     false
    
repositories:
    
    - Scala Tools:
        type:       iBiblio
        root:       http://scala-tools.org/repo-releases
        contains:   
            - org.scalaquery -> *

Now run:

$ play dependencies

To resolve and install the required jars.

2. Configure a Datasource for your application

In the conf/application.conf file of your Play application, uncomment this line to enable an in memory database:

# To quickly set up a development database, use either:
#   - mem : for a transient in memory database (H2 in memory)
#   - fs  : for a simple file written database (H2 file stored)
db=mem

3. Create an SQL evolution Script to initialize your database

Create the db/evolutions directory structure in your application if it doesn’t already exists, and add a first evolution script 1.sql:

# Users schema
 
# --- !Ups
 
CREATE TABLE MEMBERS (
    ID bigint(20) NOT NULL,
    NAME varchar(255) NOT NULL,
    EMAIL varchar(255),
    PRIMARY KEY (ID)
);
 
INSERT INTO members VALUES (1, 'Guillaume', '[email protected]');
INSERT INTO members VALUES (2, 'Sadek', NULL);
 
# --- !Downs
 
DROP TABLE MEMBERS;

This first script will be automatically applied since your database is empty and you run an in-memory database. Check this log:

…
13:31:50,674 INFO  ~ Connected to jdbc:h2:mem:play;MODE=MYSQL
13:31:50,752 INFO  ~ Application 'myScalaQueryApp' is now started !
13:31:51,064 INFO  ~ Automatically applying evolutions in in-memory database
…

Use ScalaQuery in your code

Now let’s write a simple action method that queries all the Members registred in our database:

import play.mvc._
 
import org.scalaquery.session._
import org.scalaquery.session.Database.threadLocalSession
import org.scalaquery.ql.basic.BasicDriver.Implicit._
import org.scalaquery.ql.basic.{BasicTable => Table}
import org.scalaquery.ql.TypeMapper._
import org.scalaquery.ql._
 
package models {
 
    object Members extends Table[(Int, String, Option[String])]("MEMBERS") {
        def id = column[Int]("ID")
        def name = column[String]("NAME")
        def email = column[Option[String]]("EMAIL")
        def * = id ~ name ~ email
        
        def all = (for(m <- Members) yield m.name ~ m.email).list
    }
 
}
 
package controllers {
  
    object Application extends Controller { 
 
        val db = Database.forDataSource(play.db.DB.datasource)
        
        def index = {     
            
            db withSession {
                
                import models._
            
                Template('members -> Members.all)
            
            }
    
        }
 
    }
 
}

You see that we simply link ScalaQuery with the Play managed datasource, with this line:

val db = Database.forDataSource(play.db.DB.datasource)

That’s all! You can probably adapt this short tutorial to any other Scala data access library.