5. Persistence part I: PostgreSQL


I think we’ve written enough horrible code for the sake of simplicity and now I want to focus on “doing it right”, to the best of my abilities, meaning, I want to use a real database. The example app I’ll show you in the next few chapters will be a simple Reddit-like link sharing site. As an exercise, you can later go back and redo the previous blog example app to use a database.

In this chapter I’ll use PostgreSQL and the excellent postmodern library, which is specific to Postgres. Other options exist, from the popular “NoSQL” data stores like Redis and MongoDB, to lisp specific object stores, like bknr-datastore. We’ll implement a database layer in several of these stores. I’ll show you how to use the Restas policy mechanism to define a single interface to the various back-ends.

Setting up PostgreSQL


Install postgresql using your distros package manager, for Debian based distros this looks like:

1  # apt-get install postgresql

I also usually install the graphical admin tool pgadmin3 but it is optional.

Next we need to set up a database and a user for it. Postgres automatically creates a system user account named postgres for administration of the database server. Log in from the shell, and start psql(the postgres shell) so we can configure it to our needs:

1  # su - postgres
2  $ psql

Next, we need to create a pg user and a database, I’ll name the user linkdemouser and the database linkdemo, then we quit with the \q command:

1  postgres=# CREATE USER linkdemouser WITH PASSWORD 'mypass';
2  postgres=# CREATE DATABASE linkdemo OWNER linkdemouser;
3  postgres=# \q

Log out as the user postgres, and were done:

1  $ exit


You can download a PostgreSQL graphical installer for windows from the PostgreSQL site. Installation is straightforward, and you should probably follow the default installation options.

On windows 7 you will have to run the installer as an Administrator. The installer will ask you for a password for the super user postgres, you will use this DB account only to create a new user and a database.

At the end of the installation, deselect the check-box “Launch Stack Builder at exit?”, we won’t be needing that. Click finish.

After the installation completes, click the start menu, navigate to the PostgreSQL sub-menu and open “SQL Shell(psql)”. It will prompt you for a server name, database, port and user. Press Enter on all four for the default values. Then it will ask for the postgres user password, type it in and press Enter.

Next, we need to create a pg user and a database, I’ll name the user linkdemouser and the database linkdemo, then we quit with the \q command:

1  postgres=# CREATE USER linkdemouser WITH PASSWORD 'mypass';
2  postgres=# CREATE DATABASE linkdemo OWNER linkdemouser;
3  postgres=# \q

That’s it.

What is a policy?

Although we don’t need to do so, we’ll use the restas policy mechanism to define an interface to our database. But first lets have a short discussion of the problem policies solve for us.

In our app, if we need to access the database we’ll have a bunch of queries. Usually they are encapsulated in functions. So for example we might have a set of function like (find-user id) and (auth-user username password) which both contain queries written using postmodern. Lets say we want to have the option of using MySQL or some other backend and be able to switch between them. Common Lisp has a powerful OO system that allows us to do this easily. In our project we can define a variable called for example *datastore* and depending on its value a different implementation of our database layer gets used. Using generic functions this is easy, we simply define a class for each layer, for example:

1 (defclass postmodern-datastore () ...)
2 (defclass mysql-datastore () ...)

And set the *datastore* variable to an instance of one such class:

1 (setf *datastore* (make-instance 'postmodern-datastore ...))

Now, the functions find-user and auth-user can be defined in terms of generic functions:

1 (defun find-user (id)
2   (datastore-find-user *datastore* id))
4 (defun auth-user (username password)
5   (datastore-auth-user *datastore* username password))

Here datastore-find-user and datastore-auth-user are both methods defined on the postmodern-datastore and mysql-datastore classes, an instance of which we pass as the first argument. This pattern is fairly common, and restas provides a mechanism, called a policy, for generating all of the boilerplate necessary for it, such as defining the generic functions, the dispatch variable, the functions that call the methods, and optionally, various packages to put all of the stuff in, etc.

Creating the project

Now let’s get on with writing our link sharing site. Let’s create the project skeleton with restas-project:

1  * (ql:quickload "restas-project")
2  * (restas-project:start-restas-project 
3     "linkdemo"
4     :depends-on '(:sexml 
5                   :postmodern 
6                   :ironclad 
7                   :babel))

Other than postmodern and sexml, we’ll need the ironclad and babel libraries which will be used to hash the user passwords.

Defining the policy

Our next order of business is to define our policy, and all of the packages we’ll be using. restas:define-policy has the following syntax:

 1 (restas:define-policy <policy-name>
 2   (:interface-package <interface-package-name>)
 3   (:interface-method-template <interface-method-template>)
 4   (:internal-package <internal-package-name>)
 5   (:internal-function-template <internal-function-template>)
 7   (define-method <method-name> (args...)
 8     "Documentation string")
10   <more method-definitions>)

What this does is:

In our case, this is the concrete example declaration we’ll be putting in defmodule.lisp, also containing two method declarations for find-user and auth-user(there are more methods, but we’ll add them later):

 1 (restas:define-policy datastore
 2   (:interface-package #:linkdemo.policy.datastore)
 3   (:interface-method-template "DATASTORE-~A")
 4   (:internal-package #:linkdemo.datastore)
 6   (define-method find-user (username)
 7     "Find the user by username")
 9   (define-method auth-user (username password)
10     "Check if a user exists and has the suplied password"))

The policy is named datastore, which means that the dynamic variable controlling dispatch will be named *datastore*. This variable is defined in the internal package, in our case named linkdemo.datastore. This package will also include the functions we actually call in our app, such as find-user. The interface package is called linkdemo.policy.datastore and this is where the generic functions that define our interface to the database are defined.

Notice the declaration of :interface-method-template. The declaration means that we want the generic functions in the interface package to be renamed according to the template "DATASTORE-~A" so for instance the generic function for find-user will be named datastore-find-user. I opted to skip defining such a rule for the functions in the internal package, but I could have done the same thing using :internal-function-template.

Also notice that method declarations are done with define-method. Do not be confused! Methods in Common Lisp are defined with defmethod, and here define-method is just part of the syntax of the define-policy macro. The argument lists of these method declarations will be the same as the functions in linkdemo.datastore. The argument lists of the generic functions in linkdemo.policy.datastore will have an extra argument called datastore which will be used for dispatch. For example (find-user username) -> (datastore-find-user datastore username).

Here is the complete interface we will define today, complete with all the methods we need. Put this at the top of defmodule.lisp:

 1 (restas:define-policy datastore
 2   (:interface-package #:linkdemo.policy.datastore)
 3   (:interface-method-template "DATASTORE-~A")
 4   (:internal-package #:linkdemo.datastore)
 6   (define-method init ()
 7     "initiate the datastore")
 9   (define-method find-user (username)
10     "find the user by username")
12   (define-method auth-user (username password)
13     "Check if a user exists and has the suplied password")
15   (define-method register-user (username password)
16     "Register a new user")
18   (define-method upvoted-p (link-id username)
19     "Check if a user has upvoted a link")
21   (define-method upvote (link-id user)
22     "upvote a link")
24   (define-method post-link (url title user)
25     "post a new link")
27   (define-method get-all-links (&optional user)
28     "Get all of the links in the datastore")
30   (define-method upvote-count (link-id)
31     "get the number of upvotes for a given link"))

Defining the rest of the packages

Next, we need to define the restas module for our application, and the package where we will implement the policy interface for PostgreSQL, put this code after the policy declaration in defmodule.lisp:

 1 (restas:define-module #:linkdemo
 2   (:use #:cl #:restas #:linkdemo.datastore))
 4 (defpackage #:linkdemo.pg-datastore
 5   (:use #:cl #:postmodern #:linkdemo.policy.datastore))
 7 (in-package #:linkdemo)
 9 (defparameter *template-directory*
10   (merge-pathnames #P"templates/" linkdemo-config:*base-directory*))
12 (defparameter *static-directory*
13   (merge-pathnames #P"static/" linkdemo-config:*base-directory*))

Notice that linkdemo “uses” the internal package linkdemo.datastore where all of the functions like find-user are defined, and linkdemo.pg-datastore “uses” the interface package linkdemo.policy.datastore where the generic functions we need to implement methods for are defined.

The PostgreSQL backend will be implemented in a new file called pg-datastore.lisp, lets add it to linkdemo.asd:

1 (asdf:defsystem #:linkdemo
2   :serial t
3   :description "Your description here"
4   :author "Your name here"
5   :license "Your license here"
7   :components ((:file "defmodule")
8                (:file "pg-datastore")
9                (:file "linkdemo")))

Next, we create the file pg-datastore.lisp in the project directory and add an in-package declaration:

1 ;;;; pg-datastore.lisp
3 (in-package #:linkdemo.pg-datastore)

The schema

The app will be very simple, it will have users, who can post links, and vote on them. That makes three tables:

We could have stored the upvotes as an integer in the links table, but then users would be able to vote more than once per link, and we don’t want that. What we need in this case is a many to many relation. If you are familiar with the basics of relational databases, this would be the most straightforward way to model our data.


There are two ways to connect to a PostgreSQL database, using the macro with-connection whose body will be executed in the context of a connection. Or using connect-toplevel which will create a connection and setup the special variable *database* to the new connection. This variable is used to execute queries. with-connection automatically binds it in its body. I’ll be using with-connection for our code, but connect-toplevel is useful for testing at the REPL so we don’t have to wrap all of our queries in with-connection.

In order to use the macro, we’ll need to have a variable with the connection spec, which has the following form: (database user password host). The connection spec will be stored in a slot of our pg-datastore class(the one used for dispatch). Let’s define this class in pg-datastore.lisp:

1 (defclass pg-datastore ()
2   ((connection-spec :initarg :connection-spec
3                     :accessor connection-spec)))

For testing purposes, I’ll create an instance of this class and store it in a variable called *db*(in our real app, we’ll use *datastore* in the internal package):

1 (defparameter *db*
2   (make-instance 'pg-datastore
3                  :connection-spec '("linkdemo" "linkdemouser" "mypass" "localhost")))

We can now do this:

1 (with-connection (connection-spec *db*)
2   ;query goes here
3   )

Defining the tables.

DAO classes

Postmodern isn’t an ORM, so if you’re used to them from places like Django or Rails, you’re in luck, because in my very humble opinion, they suck. If you’re using a RDBMS, learn the relational model already. Now that I got that rant out of the way, lets move on. Even though it isn’t an ORM, postmodern does allow us to work with objects, but they are just simple DAOs(Database Access Objects). DAO objects are defined the same way ordinary lisp objects are, using defclass, but they have additional syntax for giving database types to our slots, and we need to add the dao-class 1 to the definition. Here is how the users table will be defined:

1 (defclass users ()
2   ((id :col-type serial :reader user-id)
3    (name :col-type string :reader user-name :initarg :name)
4    (password :col-type string :reader user-password :initarg :password)
5    (salt :col-type string :reader user-salt :initarg :salt))
6   (:metaclass dao-class)
7   (:keys id))

The difference between a standard class definition and a dao class is that we have a :col-type option to slot definitions that specify what database type we want to create. In our case, id will be a serial which is the PostgreSQL type for an integer that will auto-increment every time we add a record. The other two fields will be strings. In order to add the :col-type option to our slots, as well as other additions to our dao classes we must specify dao-class as a metaclass. Metaclasses are the standard Common Lisp mechanism for extending the object system. We also specify that we want id to be a primary key. The password and salt slots will contain the password hash and salt from encrypting the password of the user.

We can see what SQL code will be generated by this definition with dao-table-definition:

1  * (dao-table-definition 'users)

It will give us the following output as a string(formatting by me):

1 CREATE TABLE users (
3   name TEXT NOT NULL,
4   password TEXT NOT NULL,
5   salt TEXT NOT NULL,
6   PRIMARY KEY (id)
7 )

Lets implement the method used for initiating the datastore, creating the tables seems like a good thing to put in it. The generic function is named datastore-init, here it is:

1 (defmethod datastore-init ((datastore 'pg-datastore))
2   (with-connection (connection-spec datastore)
3     (unless (table-exists-p 'users)
4       (execute (dao-table-definition 'users)))))

First we connect to the database, then, using the table-exists-p predicate we check if the table is already defined. If it isn’t, we use the execute function, which will execute an SQL expression, in our case, it will be the output of dao-table-definition. Later we’ll augment this method with the definitions of the other tables.

We can call this method like this:

1 (datastore-init *db*)

After the table is defined, we can add users by instantiating objects of the users class, and inserting them into the db using insert-dao, here is an example:

1 (with-connection (connection-spec *db*)
2   (insert-dao (make-instance 'users
3                              :name "user"
4                              :password "pass")))


Say we’ve added a bunch of users to the db, we can now query them in two ways, as DAOs, or as an ordinary table. The dao way is with select-dao, which returns a list of lisp objects:

1  * (with-connection (connection-spec *db*)
2      (select-dao 'users ))
4  => (#<USERS {10089B4443}> #<USERS {10089B63F3}> #<USERS {10089B69F3}>
5  #<USERS {10089B6FF3}>)

We can also use a normal query using S-SQL, a lispy syntax for SQL. Have a look at the example(the password and salt values are made up of course):

1  * (with-connection (connection-spec *db*)
2      (query (:select :* :from 'users)))
4  => ((1 "user" "pass" "salt")
5      (2 "user1" "pass1" "salt1")
6      (3 "user2" "pass2" "salt2")
7      (4 "user3" "pass3" "salt3"))

The query form takes an S-SQL expression. S-sql operators are keywords. Our query returns a list of lists, with the values in the table. We can get slightly more useful output with the query args/format optional parameter which specifies the format of the result. The most common values are :plists and :alists, returning the result in the format of a plist or alist, with the column names. Example:

 1  * (with-connection (connection-spec *db*)
 2      (query (:select :* :from 'users) :plists))
 4  => ((:ID 1 :NAME "user" :PASSWORD "pass" :SALT "salt")
 5      (:ID 2 :NAME "user1" :PASSWORD "pass1" :SALT "salt1")
 6      (:ID 3 :NAME "user2" :PASSWORD "pass2" :SALT "salt2")
 7      (:ID 4 :NAME "user3" :PASSWORD "pass3" :SALT "salt3"))
 9  * (with-connection (connection-spec *db*)
10      (query (:select :* :from 'users) :alists))
12  => (((:ID . 1) (:NAME . "user") (:PASSWORD . "pass") (:SALT . "salt"))
13      ((:ID . 2) (:NAME . "user1") (:PASSWORD . "pass1") (:SALT . "salt1"))
14      ((:ID . 3) (:NAME . "user2") (:PASSWORD . "pass2") (:SALT . "salt2"))
15      ((:ID . 4) (:NAME . "user3") (:PASSWORD . "pass3") (:SALT . "salt3")))

We’ll see other format examples later on.

Because defclass definitions of dao objects don’t support adding foreign keys, well have to use a slightly different method of defining tables using the deftable macro. We start off by defining our DAO:

1 (defclass links ()
2   ((id :col-type serial :reader link-id)
3    (url :col-type string :reader link-url :initarg :url)
4    (title :col-type string :reader link-title :initarg :title)
5    (submitter-id :col-type integer :reader link-submitter-id :initarg :submitter-id))
6   (:metaclass dao-class)
7   (:keys id))

Next, because we need to add the foreign key constrain, we use the deftable macro to define a table. The table will inherit all of the fields of the dao class:

1 (deftable links
2   (!dao-def)
3   (!foreign 'users 'submitter-id 'id))

!dao-def tells deftable to inherit the field definitions from the dao class definition, and !foreign tells deftable to add a foreign key constrain to the table. !foreigns first parameter is the target table, the second is the field, and if the field has a different name in the definition of the target table, add it as a third parameter.

Lets do the same for votes:

 1 (defclass votes ()
 2   ((link-id :col-type integer :reader vote-link-id :initarg :link-id)
 3    (submitter-id :col-type integer :reader vote-submitter-id :initarg :submitter-id))
 4   (:metaclass dao-class)
 5   (:keys link-id submitter-id))
 7 (deftable votes
 8   (!dao-def)
 9   (!foreign 'links 'link-id 'id)
10   (!foreign 'users 'submitter-id 'id))

Now, let’s update the datastore-init method to create these tables as well. Note that unlike ordinary dao-defined tables, tables defined with deftable are created in the database using the function create-table:

1 (defmethod datastore-init ((datastore pg-datastore))
2   (with-connection (connection-spec datastore)
3     (unless (table-exists-p 'users)
4       (execute (dao-table-definition 'users)))
5     (unless (table-exists-p 'links)
6       (create-table 'links))
7     (unless (table-exists-p 'votes)
8       (create-table 'votes))))

Defining our interface

What will the interface consist of? We’ll need a way to register a user, and authenticate one at login. We’ll also need to be able to post a link and upvote it. Also we’ll need a way to get a list of all the links for the home page, and a way to get their score, since we’ll want to sort by it. That’s about it for a quick version 1.

Hashing passwords

The original version of this chapter stored passwords in plain text, I decided to actually try to be secure in this revision. For this purpose I’ll use the ironclad cryptography library to hash passwords. We’ll use the pbkdf2 algorithm to hash our passwords:

1 (defun hash-password (password)
2   (multiple-value-bind (hash salt)
3       (ironclad:pbkdf2-hash-password (babel:string-to-octets password))
4     (list :password-hash (ironclad:byte-array-to-hex-string hash)
5           :salt (ironclad:byte-array-to-hex-string salt))))

This code is kind of dense, all you need to know about it is that it returns a plist with a password hash and a salt, ready to be stored into a database.

Checking to see if a password matches involves taking said password, hash and salt, hashing the password using the salt, and comparing hashes:

1 (defun check-password (password password-hash salt)
2   (let ((hash (ironclad:pbkdf2-hash-password
3                (babel:string-to-octets password)
4                :salt (ironclad:hex-string-to-byte-array salt))))
5     (string= (ironclad:byte-array-to-hex-string hash)
6              password-hash)))

With this out of the way, we can now go on and write the user handling logic.

Handling users

When we create and authenticate a user we’ll need a way to find if a user already exists in the database, datastore-find-user does this and returns a plist with the users credentials, and nil if no such user exists:

1 (defmethod datastore-find-user ((datastore pg-datastore) username)
2   (with-connection (connection-spec datastore)
3     (query (:select :* :from 'users
4                     :where (:= 'name username))
5            :plist)))

Note that the argument to query is :plist and not the plural :plists. This tells postmodern to return just one result.

Next, when a user logs in, we simply find the user, and check if the password matches. If so, we return the username. If no such user exists or the passwords don’t match, we return nil:

1 (defmethod datastore-auth-user ((datastore pg-datastore) username password)
2   (let ((user (datastore-find-user datastore username)))
3     (when (and user
4                (check-password password (getf user :password) 
5                                         (getf user :salt)))
6       username)))

And finally registering the user. We check if the user is registered, and if not, we create a record in the db:

 1 (defmethod datastore-register-user ((datastore pg-datastore) username password)
 2   (with-connection (connection-spec datastore)
 3     (unless (datastore-find-user datastore username)
 4       (let ((password-salt (hash-password password)))
 5         (when
 6             (save-dao
 7              (make-instance 'users
 8                             :name username
 9                             :password (getf password-salt :password-hash)
10                             :salt (getf password-salt :salt)))
11           username)))))

We check to see if the user isn’t registered if he isn’t, we hash the password, make a DAO object with the username, hash and salt, and save it. The reason save-dao is wrapped in a ‘when` is to make sure the operation was successful, if so, we return the username.

In order to handle links properly, let’s write an datastore-upvoted-p predicate method:

1 (defmethod datastore-upvoted-p ((datastore pg-datastore) link-id user)
2   (with-connection (connection-spec datastore)
3     (query (:select :link-id :users.name :from 'votes 'users
4                     :where (:and (:= 'users.id 'submitter-id)
5                                  (:= 'users.name user)
6                                  (:= 'link-id link-id)))
7            :plist)))

This is a slightly more complicated query, it even has an implicit join. Essentially, we query the votes table for any rows with the link-id and submitter-id matching that of the username of the user, and we return the link-id and the username.

Upvoting a link involves finding the id of the user, checking if the user hasn’t already upvoted that link, and then simply doing an insert:

1 (defmethod datastore-upvote ((datastore pg-datastore) link-id user)
2   (with-connection (connection-spec datastore)
3     (let ((submitter-id (getf (datastore-find-user datastore user) :id)))
4       (when (and submitter-id
5                  (not (datastore-upvoted-p datastore link-id user)))
6         (when (save-dao (make-instance 'votes
7                                        :link-id link-id
8                                        :submitter-id submitter-id))
9           link-id)))))

For posting a link, we want the user submitting it to also automatically upvote it. In order to do that though, we have to use a DAO, since query will not return us the inserted value, and we need the links id in order to upvote it. After we save-dao the DAO though, its link-id accessor function will return it for us and we can upvote it. This is how it looks like:

1 (defmethod datastore-post-link ((datastore pg-datastore) url title user)
2   (with-connection (connection-spec datastore)
3     (let* ((submitter-id (getf (datastore-find-user datastore user) :id))
4            (link (make-instance 'links
5                                 :url url
6                                 :title title
7                                 :submitter-id submitter-id)))
8       (save-dao link)
9       (datastore-upvote datastore (link-id link) user))))

Getting all the links involved 3 steps, selecting them, checking their upvote count, and then sorting them. We’ll need to write a bunch of functions to do so. First let’s write a function that selects them all:

1 (defun get-all-links/internal ()
2   (query (:select :* :from 'links) :plists))

This function doesn’t have a with-connection in its body because it is internal and will only be used in a context that has a connection.

Now, given a link-id, getting the upvote count is as easy as using the sql COUNT function. We tell query to format the result with the :single keyword, which returns a single result, in our case an integer:

1 (defmethod datastore-upvote-count ((datastore pg-datastore) link-id)
2   (with-connection (connection-spec datastore)
3     (query (:select (:count link-id) :from 'votes
4                     :where (:= link-id 'link-id))
5            :single)))

We’ll need to augment the link plist with two keys: :votes is the number of votes the link has, and voted-p is a boolean specifying whether or not the currently logged in user has upvoted it. We do this for every link returned by get-all-links/internal. Let’s define a function to do that. We have to pass datastore to it because it will call datastore-upvoted-p:

1 (defun add-vote-count (datastore links username)
2   (loop
3      for link in links
4      for id = (getf link :id)
5      collect (list* :votes (datastore-upvote-count datastore id)
6                     :voted-p (datastore-upvoted-p datastore id username)
7                     link)))

The simplest way I found to get them sorted is using common lisps sort function:

1 (defun sort-links (links)
2   (sort links #'>
3         :key #'(lambda (link) (getf link :votes))))

And finally we define our method:

1 (defmethod datastore-get-all-links ((datastore pg-datastore) 
2                                     &optional username)
3   (with-connection (connection-spec datastore)
4     (sort-links
5      (add-vote-count datastore
6                      (get-all-links/internal)
7                      (or username "")))))

Note that we use or to pass the optional value username, in case it is nil, we want to pass an empty string, since there might be a case where no user is logged in, and upvoted-p expects a string, and will choke on nil.

Exporting the interface

The only thing we’ll need to export from this package is the pg-datastore classname. Lets do that in defmodule.lisp:

1 (defpackage #:linkdemo.pg-datastore
2   (:use #:cl #:postmodern #:linkdemo.policy.datastore)
3   (:export #:pg-datastore))


That’s it for the DB layer for now, In the next chapter we’ll start using it to finish our app, and then we’ll augment it to use a different backend datastore.

Here are some links for the curious:

  1. A metaobject protocol is a scary term which basically means that even classes are instances of a class. If you aren’t familiar with object-oriented meta-programming, a metaclass controls the way other classes behave. In our case Postmodern provides us a metaclass that tells CLOS classes how to be saved into a database. [end of oversimplified footnote]