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 manuals linked below.

Assuming you have already installed quicklisp, load postmodern.

(ql:quickload :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', there are two basic ways to connect to a database. If your role/application/database(s) looks like a 1:1 relationship, you can connect like this:

(connect-toplevel "testdb" "foucault" "surveiller" "localhost" )

This 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 within that lexical scope.

Connect-toplevel will maintain a single connection for the life of the session.

If the Postgresql server is running on a port other than 5432, you would also pass the appropriate keyword port parameter. E.g.:

(connect-toplevel "testdb" "foucault" "surveiller" "localhost" :port 5434)

Ssl connections would similarly use the keyword parameter :use-ssl and pass :yes, :no or :try

If you have multiple roles connecting to one or more databases, i.e. 1:many or many:1, (in other words, changing connections) then with-connection form which establishes a connection with a lexical scope is more appropriate.

( with-connection '( "testdb" "foucault" "surveiller" "localhost" ) ...)

For example, if you are creating a database, you need to have established a connection to a currently existing database (typically "postgres"). Assuming the foucault role is a superuser and you want to stay in a development connection with your new database afterwards, you would first use with-connection to connect to postgres, create the database and then switch to connect-toplevel for development ease.

( with-connection '( "postgres" "foucault" "surveiller" "localhost" ) (create-database 'testdb :limit-public-access t :comment "This database is for testing silly theories" )) (connect-toplevel "testdb" "foucault" "surveiller" "localhost" )

Note: (create-database) functionality is new to postmodern v. 1.32. Setting the :limit-public-access parameter to t will block connections to that database from anyone who you have not explicitly given permission (except other superusers).

A word about Postgresql connections. Postgresql connections are not lightweight threads. They actually consume about 10 MB of memory per connection and Postgresql can be tuned to limit the number of connections allowed at any one time. In addition, any connections which require security (ssl or scram authentication) will take additiona time and create more overhead.

If you have an application like a web app which will make many connections, you also generally do not want to create and drop connections for every query. The usual solution is to use connection pools so that the application is grabbing an already existing connection and returning it to the pool when finished, saving connection time and memory.

To use postmodern's simple connection pooler, the with-connection call would look like:

( with-connection '( "testdb" "foucault" "surveiller" "localhost" :pooled-p t) ...)

The maximum number of connections in the pool is set in the special variable max-pool-size, which defaults to nil (no maximum).

Now for a basic sanity test which does not need a database connection at all:

(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 occurring 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, :dao 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))

You can work directly with the database or you can use a simple database-access-class (aka dao) which would cover all the fields in a row. 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 named 'country' with three columns: name, inhabitants, and sovereign. The :keys parameter specifies which column(s) are used for the primary key. Once you have created the class, you can return an instance of the country class by calling

(get-dao 'country "Croatia" )

You can also define classes that use multiple columns in the primary key:

( defclass points () ((x :col-type integer :initarg :x :reader point-x) (y :col-type integer :initarg :y :reader point-y) (value :col-type integer :initarg :value :accessor value)) ( :metaclass dao-class) ( :keys x y))

In this case, retrieving a points record would look like the following where 12 and 34 would be the values you are looking to find in the x column and y column respectively.:

(get-dao 'points 12 34)

Consider a slightly more complicated version of country:

( defclass country-c () ((id :col-type integer :col-identity t :accessor id) (name :col-type string :col-unique t :check ( :<> 'name "" ) :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) (region-id :col-type integer :col-references ((regions id)) :initarg :region-id :accessor region-id)) ( :metaclass dao-class) ( :table-name countries))

In this example we have an id column which is specified to be an identity column. Postgresql will automatically generate a sequence of of integers and this will be the primary key.

We have a name column which is specified as unique and is not null and the check will ensure that the database refuses to accept an empty string as the name.

We have a region-id column which references the id column in the regions table. This is a foreign key constraint and Postgresql will not accept inserting a country into the database unless there is an existing region with an id that matches this number. Postgresql will also not allow deleting a region if there are countries that reference that region's id. If we wanted Postgresql to delete countries when regions are deleted, that column would be specified as:

(region-id :col-type integer :col-references ((regions id) :cascade ) :initarg :region-id :accessor region-id)

Now you can see why the double parens.

We also specify that the table name is not "country" but "countries". (Some style guides recommend that table names be plural and references to rows be singular.)