Accessing Relational Databases

There are good options for accessing relational databases from Common Lisp. Personally I almost always use Postgres and in the past I used either native foreign client libraries or the socket interface to Postgres. Recently, I decided to switch to CLSQL which provides a common interface for accessing Postgres, MySQL, SQLite, and Oracle databases. There are also several recent forks of CLSQL on github. We will use CLSQL in examples in this book. Hopefully while reading the Chapter on Quicklisp you installed CLSQL and the back end for one or more databases that you use for your projects.

For some database applications when I know that I will always use the embedded SQLite database (i.e., that I will never want to switch to Postgres of another database) I will just use the sqlite library as I do in chapter Knowledge Graph Navigator.

If you have not installed CLSQL yet, then please install it now:

1 (ql:quickload "clsql")

You also need to install one or more CLSQL backends, depending on which relational databases you use:

1 (ql:quickload "clsql-postgresql")
2 (ql:quickload "clsql-mysql")
3 (ql:quickload "clsql-sqlite3")

The directory src/clsql_examples contains the standalone example files for this chapter.

While I often prefer hand crafting SQL queries, there seems to be a general movement in software development towards the data mapper or active record design patterns. CLSQL provides Object Relational Mapping (ORM) functionality to CLOS.

You will need to create a new database news in order to follow along with the examples in this chapter and later in this book. I will use Postgres for examples in this chapter and use the following to create a new database (my account is “markw” and the following assumes that I have Postgres configured to not require a password for this account when accessing the database from “localhost”):

1 ->  ~  psql
2 psql (9.1.4)
3 Type "help" for help.
4 markw=# create database news;
5 CREATE DATABASE

We will use three example programs that you can find in the src/clsql_examples directory in the book repository on github:

clsql_create_news_schema.lisp to create table “articles” in database “news”
clsql_write_to_news.lisp to write test data to table “articles”
clsql_read_from_news.lisp to read from the table “articles”

The following listing shows the file src/clsql_examples/clsql_create_news_schema.lisp:

 1 (ql:quickload :clsql)
 2 (ql:quickload :clsql-postgresql)
 3 
 4 ;; Postgres connection specification:
 5 ;;    (host db user password &optional port options tty).
 6 ;; The first argument to **clsql:connect** is a connection
 7 ;; specification list:
 8 
 9 (clsql:connect '("localhost" "news" "markw" nil)
10                :database-type :postgresql)
11 
12 (clsql:def-view-class articles ()
13   ((id
14     :db-kind :key
15     :db-constraints :not-null
16     :type integer
17     :initarg :id)
18    (uri
19     :accessor uri
20     :type (string 60)
21     :initarg :uri)
22    (title
23     :accessor title
24     :type (string 90)
25     :initarg :title)
26    (text
27     :accessor text
28     :type (string 500)
29     :nulls-ok t
30     :initarg :text)))
31 
32 (defun create-articles-table ()
33   (clsql:create-view-from-class 'articles))

In this repl listing, we create the database table “articles” using the function create-articles-table that we just defined:

1 ->  src git:(master) sbcl
2 (running SBCL from: /Users/markw/sbcl)
3 * (load "clsql_create_news_schema.lisp")
4 * (create-articles-table)
5 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
6          "article_pk" for table "articles"
7 T
8 *

The following listing shows the file src/clsql_examples/clsql_write_to_news.lisp:

 1 (ql:quickload :clsql)
 2 (ql:quickload :clsql-postgresql)
 3 
 4 ;; Open connection to database and create CLOS class and database view
 5 ;; for table 'articles':
 6 (load "clsql_create_news_schema.lisp")
 7 
 8 (defvar *a1*
 9   (make-instance
10     'article
11     :uri "http://test.com"
12     :title "Trout Season is Open on Oak Creek"
13     :text "State Fish and Game announced the opening of trout season"))
14 
15 (clsql:update-records-from-instance *a1*)
16 ;; modify a slot value and update database:
17 (setf (slot-value *a1* 'title) "Trout season is open on Oak Creek!!!")
18 (clsql:update-records-from-instance *a1*)
19 ;; warning: the last statement changes the "id" column in the table

You should load the file clsql_write_to_news.lisp one time in a repl to create the test data. The following listing shows file clsql_read_from_news.lisp:

 1 (ql:quickload :clsql)
 2 (ql:quickload :clsql-postgresql)
 3 
 4 ;; Open connection to database and create CLOS class and database view
 5 ;; for table 'articles':
 6 (load "clsql_create_news_schema.lisp")
 7 
 8 (defun pp-article (article)
 9   (format t
10     "~%URI: ~S ~%Title: ~S ~%Text: ~S ~%"
11     (slot-value article 'uri)
12     (slot-value article 'title)
13     (slot-value article 'text)))
14 
15 (dolist (a (clsql:select 'article))
16   (pp-article (car a)))

Loading the file clsql_read_from_news.lisp produces the following output:

1 URI: "http://test.com" 
2 Title: "Trout season is open on Oak Creek!!!" 
3 Text: "State Fish and Game announced the opening of trout season"
4 
5 URI: "http://example.com" 
6 Title: "Longest day of year" 
7 Text: "The summer solstice is on Friday."

You can also embed SQL where clauses in queries:

1 (dolist (a (clsql:select 'article :where "title like '%season%'"))
2   (pp-article (car a)))

which produces this output:

1 URI: "http://test.com" 
2 Title: "Trout season is open on Oak Creek!!!" 
3 Text: "State Fish and Game announced the opening of
4        trout season"

In this example, I am using a SQL like expression to perform partial text matching.

Database Wrap Up

You learned the basics for accessing relational databases. When I am designing new systems for processing data I like to think of my Common Lisp code as being purely functional: my Lisp functions accept arguments that they do not modify and return results. I like to avoid side effects, that is changing global state. When I do have to handle mutable state (or data) I prefer storing mutable state in an external database. I use this same approach when I use the Haskell functional programming language.