CLSQL & Webapps

A commonly experienced error when using CLSQL in a web environment is database connections conflicting with each other from simultaneous web requests. These problems arise because, by default, clsql standard-db-objects keep a reference to the connection they were queried / created from and reuse this database connection (rather than a new one you may have provided with clsql-sys:with-database). This means that two separate threads could try to use the same database connection (provided through clsql-sys:with-database or by having objects queried from the same connection accessed in multiple threads / http requests).

We solved this problem by introducing a clsql-sys::choose-database-for-instance method (available in clsql master branch from http://git.b9.com/clsql.git. (This branch will eventually be released as CLSQL6) Then in our web applications we define the following class and method override. Usually I then pass this name to clsql-orm or as a direct superclass to any of my web def-view-classes. After this, I just use with-database to establish dynamic connection bindings and everything pretty much works out (as these dynamic bindings are not shared across threads).

(defclass clsql::web-db-obj (clsql-sys:standard-db-object)
    nil
    (:metaclass clsql-sys::standard-db-class))

(defmethod clsql-sys::choose-database-for-instance
    ((object clsql::web-db-obj) &optional database)
  (or database clsql-sys:*default-database*))

(clsql-sys:def-view-class table-1 (clsql::web-db-obj)
    (...))

(clsql-orm:gen-view-classes
 :package :net.company.my.db
 :nicknames :my-db
 :export-symbols t
 :classes '(users employees salaries)
 :inherits-from '(clsql::web-db-obj))

Note: CLSQL-Fluid seems to be trying to accomplish much the same goals.

4 thoughts on “CLSQL & Webapps

  1. This a helpful article. Thanks for the distillation. Can you also show the with-database call you would use? Just wondering if you use a macro to get defaults for the with-database call so as not to have to specify the lambda list for with-database everytime? Thanks!

  2. I should add that for our purposes it would seem the only real benefit of with-database use with objects is to ensure the close of the database connection, correct?

  3. The only real benefit of using with-database is to ensure that database connections are closed (or released to the pool) when they are done being used. It also sets a dynamic variable such that some one high on the stack can create database connections to be used by someone much further down the stack without needing to thread that connection through the parameters of each intermediary function.

    We tend to use a macro we wrote with-a-database that says if *default-database* is set, call the body, otherwise call the body inside of with-database. This allows any toplevel function to create a database to be used by all enclosed functions and each of those functions to also allocate a connection if it doesnt receive one. This means each function that needs a database connection handles that itself and we never open more than one database connection to the same database from the same thread.

    see clsql-helper/connections.lisp for an example of this approach.

    We also tend to have application specific “with-a-database” macros that default the parameters of which database to talk to.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>