Reactive Postgres with Play Framework & ScalikeJDBC

Lately I’ve built a few apps that have relational data. Instead of trying to shoehorn that data into a NoSQL model I decided to use the awesome Heroku Postgres service but I didn’t want to lose out on the Reactiveness that most of the NoSQL data stores support. I discovered ScalikeJDBC-Async which uses postgresql-async, a Reactive (non-blocking), JDBC-ish, Postgres driver. With those libraries I was able to keep my data relational and my app Reactive all the way down. Lets walk through how to do it in a Play Framework app. (TL;DR: Jump to the the full source.)

If you want to start from scratch, create a new Play app from the Play Scala Seed.

The minimum dependencies needed in the build.sbt file are:

libraryDependencies ++= Seq(
  "org.postgresql"       %  "postgresql"                    % "9.3-1102-jdbc41",
  "com.github.tototoshi" %% "play-flyway"                   % "1.2.0",
 
  "com.github.mauricio"  %% "postgresql-async"              % "0.2.16",
  "org.scalikejdbc"      %% "scalikejdbc-async"             % "0.5.5",
  "org.scalikejdbc"      %% "scalikejdbc-async-play-plugin" % "0.5.5"
)

The play-flyway library handles schema evolutions using Flyway. It is a great alternative to Play’s JDBC module because it just does evolutions and does one-way evolutions (i.e. no downs). But because play-flyway doesn’t use the postgresql-async driver, it needs the standard postgresql JDBC driver as well.

The scalikejdbc-async-play-plugin library manages the lifecycle of the connection pool used by scalikejdbc-async in a Play app.

To use play-flyway and scalikejdbc-async-play-plugin a conf/play.plugins file must tell Play about the plugins:

776:com.github.tototoshi.play2.flyway.Plugin
777:scalikejdbc.async.PlayPlugin

A first evolution script in conf/db/migration/default/V1__create_tables.sql will create a table named bar that will hold a list of bars for our little sample app:

DROP TABLE IF EXISTS bar;
CREATE TABLE bar (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL
);

You will of course need a Postgres database to proceed. You can either install one locally or create a free one on the Heroku Postres cloud service. Then update the conf/application.conf file to point to the database:

db.default.driver="org.postgresql.Driver"
db.default.url="postgres://admin:admin@localhost:5432/test"
db.default.url=${?DATABASE_URL}

The last line above overrides the database connection url if there is a DATABASE_URL environment variable set (which is the case if your app is running on Heroku).

To run this app locally you can start the Play app by starting the Activator UI or from the command line with:

activator ~run

When you first open your app in the browser, the play-flyway plugin should detect that evolutions needs to be applied and ask you to apply them. Once applied you will be ready to create a simple database object and a few reactive request handlers.

Here is a Bar database object named app/models/Bar.scala that uses scalikejdbc-async for reactive creation and querying of Bars:

package models
 
import play.api.libs.json.Json
import scalikejdbc.WrappedResultSet
import scalikejdbc._
import scalikejdbc.async._
import scalikejdbc.async.FutureImplicits._
 
import scala.concurrent.Future
import scala.concurrent.ExecutionContext.Implicits.global
 
case class Bar(id: Long, name: String)
 
object Bar extends SQLSyntaxSupport[Bar] {
 
  implicit val jsonFormat = Json.format[Bar]
 
  override val columnNames = Seq("id", "name")
 
  lazy val b = Bar.syntax
 
  def db(b: SyntaxProvider[Bar])(rs: WrappedResultSet): Bar = db(b.resultName)(rs)
 
  def db(b: ResultName[Bar])(rs: WrappedResultSet): Bar = Bar(
    rs.long(b.id),
    rs.string(b.name)
  )
 
  def create(name: String)(implicit session: AsyncDBSession = AsyncDB.sharedSession): Future[Bar] = {
    val sql = withSQL(insert.into(Bar).namedValues(column.name -> name).returningId)
    sql.updateAndReturnGeneratedKey().map(id => Bar(id, name))
  }
 
  def findAll(implicit session: AsyncDBSession = AsyncDB.sharedSession): Future[List[Bar]] = {
    withSQL(select.from[Bar](Bar as b)).map(Bar.db(b))
  }
 
}

The db functions perform the mapping from SQL results to the Bar case class.

The create function takes a Bar name and returns a Future[Bar] by doing a non-blocking insert using the ScalikeJDBC Query DSL. When the insert has completed the primary key is returned and a new Bar instance is created and returned.

The findAll method uses the ScalikeJDBC Query DSL to select all of the Bars from the database, returning a Future[List[Bar]]].

Now that we have a reactive database object, lets expose these through reactive request handlers. First setup the routes in the conf/routes file:

GET        /bars                   controllers.Application.getBars
POST       /bars                   controllers.Application.createBar

Define the controller functions in the app/controllers/Application.scala file:

def getBars = Action.async {
  Bar.findAll.map { bars =>
    Ok(Json.toJson(bars))
  }
}
 
def createBar = Action.async(parse.urlFormEncoded) { request =>
  Bar.create(request.body("name").head).map { bar =>
    Redirect(routes.Application.index())
  }
}

Both functions use Action.async which holds a function that takes a request and returns a response (Result) in the future. By returning a Future[Result] Play is able to make requests to the controller function non-blocking. The getBars controller function calls the Bar.findAll and then transforms the Future[List[Bar]] into a Future[Result], the 200 response containing the JSON serialized list of bars. The createBar controller function parses the request, creates the Bar, and then transforms the Future[Bar] into a Future[Result] once the Bar has been created.

From the non-blocking perspective, here is what a request to the getBars controller function looks like:

  1. Web request made to /bars
  2. Thread allocated to web request
  3. Database request made for the SQL select
  4. Thread allocated to the database request
  5. Web request thread is deallocated (but the connection remains open)
  6. Database request thread is deallocated (but the connection remains open)
  7. Database response handler reallocates a thread
  8. SQL result is transformed to List[Bar]
  9. Database response thread is deallocated
  10. Web response handler reallocates a thread
  11. Web response is created from the list of bars
  12. Web response thread is deallocated

So everything is now reactive all the way down because there is a moment where the web request is waiting on the database to respond but no threads are allocated to the request.

Try it yourself with curl:

$ curl -X POST -d "name=foo" http://localhost:9000/bars
$ curl http://localhost:9000/bars
[{"id":1,"name":"foo"}]

Grab the the full source and let me know if you have any questions. Thanks!

NoSQL Inside SQL with Java, Spring, Hibernate, and PostgreSQL

There are many benefits to schema-less NoSQL datastores, but there are always trade-offs. The primary gift the NoSQL movement has given us is the variety of options we now have for data persistence. With NoSQL we no longer must try to shoehorn everything into a relational model. Now the challenge is in deciding which persistence model fits best with each domain in a system and then combining those models in a cohesive way. The general term to describe this is Polyglot Persistence and there are many ways to accomplish it. Lets walk through how you can combine a regular SQL model with a key-value NoSQL model using Java, Spring, Hibernate, and PostgreSQL.

This article covers the pieces of a simple web application which uses regular SQL and PostgreSQL’s hstore for key value pairs. This method is a mix of NoSQL inside SQL. One benefit of this approach is that the same datastore can be used for both the SQL and the NoSQL data.

In this example the server technologies will be Java, Spring, and Hibernate. (The same thing can also be done with Rails, Django, and many other technologies.) To add Hibernate support for hstore I found a fantastic blog about “Storing sets of key/value pairs in a single db column with Hibernate using PostgreSQL hstore type“. I won’t go through that code here but you can find everything in the GitHub repo for my demo project.

This demo app uses Maven to define the dependencies. Embedded Jetty is started via a plain ‘ole Java application that sets up Spring MVC. Spring is configured via Java Config for the main stuff, the web stuff, and the database stuff.

The client technologies will be jQuery and Bootstrap and there is a strict seperation between the client and server via RESTful JSON services. The whole client-side is in a plain ‘ole HTML file. Via jQuery / Ajax the client communicates to JSON services exposed via a Spring MVC Controller.

Ok. Now onto the NoSQL inside SQL stuff. This application stores “Contacts” that have a name but also can have many “Contact Methods” (e.g. phone numbers and email addresses). The “Contact Methods” are a good use of a schema-less, key-value pair column because it avoids the cumbersome alternatives: putting that information into a separate table or trying to create a model object that has all of the possible “Contact Methods”. So lets take a look at the simple Contact Entity:

package com.jamesward.model;
 
import net.backtothefront.HstoreUserType;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
 
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import java.util.HashMap;
import java.util.Map;
 
@Entity
@TypeDef(name = "hstore", typeClass = HstoreUserType.class)
public class Contact {
 
    @Id
    @GeneratedValue
    public Integer id;
 
    @Column(nullable = false)
    public String name;
 
    @Type(type = "hstore")
    @Column(columnDefinition = "hstore")
    public Map<String, String> contactMethods = new HashMap<String, String>();
 
}

If you are familiar with Hibernate / JPA then most of this should look pretty familiar to you. The new / interesting stuff is the contactMethods property. It is a Map<String, String> and it uses PostgreSQL’s hstore datatype. In order for that to work, the type has to be defined and the columnDefinition set. Thanks again to Jakub Głuszecki for putting together the HstoreHelper and HstoreUserType that make this possible.

Now the rest is simple because it’s just plain Hibernate / JPA. Here is the ContactService that does the basic query and updates:

package com.jamesward.service;
 
import com.jamesward.model.Contact;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaQuery;
 
import java.util.List;
 
@Service
@Transactional
public class ContactServiceImpl implements ContactService {
 
    @PersistenceContext
    EntityManager em;
 
    @Override
    public void addContact(Contact contact) {
        em.persist(contact);
    }
 
    @Override
    public List<Contact> getAllContacts() {
        CriteriaQuery<Contact> c = em.getCriteriaBuilder().createQuery(Contact.class);
        c.from(Contact.class);
        return em.createQuery(c).getResultList();
    }
 
    public Contact getContact(Integer id) {
        return em.find(Contact.class, id);
    }
 
    @Override
    public void addContactMethod(Integer contactId, String name, String value) {
        Contact contact = getContact(contactId);
        contact.contactMethods.put(name, value);
    }
 
}

Now that you understand how it all works, check out a live demo on Heroku.

If you want to run this app locally or on Heroku, then first you need to grab the source code and continue working inside the newly created spring_hibernate_hstore_demo directory:

$ git clone https://github.com/jamesward/spring_hibernate_hstore_demo.git
$ cd spring_hibernate_hstore_demo

To run locally:

  1. Setup your PostgreSQL database to support hstore by opening a psql connection to it:
    $ psql -U username -W -h localhost database
  2. Then enable hstore:
    => create extension hstore;
    => \q
  3. Build the app (depends on having Maven installed):
    $ mvn package
  4. Set the DATABASE_URL environment variable to point to your PostgreSQL server:
    $ export DATABASE_URL=postgres://username:password@localhost/databasename
  5. Start the app:
    $ java -cp target/classes:target/dependency/* com.jamesward.Webapp
  6. Try it out

Cool! Now you can run it on the cloud with Heroku. Here is what you need to do:

  1. Install the Heroku Toolbelt
  2. Login to Heroku:
    $ heroku login
  3. Create a new app:
    $ heroku create
  4. Add Heroku Postgres:
    $ heroku addons:add heroku-postgresql:dev
  5. Tell Heroku to set the DATABASE_URL environment variable based on the database that was just added (replace YOUR_HEROKU_POSTGRESQL_COLOR_URL with your own):
    $ heroku pg:promote YOUR_HEROKU_POSTGRESQL_COLOR_URL
  6. Open a psql connection to the database:
    $ heroku pg:psql
  7. Enable hstore support in your database:
    => create extension hstore;
    => \q
  8. Deploy the app:
    $ git push heroku master
  9. View the app on the cloud:
    $ heroku open

Fantastic! Let me know if you have any questions.