Squeryl - simplicity and grace

Good afternoon, habr!

I decided to write a short review with examples on the lightweight ORM for Scala - Squeryl 0.9.5

Let's start with the main advantages of this framework.

1) Squeryl provides DSL for SQL queries. For example

def songs =  from(MusicDb.songs)(s => where(s.artistId === id) select(s))
def fixArtistName = update(songs)(s =>
  where(s.title === "Prodigy")
    s.title := "The Prodigy",

The syntax is reminiscent of C # LINQ. As you can see in the queries, lambda expressions are used, which significantly reduces the amount of code.

In this example, the songs method returns a Query [Song] object that implements the Iterable interface, which allows you to work with it like a regular collection.

It is also worth noting that queries can be used as subqueries, for this it is enough to specify the query in the from construct instead of a table.

2) The simplest description of models

class User(var id:Long, var username:String) extends KeyedEntity[Long]
object MySchema extends Schema{ 
  val userTable = table[User]

In this example, you are describing a model with the primary key id of type Long and field username of type String, some additional configs are not required. After we have described the model, it is necessary to register it in the circuit.

By default, Squeryl uses class names for table names and class property names for field names.
To explicitly indicate the table name, you can use:

  val userTable = table[User]("USER_TABLE")

and for columns you can use the @Column attribute

class User(var id:Long, @Column("USER_NAME") var username:String) extends KeyedEntity[Long]

For composite keys, the types CompositeKey2 [K1, K2], CompositeKey3 [K1, K2, K3], etc., are used, in accordance with the number of fields in the composite key.

To ensure that the field is not saved in the database, just mark it with the Transient annotation .

3) Custom functions.

Squeryl contains the necessary minimum functions for working with the database, this set can be easily supplemented.

For example, we implement the date_trunc function for PostgreSQL

class DateTrunc(span: String, e: DateExpression[Timestamp], m: OutMapper[Timestamp])
  extends FunctionNode[Timestamp](
    "date_trunc", Some(m), Seq(new TokenExpressionNode("'" + span + "'"), e)
  ) with DateExpression[Timestamp]
def dateTrunc(span: String, e: DateExpression[Timestamp])(implicit m: OutMapper[Timestamp]) = new DateTrunc(span, e, m)

You can find a more detailed description on the official website squeryl.org/getting-started.html

Well, what is closer to practice


To demonstrate the operation of ORM, we will write a small application on Play Framework 2, which will provide a universal API for obtaining an object, saving / creating an object, and deleting it by class name and identifier.

We will use PostgreSQL 9.3 as the database.


Add to build.sbt

  "org.squeryl" %% "squeryl" % "0.9.5-7",
  "org.postgresql" % "postgresql" % "9.3-1101-jdbc41"

Add to conf / application.conf

db.default.driver = org.postgresql.Driver
db.default.url = "postgres://postgres:password@localhost/database"
db.default.logStatements = true
evolutionplugin = disabled

Create Global.scala in the app directory

import org.squeryl.adapters.PostgreSqlAdapter
import org.squeryl.{Session, SessionFactory}
import play.api.db.DB
import play.api.mvc.WithFilters
import play.api.{Application, GlobalSettings}
object Global extends GlobalSettings {
  override def onStart(app: Application) {
    SessionFactory.concreteFactory = Some(() => Session.create(DB.getConnection()(app), new PostgreSqlAdapter))

So when you start the application, we initialize the default session factory.


We implement the base trait for models, which will contain id fields of type Long, created - the time the model was created in the database, updated - the time of the last change, (maybe I'll call a hollywood, but still) the deleted field of type Boolean, which will be the flag removed object or not, and if necessary, this object can be restored.

We also immediately implement the functionality for converting the object to json, for this we will use the Gson library to add it you will write in build.sbt: Of course, the Play Framework has already built-in mechanisms for working with json, but in my opinion they have drawbacks, so we will combine them along with gson. To do this, create app / models / Entity.scala

"com.google.code.gson" % "gson" % "2.2.4"

package models
import com.google.gson.Gson
import org.joda.time.DateTime
import org.squeryl.KeyedEntity
import play.api.libs.json.JsValue
trait EntityBase[K] extends KeyedEntity[K] {
  def table = findTablesFor(this).head
  def json(implicit gson: Gson): JsValue = play.api.libs.json.Json.parse(gson.toJson(this))
  def isNew: Boolean
  def save(): this.type = transaction {
    if (isNew) table.insert(this)
    else table.update(this)
trait EntityC[K] extends EntityBase[K] {
  var created: TimeStamp = null
  override def save(): this.type = {
    if (isNew) created = DateTime.now()
trait EntityCUD[K] extends EntityC[K] {
  var updated: TimeStamp = null
  var deleted = false
  override def save(): this.type = {
    updated = DateTime.now()
  def delete(): this.type = {
    deleted = true
class Entity extends EntityCUD[Long] {
  var id = 0L
  override def isNew = id == 0L

This code implements several traits that are inherited from each other adding new functionality.

The main concept: the save () method, checks whether the given object is saved in the database or not, and depending on this, the create or update method is called on the corresponding table.

Squeryl uses the java.sql.Timestamp type for storing time, which for me (and many will agree with me) is very inconvenient to use. For working over time, I prefer to use joda.DateTime. Fortunately, Scala provides a convenient mechanism for implicit type conversions.

We will create a data scheme and a set of useful utilities, for convenience we will create a package object, for this we create an app / models / package.scala file with the following code:

import java.sql.Timestamp
import com.google.gson.Gson
import org.joda.time.DateTime
import org.squeryl.customtypes._
import org.squeryl.{Schema, Table}
import play.api.libs.json.{JsObject, JsValue, Json}
import scala.language.implicitConversions
package object models extends Schema with CustomTypesMode {
  val logins = table[Login]
  def getTable[E <: Entity]()(implicit manifestT: Manifest[E]): Table[E]
  = tables.find(_.posoMetaData.clasz == manifestT.runtimeClass).get.asInstanceOf[Table[E]]
  def getTable(name: String): Table[_ <: Entity] = tables.find(_.posoMetaData.clasz.getSimpleName.toLowerCase == name)
    .get.asInstanceOf[Table[_ <: Entity]]
  def get[T <: Entity](id: Long)(implicit manifestT: Manifest[T]): Option[T] = getTable[T]().lookup(id).map(e => {
    if (e.deleted) None
    else Some(e)
  def get(table: String, id: Long): Option[Entity] = getTable(table).lookup(id).map(e => {
    if (e.deleted) None
    else Some(e)
  def getAll(table: String): Seq[Entity] = from(getTable(table))(e => select(e)).toSeq
  def save(table: String, json: String)(implicit gson: Gson) = gson.fromJson(
    json, getTable(table).posoMetaData.clasz
  def delete(table: String, id: Long) = get(table, id).map(_.delete())
  class TimeStamp(t: Timestamp) extends TimestampField(t)
  implicit def jodaToTimeStamp(dateTime: DateTime): TimeStamp = new TimeStamp(new Timestamp(dateTime.getMillis))
  implicit def timeStampToJoda(timeStamp: TimeStamp): DateTime = new DateTime(timeStamp.value.getTime)
  class Json(s: String) extends StringField(s)
  implicit def stringToJson(s: String): Json = new Json(s)
  implicit def jsonToString(json: Json): String = json.value
  implicit def jsValueToJson(jsValue: JsValue): Json = new Json(jsValue.toString())
  implicit def jsonToJsObject(json: Json): JsObject = Json.parse(json.value).asInstanceOf[JsObject]
  class ForeignKey[E <: Entity](l: Long) extends LongField(l) {
    private var _entity = Option.empty[E]
    def entity(implicit manifestT: Manifest[E]): E = _entity.getOrElse({
      val res = get[E](value).get
      _entity = Some(res)
    def entity_=(value: E) {
      _entity = Some(value)
  implicit def entityToForeignKey[E <: Entity](entity: E): ForeignKey[E] = {
    val fk = new ForeignKey[E](entity.id)
    fk.entity = entity
  implicit def foreignKeyToEntity[T <: Entity](fk: ForeignKey[T])(implicit manifestT: Manifest[T]): T = fk.entity
  implicit def longToForeignKey[T <: Entity](l: Long)(implicit manifestT: Manifest[T]) = new ForeignKey[T](l)

It implements the basic methods for working with the database, creates its own class for TimeStamp time, its own class for storing json in the database, and its own class for foreign keys with all the necessary implicit conversions. Many people consider the code to be an overkill, but I’ll say right away in most tasks in practice that code is completely useless, I tried to demonstrate to you what functionality Squeryl has.

And finally, we will write the Login model with the login, password field and foreign key on the Login that invited him and do not forget to create the corresponding table in the database with test data.

package models
class Login extends Entity {
  var login = ""
  var password = ""
  var parent: ForeignKey[Login] = null


In order to fulfill the request, you must put the code in inTransaction {} or transaction {}.

inTransaction {} adds a request to the current transaction.

transaction {} executes the code in a single transaction.

We assume that one action corresponds to one transaction and in order not to write a transaction block in each action, create a DbAction in the file app / controller / BaseController.scala

package controllers
import models._
import play.api.mvc._
import utils.Jsons
import scala.concurrent.Future
import scala.language.implicitConversions
trait BaseController extends Controller {
  implicit val gson = new Gson
  object DbAction extends ActionBuilder[Request] {
    override def invokeBlock[A](request: Request[A],
                                block: (Request[A]) => Future[Result]): Future[Result] = transaction {

Here we specified the gson object that will be used to convert the model to json /

Well, finally, we will write a controller for the API, app / controllers / Api.scala

package controllers
import play.api.libs.json.Json
import play.api.mvc.Action
object Api extends BaseController {
  def get(cls: String, id: Long) = DbAction {
    Ok(models.get(cls, id).map(_.json).getOrElse(Json.obj()))
  def save(cls: String) = DbAction{
    request => Ok(models.save(cls, request.form.getOrElse("data", "{}")).json)
  def delete(cls: String, id: Long) = DbAction {
    Ok(models.delete(cls, id).map(_.json).getOrElse(Json.obj()))

Add actions to the conf / routes routes

# Api
GET         /api/:cls/:id               controllers.Api.get(cls:String,id:Long)
POST        /api/save/:cls              controllers.Api.save(cls:String)
POST        /api/delete/:cls/:id        controllers.Api.delete(cls:String,id:Long)

And finally, we run:


In this case, you can register any id in the url, any class instead of login and get the Json you need in response. If necessary, in models, you can overload the json method to add / hide any data. It is worth noting that Gson does not serialize Scala collections, so for this you will have to use the transformations in the Java collections, or use the built-in mechanism in the Play Framework for working with Json.


The written code perfectly demonstrates the wide capabilities of Squeryl, but it is worth noting that for small tasks it is not at all necessary to implement something similar, Squeryl will be able to provide you with full-fledged work with the database in just 5 lines.

The main drawback in my opinion is the lack of a migration mechanism, the maximum that Squeryl can do is to issue the current DDL.

I will not conduct a comparative analysis of Squeryl with other ORMs (at least in this article), but for me personally a person who is very lazy and does not want to write something extra when adding new entities to the database, this ORM fits perfectly.

Also popular now: