A number of backwards-incompatible changes are introduced in this version of the library ― there are a few small cleanups, and the database-access object system has been completely overhauled.
with-connection form has been replaced by
what used to be called
This means that if you had code like this...
(with-connection ("my-db" "harry" "****" "localhost") ...)
... you should change it to ...
(with-connection (list "my-db" "harry" "****" "localhost") ...)
... since the whole list is now evaluated. Similarly, if you
with-connection*, you should remove the
CL-postgres now exports ways
to manipulate the way it reads values from query results. The
s-sql:sql-ize generic has been moved to
and can be used to control the way values are written out when
passed as arguments to prepared statements or inserted in S-SQL query forms.
simple-date is no
longer depended on by CL-postgres and S-SQL, but uses the above
interface to integrate itself. Load it after loading
CL-postgres, and suitable readers and writers for its types will
be registered. Integrating other date/time libraries is
In previous versions, only the
database-connection-lost conditions offered a
:reconnect restart. There are now various conditions
offering this restart, all subtypes of
and the library tries its very best to wrap all hard-to-prevent
errors with such a restart (socket errors, database shutdowns).
The goal is that you can use this feature to cleanly and simply
add functionality for recovering from connectivity problems and
server restarts. If you still have issues here, please discuss
them on the mailing list (universal error recovery is rather hard
There is now also a large set of condition types exported from
cl-postgres-error package, which can make writing
handler-case forms around database code a lot more
cl-postgres/error.lisp for the list (or
just cause the error you are interested in to be raised, and look
at its type).
This is where upgrading might be somewhat painful. The old
deftable macro has been dropped completely, in favour
metaclass. The focus of this part of the library has shifted from
defining tables to defining access objects. You
can still generate simple CREATE TABLE statements using the
function, but this is intended to just be a shortcut. Table
definition is now the responsibility of the library user, not the
So why this regression in functionality? It turned out that
coupling access objects and table definitions like this was not
such a good idea. You might want to create access objects for
views, or for tables with all kinds of complicated constraints.
Adding support for this to
deftable would have turned
it into an even bigger behemoth than it already was, and not
fundamentally solve the problem.
So now we have a nice, clean DAO interface, and no
schema-definition interface at all (
and friends were also dropped). The most notable change is
probably that the
:auto-id option is gone. This was
very convenient but horribly 'magical'. If you had something like
(deftable product () ((name :type string :initarg :name :accessor product-name) (weight :type float :initarg :weight :accessor product-weight)) (:class-name product) (:auto-id t) (:indices (:unique name))) (defun create-tables () ; ... (create-table 'product))
The equivalent could look like this:
(defclass product () ((id :col-type serial :initarg :id :accessor product-id) (name :col-type string :initarg :name :accessor product-name) (weight :col-type float :initarg :weight :accessor product-weight)) (:keys id) (:metaclass dao-class)) (defun create-tables () ; ... (execute (dao-table-definition 'product)) (execute (:create-unique-index 'product-name :on 'product :fields 'name)))
Or you could explicitly create the id sequence and give the
id field a
(:nextval "product_ids"), to have more control over
the id generation.
The above example should give you a basic idea of the new
interface: DAO classes are now created by regular class
definitions. Instead of
:type options, column slots
The semantics of creating and inserting DAOs have been slightly
adjusted: There is no magic happening when you create a DAO
instance (it used to fetch id values), except when you give
argument, in which case it will query the database for the rows'
default values, and put them in the instance. Usually, it is
cleaner to not use this, since it generates extra queries and does
not work for stuff like
serial fields anyway, where no
:col-default can be given. When an object is
inserted into the database with
slots may be unbound. These will then, both in the database and in
the object, be assigned values based on the column defaults. For
example, if you have the above
(defvar *p* (make-instance 'product :name "brick" :weight 2)) ;; The id slot is unbound (insert-dao *p*) (print (product-id *p*)) ;; Here it will have received a new id value
Note that this works even for
serial types, since
the defaults are fetched by giving the INSERT statement a
RETURNING clause, so the association between default values and
columns is handled by the database, not the DAO class.