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.

    <dependency>
      <groupId>org.squeryl</groupId>
      <artifactId>squeryl_2.8.1</artifactId>
      <version>0.9.4-RC6</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.15</version>
    </dependency>

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(
      user.id is (autoIncremented),
      user.email 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 = {
    Class.forName("com.mysql.jdbc.Driver")
      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

  startDatabaseSession()

  transaction {
    Schema.create
    println("Created the schema")
  }

Step 6 – Insert and Update

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

  transaction {
    val user1:User = new User("user1@domain.com", "oldPassword")
    Schema.users.insert(user1)
    println("Inserted user1")

    user1.password = "newPassword"
    Schema.users.update(user1)
    println("Updated user1")
  }

Step 7 – Select

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

The entire squeryl-example source code is available at github.com/Srirangan/squeryl-example

More examples on Squeryl.org.