Postgresql and Lisp

How to create a Data Access Object for PostgreSQL Data using Postmodern

There is a project called postmodern.

Postmodern is a Common Lisp library for interacting with PostgreSQL databases.

see for more information: http://marijnhaverbeke.nl/postmodern/#quickstart

First I am going to create the user and  database into postgresql:

postgres=# create user lisp_user with password 'your-secret';

postgres=# create database my_database owner lisp_user ;

Now we need to load the library by quicklisp:

(ql:quickload "postmodern")

To load "postmodern":

  Load 1 ASDF system:

    postmodern

; Loading "postmodern"

("postmodern")

After that, we need to create a file postgresql-and-lisp.lisp

(in-package :postmodern)

(defvar *db-parameters* '("my_database" "lisp_user" "your-secret" "localhost" :POOLED-P T)
  "Information about the connection of database.")

(defparameter *my-fruit* nil
  "This var will has an object of fruits.")

;;; Define the macro to connect to the postgresql server
(defmacro with-database (&body query)
  "This macro creates the connection with specified database in *db-parameter* and execute the query."
  `(postmodern:with-connection *db-parameters* ,@query))

;;; define a simple table in other words define a table called fruits
(defclass fruits ()
  ((fruit-id :accessor fruit-id :col-type serial :initarg :fruit-id)
   (name :accessor name :col-type string :initarg :name :initform ""))
  (:documentation "DAO class example")
  (:metaclass postmodern:dao-class)
  (:table-name fruits)(:keys fruit-id))


(deftable fruits (!dao-def))
;;create the table in postgresql server
(with-database (create-table 'fruits))

;;; Database access objects (CRUD)

;;; Crate a record
(with-database
  (insert-dao (make-instance 'fruits :name "apple")))

;; Read a record
(with-database
  (get-dao 'fruits 1))

;; Read the information and set it in *my-fruit* var.
(setf *my-fruit* (with-database
           (get-dao 'fruits 1)))

;;; define a method in order to display the information
(defmethod get-fruit-information ((obj fruits))
  (format t "id= ~a ~%name= ~a~%" (fruit-id obj) (name obj)))

;;; Update information
(defmethod update-my-fruit ((obj fruits) new-name)
  ;; set the new value
  (setf (name obj) new-name)
  ;; finally update the record
  (with-database
    (update-dao obj)))

;;; Delete information
(defmethod delete-my-fruit ((obj fruits))
  (with-database
    ;; Delete the object
    (delete-dao obj)))

You can get the file:

https://gist.github.com/juan-reynoso/f9595fac1d2693b684920c58be5c862e

#postgres #lisp #postmodern