Postmodern is a Common Lisp library for interacting with PostgreSQL databases. Features are:
The biggest differences between this library and CLSQL/CommonSQL are that Postmodern has no intention of being portable across different SQL implementations (it embraces non-standard Postgres features), and approaches extensions like lispy SQL and database access objects in a quite different way. This library was written because the CLSQL approach did not really work for me, your mileage may vary.
02-02-2011: Version
1.17: Another modest maintenance release.
Fixes day-of-week
in simple-date. Makes
the :plist query
format actually work.
Adds sql-escape
as an exported symbol for client code that needs to escape stuff.
Adds support for multi-dimensional arrays. Adds
the *ignore-unknown-columns*
configuration variable.
02-04-2010: Version
1.16: Introduces a save-dao/transaction,
because the old semantics were broken (originally inside of
transactions, after fixing that outside of them). Add support for
passing vectors as argument to prepared queries, and reading them
from query results. Add :on-update and
:on-delete arguments to !foreign. Add
:plist and :plists query result formats.
Guarantee that deftable definitions
are executed in the order they were defined in. Moves the ieee-floats
and trivial-utf-8
dependencies into the repository, so they don't have to separately
fetched.
02-04-2010: We are moving from common-lisp.net to marijnhaverbeke.nl, and from darcs to git. New project home: http://marijnhaverbeke.nl/postmodern.
01-03-2009: Version
1.15: Almost a year since the last release. Postmodern is
still alive—it is just so perfect it hardly needs updates
anymore. In this release: Stop depending on the usocket library in
Allegro CL and SBCL, just use the built-in socket bindings
instead. Allow connecting over a Unix socket in SBCL. Support
natural joins in :select. Add :if-exists
argument to :drop-... operators, add support for
:add-column form to :alter-table. Add
:between-symmetric operator. Introduce "ghost slot"
hack to DAO system to support fetching OIDs. Extend
doquery to also handle parameterised queries.
07-03-2009: Version
1.14: Some more syntax supported in S-SQL, add *silently-truncate-rationals*
and *query-callback*,
export dao-keys, and fix
some small bugs.
28-08-2008: Version
1.13: A few small fixes and optimisations, and there is once
again a deftable ―
though it has a different role than it used to have.
30-04-2008: Version
1.12: Restores the save-dao function
(though you should still be careful with it), adds support for SSL
connections, makes some error messages clearer, adds some S-SQL operators, and fixes a few bugs.
09-04-2008: Version
1.11: Fixes bugs, most importantly one that caused CL-postgres to blow up whenever a connection
parameter was changed for an open connection. Also adds automatic escaping of
reserved words to S-SQL, a *max-pool-size*
parameter and a coalesce function
to Postmodern
19-03-2008: Version 1.10: Note that this release is not entirely backwards-compatible. It introduces a new, more flexible approach to DAO objects, a more pluggable handling of custom data types, and a bunch of small clean-ups, optimizations, and enhancements. See the migration guide for details.
The library depends on usocket (except on SBCL and ACL, where the built-in socket library is used), md5, closer-mop, bordeaux-threads if you want thread-safe connection pools, and CL+SSL when SSL connections are needed.
Postmodern itself is split into four different packages, some of which can be used independently. Simple-date is a very basic implementation of date and time objects, used to support storing and retrieving time-related SQL types. CL-postgres is the low-level library used for interfacing with a PostgreSQL server over a socket. S-SQL is used to compile s-expressions to strings of SQL code, escaping any Lisp values inside, and doing as much as possible of the work at compile time. Finally, Postmodern itself is the library that tries to put all these things together into a convenient programming interface.
Postmodern is released under a zlib-style license. Which approximately means you can use the code in whatever way you like, except for passing it off as your own or releasing a modified version without indication that it is not the original.
The latest release of Postmodern can be downloaded from http://marijnhaverbeke.nl/postmodern/postmodern.tgz, or installed with asdf-install.
A git repository with the most recent changes can be checked out with:
> git clone http://marijnhaverbeke.nl/git/postmodern
You can also view the repository on github.
The file http://marijnhaverbeke.nl/postmodern/postmodern-latest.tgz always contains a snapshot of the current repository head.
The postmodern-devel mailing list can be used for questions, discussion, bug-reports, patches, or anything else relating to this library. Or mail the author/maintainer directly: Marijn Haverbeke.
This quickstart is intended to give you a feel of the way coding with Postmodern works. Further details about the workings of the library can be found in the reference manual.
Assuming you have already installed it, first load and use the system:
(asdf:oos 'asdf:load-op :postmodern) (use-package :postmodern)
If you have a PostgreSQL server running on localhost, with a database called 'testdb' on it, which is accessible for user 'foucault' with password 'surveiller', you can connect like this:
(connect-toplevel "testdb" "foucault" "surveiller" "localhost")
Which will establish a connection to be used by all code,
except for that wrapped in a with-connection
form, which takes the same arguments but only establishes the
connection locally.
Now for a basic sanity test:
(query "select 22, 'Folie et déraison', 4.5") ;; => ((22 "Folie et déraison" 9/2))
That should work. query is the basic way to send queries to the database. The same query can be expressed like this:
(query (:select 22 "Folie et déraison" 4.5)) ;; => ((22 "Folie et déraison" 9/2))
In many contexts, query strings and lists starting with keywords can be used interchangeably. The lists will be compiled to SQL. The S-SQL manual describes the syntax used by these expressions. Lisp values occuring in them are automatically escaped. In the above query, only constant values are used, but it is possible to transparently use run-time values as well:
(defun database-powered-addition (a b) (query (:select (:+ a b)) :single)) (database-powered-addition 1030 204) ;; => 1234
That last argument, :single, indicates that we
want the result not as a list of lists (for the result rows), but
as a single value, since we know that we are only selecting one
value. Some other options are :rows,
:row, :column, :alists, and
:none. Their precise effect is documented in the reference manual.
You do not have to pull in the whole result of a query at once,
you can also iterate over it with the doquery macro:
(doquery (:select 'x 'y :from 'some-imaginary-table) (x y) (format t "On this row, x = ~A and y = ~A.~%" x y))
This is what a database-access class looks like:
(defclass country ()
((name :col-type string :initarg :name
:reader country-name)
(inhabitants :col-type integer :initarg :inhabitants
:accessor country-inhabitants)
(sovereign :col-type (or db-null string) :initarg :sovereign
:accessor country-sovereign))
(:metaclass dao-class)
(:keys name))
The above defines a class that can be used to handle records in a table with three columns: name, inhabitants, and sovereign. In simple cases, the information above is enough to define the table as well:
(dao-table-definition 'country) ;; => "CREATE TABLE country ( ;; name TEXT NOT NULL, ;; inhabitants INTEGER NOT NULL, ;; sovereign TEXT, ;; PRIMARY KEY (name))" (execute (dao-table-definition 'country))
This defines our table in the database. execute works like query, but does not expect any results back.
Let us add a few countries:
(insert-dao (make-instance 'country :name "The Netherlands"
:inhabitants 16400000
:sovereign "Beatrix"))
(insert-dao (make-instance 'country :name "Croatia"
:inhabitants 4400000))
Then, to update Croatia's population, we could do this:
(let ((croatia (get-dao 'country "Croatia")))
(setf (country-inhabitants croatia) 4500000)
(update-dao croatia))
(query (:select '* :from 'country))
;; => (("The Netherlands" 16400000 "Beatrix")
;; ("Croatia" 4500000 :NULL))
Next, to demonstrate a bit more of the S-SQL syntax, here is
the query the utility function list-tables
uses to get a list of the tables in a database:
(sql (:select 'relname :from 'pg-catalog.pg-class
:inner-join 'pg-catalog.pg-namespace :on (:= 'relnamespace 'pg-namespace.oid)
:where (:and (:= 'relkind "r")
(:not-in 'nspname (:set "pg_catalog" "pg_toast"))
(:pg-catalog.pg-table-is-visible 'pg-class.oid))))
;; => "(SELECT relname FROM pg_catalog.pg_class
;; INNER JOIN pg_catalog.pg_namespace ON (relnamespace = pg_namespace.oid)
;; WHERE ((relkind = 'r') and (nspname NOT IN ('pg_catalog', 'pg_toast'))
;; and pg_catalog.pg_table_is_visible(pg_class.oid)))"
sql is a macro that
will simply compile a query, it can be useful for seeing how your
queries are expanded or if you want to do something unexpected
with them.
As you can see, lists starting with keywords are used to express SQL commands and operators (lists starting with something else will be evaluated and then inserted into the query). Quoted symbols name columns or tables (keywords can also be used but might introduce ambiguities). The syntax supports subqueries, multiple joins, stored procedures, etc. See the S-SQL reference manual for a complete treatment.
Finally, here is an example of the use of prepared statements:
(defprepared sovereign-of (:select 'sovereign :from 'country :where (:= 'name '$1)) :single!) (sovereign-of "The Netherlands") ;; => "Beatrix"
The defprepared
macro creates a function that takes the same amount of arguments
as there are $X placeholders in the given query. The
query will only be parsed and planned once (per database
connection), which can be faster, especially for complex
queries.
(disconnect-toplevel)
The reference manuals for the different components of postmodern are kept in separate files. For using the library in the most straighforward way, you only really need to read the Postmodern reference and glance over the S-SQL reference. The simple-date reference explains the time-related data types included in Postmodern, and the CL-postgres reference might be useful if you just want a low-level library for talking to a PostgreSQL server.
Simple-date has no concept of time zones. This means that using it is rather error-prone, and if you really need your time-keeping to be reliable and/or universal you should either not use the types it provides or think really hard about the way you handle time zones.
Recenctly, a lot of work has been done on local-time, which solves the same problem as simple-date, but does understand time zones. The 1.0 repository currently has code for integration with CL-postgres, though this might not be stable yet.
The Lisp code in Postmodern is theoretically portable across implementations, and seems to work on all major ones. I am not actively testing against new releases or obscure implementations, but if you run into problems you are welcome to contact me through the mailing list, and we can try to solve them. Implementations that do not have meta-object protocol support will not have DAOs, but all other parts of the library should work (all widely used implementations do support this).
The library will definitely not work for PostgreSQL versions
older than 7.4 (it uses a client/server protocol that was
introduced in that version). On versions prior to 8.1, retrieving
date and time objects is broken, because their binary
representation was changed. Part of the functionality of insert-dao
(automatic defaulting of unbound slots) only works in PostgreSQL
8.2 and up.
It would be a nice feature if Postmodern could help you with defining your database schemas and, more importantly, updating your databases when your code changes. It would theoretically not be hard to build a function that compares a schema on the lisp side with the state of the database, and helps you to interactively update your database. PostgreSQL has a quite complete introspection system. Unfortunately it would be a lot of work to implement this, since databases can contain so many different types of entities (tables, views, indices, procedures, constraints, sequences, etc.) which are all created, changed, and dropped in different ways.