CL-DBI - Database-independent interface for Common Lisp

Usage

Connecting - MYSQL

(defvar *connection*
  (dbi:connect :mysql
               :database-name "test"
               :username "nobody"
               :password "1234"))

Connecting - SQLite

 (defvar *connection*
  (dbi:connect :sqlite3
               :database-name "/home/gt/test.sqlite3"))

Executing a query

(let* ((query (dbi:prepare *connection*
                           "SELECT * FROM somewhere WHERE flag = ? OR updated_at > ?"))
       (query (dbi:execute query (list 0 "2011-11-01"))))
  (loop for row = (dbi:fetch query)
        while row
        ;; process "row".
        ))

;; Do it all at once
(dbi:fetch-all (dbi:execute (dbi:prepare *connection* "SELECT * FROM somewhere WHERE flag = ? OR updated_at > ?")
                            (list 0 "2011-11-01")))

dbi:do-sql is another option that prepares and executes a single statement. It returns the number of rows affected. It's typically used for non-SELECT statements.

(dbi:do-sql *connection*
            "INSERT INTO somewhere (flag, updated_at) VALUES (?, NOW())"
            (list 0))
;=> 1

Breaking change warning: cl-dbi prior to march 2020 did not pass arguments with list:

(dbi:execute query 0 "2011-11-01")
;; is now:
(dbi:execute query (list 0 "2011-11-01")

The version in Quicklisp 2020-03-25 is incompatible with older code.

Using `dbi:with-connection` to ensure connections are closed

(dbi:with-connection (conn :sqlite3 :database-name "/home/fukamachi/test.db")
  (let* ((query (dbi:prepare conn "SELECT * FROM People"))
         (query (dbi:execute query)))
    (loop for row = (dbi:fetch query)
          while row
          do (format t "~A~%" row))))

Connection pooling

dbi:connect-cached returns a existing connection if the database is already connected. Since one cache will be created for each thread, it's safe to use in a multithread application.

Description

CL-DBI provides a uniform interface for many SQL databases, so you need not learn a separate API for each database.

This library is especially convenient when you want to use different databases in different environments. For example, you might use MySQL as a production database, but use SQLite3 on your development system. To switch database backends you need only change the arguments to dbi:connect.

Databases

SQLite3
PostgreSQL
MySQL

Installation

This library is available on Quicklisp.

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

(:CL-DBI)

cl-dbi will load another system on the fly depending on your database's driver:

:dbd-sqlite3
:dbd-mysql
:dbd-postgres

You must reference the required one in your system definition if you plan to build an executable (and if you plan to run it on a machine where Quicklisp is not installed).

API

User-Level API

connect [driver-name & params] => <dbi-connection>
connect-cached [driver-name & params] => <dbi-connection>
disconnect [<dbi-connection>] => T or NIL
prepare [conn sql] => <dbi-query>
prepare-cached [conn sql] => <dbi-query>
execute [query &optional params] => something
fetch [result] => a row data as plist
fetch-all [result] => a list of all row data
do-sql [conn sql &optional params]
list-all-drivers [] => (<dbi-driver> ..)
find-driver [driver-name] => <dbi-driver>
with-transaction [conn]
begin-transaction [conn]
commit [conn]
rollback [conn]
ping [conn] => T or NIL
row-count [conn] => a number of rows modified by the last executed INSERT/UPDATE/DELETE
with-connection [connection-variable-name &body body]

Driver-Level API

<dbi-driver>
<dbi-connection>
make-connection [driver params]
disconnect [<dbi-connection>] => T or NIL
prepare [conn sql] => <dbi-query>
prepare-cached [conn sql] => <dbi-query>
fetch-using-connection [conn result] => a row data as plist
do-sql [conn sql &optional params]
execute-using-connection => something
escape-sql => string
begin-transaction [conn]
commit [conn]
rollback [conn]
ping [conn] => T or NIL
row-count [conn] => a number of rows modified by the last executed INSERT/UPDATE/DELETE
free-query-resources [query] free resources associated with a prepared query (this is required only for sqlite3 driver at the moment)

Creating a new driver

Create a subclass of <dbi-driver> and implement following methods.

make-connection
disconnect [<dbi-connection>] => T or NIL
execute-using-connection

These methods can be overriden if needed.

prepare
fetch-using-connection
do-sql
escape-sql

Hook of SQL execution

CL-DBI provides dbi:*sql-execution-hooks*, a hook to run for each SQL execution, particularly used for logging.

The hook function takes these 4 values:

SQL (string)
placeholder parameters (list)
Row count of the results (integer or null)
Took time in miliseconds (integer or null)

The row count and its execution time can be null, if those values are not available for the driver for some reason.

dbi:simple-sql-logger is also provided for printing those values directly to *standard-output*. It can be enabled as so:

(push #'dbi:simple-sql-logger dbi:*sql-execution-hooks*)

Development

Running all tests in the Docker

This will not require you to install Postgres or Mysql. All you need is Docker and Docker Compose.

To run all tests, execute this in the shell:

docker compose up tests

Running specific driver's unittests

Running tests with 'docker compose' does not allow you to debug code in SLIME or SLY. To do this, you need to start databases as separate containers and to make their ports available to the host machine.

Here is how you can start Postgres and Mysql in Docker and run unittests agains them:

Start a docker container with the database

For example, with postgres:

docker run --rm -ti \
       -e POSTGRES_USER=cl-dbi \
       -e POSTGRES_PASSWORD=cl-dbi \
       -p 5432:5432 \
       postgres:10

Or with mysql:

docker run --rm -ti \
       --name cl-dbi \
       -e MYSQL_ROOT_PASSWORD=cl-dbi \
       -p 3306:3306 \
       mysql:8

docker exec -ti \
       cl-dbi \
       mysql -pcl-dbi \
             -e 'create database if not exists `cl-dbi`'

Then in Lisp repl load the unittests:

(ql:quickload :dbi/test)
;; Turn off colors if you are in the Emacs
(setf rove:*enable-colors* nil)
;; Set this to debug failed test
(setf rove:*debug-on-error* t)

And start driver's unittests:

For postgres:

(dbi.test:run-driver-tests :postgres
                           :database-name "postgres"
                           :host "localhost"
                           :port 5432
                           :username "cl-dbi"
                           :password "cl-dbi")

For mysql:

;; Probably you will need to load library manually if
;; it was installed using Homebrew:
(push "/usr/local/opt/mysql-client/lib/" cffi:*foreign-library-directories*)
(cffi:load-foreign-library "libmysqlclient.20.dylib"
                           :search-path "/usr/local/opt/mysql-client/lib/")
(dbi.test:run-driver-tests :mysql
                           :database-name "cl-dbi"
                           :host "127.0.0.1"
                           :port 3306
                           :username "root"
                           :password "cl-dbi")

Also, you can run a single test like this:

(dbi.test:run-driver-tests :mysql
                           :database-name "cl-dbi"
                           :host "127.0.0.1"
                           :port 3306
                           :username "root"
                           :password "cl-dbi"
                           :test-name 'select-after-commit)

Changelog

2020-03

breaking change: dbi:execute now takes its parameters as a list (to avoid the call arguments limit, see !61:

(dbi:execute query 0 "2011-11-01")
;; is now:
(dbi:execute query (list 0 "2020-03-13")