Scala ORM with Squeryl – A simple getting started guide

Since my pet project (I will eventually blog about that) is in desperate need of a database and I’m doing enough Java on my day job I decided to give a Scala ORM framework a shot.
I have to warn you that I’m kind of a Scala hacker. I abuse it like a scripting language and usually grab some examples, put them together and wait for my colleagues to say "You don’t want that" or "You’re doing it wrong". So don’t hesitate to correct me, maybe I’ll learn something too 😉

Picking the right ORM-framework

I didn’t do any extensive research, just some googling. A framework called Scweery popped up. The name Scweery sounded very nice and it has a Twitter account with a funny avatar, so why look any further? Well, it seems the project isn’t that active anymore (no updates for over a year and that’s a long time with Scala).
Finally Stackoverflow came to the rescue. I found a question called Examples of Scala database persistence . I picked Squeryl, the syntax looked nice and there was useful documentation. I’m not sure whether I picked the right framework, but it survived my initial selection, so let’s find out.

Setting up the environment

For this article I assume the following:
-You have version 2.9.0 of Scala installed
–Git is on your system
–SBT is available on the command line (I used version 0.7.7)
I believe the links above explain everything pretty well, so let’s get started!

Getting started

I used the ‘minimal example’ project to get started:
[bash]
git clone https://github.com/pbrant/squeryl_sbt_minimal_example
cd squeryl_sbt_minimal_example
squeryl_sbt_minimal_example>sbt
….
[info]    using ExampleProject with sbt 0.7.7 and Scala 2.9.0
squeryl_sbt_minimal_example>update
…..
squeryl_sbt_minimal_example>compile
squeryl_sbt_minimal_example>run
[/bash]
I updated the Scala version to 2.9.0. This can be done by changing the version number project/build/build.properties. For this article it doesn’t matter much, but for your real project this newer version has lots of improvements.
That went pretty smooth. Let’s rip everything out and use Postgres. Open the file Main.scala (/src/main/scala/code). First remove drop, create and printDdl. We don’t want our valuable production database to be destroyed!
For my database testing I use my good old movie database. With ancient column names (in Dutch) and the ID column is a String. This might sound like a bad idea, but real databases are like this and I found out it’s a good way to test a new framework.

Schema definition

The first section to change is Library extends Schema. This is the section where the tables are defined. My table is called movies.
[scala]
val movies = table[ Movies ]
[/scala]
When running the example (with some additional code, so don’t try it yet) I got the following error:
org.postgresql.util.PSQLException: ERROR: relation "Movies" does not exist
It seems the queries are executed with the table names surrounded with double quotes, which means case sensitive. To solve this append ("MOVIES") to the table definition. Now we can also solve the problem that a class is singular (unlike a database table). Now I can call the class Movie instead of Movies:
[scala]
val movies = table[ Movie] (" MOVIES ")
[/scala]

Table definition

Our next step is the table definition. (In Dutch, "jaar" means "year" and "naam" means "name") This time we have to use annotations to use other column names (to get rid of typos and underscores):
[scala]
class Movie(
@Column("imdb_id")
val imdbId: String,
@Column("jaar")
val year: Int,
@Column("naam_film")
val name: String,
@Column("genre")
val genres: String
)
[/scala]
Note that I used val for the columns, when the columns are mutable it’s allowed to use var.

Connecting to Postgresql

To connect to to a Postgres database replace the H2 dependency in /project/build/ExampleProject.scala with
[scala]
val pg = "postgresql" % "postgresql" % "9.0-801.jdbc4"
[/scala]
Now go to the main method in Main.scala and replace the driver and connection parameters:
[scala]
Class.forName("org.postgresql.Driver");
SessionFactory.concreteFactory = Some(() =>
Session.create(
java.sql.DriverManager.getConnection("jdbc:postgresql://localhost/moviedb", "bill", "s3cr3tp455w0rdth4t15nt5053cr3t4nym0r3n0w"),
new PostgreSqlAdapter)
)
[/scala]
At a first glance it just looks like JDBC, but the Some(()=>..) notation might be a bit odd. This is by-name-parameter, just see it as a function that creates a session whenever needed.

A select * from

The final section is doing the actual query:
[scala]
inTransaction {
val movies = from(Library.movie)(select(_))
for (movie <- movies){
println (movie.name)
}
}
[/scala]
To execute queries you need a transaction (also for read only queries). A transaction is available after the initialization of the SessionFactory.concreteFactory. To run code in a transaction wrap it in an inTransaction block (you can also use ‘transaction’, this always starts a new transaction, inTransaction only does when there is no transaction in progress).
The first line in the inTransaction block does a select * from movie.
Scala might be a bit confusing when you’re new to it, like me. The scaladocs might help. From can be found here and select here.
The statement should read like this: from Library.movie apply each row to the select function. The second set of parameters for from (the so called Queryable) can be expanded with where and order by clauses. For more information read the page about selects on the Squeryl website.
The for loop of course prints all the movie names.

‘Advanced’ queries

The previous paragraph was just a simple query you’ll probably never execute in real life. So let’s try something a little bit more difficult:
[scala]
inTransaction {
val movies = from(Library.movie)(s=>
where(s.year === 1994)
select(s) )
println(movies)
for (movie <- movies){
println (movie.name)
}
}
[/scala]
The operator === is a method that is added to Int (this construction is called ‘implicit’) to create the actual where-statement in SQL. I also added a println(movies), this prints the actual SQL-statement, very useful!

Conclusion

I really like Squeryl, it was easy to set up and the basic documentation is sufficient. Of course a next step is inserting and adding more expressions (and for me, handling Postgis geometry objects).
I hope this article gives you a head start.

Sources

–Stackoverflow – Examples of Scala database persistence
–Scala installation
–Git
–Sbt setup
–Scweery
–Squeryl
–Squeryl on github
–Squeryl google group