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:

    postmodern

; Loading "postmodern"


("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
(with-database
  (insert-dao (make-instance 'fruits :name "apple"))
  (insert-dao (make-instance 'fruits :name "orange")))
;; select
(with-database
  (get-dao 'fruits 1))

(with-database
  (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
(with-database
  (delete-dao (make-instance 'fruits :id-fruit 1)))


;;update
(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
    (with-database
      (update-dao record))))