DBAL

A DataBase Abstraction Layer (DBAL) over cl-dbi for fetching database query results as plists, alists, or plain values, with automatic conversion of column names (snake_case -> :LISP-CASE keywords) and SQL NULL -> NIL.

Dependencies

• cl-dbi
• alexandria
• assoc-utils

Installation

This system can be installed from UltraLisp like this:

(ql-dist:install-dist "http://dist.ultralisp.org/"
                      :prompt nil)
(ql:quickload :dbal)

Usage

(use-package :dbal)

(with-connection (conn (connect :sqlite3 :database-name "/tmp/test.db"))
  ;; plist rows (default)
  (fetch-all conn "SELECT id, name FROM users")
  ;; => ((:ID 1 :NAME "Ann")
  ;;     (:ID 2 :NAME "Bob"))

  ;; single row as an alist
  (fetch-assoc conn "SELECT id, name FROM users WHERE id = ?" '(1))
  ;; => ((:ID . 1) (:NAME . "Ann"))

  ;; rows indexed by the first column's value
  (fetch-all-indexed conn "SELECT id, name, email FROM users")
  ;; => ((1 (:NAME "Ann" :EMAIL "ann@example.com"))
  ;;     (2 (:NAME "Bob" :EMAIL "bob@example.com")))

  ;; first two columns as key-value alist
  (fetch-all-key-value conn "SELECT id, name FROM users")
  ;; => ((1 . "Ann") (2 . "Bob"))

  ;; scalar and value-list helpers
  (fetch-column conn "SELECT count(*) FROM users")        ; => 2
  (fetch-all-column conn "SELECT id FROM users")          ; => (1 2)
  (fetch-columns conn "SELECT id, name FROM users" )      ; => (1 "Ann")
  (fetch-values conn "SELECT id, name FROM users")        ; => 1, "Ann"
  )

NULL handling

SQL NULL is converted to NIL in plist and alist results:

(fetch conn "SELECT id, deleted_at FROM users WHERE id = ?" '(1))
;; => (:ID 1 :DELETED-AT NIL)

Options

*prettify* (default: T)

When T, column names are upcased and underscores become dashes: user_name -> :USER-NAME. When NIL, column names are returned exactly as the driver reports them.

*dedup-fields* (default: NIL)

Controls handling of duplicate column names in a result row, e.g. SELECT id, name, 'Guiseppe' AS name FROM user:

• NIL — keep duplicates:

  (:ID 1 :NAME "John" :NAME "Guiseppe")

• T — keep one entry per column name (last value wins):

  (:ID 1 :NAME "Guiseppe")

Bind it dynamically to scope the effect:

(let ((*dedup-fields* t))
  (fetch-all conn "SELECT id, name, 'Guiseppe' AS name FROM user"))

API

All fetchers take (CONN SQL &optional PARAMS).

Function	Returns
fetch	First row as a plist, NIL if no rows
fetch-all	All rows as plists
fetch-assoc	First row as an alist
fetch-all-assoc	All rows as alists
fetch-all-indexed	Alist: first column's value -> plist of remaining columns
fetch-all-assoc-indexed	Alist: first column's value -> alist of remaining columns
fetch-all-key-value	Alist: first column -> second column
fetch-column	Value of the first column of the first row
fetch-all-column	List of first-column values of all rows
fetch-columns	List of all values of the first row
fetch-values	First row's values as multiple values

Re-exported from cl-dbi

Connection and transaction management pass through unchanged: with-connection, connect, connect-cached, disconnect, with-transaction, begin-transaction, commit, rollback, ping, row-count, do-sql, prepare, prepare-cached, *sql-execution-hooks*.

(with-connection (conn (connect '(:postgres
                                  :database-name "mydb"
                                  :username "me"
                                  :password "secret")))
  (with-transaction conn
    (do-sql conn "INSERT INTO users (name) VALUES ('Ann')")
    (do-sql conn "INSERT INTO users (name) VALUES ('Bob')")))