ScalaQuery, a Small Database Library

On this blog I usually talk about no relational databases, like RavenDB or db4o. But guess what, I still like regular relational databases.

Relational Stuff fits Scala well

A while back I needed to read some data from a relational database in Scala. What I wanted was a small library, which removes most of the JDBC boiler plate, but not a full blown ORM. I ended up using is ScalaQuery.

This library gives you a thin, rational layer on top of JDBC. This is how it works. First you define a ‘table’. This is done extending a table class and adding field definitions to it:

val ApiKeys = new ExtendedTable[(String, String)]("googleApiKeys") {
	def deviceId = column[String]("deviceId", O.PrimaryKey)

	def apiKey = column[String]("apiKey")

	def * = deviceId ~ apiKey
}

Afterwards you need to create a database instance:

val database: Database = Database.forURL("jdbc:h2:~/apiKeyStore", driver = "org.h2.Driver")

To do an operation we need a transaction / session. On way to create such a session is to use the withSession method. Basically all operations require a session. We can manually pass the session to the methods. Or we can declare an implicit session which is used. For example the built in thread local session:

// We use the thread local session management
// So we import this implicit parameter which returns the current session
// Of course we pass any other session / use our own implicits
import org.scalaquery.session.Database.threadLocalSession


// and we do stuff in our session
database withSession {
  // do stuff
}

For example we can create the database schema:

database withSession {
    ApiKeys.ddl.create
}

Now to the important part: We can query and update data with our table object. The cool thing is that we just can use the Scala for-construct to do so. The library will transform our code into the appropriate SQL statement. Everything lives in the ‘Scala’ world, no strings and casts etc. are required. Also not that some imports are required for these API features:

// Imports for the query API
import org.scalaquery.ql.extended.H2Driver.Implicit._
import org.scalaquery.session.{Session, Database}
import org.scalaquery.ResultSetInvoker
import org.scalaquery.simple.StaticQuery
import org.scalaquery.ql.Query


// Query
val devicesByApiKey = for {
	a <- ApiKeys if a.deviceId.like(deviceId)
} yield a.apiKey

// Do stuff with the result
devicesByApiKey.foreach(i=>doStuff(i))

We also can insert and update tables:

val devicesByApiKey = for {
	a <- ApiKeys if a.deviceId.like(deviceId)
} yield a.apiKey

devicesByApiKey.firstOption match {
	case None => {
	  ApiKeys.insert(deviceId, googleKey)
	}
	case Some(_) => {
	  query.update(googleKey)
	}
}

My Opinion & State of the Library

I really like the approach of this library. It is lightweight, close the SQL-metal and yet it blends perfectly with the Scala code. It really doesn’t feel like you entering into a new domain when talking to the database. It only surfaces operations which work in SQL and doesn’t do any operations behind the scenes. For many use cases this works way better than heavy weight ORM frameworks.

However I have to point out that many features are not there and there doesn’t seem any active development going on. For example you cannot check if a table already exists before calling the ‘create schema’ method. I also miss a nice method for using raw SQL in case you need a missing feature.

So right now I only would use it for pet projects, or when you are willing to invest your time to patch and improve things.

• ← 음악이 좋아요 : Machinae Supremacy / Good Music: Machinae Supremacy
• Modify Annotations in Scala →