(defvar *connection*
(dbi:connect :mysql
:database-name "test"
:username "nobody"
:password "1234"))
(defvar *connection*
(dbi:connect :sqlite3
:database-name "/home/gt/test.sqlite3"))
(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.
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))))
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.
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
.
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).
Create a subclass of <dbi-driver> and implement following methods.
These methods can be overriden if needed.
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:
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*)
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 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)
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")
The version in Quicklisp 2020-03-25 is incompatible with older code.
Copyright (c) 2011 Eitaro Fukamachi (e.arrows@gmail.com)
Licensed under the BSD 2-Clause License.