Lift

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

• 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 (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 = {
    	inTransaction{
    	    f
    	}
    }
})

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

• 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 (see the lift book for details). 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 (such as create schema, create row, update, select) have to be executed in the context of a transaction.

• If you use squeryl’s session management, you have to use transaction or inTransaction (see squeryl docs).
• 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 (Michael Gottschalk).

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 (and a custom field type), 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 (i.e OptionalStringField, OptionalBooleanField, etc) or an IntField. To get around this you can use the ~is syntax:

  on(table)(t => declare(t.field.~is(indexed)))

  
  Bug Report (fixed in 2.3-SNAPSHOT → you can now use “defineAs” instead of the ambiguous “is”)

• When using DecimalField, you need to use a @Column annotation, in order to have length and scale set properly, as there is currently no bypass, that’ll get these metadata directly from field (as is the case of StringField). Failing to do so will cause, that Squeryl will use default definition numeric(20,16):
  

  
  val field1 = DecimalField(this, new MathContext(13),2 )  //this will result to "field1 numeric(20,16)" DDL
  @Column(length=13, scale=2)
  val field2 = DecimalField(this, new MathContext(13),2 )  //this will result to "field numeric(13,2)" DDL