How to return a single record from a Play Framework Anorm SELECT query

By Alvin Alexander. Last updated: April 1, 2018

The way you get a single row with Anorm has changed over time, so I’ll first show the approach with Play Framework 2.6 (which is currently the current version), and then I’ll show the older approach.

Getting a single database table row with Anorm and Play Framework 2.6

With Anorm in Play Framework 2.6 you use an approach like this:

val query: SimpleSql[Row] = SQL(
    """
      |select n.nid, n.vid, n.type, n.status, n.created, n.changed,
      |       nr.title, nr.body, nr.teaser, nr.timestamp
      |from node n, node_revisions nr
      |where n.nid = {nodeId}
      |and nr.nid = n.nid
    """.stripMargin)
    .on("nodeId" -> nodeId)
query.as(nodeRowParser.single)

The key there is to call the single method on your parser.

That code may not make any sense out of context, so here’s the entire Anorm class:

package models

import javax.inject.Inject
import anorm._
import anorm.SqlParser.{get, str}
import play.api.db.DBApi
import scala.concurrent.Future
import scala.util.{Failure, Success}

case class DrupalNode(
    nid: Long,
    vid: Long,
    nodeType: String,
    status: Int,
    created: Long,
    changed: Long,
    timestamp: Long,
    title: String,
    body: String,
    teaser: String
)

@javax.inject.Singleton
class DrupalNodeDao @Inject()(dbapi: DBApi)(implicit ec: DatabaseExecutionContext) {

    private val db = dbapi.database("default")

    // uses the Parser API
    val nodeRowParser: anorm.RowParser[DrupalNode] = {
        get[Long]("nid") ~
        get[Long]("vid") ~
        get[String]("type") ~
        get[Int]("status") ~
        get[Long]("created") ~
        get[Long]("changed") ~
        get[String]("title") ~
        get[String]("body") ~
        get[String]("teaser") ~
        get[Long]("timestamp") map {
            case nid~vid~nodeType~status~created~changed~title~body~teaser~timestamp =>
                DrupalNode(nid,vid,nodeType,status,created,changed,timestamp,title,body,teaser)
        }
    }

    def getNodeDetails(nodeId: Long): DrupalNode = db.withConnection { implicit c =>
        val query: SimpleSql[Row] = SQL(
            """
              |select n.nid, n.vid, n.type, n.status, n.created, n.changed,
              |       nr.title, nr.body, nr.teaser, nr.timestamp
              |from node n, node_revisions nr
              |where n.nid = {nodeId}
              |and nr.nid = n.nid
            """.stripMargin)
            .on("nodeId" -> nodeId)
        query.as(nodeRowParser.single)
    }

}

The key there is that I create an Anorm RowParser and then invoke the single method on it so that it only returns one row.

Note that as another possible approach you can probably call the head or headOption methods on your query.

The remainder of this article shows the approach I used to use with Play Framework 2.1.

Getting a single row with Anorm and Play Framework 2.1

I don’t know if there is a better way to do this yet, but if you want to select one record from a database table using the Play Framework and Anorm, I do know that this code works:

/**
 * Look up the `uid` for the given username and password.
 */
def getUserId(username: String, password: String): Option[Long] = DB.withConnection { implicit c =>
    val rowOption = SQL("select id from users where username = {username} and password = {password} limit 1")
        .on('username -> username, 'password -> password)
        .apply
        .headOption
    rowOption match {
      case Some(row) => Some(row[Long]("id"))  // the uid
      case None => None
    }
}

The key to the solution is using the apply and headOption method calls after the usual SQL constructor. I also use a LIMIT 1 in the SQL SELECT query, though that isn’t really needed if there’s a proper index on the users database table.

Another approach (with Play Framework 2.1)

Instead of using match, you can use code like this, if you prefer:

// returns a Long instead of Option[Long]
def getUserId(username: String, password: String): Long = DB.withConnection { implicit c =>
    val rowOption = SQL("select id from users where username = {username} and password = {password} limit 1")
        .on('username -> username, 'password -> password)
        .apply
        .headOption
    rowOption.map(row => row[Long]("id")).getOrElse(0L)
}

Personally I prefer the first approach, where I return an Option from this method and let the caller handle the result as they wish, but I just wanted to show another possible way to deal with the Option result in this method, i.e., using map and getOrElse.

scala

database