This post deals with writing a simple CRUD application in Common Lisp.
The code for this demo project is available on github
Introduction
We will write a simple TODO list application which will store its data in a postgres database. This application will have a basic terminal based interface.
Running The Application
Clone the git repository into a location where asdf can find it.
$ cd ~/common-lisp
$ git clone https://github.com/pvik/cl-demo-crud-app.git
$ cd cl-demo-crud-app
Now start the docker image with a postgres database:
$ docker-compose -f docker/docker-compose.yaml up
Create the database tables from migrations/01-setup.sql:
$ psql -h localhost -U postgres -f migrations/01-setup.sql
Password for user postgres:
Timing is on.
Null display is "(null)".
Line style is unicode.
Border style is 2.
Expanded display is used automatically.
CREATE DATABASE
Time: 251.229 ms
You are now connected to database "sample_crud" as user "postgres".
CREATE SCHEMA
Time: 1.800 ms
CREATE TABLE
Time: 7.906 ms
(Note: default password for our DB is docker)
Open up the sbcl REPL and:
* (require 'cl-demo-crud-app)
WARNING: System definition file #P"/home/elric/.quicklisp/dists/quicklisp/software/cl-fad-20180430-git/cl-fad.asd" contains definition for system "cl-fad-test". Please only define "cl-fad" and secondary systems with a name starting with "cl-fad/" (e.g. "cl-fad/test") in that file.
; compiling file "/home/elric/common-lisp/cl-demo-crud-app/src/db.lisp" (written 14 AUG 2019 06:19:25 PM):
; compiling (DEFPACKAGE DB ...)
; compiling (IN-PACKAGE :DB)
; compiling (DEFVAR *TODO-TABLE* ...)
; compiling (DEFSTRUCT TODO ...)
; compiling (DEFUN CONNECT-DB ...)
; compiling (DEFUN LIST-TO-TODO ...)
; compiling (DEFMACRO GET-TODO ...)
; compiling (DEFUN GET-TODO-BY-ID ...)
; compiling (DEFUN GET-TODO-BY-COMPLETED ...)
; compiling (DEFMETHOD INSERT ...)
; wrote /home/elric/.cache/common-lisp/sbcl-1.5.3-linux-x64/home/elric/common-lisp/cl-demo-crud-app/src/db-tmpGHU3ALSV.fasl
; compilation finished in 0:00:00.033
; compiling file "/home/elric/common-lisp/cl-demo-crud-app/src/main.lisp" (written 14 AUG 2019 06:19:25 PM):
; compiling (DEFPACKAGE CL-DEMO-CRUD-APP ...)
; compiling (IN-PACKAGE :CL-DEMO-CRUD-APP)
; compiling (DEFPARAMETER *ALLOWED-COMMANDS* ...)
; compiling (DEFUN SHOW ...)
; compiling (DEFUN NEW ...)
; compiling (DEFUN COMPLETED ...)
; compiling (DEFUN EDIT ...)
; compiling (DEFUN APP-READ ...)
; compiling (DEFUN APP-EVAL ...)
; compiling (DEFUN PRINT-TODO ...)
; compiling (DEFUN PRINT-TODO-DETAIL ...)
; compiling (DEFUN PRINT-TODO-LIST ...)
; compiling (DEFUN REPL ...)
; compiling (DEFUN MAIN ...)
; wrote /home/elric/.cache/common-lisp/sbcl-1.5.3-linux-x64/home/elric/common-lisp/cl-demo-crud-app/src/main-tmpAAURSO1.fasl
; compilation finished in 0:00:00.011
("SB-ROTATE-BYTE")
Finally start the application in the REPL:
(cl-demo-crud-app:main)
The User Interface
The terminal interface has 5 commands
showshow completedshow id <id>
newupdate <id>completed <id>quit
A sample of how the user interface works is as shown below:
Connected to postgres@localhost/sample_crud!Welcome!
> show
> new
Todo: test1
Note: test 1 notes
inserting test1
Created Todo 1
> sjow
Unknown Command
> show
ID:1 [ ] test1 <2019-08-14T19:19:56.000000-04:00> < >
> show id 1
ID:1 [ ] test1 <2019-08-14T19:19:56.000000-04:00> < >
Note: test 1 notes
> completed 1
updating record 1
> show
> show completed
ID:1 [x] test1 <2019-08-14T19:19:56.000000-04:00> <2019-08-14T19:20:11.000000-04:00>
Interacting with the DB
We use the postmodern library to interact with our postgres DB.
postmodern:*database* is a package level variable that holds a database connection object.
You can use the connect-toplevel or connect functions to connect to the DB. Once the *database* variable is set with a valid connection, all other DB interaction functions will use it.
postmodern also defines queries in s-sql, which is very similar to writing queries using honeysql for clojure.
Some of the s-sql statements from the app are as follows, and are fairly straight forward
(:select 'id 'item 'note 'completed 'create_date 'completed_date
:from *todo-table*
:where (:= 'id id))
(:update *todo-table*
:set
'item item 'note note 'completed completed
'create_date created-date
'completed_date completed-date
:where (:= 'id id))
Finally, we use postmodern:query to run queries against the database
The query to insert a row with a parameterized values is as follows
(postmodern:query
(:insert-into *todo-table*
:set 'item '$1 'note '$2 'completed '$3
'create_date '$4 'completed_date '$5
:returning 'id)
item note completed created-date completed-date
:single)))
The :single keyword parameter to query is useful in situation where a single value is being returned from the DB (like when using postgres’s RETURNING keyword)