Using Querulous with Scala on postgresql

I’ve started diving deeper into scala and as a language I’m starting to like it. Combine it with the Play framework and you’ve got a nice, statically typed, rapid development environment. Especially for creating REST services this setup works great:

• Play to host the application
• Scala as a powerful language to tie everything together
• lift-json as an easy way to serialze objects to json

But what was missing was an easy, flexible way to talk to the database. I didn’t want to have to create a complete object model and use ORM to map this to the database, I just wanted a simple basic framework that hid the ugly part of JDBC from me. I have to admit, I started out with Anorm, but that wasn’t a success. I couldn’t get Anorm to play nice with numeric fields in postgresql, and the mapping-convention wasn’t working for me neither, since I had some GIS data in the database for which I needed to call specific postGis functions.

After getting frustated with Anorm, I started looking at the various database frameworks that were out there for Scala. The scala site lists a couple of them on its wiki here. A lot of these libraries focus on getting type-safety into the way you access a database. Good goal, but most of the libraries I’ve looked into, map to specific tables and column names. This wasn’t going to work for me, because of the postGis functions I needed.

One of the libraries listed, however, presented itself as a simple wrapper around JDBC: Querulous. Looking at the sparse documentation this library looked pretty simple to use, and nicely matched my requirements.

Get querulous

I’ve developed this service using play, and so to get this library I needed to alter the dependencies.yml file to include the querulous libs. My total depenencies.yml file looks like this:

require:
    - play
    - play -> scala 0.9.1
    - play -> jersey 0.1
    - com.sun.jersey -> jersey-server 1.4
    - net.liftweb -> lift-json_2.8.1 2.4
    - com.twitter -> querulous 2.6.5
    

repositories:

    - devjava:
        type: iBiblio
        root: "http://download.java.net/maven/2/"
        contains:
            - com.sun -> *
            
    - twitter:
        type: iBiblio
        root: "http://maven.twttr.com/"
        contains:
            - com.twitter -> *

Nothing to fancy. I include a small set of depedencies for jax-rs, json and querulous. I’ve also added a couple of extra repositories since not all the artifacts can be found in the default repositories. After this the depedencies need to be added to play. You do this by just running the “play dependencies –sync” command. Depending on which IDE you use, you might need to manually add the querulous (and the other) libraries to your classpath to get your IDE to pick them up.

Using Querulous

Using Querulous really isn’t that hard. You need access to a QueryEvaluator, and using that QueryEvaluator you can execute SQL statements. I do this using the following scala object:

object Database {
 
  private val queryFactory = new SqlQueryFactory
  private val apachePoolingDatabaseFactory = new ApachePoolingDatabaseFactory(
    1,
    5,
    Duration.fromTimeUnit(1000, TimeUnit.MILLISECONDS),
    Duration.fromTimeUnit(1000, TimeUnit.MILLISECONDS),
    true,
    Duration.fromTimeUnit(1000, TimeUnit.MILLISECONDS))

  private val queryEvaluatorFactory = new StandardQueryEvaluatorFactory(apachePoolingDatabaseFactory, queryFactory)
  private val queryEvaluator = queryEvaluatorFactory("localhost", "<db_name>", "<user_name>", "<password>", Map[String, String](), "jdbc:postgresql")
  
  def getQueryEvaluator(): QueryEvaluator = {
    Logger.info("Getting evaluator: " + queryEvaluator);
    return queryEvaluator;
  }
}

This object creates a pooled database factory, which we use to create a QueryEvaluatorFactory. From this second factory we create the queryEvaluator for our specific database. Accessing the database is very easy, you just pass in a query to the QueryEvaluator and process the results. For my service I’ve created a set of commands that each execute and process a specific query. This command is executed from within the receive method of an Akka Actor:

class JsonActor extends Actor {
   
  /**
   * Based on the type recieved we determine what command to execute, most case classes
   * can be executed using the normal two steps. Execute a query, convert result to
   * a set of json data and return this result.
   */
  def receive = {
    
    // when we receive a Command we process it and return the result
    case some: Command => {
      
      // execute the query from the FULL command and process the results using the
      // processRows function
      var records:Seq[GeoRecord] = null;
      
      // if the match parameter is null we do the normal query, if not we pass in a set of varargs
      some.parameters match {
        case null =>  records = Database.getQueryEvaluator.select(some.query) {some.processRows}
        case _ => records = Database.getQueryEvaluator.select(some.query, some.parameters:_*) {some.processRows}
      }
       
      // return the result as a json string
      self.reply(some.toJson(records))
    }
    
    case _ => self.reply(null)
  }
}

In this code you can see that we use the Database.getQueryEvaluator function to get QueryEvaluator. We then call the “select” operation on this QueryEvaluator with the some.query parameter, which holds the SQL Query. The result is passed on to the some.processRows function. To determine whether we need to pass in arguments to the select function, we use a case statement to check if the command provides us with a set of parameters to add. After the SQL query is processed we let the command format this result to JSon and return from the receive function.

To get a complete overview of how this works together lets look at the commands we pass to our actor.

sealed trait Command {
  def name: String;
  def query: String
  var parameters: List[Any] = null;

  /**
   * Default function that maps georecords to json
   */
  def toJson(records: Seq[GeoRecord]): String = {
    // map the object to json
    val json =
      ("type" -> "featureCollection") ~
        ("features" -> records.map(r =>
          (("type" -> "Feature") ~
            ("gm_naam" -> r.name) ~
            ("geometry" -> parse(r.geojson)) ~
            ("properties" -> ({
              // create an empty object
              var obj = JNothing(0)
              // iterate over the properties
              r.properties.foreach(entry => (
                // add each property to the object, the reason
                // we do this is, that else it results in an 
                // arraylist, not a list of seperate properties
                obj = concat(obj, JField(entry._1, entry._2))))
              obj
            })))))

    return pretty(render(json))
  }

  /**
   * Default function that processes resultsets to a georecord
   */
  def processRows(row: ResultSet): GeoRecord = {
    // filter out the gid, the_geom and geo from the 
    // result, the rest is put into a map
    val map = HashMap[String, String]()
    val metadata = row.getMetaData();
    for (i <- 0 until metadata.getColumnCount()) {
      map += (metadata.getColumnName(i + 1) -> row.getString(i + 1));
    }
    // remove elements
    val geo = map.get("geo").get;
    map -= ("geo", "the_geom", "gid")

    new GeoRecord(map("gm_naam"), geo, map)
  }

}

/**
 * Command to get all the information from the database.
 */
case class FULL extends Command {
  val name = "full";
  val query = """ 
             SELECT *, ST_AsGeoJson(ST_Transform(ST_SetSRID(gem.the_geom,28992),4326)) as geo
              FROM geodata.gem;
          """
}

case class SINGLE extends Command {

  val name = "single";
  val query = """ 
           SELECT *, ST_AsGeoJson(ST_Transform(ST_SetSRID(gem.the_geom,28992),4326)) as geo
              FROM geodata.gem WHERE gm_code = ?;
          """
}

In the code above you see the FULL and SINGLE command. The FULL command retrieves all the records and the SINGLE command retrieves a single one. Since both these commands need to process the resultset in the same manner a trait is defined that contains the logic for processing the resultset ( def processRows(row: ResultSet): GeoRecord ) and for the transformation to json (def toJson(records: Seq[GeoRecord]): String).

To get the results of a specific command I only have to pass in the command itself and the rest is handled by Akka and Querulous. Creating new (select) queries is as easy as creating a basic new command.