(rontolisp) docs

O/R Mapping (mito, sxql)

Mito — an O/R mapper for Common Lisp — loads verbatim via (ql:quickload "mito"), and that pulls the whole system: mito-core (the DAO layer), mito-migration (schema diffs and migration files) and lack-middleware-mito. Its query clauses are written in SxQL, which also loads and works on its own.

PostgreSQL is the only database. dbd-mysql and dbd-sqlite3 need FFI and are absent; the PostgreSQL driver rides the cl-postgres stack, so mito reaches every backend that can open a TCP socket — the interpreter, a JVM class and a WASM component — and not WASM Preview 1.

Defining a table and connecting

dbd-postgres must be quickloaded explicitly, next to mito: dbi:connect resolves its driver over the already-loaded systems, and a compiled program cannot load a system at run time.

deftable is a defclass over mito's dao-table-class metaclass. The metaclass protocol runs at DEFINITION time, so a deftable must be a top-level form with literal options. mito:table-definition renders the DDL mito would create — it asks the driver for its type, so it needs a live connection:

$ cat blog.lisp
(ql:quickload '("mito" "dbd-postgres"))

(mito:deftable article ()
  ((title :col-type (:varchar 64))
   (body  :col-type (or :text :null))))

(mito:connect-toplevel :postgres :database-name "blog" :username "postgres"
                       :password "secret" :host "127.0.0.1" :port 5432)

(dolist (statement (mito:table-definition 'article))
  (format t "~a~%" (sxql:yield statement)))
$ rontolisp blog.lisp
CREATE TABLE article (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    title VARCHAR(64) NOT NULL,
    body TEXT,
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
)

The id primary key and the created_at / updated_at pair are mito's defaults (:auto-pk, record-timestamps-mixin); (:auto-pk :uuid) switches the key to a generated v4 UUID stored as VARCHAR(36), and (:table-name "...") overrides the derived table name.

mito:disconnect-toplevel closes the connection. Call it: the trivial-garbage dependency resolves to a no-op finalizer shim, so nothing reclaims a connection for you.

CRUD

$ cat crud.lisp
(ql:quickload '("mito" "dbd-postgres"))
;; ... the deftable and connect-toplevel from above ...

(mito:ensure-table-exists 'article)

(let ((a (mito:create-dao 'article :title "Hello" :body "First post")))
  (format t "~a ~a~%" (mito:object-id a) (slot-value a 'title)))
(mito:insert-dao (make-instance 'article :title "Hello again" :body nil))

(let ((found (mito:find-dao 'article :title "Hello")))
  (setf (slot-value found 'body) "Edited")
  (mito:save-dao found))

(format t "~a~%"
        (mapcar (lambda (a) (slot-value a 'title))
                (mito:select-dao 'article
                                 (sxql:where (:like :title "Hello%"))
                                 (sxql:order-by :id))))

(mito:delete-dao (mito:find-dao 'article :title "Hello again"))
(format t "~a~%" (length (mito:select-dao 'article)))
$ rontolisp crud.lisp
1 Hello
(Hello Hello again)
1

Slots are read with slot-value: mito injects its :conc-name readers from a metaclass hook that rontolisp records as metaobject data only, so the article-title-style accessors are not defined (see "Current limits"). mito:retrieve-by-sql and mito:execute-sql are there for the raw-SQL cases.

Schema migration

There are two ways in, and they differ in which backends can run them.

Diff against the live schema — all three backends. mito.migration:migration-expressions compares the current class definition with the table as it exists and answers the statements that would close the gap; migrate-table runs them in a transaction:

$ cat migrate-table.lisp
(ql:quickload '("mito" "dbd-postgres"))
;; ... the same connect-toplevel, and `article` redefined one column wider ...
(mito:deftable article ()
  ((title :col-type (:varchar 64))
   (body  :col-type (or :text :null))
   (tag   :col-type (or (:varchar 16) :null))))

(dolist (statement (mito.migration:migration-expressions 'article))
  (format t "~a~%" (sxql:yield statement)))
(mito.migration:migrate-table 'article)
(format t "~a~%" (mito.migration:migration-expressions 'article))
$ rontolisp migrate-table.lisp
ALTER TABLE article ADD COLUMN tag character varying(16)
NIL

Migration files — interpreter and JVM only (see "Current limits"). generate-migrations writes db/schema.sql plus a timestamped .up.sql / .down.sql pair, migration-status reports what is applied, and migrate applies the pending files (parsing them with esrap):

$ cat db.lisp
(ql:quickload '("mito" "dbd-postgres"))
;; ... the deftable and connect-toplevel from above ...
(mito:generate-migrations #P"db/")
(mito:migration-status #P"db/")
(mito:migrate #P"db/")
$ rontolisp db.lisp
CREATE TABLE "article" (
    "id" BIGSERIAL NOT NULL PRIMARY KEY,
    "title" VARCHAR(64) NOT NULL,
    "body" TEXT,
    "created_at" TIMESTAMPTZ,
    "updated_at" TIMESTAMPTZ
);
Successfully generated: db/migrations/20260804003900.up.sql
 Status   Migration ID
--------------------------
  down    20260804003900
Applying 'db/schema.sql'...
-> CREATE TABLE "article" (
    "id" BIGSERIAL NOT NULL PRIMARY KEY,
    "title" VARCHAR(64) NOT NULL,
    "body" TEXT,
    "created_at" TIMESTAMPTZ,
    "updated_at" TIMESTAMPTZ
);
-> CREATE TABLE IF NOT EXISTS "schema_migrations" (
    "version" BIGINT PRIMARY KEY,
    "applied_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "dirty" BOOLEAN NOT NULL DEFAULT false
);
Successfully updated to the version 20260804003900.

SQL generation with SxQL

SxQL is what select-dao's clauses are, and it stands on its own — (ql:quickload "sxql") opens no sockets and renders identical SQL on all four backends. sxql:yield answers the SQL string and the bind-value list as two values:

$ cat query.lisp
(ql:quickload "sxql")
(multiple-value-bind (sql binds)
    (sxql:yield (sxql:select :*
                  (sxql:from :article)
                  (sxql:where (:and (:like :title "%lisp%")
                                    (:in :status '("published" "draft"))))
                  (sxql:order-by (:desc :id))
                  (sxql:limit 10)))
  (format t "~a~%~a~%" sql binds))
(format t "~a~%" (sxql:yield (sxql:insert-into :article
                               (sxql:set= :title "Hello" :body "First post"))))
(format t "~a~%" (sxql:yield (sxql:update :article
                               (sxql:set= :title "Hi")
                               (sxql:where (:= :id 1)))))
(format t "~a~%" (sxql:yield (sxql:delete-from :article (sxql:where (:= :id 1)))))
$ rontolisp query.lisp
SELECT * FROM article WHERE ((title LIKE ?) AND (status IN (?, ?))) ORDER BY id DESC LIMIT 10
(%lisp% published draft)
INSERT INTO article (title, body) VALUES (?, ?)
UPDATE article SET title = ? WHERE (id = ?)
DELETE FROM article WHERE (id = ?)

create-table, drop-table, alter-table with add-column, left-join ... :on, limit / offset and order-by with :desc / nulls are all there. Binding sxql:*use-placeholder* to nil renders the values inline instead of as ? placeholders.

Like every macro-heavy library, hot query construction belongs on a compiled backend — the interpreter re-expands macros on every evaluation.

Backends

  • Interpreter — everything above.

  • JVM class — rontolisp blog.lisp -o Blog.class && java Blog. The compiled class is self-contained.

  • WASM component (--component) — needs both wasmtime features and both socket permissions:

    rontolisp blog.lisp -o blog.wasm --component
wasmtime run -W gc=y -W exceptions=y -S tcp=y -S inherit-network=y blog.wasm

    The :host must be an IPv4 literal — hostname lookup is unwired on WASM.

  • WASM Preview 1 has no host socket API by design. A mito program compiles there (socket call sites become call-time errors, so spliced dead code builds), and the first socket call fails loudly at run time with a message naming the backends that work.

Current limits

  • PostgreSQL only. dbd-mysql / dbd-sqlite3 need FFI. mito's own mysql / sqlite3 source files still load; they are simply never selected.
  • :conc-name accessors are not generated. mito adds its readers and writers from a metaclass hook, and accessor methods here come from the original defclass form, which carried none. Use slot-value.
  • SQL function operators are interpreter-only: (:count ...), (:sum ...), (:max ...) and anything else SxQL resolves as a function call — and therefore mito:count-dao — signal on the JVM and WASM backends. Count with (length (mito:select-dao ...)) there.
  • Writing migration files is interpreter + JVM. generate-migrations and the file-deleting branch of migrate need directory creation and file removal, which the WASM backends do not import; they signal at the call. The diff-and-apply path (migration-expressions, migrate-table, migration-status, and migrate over files that already exist) runs everywhere.
  • A bare :references needs a :col-type. (other-id :references (other id)) alone signals — an upstream defect, reproduced identically by SBCL on the same sources. Write (other-id :col-type :bigint :references (other id)).
  • Adding a NOT NULL column that has an :initform makes migrate-table emit DEFAULT ? with an empty bind list, and PostgreSQL answers there is no parameter $1 — again upstream, again identical on SBCL. Wrap the call: (let ((sxql:*use-placeholder* nil)) (mito.migration:migrate-table 'article)).

See also: Systems (asdf) for the whole library catalogue and the cl-dbi / cl-postgres layers underneath, TCP Sockets for the per-backend socket rules, and Clack Web Applications for the web side lack-middleware-mito plugs into.