Databases

Also: cl-redis, cl-mongo, elephant/bknr.datastore (persistent object stores), AllegroGraph (Allegro), cl-sqlite (direct SQLite bindings).

;; PostgreSQL with Postmodern
(ql:quickload "postmodern")
(postmodern:with-connection '("appdb" "appuser" "secret" "localhost")
  (postmodern:query "CREATE TABLE IF NOT EXISTS users (id serial PRIMARY KEY, name text NOT NULL)")
  (postmodern:query "INSERT INTO users (name) VALUES ($1)" "Ana")     ; parameterized (prevents SQL injection)
  (postmodern:query "SELECT id, name FROM users WHERE name LIKE $1" "A%" :plists)
  ;; S-SQL: SQL as s-expressions
  (postmodern:query (:select 'id 'name :from 'users :where (:= 'name "Ana")))
  (postmodern:with-transaction ()
    (postmodern:query "UPDATE users SET name = $1 WHERE id = $2" "Ana B." 1)))

;; SQLite with cl-dbi
(ql:quickload "cl-dbi")
(dbi:with-connection (conn :sqlite3 :database-name "app.db")
  (dbi:do-sql conn "CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
  (dbi:do-sql conn "INSERT INTO notes (body) VALUES (?)" (list "Learn CLOS"))
  (let* ((query (dbi:prepare conn "SELECT * FROM notes"))
         (result (dbi:execute query)))           ; execute once, then fetch rows
    (loop for row = (dbi:fetch result)
          while row
          collect row)))

;; Mito: ORM on top of cl-dbi
(ql:quickload "mito")
(m
…