cl-sql-utils

cl-sql-utils - A Common Lisp SQL Database Utility Library

cl-sql-utils is a Common Lisp library providing a high-level interface for working with SQL databases, with initial support for SQLite. It aims to provide an intuitive API similar to Python’s sqlite-utils while embracing Common Lisp idioms.

Features

• Simple database connection management
• Table creation and modification
• Foreign key support
• Schema transformation capabilities
• Row insertion and querying
• (PLANNED) Index management
• (PLANNED) Transaction support
• (IN PROGRESS) robust CLI interface

Installation

This project is not in quicklisp. You will need to checkout this project into your $HOME/quicklisp/local-projects dir. Then:

(ql:quickload :sql-utils)

Run the tests:

# assumes the project is in your quicklisp local-projects dir
sbcl --eval "(ql:quickload '(:sql-utils :sql-utils/tests))" --eval "(5am:run! 'sql-utils-test.sql-utils-tests::sql-utils-suite)" --eval "(quit)"

API Quick Start

;; Create a new database file
(defvar *db* (sql-utils:make-db-connection :sqlite :filename "examples/test.db"))

;; Create a table with constraints
(sql-utils:create-table *db* "people"
                       '(("id" . "INTEGER")
                         ("name" . "TEXT")
                         ("email" . "TEXT")
                         ("age" . "INTEGER"))
                       :pk "id"
                       :not-null '("name" "email")
                       :defaults '(("age" . "0")))

;; Insert some data
(sql-utils:insert (sql-utils:make-table *db* "people")
                 '(:name "Alice" :email "alice@example.com" :age 30))

;; Query the data
(sql-utils:rows (sql-utils:make-table *db* "people"))

API Documentation

Database Operations

• make-db-connection - Create a new database connection
• create-table - Create a new table
• execute - Execute raw SQL

Table Operations

• transform - Modify table schema
• add-column - Add a new column
• add-foreign-key - Add a foreign key constraint
• drop - Drop/delete a table

Query Operations

• lookup - Find a record by primary key
• row-count - Get total number of rows
• row-count-where - Get total number of rows matching a condition
• rows - Query rows from a table
• rows-where - Query rows with conditions
• insert - Insert a single record
• insert-all - Insert multiple records
• delete-record - Delete a single record
• delete-where - Delete records matching a condition

API Examples

Creating Tables with Foreign Keys

;; Create parent table
(sql-utils:create-table *db* "authors"
                       '(("id" . "INTEGER")
                         ("name" . "TEXT"))
                       :pk "id")

;; Create child table with foreign key
(sql-utils:create-table *db* "books"
                       '(("id" . "INTEGER")
                         ("title" . "TEXT")
                         ("author_id" . "INTEGER"))
                       :pk "id"
                       :foreign-keys '(("author_id" "authors" "id")))

Transforming Tables

;; Add a column and change types
(sql-utils:transform table
                    :types '(("age" . "TEXT"))
                    :add-column '(("email" . "TEXT")))

CLI

The project includes a command-line interface for interacting with SQLite databases.

Building the CLI binary executable

To build the CLI binary executable:

# From within the root project directory:
sbcl --load "sql-utils.asd" --eval "(ql:quickload '(:sql-utils :sql-utils/sqlite-cli))" --eval "(dump-system-executable :sql-utils/sqlite-cli)"

This will create an executable at bin/sql-utils.

Available Commands

• create-database - Create a new SQLite database file
  • --enable-wal - Enable Write-Ahead Logging mode
• create-table - Create a new table with specified columns
  • --pk - Specify primary key column(s)
  • --not-null - Mark columns as NOT NULL
  • --default - Set default values for columns
  • --fk - Add foreign key constraints
  • --ignore - Skip if table exists
  • --replace - Replace existing table
  • --strict - Apply STRICT mode
• drop - Drop/delete a table
  • --ignore - Skip if table doesn’t exist
• tables - List tables in the database
  • --fts4 - Show only FTS4 enabled tables
  • --fts5 - Show only FTS5 enabled tables
  • --counts - Include row counts
  • --columns - Show column information
  • --schema - Show table schemas
• rows - Output rows from a table
  • -c/--column - Select specific columns
  • --where - Filter rows with WHERE clause
  • -o/--order - Order results
  • --limit - Limit number of rows
  • --offset - Skip initial rows
• insert - Insert records into a table
  • --pk - Specify column(s) to use as primary key
  • --nl - Read newline-delimited JSON
• delete-record - Delete a single row by primary key value(s)
• delete-where - Delete rows matching a WHERE clause
  • --where - WHERE clause for deletion
  • --analyze - Run ANALYZE after deletion

Command Examples

Create a new database:

$ bin/sql-utils create-database examples/test.db --enable-wal

Create a table with constraints:

bin/sql-utils create-table examples/test.db people \
  id integer \
  name text \
  email text \
  age integer \
  --pk id \
  --not-null name \
  --not-null email \
  --default "age=0"

Insert records:

# Insert a single record
$ echo '(:name "Alice" :email "alice@example.com" :age 30)' | \
  bin/sql-utils insert examples/test.db people

$ echo '(:name "FOO")' | bin/sql-utils insert examples/test.db chickens -

# Insert multiple records (list of plists)
$ echo '((:name "Bob" :email "bob@example.com" :age 25)(:name "Carol" :email "carol@example.com" :age 35))' | bin/sql-utils insert examples/test.db people
$ echo '((:id 82 :name "BAZ" :parent_id 44) (:id 67 :name "BUB" :parent_id 44))' | bin/sql-utils insert examples/test.db chickens -

# Insert with primary key
$ echo '(:id 1 :name "BAR")' | bin/sql-utils insert examples/test.db chickens --pk id -

Query table information:

$ bin/sql-utils tables examples/test.db
test_table
test_table_2
test_table_3
people

# List all tables showing schema
$ bin/sql-utils tables examples/test.db --schema
test_table
  Schema: CREATE TABLE "test_table" (
   [id] INTEGER,
   [name] TEXT DEFAULT 'FOO',
   [parent_id] INTEGER REFERENCES [test_table]([id])
)
test_table_2
  Schema: CREATE TABLE [test_table_2] (
   [id] INTEGER,
   [test_text] TEXT,
   [blah] TEXT
)
test_table_3
  Schema: CREATE TABLE [test_table_3] (
   [id] INTEGER,
   [test_text] TEXT,
   [created_at] TEXT DEFAULT CURRENT_TIMESTAMP
)
people
  Schema: CREATE TABLE "people" ([id] INTEGER, [name] TEXT, [email] TEXT, [age] INTEGER, PRIMARY KEY([id]))


# Show table contents
$ bin/sql-utils rows examples/test.db people
(id 1 name Alice email alice@example.com age 30)
(id 2 name Bob email bob@example.com age 25)
(id 3 name Carol email carol@example.com age 35)

# Filter and order results
$ bin/sql-utils rows examples/test.db people \
  --where "age > 25" \
  --order "name DESC" \
  --limit 10
(id 3 name Carol email carol@example.com age 35)
(id 1 name Alice email alice@example.com age 30)

Delete records:

# Delete a single record by primary key
$ bin/sql-utils delete-record examples/test.db people 1

# Delete records matching a condition
$ bin/sql-utils delete-where examples/test.db people --where "age < 18"

# Delete all records in a table
$ bin/sql-utils delete-where examples/test.db people

# Drop/delete an entire table
$ bin/sql-utils drop examples/test.db old_table --ignore

Contributing

Contributions are welcome! Please feel free to submit pull requests.

1. Fork the repository
2. Create your feature branch
3. Commit your changes
4. Push to the branch
5. Create a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

This project was heavily inspired by the Python sqlite-utils library by Simon Willison.