Pure Common Lisp Firebird Database Client & Driver Full 1:1 feature parity with
node-firebird.
cl-firebird is a pure Common Lisp client for the Firebird database wire protocol (supporting Firebird 3.0, 4.0, 5.0, and 6.0+). It requires no C libraries or foreign function bindings (FFI) — it communicates directly over TCP sockets with SRP authentication, wire encryption, connection pooling, and full protocol capability negotiation.
- Installation
- Quick Start
- Connection Management
- Querying & Parameter Binding
- Type Conversion & Custom Parsers (
type-cast) - Prepared Statement Cache
- Connection Pooling (
connection-pool) - Streaming & Bulk Batch Execution
- Firebird 6.0 Features
- Database Events (
POST_EVENT) - Service Manager (
service-manager) - Testing Matrix
- License
cl-firebird can be loaded via Quicklisp or ASDF:
(ql:quickload :cl-firebird)Or push the repository directory onto your ASDF central registry:
(push #p"/path/to/cl-firebird/" asdf:*central-registry*)
(asdf:load-system :cl-firebird)(use-package :cl-firebird)
;; 1. Connect using a URI
(with-connection ("firebird://SYSDBA:masterkey@localhost:3050/employee?lowercase-keys=true&named-placeholders=true")
;; 2. Query with named placeholders
(let ((users (query "SELECT id, name FROM users WHERE role = :role" '(:role "admin"))))
(dolist (u users)
(format t "User ID: ~a, Name: ~a~%" (getf u :id) (getf u :name)))))Pass 12-factor standard URIs directly to connect, with-connection, or make-pool:
;; Standard database alias
(connect "firebird://SYSDBA:masterkey@db.example.com:3050/employee")
;; Explicit file path (leading double-slash indicates root /)
(connect "firebird://SYSDBA:masterkey@db.example.com:3050//var/fb/production.fdb?encoding=UTF8&pageSize=8192")
;; IPv6 support
(connect "firebird://SYSDBA:masterkey@[::1]:3050/employee")Traditional Firebird connection strings ([host[/port]:]database) are fully supported:
(connect "db.example.com/3051:/var/fb/prod.fdb" :user "SYSDBA" :password "masterkey")All standard connection settings can be supplied via URI query parameters or keyword arguments:
| Option | Type | Default | Description |
|---|---|---|---|
:host |
string | "localhost" |
Firebird server host |
:port |
integer | 3050 |
Server TCP port |
:database |
string | nil |
Database path or alias |
:user |
string | "SYSDBA" |
Username |
:password |
string | nil |
User password |
:role |
string | nil |
SQL role |
:charset / :encoding |
string | "UTF8" |
Wire encoding |
:page-size |
integer | 4096 |
Page size for new database creation |
:lowercase-keys |
boolean | nil |
Return column name keywords in lowercase (e.g. :id) |
:blob-as-text |
boolean | nil |
Automatically fetch text BLOBs (subtype 1) as strings |
:blob-chunk-size |
integer | 1024 |
Writing BLOB chunk size |
:blob-read-chunk-size |
integer | 1024 |
Reading BLOB chunk size |
:named-placeholders |
boolean | nil |
Enable :name SQL markers and parameter maps |
:type-cast |
function | nil |
Custom type decoder hook (lambda (col default-fn) ...) |
:statement-cache-size |
integer | 0 |
Per-connection statement cache size (0 = disabled) |
:wire-crypt |
boolean | t |
Request wire encryption (FB >= 3) |
:auth-plugin-name |
symbol | :srp |
Authentication plugin (:srp, :srp256, :legacy) |
Attaches to an existing database. If the database file/alias does not exist, it creates it automatically:
(defvar *conn* (attach-or-create "firebird://SYSDBA:masterkey@localhost:3050/my_new_db.fdb?pageSize=8192"))(query "INSERT INTO users (id, name, created) VALUES (?, ?, ?)" 1 "Peter" '(:timestamp 2026 7 31 12 0 0))
(query "SELECT * FROM users WHERE name = ?" "Peter")Enable named-placeholders on connection or query to use :name markers with property lists, association lists, or hash-tables:
(with-connection ("firebird://SYSDBA:masterkey@localhost:3050/employee?named-placeholders=true")
(query "SELECT * FROM users WHERE name = :name AND age > :age"
'(:name "Peter" :age 25))
;; Repeated placeholder markers bind each occurrence automatically
(query "SELECT * FROM t WHERE a = :val OR b = :val" '(:val 42))
;; Alist or Hash-Table parameters
(query "INSERT INTO audit (msg, code) VALUES (:msg, :code)"
'(("msg" . "Login success") ("code" . 200))))Placeholders inside single quotes '...', quoted identifiers "...", or comments -- are safely ignored.
Prevent SQL injection when building dynamic queries:
(escape "O'Connor") ; => "'O''Connor'"
(escape 42) ; => "42"
(escape nil) ; => "NULL"
(escape t) ; => "TRUE"
(escape #(1 15 255)) ; => "x'010fff'"
(escape '(:date 2026 7 31)) ; => "'2026-07-31'"Override default type decoding per SQL type or result column:
(defvar *conn*
(connect "firebird://SYSDBA:masterkey@localhost:3050/employee"
:type-cast (lambda (col default-fn)
(cond
;; Format BIGINT columns as formatted strings
((eq (getf col :type-name) :int64)
(format nil "BIGINT-~a" (funcall default-fn)))
;; Default decoding for all other columns
(t (funcall default-fn))))))col contains: :type, :type-name, :sub-type, :scale, :length, :field, :relation, :alias.
Enable transparent statement reuse per connection:
(with-connection ("firebird://SYSDBA:masterkey@localhost:3050/employee?statementCacheSize=100")
;; First execution prepares server-side statement
(query "SELECT * FROM users WHERE id = ?" 1)
;; Second execution reuses cached statement without re-preparing
(query "SELECT * FROM users WHERE id = ?" 2))Create thread-safe, auto-reaping connection pools:
;; Create a pool of up to 10 connections (minimum 2 idle, 30s idle timeout)
(defvar *pool*
(create-pool 10 "firebird://SYSDBA:masterkey@localhost:3050/employee?min=2&idleTimeoutMillis=30000"))
;; Execute queries using pooled connections
(with-pooled-connection (conn *pool*)
(query "SELECT * FROM employee"))
;; Monitor live pool status
(format t "Total: ~a | Idle: ~a | Active: ~a | Waiting: ~a~%"
(pool-total-count *pool*)
(pool-idle-count *pool*)
(pool-active-count *pool*)
(pool-waiting-count *pool*))
;; Destroy pool when shutting down
(pool-destroy *pool*)Process massive result sets with low memory overhead:
(sequentially "SELECT * FROM large_log_table WHERE level = ?" '("ERROR")
(lambda (row index)
(format t "Row ~a: ~a~%" index (getf row :message)))
(lambda (total-count)
(format t "Processed ~a rows total.~%" total-count)))Execute a statement across multiple parameter sets efficiently:
(execute-batch "INSERT INTO users (id, name) VALUES (?, ?)"
'((1 "Alice")
(2 "Bob")
(3 "Charlie")))(create-tablespace "TS_DATA" "/var/fb/ts_data.ts")
(alter-tablespace "TS_DATA" "/var/fb/ts_data_new.ts")
(create-schema "HR_SCHEMA" :tablespace "TS_DATA")
(drop-tablespace "TS_DATA")Register event listeners for Firebird database events:
(defvar *event-id*
(attach-event *conn* '("EVT_INSERT" "EVT_UPDATE")
(lambda (event-name count)
(format t "Received event ~a (count: ~a)~%" event-name count))))
;; Unregister listener
(detach-event *conn* *event-id*)Perform server administration over the service manager:
(defvar *svc* (service-connect :host "localhost" :user "SYSDBA" :password "masterkey"))
;; Database backup & restore
(service-backup *svc* "/var/fb/employee.fbk" :database-file "employee")
(service-restore *svc* "/var/fb/employee.fbk" "employee_restored.fdb")
;; User Management
(service-add-user *svc* "john" "secret_pass" :first-name "John" :last-name "Doe")
(service-get-users *svc*)
(service-delete-user *svc* "john")
;; Server info & traces
(service-get-server-info *svc*)
(service-start-trace *svc* "my_trace" "config_data")cl-firebird includes a comprehensive FiveAM test suite and matrix runner testing feature modules and Firebird versions (3.0, 4.0, 5.0, 6.0).
Run testing matrix:
sbcl --load test/run-matrix.lisp --quitOr via ASDF:
(asdf:test-system :cl-firebird)