Squeryl - simplicity and grace

Good afternoon, Habr!

I decided to write a small 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") set( s.title := "The Prodigy", ) ) 

The syntax is reminiscent of C # LINQ. As you may have noticed, lambda expressions are used in queries, 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 as with a regular collection.

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

2) The simplest description of the models

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

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

By default, Squeryl uses class names for table names and class property names for field names.
To explicitly specify the name of the table, 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] and so on are used, according to the number of fields in the composite key.

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

3) custom functions.

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

For example, we implement the function date_trunc 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) 

A more detailed description can be found on the official website squeryl.org/getting-started.html

Well closer to practice

Task

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

We will use PostgreSQL 9.3 as the database.

Integration

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 session factory with the default connection.

Models

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

We also immediately implement the functionality for converting an object to json, for this we will use the Gson library, to add it you will register in build.sbt:

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


Of course, the Play Framework has built-in mechanisms for working with json, but in my opinion they have flaws, so we will combine them with Gson.

To do this, create app / models / Entity.scala

 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) this } } trait EntityC[K] extends EntityBase[K] { var created: TimeStamp = null override def save(): this.type = { if (isNew) created = DateTime.now() super.save() } } trait EntityCUD[K] extends EntityC[K] { var updated: TimeStamp = null var deleted = false override def save(): this.type = { updated = DateTime.now() super.save() } def delete(): this.type = { deleted = true save() } } class Entity extends EntityCUD[Long] { var id = 0L override def isNew = id == 0L } 

In this code, several traits are implemented, which are inherited from each other by adding new functionality.

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

To store time, Squeryl uses the java.sql.Timestamp type, which for me (and many will agree with me) is not very convenient to use. To work with time, I prefer to use joda.DateTime. The benefit of Scala provides a convenient mechanism for implicit type conversions.

Let's create a data scheme and a set of useful utilities, for convenience, create a package object, for this we create the file app / models / package.scala 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) }).getOrElse(None) def get(table: String, id: Long): Option[Entity] = getTable(table).lookup(id).map(e => { if (e.deleted) None else Some(e) }).getOrElse(None) 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 ).save() 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) 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 fk } 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) } 

Here the main methods for working with the database are implemented, a class for the TimeStamp time, a class for storing json in the database and a class for foreign keys with all the necessary implicit conversions are created. Many will find the code overkilom, but I will immediately say in most tasks in practice, this code does not need anything at all, I tried to show you what kind of functionality Squeryl has.

And finally, we will write a Login model with the login, password field and an external key to the Login who invited it 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 } 

Actions

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

inTransaction {} adds the request to the current transaction.

transaction {} executes code in a single transaction.

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

 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 { block(request) } } } 

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

Finally, let's 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 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 run:



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

Summarize

The written code perfectly demonstrates the wide possibilities of Squeryl, but it is worth noting that for small tasks it is not necessary to implement something like this, Squeryl can provide you with full-fledged work with the database in just 5 lines.

The main drawback in my opinion is the absence of the 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 very lazy person and not willing to write something extra when adding new entities to the database, this ORM fits perfectly.