How to output JSON from Slick query result?

Question

I'm a newbie when it comes to Scala and right now trying to learn Slick. I have a database set up much like in the Slick tutorial and querying it with

val users:TableQuery[user] = TableQuery[user]

How do I output users -value in to JSON from Play framework? I have tried defining case class for user and making reader+writer for userFormat = Json.format[user] but I have no clue where I'm going wrong or anything. I've done similar kind of stuff with Spray.IO and it seemed simple and straightforward there, but I want to learn how to do it with Play and Slick and I am completely lost :(

Here is my Tables.scala:

    package assets

    import org.joda.time.DateTime
    import play.api.libs.json._
    import scala.slick.driver.MySQLDriver.simple._
    import com.github.tototoshi.slick.MySQLJodaSupport._
    import scala.slick.lifted.{ProvenShape}

    case class user
      (
      id:Int,
      textId:String,
      sessionId:String,
      stamp:DateTime
    )

    class users(tag: Tag) extends Table[(Int, String, String, DateTime)](tag, "USER") {

      implicit object userFormat extends Format[user] {

        def writes(a: user): JsValue = {
          Json.obj(
            "id" -> JsNumber(a.id),
            "textId" -> JsString(a.textId),
            "sessionId" -> JsString(a.sessionId),
            "stamp" -> JsString(a.stamp.toString())
          )
        }

        def reads(json: JsValue): user = user(
          (json \ "id").as[Int],
          (json \ "textId").as[String],
          (json \ "sessionId").as[String],
          (json \ "stamp").as[DateTime]
        )
      }

      def id: Column[Int] = column[Int]("id", O.PrimaryKey)
      def textId: Column[String] = column[String]("textId")
      def sessionId: Column[String] = column[String]("sessionId")
      def stamp: Column[DateTime] = column[DateTime]("stamp")

      def * : ProvenShape[(Int, String, String, DateTime)] = (id,textId,sessionId,stamp)
    }

Aldo Stracquadanio Aldo Stracquadanio · Accepted Answer · 2015-11-16T15:04:22

First of all, please add some context to your question as it's very hard to see what is not working (e.g. do you get compilation errors? Do you get exceptions?).

With respect to the question content, you should think of getting data from slick and translating it into JSON as two totally independent steps; in fact, there is nothing in slick that will help you in making JSON out of your case classes.

Given that, you will need to run your slick queries and get case class instances for your types out of them, e.g. you should get a Seq[user]. To convert that to JSON you should invoke Json.toJson(myUserSeq). There is one thing that will probably get in your way for what I can see in your code: to have the Format correctly in scope you should put it in the companion object of your case class, e.g.:

case class User(...)

object User {
  implicit object format extends Format[User] { ... }
}

As a sideline, please be aware of the language's coding standards if you want people to help you and your code to be readable (e.g. having lower-case class names in your code makes it very hard to follow).

How to output JSON from Slick query result?

1 Answers