PostgreSQL and Lisp.

You can get the example

Install postmodern

CL-USER> (ql:quickload "postmodern")
To load "postmodern":
  Load 1 ASDF system:
    postmodern
; Loading "postmodern"

("postmodern")

Use the package

CL-USER> (in-package :postmodern)
POMO> 

Define a global variable

POMO> (defparameter *db-parameters* '("db_name" "user_name" "1ultrasecret123" "localhost" :POOLED-P T))

Define a macro in order to connect the postgresql server

POMO> (defmacro with-database (&body query)
  "This macro creates the connection with specified database information in *db-parameter* and execute the query."
  `(postmodern:with-connection *db-parameters*
  ,@query))

Test connection

POMO> (defun postgresql-test ()
  "Returns a string which is the version of the PostgreSQL"
  (let ((version nil))
    (with-database
      (setf version (query (:select (:version))
               :single)))
    (format t "~a ~%" version)))
POMO> (postgresql-test)
PostgreSQL 11.9 (Debian 11.9-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
NIL
POMO>

Define a simple table

POMO> (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))

See the SQL query string

POMO> (dao-table-definition 'fruits)
"CREATE TABLE fruits (id_fruit SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id_fruit))"

Create the table

POMO> (with-database
  (execute (dao-table-definition 'fruits)))

Or

POMO> (deftable fruits (!dao-def))
POMO> (with-database
  (create-table 'fruits))

Database access objects (CRUD)

 Insert

POMO> (with-database
  (insert-dao (make-instance 'fruits :name "apple"))
  (insert-dao (make-instance 'fruits :name "orange")))

Select

POMO> (with-database
  (get-dao 'fruits 1))

Define a method to display information

POMO> (defparameter *sql-result* nil)
POMO> (defmethod read-information ((obj fruits))
  (format t "id= ~a~%name= ~a~%" (id-fruit obj) (name obj)))

POMO> (setf *sql-result* (with-database
             (get-dao 'fruits 1)))

POMO> (read-information *sql-result*)
id= 1
name= apple
NIL
POMO>

Update

POMO> (defun 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))))

POMO> (update 1 "mango")
POMO> (setf *sql-result* (with-database
             (get-dao 'fruits 1)))
POMO> (read-information *sql-result*)
id= 1
name= mango
NIL
POMO>

Delete

POMO> (with-database
  (delete-dao (make-instance 'fruits :id-fruit 1)))

#posgres #lisp