Posts Tagged ‘Persistence’

Simple Database Migrations with Scala and Querulous

April 12, 2011 3 comments

Inphina provides specialized Scala consulting, training and offshoring … Learn more about Scala@Inphina!

(Cross posted on my personal blog)

We hate doing it, but one time or the other, each of us ends up writing a quick-and-dirty database migration utility for a project we’re working on. I recently had to do the same, and surprisingly the process was really smooth .. largely thanks to Scala goodness and Querulous — a very cool, simple and lightweight database library created by some folks at Twitter.

Let’s start by creating a Mavenized Scala project, check out Getting Started – Maven Archetype for Scala.

Does the Scala project build successfully? Good.

The next step, add the Querulous dependencies to your project pom.xml file:


In our example, we will migrate a source database boysDb to a destination database girlsDb. No real migration business logic is being applied, think of it as a one-to-one migration.

We start off by declaring QueryEvaluators for our source and destination database schemas:

  val sourceDb = QueryEvaluator("localhost/boysdb", "root", "")
  val destinationDb = QueryEvaluator("localhost/girlsdb", "root", "")

QueryEvaluator object allows us to execute SQL queries on a referenced database.

Next step, get boys from the source database:

val boys ="select id, name from boys") {
      row => (row.getInt(1), row.getString(2))

boys is a collection of Tuple2[Int, String] containing the id and name attributes of a boy.

For each boy, insert a girl:

boys.foreach(boy => insertGirls(boy))

Let’s now define our insertGirls() method

  def insertGirls(boy: Tuple2[Int, String]): Unit = {
    destinationDb.execute("insert into girls (id, name) values (?, ?)", boy._1, boy._2)

..and we’re done.

Scala + Querulous makes it easy to connect to multiple database instances, makes querying extremely straightforward by exposing SQL directly without unnecessary ORM’ing or configuration / property file mess.

Full source code has been published on GitHub –

For serious database migrations, do consider Scala Migrations.

Srirangan is a programmer / senior consultant with Inphina Technologies
Blog   GitHub   LinkedIn   Twitter

Categories: Scala Tags: ,

Getting Started – Scala Persistence with Squeryl

March 18, 2011 2 comments

Inphina provides specialized Scala consulting, training and offshoring … Learn more about Scala@Inphina!

(Cross posted on my personal blog)

Recently I had to implement simple entity persistence in Scala. I used Squeryl which defines itself as “a Scala ORM and DSL for talking with Databases with minimum verbosity and maximum type safety”. I tried it out and it was extremely easy to get started with.

This blog post will cover the basics of Scala persistence using Squeryl and the target database system is MySQL.

You will need a Scala project to work with. If you don’t have one already, you can create a Scala project with the Maven archetype.

Step 1 – Define dependencies

Our first dependency is Squeryl. As I blog this, the latest Squeryl version is “0.9.4-RC6″. We need to make this available to our code. Additionally, we need to make available the MySQL Connector as this example persists data into a MySQL database. MySQL Connector is a run-time dependency internally consumed by Squeryl.


If you are using Apache Maven, add the code shown above to the “dependencies” element of your project “pom.xml” file. If you don’t use Maven, make sure the artifacts / JARs are available in your project.

Step 2 – Define entities

Squeryl entities are basic Scala objects.. should we call them POSO’s? Here I’ve defined a BaseEntity and extended it to define a User entity.

package net.srirangan.opensource.squerylexample.entities

import java.sql.Timestamp
import org.squeryl.KeyedEntity

class BaseEntity extends KeyedEntity[Long] {

  val id:Long = 0
  var lastModified = new Timestamp(System.currentTimeMillis)


All entities will extend and build on the BaseEntity. The BaseEntity introduces two fields “id” and “lastModified”.

package net.srirangan.opensource.squerylexample.entities

class User(var email:String, var password:String) extends BaseEntity {

  // Zero argument constructor required
  // Squeryl Roadmap says 0.9.5 will not need them :-) 
  def this() = this("", "")

We’ve now defined the User extending the BaseEntity. In addition to the fields “id” and “lastModified”, a User instance will have “email” and “password” attributes. In addition, we have had to define a zero-argument constructor, however, the Squeryl road-map says that this would not be required from version “0.9.5″ onwards.

Step 3 – Define schema

Squeryl requires us to define a Schema object. Our entity classes (i.e. User) needs to be mapped as tables inside the Schema. Table column properties (unique, index, auto_increment etc.) are also defined here.

package net.srirangan.opensource.squerylexample.schema

import org.squeryl._
import org.squeryl.PrimitiveTypeMode._
import net.srirangan.opensource.squerylexample.entities.User

object Schema extends Schema {

  val users = table[User]

  on(users)(user => declare( is (autoIncremented), is (unique)


In the code above, we map the User entity to the User table and we’ve declared the email column as unique and the id to be auto incremented. Custom table and column names are possible to define through Squeryl annotations provided, check out the Squeryl docs.

Step 4 – Start database session

Needless to mention, we will need an active database session if we are to perform CRUD operations on the database. Here’s how I’ve done it:

  import org.squeryl.Session
  import org.squeryl.SessionFactory
  import org.squeryl.adapters.MySQLAdapter
  val databaseUsername = "squeryl-example"
  val databasePassword = "squeryl-example"
  val databaseConnection = "jdbc:mysql://localhost:3306/squeryl-example"
  def startDatabaseSession():Unit = {
      SessionFactory.concreteFactory = Some(() => Session.create(
          java.sql.DriverManager.getConnection(databaseConnection, databaseUsername, databasePassword),
          new MySQLAdapter)

Step 5 – Generate schema

Once we’ve initialized the database session, we can generate the database schema as shown below:

  import org.squeryl.PrimitiveTypeMode._
  import net.srirangan.opensource.squerylexample.schema.Schema

  transaction {
    println("Created the schema")

Step 6 – Insert and Update

  import net.srirangan.opensource.squerylexample.entities.User

  transaction {
    val user1:User = new User("", "oldPassword")
    println("Inserted user1")
    user1.password = "newPassword"
    println("Updated user1")

Step 7 – Select

    transaction {
      val queriedUser:User = Schema.users.where(user => === 2L).single
      println( + " -- " +

The entire squeryl-example source code is available at

More examples on

Srirangan is a programmer / senior consultant with Inphina Technologies
Blog   GitHub   LinkedIn   Twitter

Categories: Scala Tags: , , ,

Managing Multiple Parent Persistence Problem in App Engine

August 7, 2010 3 comments

We are in process of porting an entire application on google app engine. This application has persistence using JPA and as usual contains mapping between entities using annotations. The mapping like @OnetoMany and @ManytoOne in Jpa may throw lot of errors in google app engine and therefore is neither straightforward nor trivial. In this blog we will see where our mapping may fail, with specific problem related to multiple parent key provider fields. Google app engine may throw this error in our application, we will see how can we manage it with unowned relationships in our application.

We have three entities User, Project and ProjectAssignment. ProjectAssignment entity has @ManyToOne mapping with an User and a Project. In these relationships we also have a bidirectional mapping using @OneToMany relationship from User and Project for ProjectAssignment.
Read more…


Get every new post delivered to your Inbox.

%d bloggers like this: