Juan Reynoso Elias
En 2021-04-21 12:03:16

Postgresql and Lisp

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:

create user foo with password 'foobar';

create database bar owner foo ;

Now we need to load the library by quicklisp

(ql:quickload "postmodern")

To load "postmodern":

  Load 1 ASDF system:


; Loading "postmodern"


After that, we are going to create a file with:

(in-package :postmodern)

;;; define the var with the values of the database
(defvar *db-parameters* '("bar" "foo" "foobar" "localhost" :POOLED-P T))

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

Create the table

;;; define a simple table

(defclass fruits ()

  ((id-fruit :accessor id-fruit :col-type serial :initarg :id-fruit)

   (name :accessor name :col-type string :initarg :name :initform ""))

  (:documentation "Dao class for a fruit record.")

  (:metaclass postmodern:dao-class)

  (:table-name fruits)(:keys id-fruit))

;;create the table in postgresql server

(with-database (create-table 'fruits))

Database access objects (CRUD)

;; insert
  (insert-dao (make-instance 'fruits :name "apple"))
  (insert-dao (make-instance 'fruits :name "orange")))
;; select
  (get-dao 'fruits 1))

  (select-dao 'fruits (:= 'id-fruit 2)))

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

  (delete-dao (make-instance 'fruits :id-fruit 1)))

(defun the-update (id new-name)
  (let ((record nil))
    ;; get the record
    (setf record (with-database
           (get-dao 'fruits id)))
    ;; set the new value
    (setf (name record) new-name)
    ;; finally update the record
      (update-dao record))))