Cl-Batis is a library for generating prepared statement queries and their parameters. This library focuses on SQL definition and generation, delegating query execution to other libraries.
There are two types of methods for defining SQL:
update- for INSERT, UPDATE, DELETE statementsselect- for SELECT statements
When using (cl-syntax:use-syntax :annot), @update and @select annotations can be used.
@update ("insert into product (id, name, price) values (:id, :name, :price)")
(defsql register-product (id name price))
@update ("update
product "
(sql-set
(sql-cond (not (null name))
" name = :name, ")
(sql-cond (not (null price))
" price = :price "))
(sql-where
" id = :id "))
(defsql update-product (id name price))@select ("select name, price from product where id = :id")
(defsql search-product (id))
@select ("select id, name, price from product"
(sql-where
(sql-cond (not (null name))
" and name = :name ")
(sql-cond (not (null price_low))
" and price >= :price_low ")
(sql-cond (not (null price_high))
" and price <= :price_high "))
" order by id ")
(defsql filter-product (name price_low price_high))Use gen-sql-and-params to generate the prepared statement SQL and its parameters.
Parameters should be passed as a property list:
(gen-sql-and-params register-product '(:id 1 :name "NES" :price 14800))
; => "insert into product (id, name, price) values (?, ?, ?)"
; (1 "NES" 14800)
(gen-sql-and-params filter-product '(:name nil :price_low 20000 :price_high nil))
; => "select id, name, price from product WHERE price >= ? order by id "
; (20000)Cl-Batis supports dynamic IN clause expansion based on parameter types:
- ATOM (non-list): Expands to a single
? - LIST: Expands to
?, ?, ...based on list length - NIL: Expands to a single
?withnilvalue
@select ("SELECT * FROM products WHERE id IN (:ids)")
(defsql find-products-by-ids (ids))
;; With a list of values
(gen-sql-and-params find-products-by-ids '(:ids (1 2 3)))
; => "SELECT * FROM products WHERE id IN (?, ?, ?)"
; (1 2 3)
;; Dynamically change the number of values
(gen-sql-and-params find-products-by-ids '(:ids (1 2 3 4 5)))
; => "SELECT * FROM products WHERE id IN (?, ?, ?, ?, ?)"
; (1 2 3 4 5)
;; With a single value (as ATOM)
(gen-sql-and-params find-products-by-ids '(:ids 42))
; => "SELECT * FROM products WHERE id IN (?)"
; (42)
;; With NIL (empty list)
(gen-sql-and-params find-products-by-ids '(:ids nil))
; => "SELECT * FROM products WHERE id IN (?)"
; (NIL)@select ("SELECT * FROM users WHERE id NOT IN (:excluded_ids)")
(defsql find-users-not-in (excluded_ids))
(gen-sql-and-params find-users-not-in '(:excluded_ids (10 20)))
; => "SELECT * FROM users WHERE id NOT IN (?, ?)"
; (10 20)@select ("SELECT * FROM products WHERE category_id = :category_id AND id IN (:ids)")
(defsql find-products-by-category (category_id ids))
(gen-sql-and-params find-products-by-category '(:category_id 10 :ids (1 2 3)))
; => "SELECT * FROM products WHERE category_id = ? AND id IN (?, ?, ?)"
; (10 1 2 3)When dealing with optional IN clauses, use sql-where to conditionally include them:
@select ("SELECT * FROM orders"
(sql-where
"user_id = :user_id"
(when product_ids "AND product_id IN (:product_ids)")
(when status "AND status = :status")))
(defsql find-orders (user_id product_ids status))
;; All conditions
(gen-sql-and-params find-orders
'(:user_id 100 :product_ids (1 2 3) :status "shipped"))
; => "SELECT * FROM orders WHERE user_id = ? AND product_id IN (?, ?, ?) AND status = ?"
; (100 1 2 3 "shipped")
;; Without product_ids
(gen-sql-and-params find-orders
'(:user_id 100 :product_ids nil :status "shipped"))
; => "SELECT * FROM orders WHERE user_id = ? AND status = ?"
; (100 "shipped")The traditional method (multiple parameters with commas) still works:
@select ("SELECT * FROM users WHERE id IN (:id1, :id2, :id3)")
(defsql find-by-three-ids (id1 id2 id3))
(gen-sql-and-params find-by-three-ids '(:id1 1 :id2 2 :id3 3))
; => "SELECT * FROM users WHERE id IN (?, ?, ?)"
; (1 2 3)When nil is passed to an IN clause parameter, it expands to IN (?) with a nil value.
Since IN (NULL) typically returns 0 rows in SQL, it's recommended to use sql-where
to conditionally exclude the IN clause when the list is empty.
Library Responsibility: Generate ? placeholders based on parameter types
Developer Responsibility: Handle empty lists appropriately using dynamic SQL construction
In dynamic conditions, if sql-cond returns nothing, you would end up with SQL that looked like this:
select * from product whereThis would fail. And, if only the second condition was met, you would end up with SQL that looked like this:
select * from product where
and valid_flag = '1'This would also fail.
So, cl-batis provides sql-where function.
@select
("select * from product"
(sql-where
(sql-cond (not (null price))
" price = :price")
(sql-cond (not (null valid_flag))
" and valid_flag = :valid_flag ")))
(defsql search-by-product (price valid_flag))The sql-where knows to only insert WHERE if there is any condition.
Furthermore, if that content begins with AND or OR, it strips it off.
@update
("update product"
(sql-set
(sql-cond (not (null price))
" price = :price, ")
(sql-cond (not (null name))
" name = :name "))
(sql-where
" id = :id "))
(defsql update-product-info (id price name))There is a similar solution for dynamic update statements called sql-set.
The sql-set knows to strip the last comma off.
This library is available on Quicklisp.
use qlot.
- tamura shingo (tamura.shingo@gmail.com)
Copyright (c) 2017, 2024, 2025 tamura shingo (tamura.shingo@gmail.com)
Licensed under the MIT License.
The following features are deprecated and will be removed in a future version. These features were related to session management and SQL execution, which are now delegated to other libraries.
;; with CL-DBI connection
(defparameter *conn-dbi* (dbi:connect :mysql
:database-name "batis"
:username "nobody"
:password "nobody"))
(defparameter *session* (create-sql-session *conn-dbi*))
;; with CL-DBI-Connection-Pool
(defparameter *conn-pool* (dbi-cp:make-dbi-connection-pool :mysql
:database-name "batis"
:username "nobody"
:password "nobody"))
(defparameter *session* (create-sql-session *conn-pool*))
;; direct
(defparameter *session* (create-sql-session :mysql
:database-name "batis"
:username "nobody"
:password "nobody"))
Cl-Batis does not support DDL.
If you want to use DDL, use do-sql.
(do-sql session "truncate table product")(update-one *session* register-product :id 1 :name "NES" :price 14800)(select-one *session* search-product :id 1)
-> (:|name| "NES" :|price| 14800))(select-list *session* filter-product :price_low 20000)
->((:|id| 2 :|name| "SNES" :|price| 25000)
(:|id| 3 :|name| "MEGA DRIVE" :|price| 21000)
(:|id| 4 :|name| "PC Engine" :|price| 24800)))When exiting the transaction-macro block, it will automatically commit.
(with-transaction *session*
; blah blah blah
)To explicitly commit, use commit.
(with-transaction *session*
(update-one *session* register-product :id 1 :name "NES" :price 14800)
(commit *session*))You can roll back using rollback.
(with-transaction *session*
;blah
;blah
;blah
(rollback *session*))(close-sql-session *session*)- SQLite3
- PostgreSQL
- MySQL
;;;
;;; create session
;;;
CL-USER> (defparameter session
(create-sql-session :mysql
:database-name "scdata"
:username "root"
:password "password"))
SESSION
;;;
;;; create table
;;;
CL-USER> (do-sql session "create table product (id integer primary key, name varchar(20) not null, price integer not null)")
; No value
;;;
;;; define sql
;;;
CL-USER> (select (" select * from product where id = :id ")
(defsql select-product (id)))
SELECT-PRODUCT
CL-USER> (select (" select name, price from product "
(sql-where
(sql-cond (not (null name))
" and name = :name ")
(sql-cond (not (null price_low))
" and price >= :price_low ")
(sql-cond (not (null price_high))
" and price <= :price_high "))
" order by id ")
(defsql select-product-by-name-or-price (name price_low price_high)))
; in:
; SELECT (" select name, price from product "
; (SQL-WHERE (SQL-COND (NOT (NULL NAME)) " and name = :name ")
; (SQL-COND (NOT (NULL PRICE_LOW))
; " and price >= :price_low ")
; (SQL-COND (NOT (NULL PRICE_HIGH))
; " and price <= :price_high "))
; " order by id ")
; (NULL NAME)
; --> IF
; ==>
; NAME
;
; caught STYLE-WARNING:
; reading an ignored variable: NAME
; (NULL PRICE_LOW)
; --> IF
; ==>
; PRICE_LOW
;
; caught STYLE-WARNING:
; reading an ignored variable: PRICE_LOW
; (NULL PRICE_HIGH)
; --> IF
; ==>
; PRICE_HIGH
;
; caught STYLE-WARNING:
; reading an ignored variable: PRICE_HIGH
;
; compilation unit finished
; caught 3 STYLE-WARNING conditions
SELECT-PRODUCT-BY-NAME-OR-PRICE
CL-USER> (update ("insert into product (id, name, price) values (:id, :name, :price)")
(defsql register-product (id name price)))
REGISTER-PRODUCT
;;;
;;; insert
;;;
CL-USER> (update-one session register-product :id 1 :name "NES" :price 14800)
(1)
CL-USER> (update-one session register-product :id 2 :name "SNES" :price 25000)
(1)
CL-USER> (update-one session register-product :id 3 :name "MEGA DRIVE" :price 21000)
(1)
CL-USER> (update-one session register-product :id 4 :name "PC Engine" :price 24800)
(1)
;;;
;;; select one record
;;;
CL-USER> (select-one session select-product :id 1)
(:|id| 1 :|name| "NES" :|price| 14800)
;;;
;;; select some records
;;;
CL-USER> (select-list session select-product-by-name-or-price)
((:|name| "NES" :|price| 14800) (:|name| "SNES" :|price| 25000)
(:|name| "MEGA DRIVE" :|price| 21000) (:|name| "PC Engine" :|price| 24800))
CL-USER> (select-list session select-product-by-name-or-price :price_low 20000)
((:|name| "SNES" :|price| 25000) (:|name| "MEGA DRIVE" :|price| 21000)
(:|name| "PC Engine" :|price| 24800))
CL-USER> (select-list session select-product-by-name-or-price :price_low 20000 :price_high 22000)
((:|name| "MEGA DRIVE" :|price| 21000))
CL-USER> (select-list session select-product-by-name-or-price :name "PC Engine")
((:|name| "PC Engine" :|price| 24800))