A short guide to Anorm

anorm.scala

/*

Overview
--------

To run a query using anorm you need to do three things:

 1. Connect to the database (with or without a transaction)
 2. Create an instance of `anorm.SqlQuery` using the `SQL` string interpolator
 3. Call one of the methods on `SqlQuery` to actually run the query
 4. In some cases, pass a `ResultSetParser` as an argument to the method in step 3

In some cases step 3 involves creating a `ResultSetParser` to dictate how to
parse the results. We'll come to that below.

Step 1: Connect to the database
-------------------------------

There are a few ways of doing this. We can connect with or without a transaction,
and we can connect to any of the databases we have defined in `conf/application.conf`.

We give each database in `application.conf` a name:

    db.databaseid.driver=org.postgresql.Driver
    db.databaseid.url="jdbc:postgresql://localhost:5432/databasename"

Play expects us to define a "default" database:

    db.default.driver=...
    db.default.url=...

but we can give a database any ID we like:

    db.test.driver=...
    db.test.url=...

We can define as many databases as we want. See the documentation here for full instructions:
  
    https://www.playframework.com/documentation/2.3.x/ScalaDatabase

*/

import play.api.db._

// Option A. Connect to the "default" database without a transaction:
DB.withConnection { implicit conn =>
  // query code goes here...
}

// Option B. Connect to another database without a transaction:
DB.withConnection("databaseid") { implicit conn =>
  // query code goes here...
}

// Option A. Connect to the "default" database with a transaction:
DB.withTransaction { implicit conn =>
  // query code goes here...
}

// Option B. Connect to another database with a transaction:
DB.withTransaction("databaseid") { implicit conn =>
  // query code goes here...
}

/*

Step 2. Create an SqlQuery
--------------------------

Anorm gives us a way to write raw SQL and create a Scala object of type `anorm.SqlQuery`.

`SqlQuery` has mehods to execute the query and return various types of result:

*/

import anorm._

// We create the query using the `SQL""` string interpolator:
val query = SQL"select * from mytable;"

// We can embed Scala values in the SQL using the standard `${}` syntax.
// Anorm escapes interpolated values appropriately:

val value = // some scala value
val query = SQL"select * from mytable where mycolumn = ${value};"

/*

Step 3. Call a method to execute the query
------------------------------------------

`SqlQuery` has several methods to actually start the query.
Each method accepts an implicit `Connection` as a parameter,
so we can only call them within a call to `withConnection` or
`withTransaction`:

*/

// Option A. `query.execute()` executes a query and returns nothing:
DB.withConnection { implicit conn =>
  SQL"""
    update mytable set col1 = $value where col2 = $anotherValue;
  """.execute()
  
  // etc...
}

// Option B. `query.as(...)` executes a query and returns results.
// The argument to `as(...)` is a `ResultSetParser`... see the next step:
DB.withConnection { implicit conn =>
  val rsParser: ResultSetParser[List[MyType]] = // ...

  val results: List[MyType] = SQL"""
    select * from mytable;
  """.as(rsParser)

  // etc...
}

// Option C. `query.executeInsert()` returns an `Option[Long]` primary key...
// assuming your table is set up with an auto-incrementing integer primary key:
DB.withConnection { implicit conn =>
  val newPK: Option[Long] = SQL"""
    insert into mytable (...) values (...);
  """.executeInsert()
  
  // etc...
}

// Option D. `query.executeInsert(...)` returns a primary key of another type.
// The argument to `executeInsert(...)` is a `ResultSetParser` to parse the keys:
DB.withConnection { implicit conn =>
  val rsParser: ResultSetParser[List[MyType]] = // ...

  val newPKs: List[MyType] = SQL"""
    insert into mytable (...) values (...);
  """.executeInsert(rsParser)

  // etc...
}

/*

Step 4. Create a `ResultSetParser` to use with the method from Step 3
---------------------------------------------------------------------

If you're retrieving results from the database using `as(...)`, you 
need to supply a `ResultSetParser` to tell Anorm how to parse the results 
of the query. This is also true for the result of an `executeInsert()`.

If you're using plain `execute()` you don't need this.

We create `ResultSetParsers` in two steps:

 A. Create a `RowParser` to specify what information is stored in each row.
 B. Call one of four methods on `RowParser` to convert it to a `ResultSetParser`
    that parses more than one row.

Step 4A. Create a `RowParser`
-----------------------------

A `RowParser` is a bit like a `Reads` -- it specifies a transformation from untyped data
(in this case a `java.sql.ResultSet`) to typed Scala data (an `Option` or `List` of domain objects).

*/

// Here is the syntax for creating a `RowParser`:
val myCaseClassParser: RowParser[MyCaseClass] = (
  SqlParser.somemethod1("columnname1") ~
  SqlParser.somemethod2("columnname2") ~
  SqlParser.somemethod3("columnname3") ~
  SqlParser.somemethod4("columnname4") ~
  SqlParser.somemethod5("columnname5") // etc...
) map {
  case columnvalue1 ~ columnvalue2 ~ columnvalue3 ~ columnvalue4 ~ columnvalue5 => // etc...
    MyCaseClass(columnvalue1, columnvalue2, columnvalue3, columnvalue4, columnvalue5) // etc...
}

/*

Let's look at the individual parts. First, the `SqlParser.somemethod(...)` parts.
`anorm.SqlParser` is an object with a bunch of methods for parsing column data types:

 - `SqlParser.str("columnname")`  creates a `RowParser[String]`  that parses "columname" as a `String`;
 - `SqlParser.int("columnname")`  creates a `RowParser[Int]`     that parses "columname" as a `Int`;
 - `SqlParser.long("columnname")` creates a `RowParser[Long]`    that parses "columname" as a `Long`;
 - `SqlParser.date("columnname")` creates a `RowParser[Date]`    that parses "columname" as a `Date`;
 - `SqlParser.bool("columnname")` creates a `RowParser[Boolean]` that parses "columname" as a `Boolean`;
 - and so on...

also...

 - `SqlParser.scalar[Type]` creates a `RowParser[Type]` that parses a single-column row,
   no matter what the column name is (useful for "select count(*) ..." style queries).

See https://www.playframework.com/documentation/2.3.x/api/scala/index.html#anorm.SqlParser$
for a complete list of methods.

We can combine `RowParsers` together to create bigger `RowParsers` that parse more than one column:

*/

val rowParser1: RowParser[Int]    = SqlParser.int("column1") // parses "column1" as an `Int`
val rowParser2: RowParser[String] = SqlParser.str("column2") // parses "column2" as a `String`

val rowParser3: RowParser[~[Int, String]] = rowParser1 ~ rowParser2

/*

What's going on with this last line of code? There are two things called `~`:

First, `anorm.~` is a pair-like case class that holds two values. See:
https://www.playframework.com/documentation/2.3.x/api/scala/index.html#anorm.$tilde

*/

val pairLikeThing: ~[Int, String] = ~(123, "456")

/*

It's called `~` because Scala lets us write binary (two-argument) types and patterns
using an infix syntax:

*/

val pairLikeThing: Int ~ String = ~(123, "456")

pairLikeThing match {
  case a ~ b =>
    assert(a == 123)
    assert(b == "456")
}

/*

The other use of `~` is a method on `RowParser`:

*/

rowParser1 ~ rowParser2

/*

This method combines the `RowParsers` together into a single `RowParser` that extracts
a values of type `~(a, b)` from the result-set. Its type is written like this:

*/

val rowParser3: RowParser[~[A, B]] = rowParser1 ~ rowParser2

/*

or we can write it using infix syntax:

*/

val rowParser3: RowParser[A ~ B] = rowParser1 ~ rowParser2

/*

We can `map` over a `RowParser` to produce a new parser with a different output:

*/

val rowParser4: RowParser[String] = rowParser3.map { valueFromRowParser3 =>
  valueFromRowParser3 match {
    case ~(valueFromRowParser1, valueFromRowParser2) =>
      s"I extracted the integer $valueFromRowParser1 and the string $valueFromRowParser2"
  }
}

/*

We can reduce this to the code we wrote above in two steps. First, write the pattern 
using infix syntax:

*/

val rowParser4: RowParser[String] = rowParser3.map { valueFromRowParser3 =>
  valueFromRowParser3 match {
    case valueFromRowParser1 ~ valueFromRowParser2 =>
      s"I extracted the integer $valueFromRowParser1 and the string $valueFromRowParser2"
  }
}

/*

Second, write the mapping function as a partial function:

*/

val rowParser4: RowParser[String] = rowParser3.map {
  case valueFromRowParser1 ~ valueFromRowParser2 =>
    s"I extracted the integer $valueFromRowParser1 and the string $valueFromRowParser2"
}

/*

All three versions of the code are semantically identical -- we're just using convenient
syntax to cut down on typing.

The final intuition we need to understand `RowParsers` is that instances of `~(a, b)`
can be nested:

*/

val nestedTildes: ~[A, ~[B, C]] = ~(a, ~(b, c))

/*

Again, we can write the types infix to make them easier to read:

*/

val nestedTildes: A ~ B ~ C = ~(a, ~(b, c))

/*

We can pattern match using infix syntax too:

*/

nestedTildes match {
  case a ~ b ~ c =>
    // etc...
}

/*

Bring it all back home, this gives us all the knowledge we need to understand the 
`RowParser` pattern from above.

We're extracting a bunch of values from individual columns, combining them into a set
of nested instances of `~(a, b)`, and pattern matching to pull them apart again and
turn them into something sane... in this case `MyCaseClass`:

*/

val myCaseClassParser: RowParser[MyCaseClass] = (
  SqlParser.somemethod1("columnname1") ~
  SqlParser.somemethod2("columnname2") ~
  SqlParser.somemethod3("columnname3") ~
  SqlParser.somemethod4("columnname4") ~
  SqlParser.somemethod5("columnname5") // etc...
) map {
  case columnvalue1 ~ columnvalue2 ~ columnvalue3 ~ columnvalue4 ~ columnvalue5 => // etc...
    MyCaseClass(columnvalue1, columnvalue2, columnvalue3, columnvalue4, columnvalue5) // etc...
}

/*

Confusing as this all may seem, it's just syntax. Syntax we don't see very often,
but syntax nonetheless.

Step 4B. Turn our `RowParser` into a `ResultSetParser`
------------------------------------------------------

Our `RowParser` states how we want to parse a single row from our results. We can use one
of four methods to turn that into a `ResultSetParser` that parses an entire set of results:

*/

// `rowParser.*` creates a `ResultSetParser` that parses all rows and returns a `List`:
val allRowsParser: ResultSetParser[List[MyCaseClass]] = myCaseClassParser.*

// `rowParser.single` creates a `ResultSetParser` that parses a single row
// (and fails if the result set is empty);
val singleRowParser: ResultSetParser[MyCaseClass] = myCaseClassParser.single

// `rowParser.singleOpt` creates a `ResultSetParser` that parses 0 or 1 rows and returns an `Option`:
val optionalRowParser: ResultSetParser[Option[MyCaseClass]] = myCaseClassParser.singleOpt

// We can pass any of these `ResultSetParsers` to `as(...)` or `executeInsert(...)` to receive
// the relevant results:

val allRows: List[MyCaseClass] = 
  SQL"""select * from mytable;""".
  as(allRowsParser)

val firstRow: Option[MyCaseClass] = 
  SQL"""select * from mytable limit 1;""".
  as(optionalRowParser)

val countRows: Int = 
  SQL"""select count(*) from mytable;""".
  as(SqlParser.scalar[Int].single)

/*

That's it! That's all you need to know to use Anorm.

*/

Thanks. You explained this way better than the docs. :)

Thank you davegurnell, this helped me a lot, much more than the Play documentation.

This was very helpful.... Thanks a ton :)

The best Anorm guide on the web. Many thanks.

would ne nice to know how to log sql queries without play-framework

THANKS! i've tried so hard until i found your guide! it's like a god ray in my nightmare! :)

@Sergey80 You can't. Some say you can use "acolyte", but I couldn't spend 2h on more documentation just for that.

This guide could be posted on StackOverflow Docs maybe.

you are the best! thanks a lot, very clear

super doc!

This is amazing, thank you.

Very good explanation of the actual stuff that works.
BTW, the "insert" statement is not returning me the result i.e. the PK. It's 'None'. Any suggestions on why this could be an issue ?

Super good. Thank you!

Thanks for this, sad to know we can't use it without play

Thanks for this, sad to know we can't use it without play

You can use without Play and any DI.

Simply create a java.sql.Connection using DriverManager.

declare implicit connection

implicit val conn:Connection = buildConnection

use anorm