How to implement a generic REST api for tables in Play2 with squeryl and spray-json

Question

I'm trying to implement a controller in Play2 which exposes a simple REST-style api for my db-tables. I'm using squeryl for database access and spray-json for converting objects to/from json

My idea is to have a single generic controller to do all the work, so I've set up the following routes in conf/routes:

GET  /:tableName              controllers.Crud.getAll(tableName)
GET  /:tableName/:primaryKey  controllers.Crud.getSingle(tableName, primaryKey)

.. and the following controller:

object Crud extends Controller {
    def getAll(tableName: String) = Action {..}
    def getSingle(tableName: String, primaryKey: Long) = Action {..}
}

(Yes, missing create/update/delete, but let's get read to work first)

I've mapped tables to case classes by extended squeryl's Schema:

object MyDB extends Schema {
    val accountsTable = table[Account]("accounts")
    val customersTable = table[Customer]("customers")
}

And I've told spray-json about my case classes so it knows how to convert them.

object MyJsonProtocol extends DefaultJsonProtocol {
  implicit val accountFormat = jsonFormat8(Account)
  implicit val customerFormat = jsonFormat4(Customer)    
}

So far so good, it actually works pretty well as long as I'm using the table-instances directly. The problem surfaces when I'm trying to generify the code so that I end up with excatly one controller for accessing all tables: I'm stuck with some piece of code that doesn't compile and I am not sure what's the next step.

It seems to be a type issue with spray-json which occurs when I'm trying to convert the list of objects to json in my getAll function.

Here is my generic attempt:

def getAll(tableName: String) = Action {

  val json = inTransaction {

    // lookup table based on url
    val table = MyDB.tables.find( t => t.name == tableName).get  

    // execute select all and convert to json
    from(table)(t =>
      select(t)
    ).toList.toJson // causes compile error
  }

  // convert json to string and set correct content type
  Ok(json.compactPrint).as(JSON)
}

Compile error:

[error] /Users/code/api/app/controllers/Crud.scala:29: 
Cannot find JsonWriter or JsonFormat type class for List[_$2]
[error]       ).toList.toJson
[error]                ^
[error] one error found

I'm guessing the problem could be that the json-library needs to know at compile-time which model type I'm throwing at it, but I'm not sure (notice the List[_$2] in that compile error). I have tried the following changes to the code which compile and return results:

Remove the generic table-lookup (MyDB.tables.find(.....).get) and instead use the specific table instance e.g. MyDB.accountsTable. Proves that JSON serialization for work . However this is not generic, will require a unique controller and route config per table in db.
Convert the list of objects from db query to a string before calling toJson. I.e: toList.toJson --> toList.toString.toJson. Proves that generic lookup of tables work But not a proper json response since it is a string-serialized list of objects..

Thoughts anyone?

score 1 · Accepted Answer · answered Jan 07 '14 at 17:19

Your guess is correct. MyDb.tables is a Seq[Table[_]], in other words it could hold any type of table. There is no way for the compiler to figure out the type of the table you locate using the find method, and it seems like the type is needed for the JSON conversion. There are ways to get around that, but you'd need to some type of access to the model class.

How to implement a generic REST api for tables in Play2 with squeryl and spray-json

1 Answers1