Skip to content

Instantly share code, notes, and snippets.

@davegurnell
Last active June 5, 2024 14:26
Show Gist options
  • Save davegurnell/4b432066b39949850b04 to your computer and use it in GitHub Desktop.
Save davegurnell/4b432066b39949850b04 to your computer and use it in GitHub Desktop.
A short guide to Anorm
/*
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.
*/
@leftiness
Copy link

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

@jgaye
Copy link

jgaye commented Nov 11, 2015

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

@zeeshangtk
Copy link

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

@jdelafon
Copy link

The best Anorm guide on the web. Many thanks.

@Sergey80
Copy link

Sergey80 commented Nov 2, 2016

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

@The-Nils
Copy link

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

@jdelafon
Copy link

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

@jdelafon
Copy link

This guide could be posted on StackOverflow Docs maybe.

@lilloraffa
Copy link

you are the best! thanks a lot, very clear

@pawelpanasewicz
Copy link

super doc!

@oleksiy
Copy link

oleksiy commented May 14, 2017

This is amazing, thank you.

@nkmittal
Copy link

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 ?

@brandon-rees
Copy link

Super good. Thank you!

@deepakkumarnd
Copy link

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

@integrant-io
Copy link

integrant-io commented Sep 5, 2021

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment