For several years, the best (and only) option for portable access to relational databases from Common Lisp has been the CLSQL library, a free reimplementation of LispWorks' CommonSQL interface with support for many backend databases, a lispy SQL abstraction as well as simple object-relational mapping support.
Recently, several alternatives have emerged: Postmodern provides particularly good support tailored to PostgreSQL only, other libraries abstract away from relational databases entirely, and finally, there are two new stars on the library horizon: CL-RDBMS and CL-PEREC.
Having used CLSQL for several years, I am currently investigating a switch to CL-RDBMS and PEREC for a code base of non-trivial size, aiming to improve scalability, portability across databases, and last not least readability of the code base.
And I am very happy with everything I have found so far:
In the CL-RDBMS/PEREC ecosystem, work is split into two layers.
- CL-RDBMS forms the lower layer. It abstracts over database access libraries (currently with support for PostgreSQL, Oracle, and SQLite) and defines an optional, Lispy, extensible syntax for SQL for data definition and query statements.
- The optional PEREC layer is the one which really shines. It is an object-relational mapping (ORM) seamlessly integrating CLOS classes with database tables.
Highlights:
- Sensible caching model: Configurable caching of slots within a transaction, but not between transactions.
- Object identity preserved (within a transaction).
- Protection against accidental cross-transaction access.
- Lispy SELECT statements using code that looks like ordinary CLOS use, but which usually compiles down to efficient database-side SQL statements.
Overall, an architecture that is ideal for robust, scalable setups with multiple threads and processes without risk of the "oversold concert tickets" situation reported for a popular Lisp web site based on older ORM software a few years ago...
The only fly in the ointment for me was the lack of introductory material. PEREC comes with an extensive test suite and nice examples, but I would have wished for a few more hints to get me started.
Thanks to fellow blogger pinterface, my wish has been granted. He has written a blog series about PEREC with many details about getting set up, and including various customization tips.
Articles in the series:
- Getting Started with cl-perec
- Persisting Simple Types with cl-perec
- Sensible Serializing with cl-perec
- Peering Down the Rabbit Hole with cl-perec
PEREC is still evolving, but the tips presented are definitely helpful for PEREC as implemented today. Also check the comments on the blog articles, written by PEREC's author!
