Squeryl

Overview

Squeryl is an ORM framework for scala. Queries can be written in a DSL that is close to SQL, but completely type-safe.

If you just want to use Squeryl with Lift you do not need the lift-squeryl-record module as the Lift framework is persistence agnostic. However, if you are interested in some of the features that Lift’s Record framework provides, such as CRUD support, validation and JSON then the lift-squeryl-record module can be used to tie the two technologies together. The module allows you to define your database entities as Record objects using the standard Fields types while still using Squeryl to maintain your schema and interact with your database.

Using Squeryl-Record

Squeryl-Record is its own module within the lift repository. For this reason, you need to include the following additional artifact in your project definition (if you use maven or sbt):

Each release of lift-squeryl-record depends on a specific version of Squeryl. When adding the lift-squeryl-record dependency please do not add an explicit dependency on Squeryl as well. The proper version will automatically be retrieved as a transitive dependency.

Initialization

When working with Squeryl, all database access needs to occur within a transaction. There are currently two supported transaction strategies: the native Squeryl method and the DB.use syntax that users of Mapper area already familiar with. It is highly recommended that users avoid DB.use transactions when using lift-squeryl-record as there are several known bugs and DB.use support may be deprecated.

Example using Squeryl’s session management (Recommended):
import net.liftweb.squerylrecord.SquerylRecord
import org.squeryl.Session
import org.squeryl.adapters.H2Adapter

SquerylRecord.initWithSquerylSession(Session.create(
        DriverManager.getConnection("jdbc:h2:mem:dbname;DB_CLOSE_DELAY=-1", "sa", ""),
        new H2Adapter))

(for the complete example, see the squeryl-record unit test)

The squeryl docs contain more information about session management. Please note that, unlike Mapper, Squeryl does not provide connection pooling “out of the box”. You are free to use any connection pool provider that you like and some popular choices are DBCP, C3p0 and BoneCP. Once you have created and configured your provider, simply replace the DriverManager.getConnection(…..) statement above, which will create a new connection each time it is invoked, with a connection retrieved from your pool.

If you’d like to have a transaction that spans the entire request, similar to Lifts DB.buildLoanWrapper you can use the following code as a template. Add it to your Boot class.

import net.liftweb.http.S
import net.liftweb.util.LoanWrapper
import net.liftweb.squerylrecord.RecordTypeMode._
   
S.addAround(new LoanWrapper {
  override def apply[T](f: => T): T = {
    val resultOrExcept = inTransaction {
      try {
        Right(f)
      } catch {
        case e: LiftFlowOfControlException => Left(e)
      }
    }

    resultOrExcept match {
      case Right(result) => result
      case Left(except) => throw except
    }
  }
})

(See Kevin Locke’s blog for details of this style of loan wrapper)

Example using lift’s session management:
import net.liftweb.mapper.{DB, DefaultConnectionIdentifier, StandardDBVendor}
import net.liftweb.squerylrecord.SquerylRecord
import org.squeryl.adapters.H2Adapter

val dbVendor = new StandardDBVendor("org.h2.Driver", "jdbc:h2:test", Empty, Empty)
DB.defineConnectionManager(DefaultConnectionIdentifier, dbVendor)
SquerylRecord.init(() => new H2Adapter)

(for the complete example, see the simple example application)
As you can see, apart from initializing the DB class, you only have to tell squeryl which database adapter should be used. Squeryl needs to know this for choosing the correct SQL dialect. Currently supported databases are Oracle, PostgreSQL, MySQL, H2, and DB2.

Defining the Mapped Classes

Defining the Schema

The “is” function is ambiguous for records, so from version 2.3-SNAPSHOT on, you can use “defineAs” instead. Until then, you can also use “.~.is” if there is a compiler error.

Transactions

All operations that need a database connection (such as create schema, create row, update, select) have to be executed in the context of a transaction.

Insert

Update

Select

Examples

There is not yet a comprehensive example using squeryl with lift.
I’m currently working on integrating something into the lift example application. Moreover, I want to create a more comprehensive example application, also using MegaProtoUser if possible (Michael Gottschalk).

In the meantime, the following resources should be helpful:

Issues and Work Arounds