postmodern-connections

Connecting

The following is a simple function that will start a connection between your lisp instance and your postgresql database. This uses the connect-top-level form, so use this version if you only want one connection or are working from the repl.

(defun start-database ()
  "Start the database connection."
  (unless *db*
    (postmodern:connect-toplevel
                database-name-here your-user-name-here
                your-password-here "localhost"
                :port *database-port*)))

A more normal version might be something that looks like this:

(defun start-db-connection (&optional (database *default-database*)
                                      (database-user *database-user*)
                                      (database-password *database-password*)
                                      (host "localhost"))
  "Start the database connection. Reconnects if there is an unconnected database in *database* 
which matches the database parameter in the function, it will be reconnected. 
Returns boolean on whether the global *database* is now connected."
  (unless *database*
    (setf *database*
          (postmodern:connect database database-user database-password
                              host :pooled-p t))))

As noted in the documentation, *database* is a special variable holding the current database. Most functions and macros operating on a database assume this contains a connected database.

You could use the with-connection macro and wrap your database query something like this where you have variables database-name, user-name, password and host:

(with-connection `(,database-name ,user-name ,password ,host :pooled-p t)
                 (query (:select 'id :from 'countries :where (:= 'name "US"))))

Note that this will bind special variable *database* temporarily to the new connection, but that binding will not be maintained for a subsequent call.

Unconnecting

Not surprisingly, unconnecting to the database depends on how you connected to the database. If you used the connect-toplevel function, then you would use:

(postmodern:disconnect-toplevel)

If you used the pooled connection function, then you could use:

(postmodern:clear-connection-pool)

You should note that the later does clear all the connections in the pool.