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 :

Group-ID: net.liftweb
Artifact-ID: lift-squeryl-record
Version: squeryl-record is available since Lift 2.1, but you should use the latest Lift version when possible

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 :

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))

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
    }
  }
})

Depending on the use case you might want to append your loan wrapper to LiftRules.allAround. It’s quite useful in case you want to wrap entire request into a database session.

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)

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

Example:

import net.liftweb.record.{MetaRecord, Record}
import net.liftweb.record.field._
import net.liftweb.squerylrecord.KeyedRecord
import org.squeryl.annotations.Column

class Company private() extends Record[Company] with KeyedRecord[Long] {
  
  override def meta = Company
  
  @Column(name="id")
  override val idField = new LongField(this)
  
  val name = new StringField(this, "")
  val description = new OptionalTextareaField(this, 1000)
  val country = new CountryField(this)
  val postCode = new PostalCodeField(this, country)
  val created = new DateTimeField(this)
}
object Company extends Company with MetaRecord[Company]

The mapped classes are defined similar to normal lift records . You can use all field types available in record.
The KeyedRecord trait comes from squeryl-record, however. It tells squeryl that there should be an auto incremented ID field in the record. The field must be named idField, but you can define the column name in the database using the @Column annotation.

Defining the Schema

Example:

import org.squeryl.Schema
import net.liftweb.squerylrecord.RecordTypeMode._

object MySchema extends Schema {
  val companies = table[Company]
  val employees = table[Employee]
  
  val companyToEmployees =
      oneToManyRelation(companies, employees).via((c,e) => c.id === e.companyId)
  
  on(employees)( e =>
    declare(e.companyId is(indexed("idx_employee_companyId")))
  )
}

The schema is defined just like it would be done in normal squeryl. As you can see, you can define tables, relationships between tables and indexes, for example. RecordTypeMode._ has to be imported for getting access to certain implicit conversions.

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 have to be executed in the context of a transaction.

If you use squeryl’s session management, you have to use transaction or inTransaction .
If you use lift’s transaction management, you have to use DB.use for defining transaction boundaries. Alternatively, you can use DB.buildLoanWrapper in S.around for always setting the transaction boundary around the whole http request.

Insert

Example:
```
import net.liftweb.squerylrecord.RecordTypeMode._

val company = Company.createRecord.name("First Company USA").
	created(Calendar.getInstance()).
	country(Countries.USA).postCode("12345")
MySchema.companies.insert(company)
```
You have to create the records using createRecord instead of using the constructor. This is needed for proper initialization. Then just use the insert method just as you would normally do in squeryl. However, instead of importing PrimitiveTypeMode._ from squeryl, you have to import RecordTypeMode._ so that implicit conversions for record fields are picked up automatically.

Update

Example for updating a complete object:

val company = MySchema.companies.lookup(id).get
company.name("New Name")
company.postCode("11111")
MySchema.companies.update(company)

Example for a partial update:
```
update(MySchema.companies)(c => where (c.id === id)
  set (c.name := "Name2"))
```
See the squeryl docs for more information about inserts, updates and deletes.

Select

Simple Select Example:

val company = from(MySchema.companies)(c =>
          where(c.name === "Test Company") select (c))

Select with specified column:

val companyPostCode = from(MySchema.companies)(c =>
          where(c.name === "Test Company") select (c.postCode.is))

Note that currently is needs to be used for specified column.

Select with Join:
```
val companiesWithEmployees = from(MySchema.companies, MySchema.employees)((c, e) =>
  where(c.id === e.id) select ((c, e)))
```
For details about selecting data from the database, see the squeryl docs. Just keep in mind that you have to import RecordTypeMode._ when using squeryl-record.

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 .

In the meantime, the following resources should be helpful:

A very small example application using Squeryl-Record
- Only includes a main function that initializes a database with an example schema and performs some queries
Unit Tests for Squeryl-Record
- The unit tests demonstrate how to create a schema with almost all standard field types , how to initialize the database and how to do simple queries
Lift Mailing List
- If you have any questions concerning Squeryl-Record, feel free to ask on the mailing list. For questions concerning only squeryl, please use the Squeryl Mailing List.

Issues and Work Arounds

The typical syntax for declaring an index
```
on(table)(t => declare(t.field is indexed))
```
does not currently work unless t.field is an OptionalTypedField or an IntField. To get around this you can use the ~is syntax:
```
on(table)(t => declare(t.field.~is(indexed)))
```
Bug Report

SquerylRecord currently uses Squeryl’s method of determining fields, and unfortunately Squeryl does not see definitions like:
```
object field extends StringField(this, 128)
```
as valid. Instead, you must define your fields as vars:
```
val field = new StringField(this, 128)
```