CLSQL
CLSQL is a Database interface that can access a number of different SQL database engines. It also includes a CLOS ORM. It is maintained by Kevin Rosenberg.

CLSQL now includes the CommonSQL API interface. CLSQL's manual is online at http://clsql.kpe.io/documentation.html.

CLSQL uses the UFFI interface so it works on:

  • SBCL
  • CMUCL
  • OpenMCL
  • AllegroCL
  • Lispworks
  • SCL
  • CLISP
  • ECL

CLSQL supports a number of database interfaces:

  • PostgreSQL C library API
  • PostgreSQL Socket API
  • MySQL
  • SQLite 2
  • SQLite 3
  • Oracle
  • ODBC (On all platforms)
  • AODBC (Allegro's ODBC database interface)

Not all features are supported on all DBMSes. For example, CLSQL's OODDL does not support :auto-increment on sqlite3.

There are CLSQL packages in the Debian and Gentoo distributions. CLSQL is covered by the LGPL license.
CLSQL can be downloaded from its web site.

Additional Documentation

Bill Clementson has written two entries about CLSQL, in his web log:

  • CL-SQL - Part 1 - discussing some of the advantages of CLSQL and how to install it.
  • CL-SQL - Part 2 - an interactive, annotated session using CLSQL, illustrating some of the characteristics of working with CLSQL

To quote his Part 1:

If you would like to see some more examples of how to use CLSQL, the CLSQL tutorial is quite useful. Also, the tremendous CommonSQL tutorial that Nick Levine gave at ILC2002 is an excellent introduction to CLSQL. Even though his tutorial is targetted towards LispWorks CommonSQL, the examples work equally well in CLSQL (with the occasional minor tweak).

Looping over query result

Iterate supports looping over CLSQL queries through the use of iterate-clsql extension.

Lispbox workaround

You may get an error claiming that CLSQL can't find "foreign libraries." Find the directory where those files are located, and use clsql:push-library-path. They will likely be wherever CLSQL was installed, for example:

(clsql:push-library-path #p"/home/myusername/.sbcl/site/clsql-3.5.6/db-mysql/") ;installed here by asdf-install

This may also occur with OpenMCL; same solution.

Multithreaded usage

This is not obvious from the documentation, but just using CONNECT gives you a single database connection - if you try to use that connection in multiple threads, you will get nasty race conditions. One convenient way to use CLSQL with multiple threads is to use the WITH-DATABASE macro around any code that talks to the database. Using connection pools, this is quite efficient, and thread-safe.