Play Framework: Anorm SQL query syntax and examples

In general the online Play Framework documentation is excellent, but one area where I needed more help was in seeing more examples of the Anorm syntax. To that end, here are some Anorm query examples, taken from a Play Framework application I worked on recently. But first, a little background about Anorm.

Anorm

Anorm lets you write SQL queries as multiline strings, just as though you were querying a database at the command line or with a GUI query tool. In general there are three things you need to do to run a SQL query with Anorm:

1. Connect to the database. Your can do this with or without a transaction.
2. Create your SQL query using Anorm’s SQL string interpolator.
3. Call a method like executeInsert or executeUpdate to run the query.

For SQL SELECT queries you’ll also need to create a RowParser when your query returns a custom data type, such as a User or Book, as shown in the examples that follow.

Support for single queries and transactions

At the beginning of your database-access class you’ll have some boilerplate code like this:

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

import scala.collection.mutable.ArrayBuffer

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

    private val db = dbapi.database("default")
    
    // the rest of your code ...

After that, if you need to write a method to run a single query, define your method with db.withConnection:

def getTags(nid: Long): Seq[String] = db.withConnection { implicit c =>
                                      -----------------

If you need to create a method that executes several queries as part of a transaction, use db.withTransaction:

def lookupUser(u: User): Boolean = db.withTransaction { implicit c =>
                                   ------------------

Note that when you use withTransaction, make sure your database tables support using transactions. As an example, this requires using Engine = InnoDB when creating MySQL tables.

More background ...

At some point I may write more background information about Anorm, but hopefully that’s enough to get started today. For now, it’s on to the example queries ...

A note about my coding style

Please note that I write my examples below on at least two lines, like this:

val q: SimpleSql[Row] = SQL"select vid from vocabulary where upper(name) = 'TAGS'"
val vocabularyId: Int = q.as(SqlParser.scalar[Int].single)

I do this because I found it helpful when I wanted to write information about my query q to a log, but you don’t have to write your code on multiple lines. You can write them in one expression, like this:

val vocabularyId: Int = SQL"select vid from vocabulary where upper(name) = 'TAGS'"
                        .as(SqlParser.scalar[Int].single)

Use whatever style you prefer.

SQL SELECT queries

Before getting into the examples, there are a few things to know about SELECT queries with Anorm:

• The Anorm single method returns a single value, but it throws an exception if your query returns no rows
• The singleOpt method returns a single value as an Option
• You can select simple values like Int and String, as well as your own custom data types
• If your query will return a custom data type like User or Book, you need to use a RowParser

Note that for the following SELECT examples that use a custom data type you can assume that you have this case class:

case class User (
    username: String,
    password: String
)

and this RowParser:

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

Given that background, here are some Anorm SQL SELECT examples:

Note: “*” in the Data Type column indicates that the query can throw an exception.

Here’s the Anorm syntax for those queries again so you can see the differences:

q.as(SqlParser.scalar[Int].single)    //single Int, can throw exception
q.as(SqlParser.scalar[Int].singleOpt) //single Option[Int]
q.as(rowParser.single)                //single User, can throw exception
q.as(rowParser.singleOpt)             //single Option[User]
q.as(SqlParser.scalar[String] *)      //Seq[String]
q.as(rowParser *)                     //Seq[User]

Here’s an example of a complete Anorm SELECT method:

private def getVidForNid(nid: Long): Int = db.withConnection { implicit c =>
    val q: SimpleSql[Row] = SQL"""
        select vid from node
        where nid = $nid
    """
    val maybeVid = q.as(SqlParser.scalar[Int].singleOpt)
    maybeVid match {
        case Some(i) => i
        case None => throw new Exception(s"VID could not be found where NID = $nid")
    }
}

A brief intermission

SQL SELECT statements are the most complicated part of using Anorm, so I tried to organize them and show them in several different ways in those examples. Next, I’ll cover the rest of the SQL query types.

SQL INSERT with primary key (auto increment)

Here’s a SQL INSERT statement with Anorm:

val q = SQL"""
    insert into node (vid,type,title,uid,status,created,changed)
    values (999999,'blog',${b.title},1,1,${drupalDate},${drupalDate})
"""
val maybePk: Option[Long] = q.executeInsert()

Assuming your table has a a primary key auto-increment field, this query should return that auto-increment value, so I name my variable maybePk, which stands for, “Maybe a primary key.”

SQL INSERT with no primary key

If your database table doesn’t have a primary key auto-increment field, you can use this syntax instead:

val termNodeQuery = SQL"""
    insert into term_node (nid, vid, tid)
    values ($nid, $vocabularyId, ${t.tid})
"""
val b: Boolean = termNodeQuery.execute()

The Boolean value that’s returned by execute is a little funky. Per the Scaladoc, that field is “true if the result set was returned from execution (statement is query), or false if it executed update.” From what I can tell, it’s pretty useless for an INSERT.

SQL UPDATE

Here’s an example of a SQL UPDATE query with Anorm:

val q = SQL"""
    update node set vid=${vid} where nid=${nid}
"""
val numRowsUpdated = q.executeUpdate()
// test numRowsUpdated as desired

The thing to know here is that executeUpdate returns the number of rows that were updated by the query.

SQL DELETE

You also use executeUpdate for DELETE queries:

val numRowsDeleted = SQL"delete from term_node where nid = ${b.nid}".executeUpdate()

Complete example methods

As a final resource, here are some complete examples of SELECT, INSERT, UPDATE, and DELETE methods, along with one complete Anorm example class at the end.

SELECT:

def getTags(nid: Long): Seq[String] = db.withConnection { implicit c =>
    val q: SimpleSql[Row] = SQL"""
        select name from term_data
        where tid in (select tid from term_node where nid = $nid)
        and vid = (select vid from vocabulary where upper(name) = 'TAGS')
    """
    q.as(SqlParser.scalar[String] *)
}

INSERT:

def insertNewBlogPost(b: BlogPost): Unit = db.withTransaction { implicit c =>
    val drupalDate = getDrupalDate(getDate)

    // 1st insert
    val nodeQuery = SQL"""
        insert into node (vid,type,title,uid,status,created,changed)
        values (999999,'blog',${b.title},1,1,${drupalDate},${drupalDate})
    """
    val maybeNid: Option[Long] = nodeQuery.executeInsert()

    // 2nd insert
    val nodewordsQuery: SimpleSql[Row] = SQL"""
        insert into nodewords (type,id,name,content)
        values (5,${nid},'description',${getFormattedDescriptionForNodewords(b.metaDescription)})
    """
    val maybeMtid: Option[Long] = nodewordsQuery.executeInsert()
}

(That example is a bit of a kludge because inserting a new blog post into the database requires eight queries, and I didn’t want to show them all.)

UPDATE:

def enableBlogPostStatus(nid: Long, b: Boolean): Boolean = db.withConnection { implicit c =>
    val status = if (b) 1 else 0
    val q = SQL"update node set status=$status where nid=${nid}"
    val numRowsUpdated = q.executeUpdate()
    if (numRowsUpdated < 0) false else true
}

DELETE (in a transaction):

def deleteBlogPost(nid: Long): Unit = db.withTransaction { implicit c =>
    val res1: Int = SQL"delete from node where nid = $nid".executeUpdate()
    val res2: Int = SQL"delete from node_revisions where nid = $nid".executeUpdate()
    val res3: Int = SQL"delete from nodewords where id = $nid".executeUpdate()
    val res4: Int = SQL"delete from term_node where nid = $nid".executeUpdate()
    val res5: Int = SQL"delete from url_alias where nid = $nid".executeUpdate()
    //TODO do whatever you want with those return values
}

A complete class

Finally, here’s everything you need for a complete class that uses Anorm:

package models

import javax.inject.Inject
import anorm.SqlParser.get
import anorm.{Row, SQL, SimpleSql, ~}
import play.api.db.DBApi

case class SitemapEntry (
    dateUpdated: Long,
    uri: String
)

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

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

    val rowParser: anorm.RowParser[SitemapEntry] = {
        get[Long]("node.changed") ~
        get[String]("url_alias.dst") map {
            case changed~uri => SitemapEntry(changed, uri)
        }
    }

    def getNodesForSitemap(): Seq[SitemapEntry] = db.withConnection { implicit c =>
        val query: SimpleSql[Row] = SQL"""
            select n.changed, url.dst
            from node n, url_alias url
            where n.type in ('blog', 'photo')
            and n.nid = url.nid
            and n.status=1
            order by n.created desc
        """
        query.as(rowParser *)
    }

}

this post is sponsored by my books:
#1 New Release	FP Best Seller	Learn Scala 3	Learn FP Fast

More information

For more information about Anorm, see these resources:

• The Play Anorm page is a great start
• Dave Gurnell’s Anorm overview has some terrific background information
• The Anorm.scala source code is well documented

Note that the easiest way to read the latest Anorm.scala source code is to click on a method like executeInsert or executeUpdate in your Scala IDE.

I’m sure I’ll keep writing more about the Play Framework and Anorm, but for today, I hope these Anorm example queries are helpful.