Play Framework: An Anorm ‘singleOpt’ query example

By Alvin Alexander. Last updated: April 14, 2018

It’s embarrassing how long it took to get this to work, but if you ever need to select a single row from a database using Play Framework (version 2.6), Anorm, and singleOpt, I hope this example is helpful, and will save you time and frustration:

package models.admin

import anorm.SqlParser.get
import javax.inject.Inject
import anorm._
import models.DatabaseExecutionContext
import play.api.db.DBApi

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

    private val db = dbapi.database("default")
    private val logger = play.api.Logger(this.getClass)

    val rowParser: RowParser[User] = {
        get[String]("name") ~
        get[String]("pass") map {
            case username~password => User(username, password)
        }
    }

    def lookupUser(u: User): Boolean = db.withTransaction { implicit c =>
        val q = SQL"""
            select name, pass from users where name = ${u.username} and pass = ${u.password}
        """
        val maybeUser: Option[User] = q.as(rowParser.singleOpt)
        maybeUser match {
            case None => false
            case Some(u) => true
        }
    }

}

There are other ways to write that query and the lookupUser method, but I just wanted to share this Anorm/singleOpt example before I change the code.

As mentioned, I hope that seeing a simple working Anorm/singleOpt example is helpful.

scala

database