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.
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 :
net.liftweb
lift-squeryl-record
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.
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.
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.
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.
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.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.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.
All operations that need a database connection have to be executed in the context of a transaction.
transaction
or inTransaction
.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.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.val company = MySchema.companies.lookup(id).get
company.name("New Name")
company.postCode("11111")
MySchema.companies.update(company)
update(MySchema.companies)(c => where (c.id === id)
set (c.name := "Name2"))
See the squeryl docs for more information about inserts, updates and deletes.val company = from(MySchema.companies)(c =>
where(c.name === "Test Company") select (c))
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.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.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:
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)))
object field extends StringField(this, 128)
val field = new StringField(this, 128)