Coffee. On the rocks!

REST API with Scala, Play, Slick, PostgreSQL, Redis and AWS S3

Mon 01 December 2014

My first Play application was a REST API. Here are some of the things i learned writing that API. My stack was Play(2.3.6) for the JSON API, Slick(2.1.0) for interactiong with my db, PostgreSQL as my primary database, Redis for auth, some stats and to cache some stuff and finally AWS S3 for storing files(images, audio/video). I'll create a sample schema here for an RDBMS

My package hierarchy looks like this

  -- controllers
    -- controllers go here
  -- helpers
    -- helper classes
  -- models
    -- Tables.scala                 // this contains all the table definitions
    -- UserModel.scala
  -- views
    -- I dont use this directory as its a JSON API
  -- Global.scala                       // this contains code that starts the akka actor system that Redis library uses
  -- application.conf
  -- routes

Now i need to define some tables which i do in app/models/Tables.scala. As i am using Slick and i need to tell it what attributes each of my model has and what type each attribute is so it can provide me the type safety which is one of the reasons you come to statically typed languages like Scala. My Tables.scala has 2 top level objects, Types which contains case classes for each of my postgres tables(slick needs it so it can do the type checking) and Tables which contains all my table definitions. Lets say i am building a messaging app because thats where the money is apparantely ;). So i just have 4 tables (Its a product 1O1 as the people in the suits say), User, UserAuth, Message and UserConnection. The User table has some fields that can be null like date_of_birth and gender. Also its status field has a default value of 1. So here is my object Types

import org.joda.time._          // this gives me DateTime

object Types {
  case class User(id:Option[Int]=None, name: String, gender: Option[Int]=None, dob: Option[DateTime]=None, profilePic: Option[String]=None, createdAt: DateTime, status: Int)

  case class UserAuth(userId: Int, `type`: Int, key: String, secret: String)   // type is a keyword in scala so to tell scala compiler to not interpret it the keyword type, i sorround it by backticks

  case class UserConnection(fromUserId: Int, toUserId: Int, connectedAt: DateTime)      // directed graph, used for follower/following relationship

  case class Message(id:Option[Int]=None, fromUserId: Int, toUserId: Int, sentAt: DateTime, text: Option[String]=None)

As you can see in each of these case classes, the id attribute is Option[Int] because when you insert a new row in the table, youre gonna create an object of this case class and you dont know what its id is gonna be, that is something postgres tells you once you insert the row.

In the case class User, gender is Option[Int] because the user might not provide his gender so in that case the value in the user table for gender would be null but in the code i provide a default value of None. Similar reasoning holds for fields dob and profilePic.

Now lets look at the table definitions

import scala.slick.ast.ColumnOption.DBType                  // need this to use data types like varchar and text
import scala.slick.driver.PostgresDriver.simple._           // need to interact with postgres
import com.github.tototoshi.slick.PostgresJodaSupport._     // need for use postgres's datetime types

object Tables {
  class User(tag: Tag) extends Table[Types.User](tag, "users") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    def gender = column[Option[Int]]("gender")
    def dob = column[Option[DateTime]]("dob")
    def profilePic = column[Option[String]]("profile_pic")
    def createdAt = column[DateTime]("created_at")
    def status = column[Int]("status", O.Default(1))        // status has defaul vlaue of 1
    def * = (id.?,name,gender,dob,profilePic,createdAt,status) <> (Types.User.tupled, Types.User.unapply)

  class UserAuth(tag: Tag) extends Table[Types.UserAuth](tag, "user_auth") {
    def userId = column[Int]("user_id")
    def `type` = column[Int]("type")
    def key = column[String]("key")
    def secret = column[String]("secret")
    def * = (userId,`type`,key,secret) <> (Types.UserAuth.tupled, Types.UserAuth.unapply)

    def user = foreignKey("USER_FK", userId, TableQuery[User])(
    def idx = index("USER_AUTH", (userId, `type`), unique = true)

  class UserConnection(tag: Tag) extends Table[Types.UserConnection](tag, "user_connections") {
    def fromUserId = column[Int]("from_user_id")
    def toUserId = column[Int]("to_user_id")
    def connectedAt = column[DateTime]("connected_at")
    def * = (fromUserId,toUserId,connectedAt) <> (Types.UserConnection.tupled, Types.UserConnection.unapply)

  class Message(tag: Tag) extends Table[Types.Message](tag, "messages") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def fromUserId = column[Int]("from_user_id")
    def toUserId = column[Int]("to_user_id")
    def sentAt = column[DateTime]("sent_at")
    def text = column[String]("text", DBType("text"))
    def * = (id.?,fromUserId,toUserId,sentAt,text) <> (Types.Message.tupled, Types.Message.unapply)

The line

class User(tag: Tag) extends Table[Types.User](tag, "users")

tells slick that the following table definition corresponds to case class Types.User and would be named users. For an auto-incrementing primary key called id i use this

def id = column[Int]("id", O.PrimaryKey, O.AutoInc)

The name of the user is gonna be string a want it to be named name in the table so i define it like

def name = column[String]("name")

Because the column gender can have null it is defined as

def gender = column[Option[Int]]("gender")

The line

def * = (id.?,name,gender,dob,profilePic,createdAt,status) <> (Types.User.tupled, Types.User.unapply)

is the default projection which tells slick that whenever i fetch a row from this table, return the columns that are present in the parenthesis, in this case i want all the columns. Here a nice explanation of projections in slick.

To create a foreign key like in the Table UserAuth where the member userId references the Table User's member id use this

def user = foreignKey("USER_FK", userId, TableQuery[User])(

Now i know that each user will have a unique authentication key for each type``(type can be fb, google, twitter, etc), so i create a unique compound index on ``userId and type. This is what i do

def idx = index("USER_AUTH", (userId, `type`), unique = true)

If you notice above the column types i have used above are the types scala recognize. If i wanna use any type the postgres supports like text or varchar i have to use a DBType in the column definition like i do for column text of Table Message like

def text = column[String]("text", DBType("text"))

So if i want my users to be creative and express them in 100 characters or less i can define the column like

def text = column[String]("text", DBType("varchar(100)"))

To connect with postgres i need a connection url and the name of the driver that scala uses to connect to postgres. Any time you interact with postgres you have to create a session and query/update the database using the session. One way is to do this

Database.forURL(url, driver = driver) withSession {
  implicit session =>
    // query some tables or
    // insert some rows or
    // update some rows

Here the url is the jdbc connection url and the driver is name of the driver which in my case is org.postgresql.Driver. Since all my models use the same connection url and the same driver i create a trait that is extended by all my models and uses configuration declared in application.conf. Here is a sample conf entry for postgresql

db: {
  default: {
    driver: "org.postgresql.Driver",
    url: "jdbc:postgresql://localhost/messaging_app",
    user: "postgres",
    password: "postgres"

This is the trait

import play.api.Play.current

trait ModelBase {
  val url: String = {
    current.configuration.getString("db.default.url").get +
      "?user=" +
      current.configuration.getString("db.default.user").get +
      "&password=" +
  val driver: String = current.configuration.getString("db.default.driver").get

This is how i create a User model and insert a new user row in the table

object UserModel extends ModelBase {

  val dateParser = DateTimeFormat.forPattern("MM/dd/YYYY").withZone(DateTimeZone.UTC)
  val userTable = TableQuery[Tables.User]

  def create(data: Map[String, Any]): Int = {       // return the id of the newly created user
     val userId = Database.forURL(url, driver = driver) withSession {
      implicit session =>
        val userObj = Types.User(                   // Here i create a user object
          name = data("name").toString,
          gender = data("gender") match {           // gender column can be null so its an option
            case Some("male") => 1
            case Some("female") => 2
            case _ => None

          dob = data("birthday") match {            // dob column can be null so its an option
            case Some(s: String) => Some(dateParser.parseDateTime(s))
            case _ => None

          createdAt =,       // the current datetime
          profilePic = Some(""),      // profile_pic column can be null so its an option
          status = 1

        (userTable returning += userObj    // i need to return the id of the inserted user


Most of the above code snipplet is simple. First i create a object of case class Types.User named userObj and then use this statement

(userTable returning += userObj

to insert the user row in the table and return the value of id column of the table. If i didn't care about returning the id of the inserted row i could write userTable += userObj. To create a bunch of users create a sequence of users and call them userObjs and then do

userTable ++= userObj

Doing joins is simple too. So if i want to get all messages sent by user1 to females this is what i do

val join = for {
  m <- messageTable if m.fromUserId ===
  u <- userTable if u.gender === 2 && === m.toUserId
} yield (m, u)

val rows = join.list

Sometimes slick's expressiveness doesn't seem to help or i cant figure out how to do it the slick way. Then i go back to using raw SQL. This is how i do it. Lets say i want to get ids of users who are female and where status is 1(active users)

import scala.slick.driver.JdbcDriver.backend.{Database => RawDatabase}
import scala.slick.jdbc.{GetResult, StaticQuery => Q}

val userIds = RawDatabase.forURL(url, driver = driver) withSession {
  implicit session =>
    val q = Q.query[(Int,Int), (Int)](
                select id from users where gender = ? and status = ?
    q((2, 1)).list      // gives me a list of user ids

The oversimplified query kills the mood here but bear with me here because this is not what the focus is. The placeholder statement is familiar to people familiar with prepared SQL statements. The important part here is the 2 imports used here. I need the database backend from JDBC here on which i can execute the StaticQuery which i remamed to Q here.

I am new to Slick and they way i do things here might not be the best way to do it. Suggestions, corrections and any other feedback is appreciated. In the next post i talk about using Redis and AWS S3 with play. Also there will be something about asynchronous request handling with play using futures.

This entry was tagged as scala play slick postgresql

blog comments powered by Disqus