CL-DBI - Database independent interface for Common Lisp

Usage

Connecting and executing a query

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

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

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"))
         (result (dbi:execute query)))
    (loop for row = (dbi:fetch result)
       while row
       do (format t "~A~%" row))))

Warning

This library is still under development and considered ALPHA quality. APIs are likely to change.

Description

CL-DBI provides the same interface for multiple SQL databases. You need not learn the API of each database.

This library is especially convenient when you want to use different databases in different environments. For example, you may 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 will be available on Quicklisp when ready for use.

API

User-Level API

connect [driver-name & params] => <dbi-connection>
disconnect [<dbi-connection>] => T or NIL
prepare [conn sql] => <dbi-query>
execute [query & params] => something
fetch [result] => a row data as plist
fetch-all [result] => a list of all row data
do-sql [conn sql & 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] =&gt 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>
fetch-using-connection [conn result] => a row data as plist
do-sql [conn sql & params]
execute-using-connection => something
escape-sql => string
begin-transaction [conn]
commit [conn]
rollback [conn]
ping [conn] => T or NIL
row-count [conn] =&gt a number of rows modified by the last executed INSERT/UPDATE/DELETE

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

Dependencies

cl-annot
CL-Syntax
SPLIT-SEQUENCE
closer-mop

Author

Eitarow Fukamachi (e.arrows@gmail.com)

Copyright

License

Licensed under the LLGPL License.

lambdasakura/cl-dbi