This is the reference manual for the component named postmodern, which is part of a library of the same name.
Note that this package also exports the
types from CL-postgres, and a few
operators from S-SQL.
execute, and any other function
that would logically need to communicate with the database will
raise a condition of the type
when something goes wrong. As a special case, errors that break
the connection (socket errors, database shutdowns) will be raised
as subtypes of
:reconnect restart to re-try the
operation that encountered to the error.
Objects of this type represent database connections.
connect (database user password host &key (port 5432) pooled-p use-ssl)
Create a new database connection for the given
user and database. Port will default to 5432, which is where most
PostgreSQL server are running. If
pooled-p is true, a
connection will be taken from a pool of connections of this type,
if one is available there, and when the connection is disconnected
it will be put back into this pool instead.
and defaults to the value of
The default for
argument. This starts at
:no. If you set it to
anything else, be sure to also load the CL+SSL
method disconnect (database-connection)
Disconnects a normal database connection, or moves a pooled connection into the pool.
Returns a boolean indicating whether the given connection is still connected to the server.
method reconnect (database-connection)
Reconnect a disconnected database connection. This is not allowed for pooled connections ― after they are disconnected they might be in use by some other process, and should no longer be used.
Special variable holding the current database. Most functions and macros operating on a database assume this contains a connected database.
macro with-connection (spec &body body)
Evaluates the body with
*database* bound to a
connection as specified by
spec, which should be list
connect can be applied
macro call-with-connection (spec thunk)
The functional backend to
*database* to a new connection
as specified by
spec, which should be a list that
connect can be applied to, and
runs the zero-argument function given as second argument in the
new environment. When the function returns or throws, the new
connection is disconnected.
function connect-toplevel (database user password host &key (port 5432))
*database* to a new
connection. Use this if you only need one connection, or if you
want a connection for debugging from the REPL.
function disconnect-toplevel ()
function clear-connection-pool ()
Disconnect and remove all connections in the connection pools.
Set the maximum amount of connections kept in a
single connection pool, where a pool consists of all the
stored connections with the exact same connect arguments. Defaults
NIL, which means there is no maximum.
query (query &rest args/format)
Execute the given query, which can be either a
string or an S-SQL form (list starting
with a keyword). If the query contains placeholders
$2, etc) their values can be given
as extra arguments. If one of these arguments is a keyword
occurring in the table below, it will not be used as a query
argument, but will determine the format in which the results are
returned instead. Any of the following formats can be used, with
the default being
|Ignore the result values.|
|Return a list of lists, each list containing the values for a row.|
|Return a single row as a list.|
|Return a list of alists which map column names to values,with the names represented as keywords.|
|Return a single row as an alist.|
|Return a single row as an alist, with strings for names.|
|Return a list of plists which map column names to values,with the names represented as keywords.|
|Return a single row as a plist.|
|Return a single column as a list.|
|Return a single value.|
|Return a list of DAOs of the given type. The names of the fields returned by the query must match slots in the DAO class the same way as with |
|Return a single DAO of the given type.|
If the database returns information about the amount rows that were affected, such as with updating or deleting queries, this is returned as a second value.
macro execute (query &rest args)
format :none, and returning the amount of
affected rows as its first returned value. (Also returns this
amount as the second returned value, but use of this is
macro doquery (query (&rest names) &body body)
Execute the given query (a string or a list
starting with a keyword), iterating over the rows in the result.
The body will be executed with the values in the row bound to the
symbols given in
names. To iterate over a
parameterised query, one can specify a list whose car is the
query, and whose cdr contains the arguments. For example:
(doquery (:select 'name 'score :from 'scores) (n s) (incf (gethash n *scores*) s)) (doquery ((:select 'name :from 'scores :where (:> 'score '$1)) 100) (name) (print name))
prepare (query &optional (format :rows))
Creates a function that can be used as the
interface to a prepared statement. The given query (either a
string or an S-SQL form) may contain
placeholders, which look like
etc. The resulting function takes one argument for every
placeholder in the query, executes the prepared query, and returns
the result in the format specified (allowed formats are the same
For queries that have to be run very often, especially when they are complex, it may help performance if the server only has to plan them once. See the PostgreSQL manual for details.
In some cases, the server will complain about not
being able to deduce the type of the arguments in a statement. In
that case you should add type declarations (either with the
:: syntax or with S-SQL's
:type construct) to help it
macro defprepared (name query &optional (format :rows))
This is the
defun-style variant of
prepare. It will define a
top-level function for the prepared statement.
macro defprepared-with-names (name (&rest args) (query &rest query-args) &optional (format :rows))
but allows to specify names of the function arguments as well as arguments
supplied to the query.
(defprepared-with-names user-messages (user &key (limit 10)) ("select * from messages where user_id = $1 order by date desc limit $2" (user-id user) limit) :plists)
macro with-transaction ((&optional name) &body body)
Execute the given body within a database transaction, committing it when the body exits normally, and aborting otherwise. An optional name can be given to the transaction, which can be used to force a commit or abort before the body unwinds.
function commit-transaction (transaction)
Commit the given database transaction.
function abort-transaction (transaction)
Roll back the given database transaction.
macro with-savepoint (name &body body)
Can only be used within a transaction. Establishes
a savepoint with the given name at the start of
and binds the same name to a handle for that savepoint. At the end
body, the savepoint is released, unless a
condition is thrown, in which case it is rolled back.
function release-savepoint (savepoint)
Explicitly release the given savepoint.
function rollback-savepoint (transaction)
Roll back the given savepoint.
Get the next value from a sequence. The sequence identifier can be either a string or a symbol, in the latter case it will be converted to a string with S-SQL rules.
coalesce (&rest arguments)
Returns the first non-
:null) argument, or
NIL if none
are present. Useful for providing a fall-back value for the result
of a query, or, when given only one argument, for transforming
list-tables (&optional strings-p)
Returns a list of the tables in the current
strings-p is true, the names will be
given as strings, otherwise as keywords.
Tests whether a table with the given name exists. The name can be either a string or a symbol.
table-description (name &optional schema-name)
Returns a list of the fields in the named table. Each field is represented by a list of three elements: the field name, the type, and a boolean indicating whether the field may be null. Optionally, schema-name can be specified to restrict the result to fields from the named schema. Without it, all fields in the table are returned, regardless of their schema.
list-sequences (&optional strings-p)
Returns a list of the sequences in the current
strings-p is true, the names will be
given as strings, otherwise as keywords.
Tests whether a sequence with the given name exists. The name can be either a string or a symbol.
list-views (&optional strings-p)
Returns a list of the views in the current
strings-p is true, the names will be
given as strings, otherwise as keywords.
Tests whether a view with the given name exists. The name can be either a string or a symbol.
Postmodern contains a simple system for defining CLOS classes that represent rows in the database. This is not intended as as a full-fledged object-relational magic system ― while serious ORM systems have their place, they are notoriously hard to get right, and are outside of the scope of a humble SQL library like this.
At the heart of Postmodern's DAO system is the
dao-class metaclass. It allows you to define classes
for your database-access objects as regular CLOS classes. Some of
the slots in these classes will refer to columns in the database.
To specify that a slot refers to a column, give it a
:col-type option containing an S-SQL type expression (useful if you want to
be able to derive a table definition from the class definition),
or simply a
:column option with value
Such slots can also take a
:col-default option, used
to provide a database-side default value as an S-SQL
DAO class definitions support two extra class
:table-name to give the name of the table
that the class refers to (defaults to the class name), and
:keys to provide a set of primary keys for the table.
When no primary keys are defined, operations such as
get-dao will not work.
(defclass user () ((name :col-type string :initarg :name :accessor user-name) (creditcard :col-type (or db-null integer) :initarg :card :col-default :null) (score :col-type bigint :col-default 0 :accessor user-score)) (:metaclass dao-class) (:keys name))
(or db-null integer) form is used
to indicate a column can have NULL values.
When inheriting from DAO classes, a subclass' set
of columns also contains all the columns in its superclasses. The
primary key for such a class is the union of its own keys and all
the keys from its superclasses. Classes inheriting from DAO
classes should probably always use the
When a DAO is created with
make-instance, it can be passed a
:fetch-defaults keyword parameter which, when true,
will cause a query to be made to fetch the default values for all
slots that have column default values and were not bound through
initargs. In some cases, such as
which have an implicit default, this will not work. You can work
around this by creating your own sequence and defining a
(:nextval "my_sequence") default.
Finally, DAO class slots can have an option
:ghost t to specify them as ghost slots. These are
selected when retrieving instances, but not written when updating
or inserting, or even included in the table definition. The only
know use for this to date is to create your table with
(oids=true), and specify a slot like this:
(oid :col-type integer :ghost t :accessor get-oid)
Returns list of slot names that are the primary key of DAO class CLASS.
Returns list of values that are the primary key of DAO.
Test whether a row with the same primary key as
the given DAO exists in the database. Will also return
NIL when any of the key slots in the object are
make-dao (type &rest args &key &allow-other-keys)
insert-dao. Return the
macro define-dao-finalization (((dao-name class) &rest keyword-args) &body body)
body is executed in
a lexical environment where variable
dao-name is bound
to a freshly created and inserted DAO. The representation of the DAO in the
database is then updated to reflect changes that
have introduced. Useful for processing values of slots with the type
serial, which are unknown before
get-dao (type &rest keys)
Select the DAO object from the row that has the
given primary key values, or
NIL if no such row
exists. Objects created by this function will have
initialize-instance called on them (after loading in
the values from the database) without any arguments ― even
:default-initargs are skipped. The same goes for
select-dao (type &optional (test t) &rest sort)
Select DAO objects for the rows in the associated table for which the given test (either an S-SQL expression or a string) holds. When sorting arguments are given, which can also be S-SQL forms or strings, these are used to sort the result. (Note that, if you want to sort, you have to pass a test value.)
(select-dao 'user (:> 'score 10000) 'name)
query-dao (type query &rest args)
Execute the given query (which can be either a
string or an S-SQL expression) and return
the result as DAOs of the given type. If the query contains placeholders
($1, $2, etc) their values can be given as extra arguments.
The names of the fields returned by the query must either match slots
in the DAO class, or be bound through
query-dao finds a column
in the database that's not in the DAO class, it will raise an
error. Setting this variable to a truthy value will cause it to
simply ignore the unknown column.
Insert the given DAO into the database. When any column slots in the object are unbound, these will be updated with the values they default to in the database. (If they have no defaults, it is an error to insert them.) Note: This feature only works on PostgreSQL 8.2 and up. On older versions, do not insert DAOs with unbound slots.
Update the representation of the given DAO in the database with the values in the object. This is not defined for tables that do not have any non-primary-key columns. Raises an error when no row matching the DAO exists.
Tries to insert the given DAO using
insert-dao. If this raises a
unique key violation error, it tries to update it using
update-dao instead. Be aware
that there is a possible race condition here ― if some
other process deletes the row at just the right moment, the update
fails as well. Returns a boolean telling you whether a new row was
This function is unsafe to use inside of a
transaction ― when a row with the given keys already
exists, the transaction will be abandoned. Use
instead in such a situation.
Acts exactly like
save-dao, except that it
protects its attempt to insert the object with a rollback point,
so that a failure will not abort the transaction.
but using a different method that doesn't involve a database
exception. This is safe to use both in and outside a transaction,
though it's advisable to always do it in a transaction to prevent a
race condition. The way it works is:
insert-daodirectly, thus the behavior is like
The race condition might occur at step 3 if there's no
update returns zero and another
thread inserts the record at that moment, our insertion will fail.
This method returns two values: the DAO object and a boolean
T if the object was inserted,
it was updated).
method delete-dao (dao)
Delete the given DAO from the database.
Get the table name associated with the given DAO class (or symbol naming such a class).
Given a DAO class, or the name of one, this will produce an SQL query string with a definition of the table. This is just the bare simple definition, so if you need any extra indices or or constraints, you'll have to write your own queries to add them.
macro with-column-writers ((&rest writers) &body body)
Provides control over the way
query-dao read values from the
database. This is not commonly needed, but can be used to reduce
the amount of queries a system makes.
be a list of alternating column names (strings or symbols) and
writers, where writers are either symbols referring to a slot in
the objects, or functions taking two arguments ― an
instance and a value ― which can be used to somehow store
the value in the new instance. When any DAO-fetching function is
called in the body, and columns matching the given names are
encountered in the result, the writers are used instead of the
default behaviour (try and store the value in the slot that
matches the column name).
A common use for this is to add some non-column
slots to a DAO class, and use
query-dao within a
with-column-writers form to pull in extra information
about the objects, and immediately store it in the new
It can be useful to have the SQL statements needed to build an application's tables available from the code, to do things like automatically initialising a database. The following macro and functions allow you to group sets of SQL statements under symbols, with some shortcuts for common elements in table definitions.
macro deftable (name &body definition)
Define a table. name can be either a symbol or a
(symbol string) list. In the first case, the table
name is derived from the symbol by S-SQL's rules, in the second case, the name
is given explicitly. The body of definitions can contain anything
that evaluates to a string, as well as S-SQL expressions. In this
body, the variables
*table-symbol* are bound to
the relevant values. Note that the definitions are evaluated in
order, so you'll generally want to first create your table and
then start defining indices on it.
function create-table (symbol)
Creates the table identified by
symbol by executing the result
of all the forms in its definition.
function create-all-tables ()
Creates all defined tables.
function create-package-tables (package)
Creates all tables whose identifying symbol is interned in the given package.
variables *table-name*, *table-symbol*
These are bound to the relevant symbol and name while the clauses of a table definition are evaluated. Can be used to define shorthands like the ones below.
function !dao-def ()
Should only be used inside
deftable forms. Adds the result
function !index (&rest columns), !unique-index (&rest columns)
Define an index on the table being defined. The columns can be given as symbols or strings.
function !foreign (target-table columns &optional target-columns &key on-delete on-update deferrable initially-deferred)
Add a foreign key to the table being defined.
target-table is the table the index refers to,
columns is a list of column names or single name in
this table, and, if the columns have different names in
the table referred to,
target-columns should be
another list of names or single name for the target table, or
:primary-key to indicate that the primary key of the
target table should be referenced.
on-update arguments can be used to specify ON DELETE
and ON UPDATE actions, as per the keywords allowed in
addition, the deferrable and initially-deferred arguments can be
used to indicate whether constraint checking can be deferred until
the current transaction is completed, and whether this should be
done by default. Note that none of these are really &key
arguments, but rather are picked out of a &rest arg at
runtime, so that they can be specified even when
target-columns is not given.
function !unique (target-fields &key deferrable initially-deferred)
Constrains one or more columns to only contain
unique (combinations of) values, with deferrable and
initially-deferred defined as in