orm

A database-agnostic ORM for Carp.

derive-model reads a deftype's fields with members and emits CRUD functions in the type's module. The SQL dialect and row marshalling are delegated to a backend module, so the same model definition works against any database with a backend.

Installation

The package ships the ORM core and a set of backends. Load the backend you want via the two-argument form of load:

; SQLite (also pulls in the carpentry-org/sqlite3 package transitively)
(load "git@github.com:carpentry-org/orm@0.5.2" "backends/sqlite3.carp")

If you are writing your own backend you can load just the core:

(load "git@github.com:carpentry-org/orm@0.5.2")

This gives you the derive-model macro without pulling in any database driver.

Usage

Defining a model

(deftype Item [id Int text String done Bool])
(derive-model Item SQLiteBackend [id Int])

The first argument to derive-model is the type, the second is the backend module, and the third is the array of primary-key fields (one or more).

Nullable columns

A field typed (Maybe T) becomes a nullable column. Maybe.Nothing is written as SQL NULL, and a NULL read back becomes Maybe.Nothing:

(deftype Profile [id Int handle String bio (Maybe String) age (Maybe Int)])
(derive-model Profile SQLiteBackend [id Int])

(ignore
  (Profile.insert &db
                  &(Profile.init 0 @"ann" (Maybe.Just @"hi") (Maybe.Nothing))))

(Profile.find-where &db "age IS NULL" &[])
; => (Success [(Profile 1 @"ann" (Just @"hi") (Nothing))])

update and upsert set a column back to NULL by writing Maybe.Nothing, and IS NULL / IS NOT NULL work in any WHERE clause. Aggregates follow SQL semantics and skip NULL rows, so sum over a column that is NULL everywhere returns 0.0.

Primary-key fields cannot be nullable, and neither (Maybe (Maybe T)) nor a Maybe of an unsupported type is allowed; both raise a macro error at expansion time.

create-table emits NOT NULL for every column that is not a Maybe, so the schema enforces what the Carp types claim. Since the DDL is CREATE TABLE IF NOT EXISTS, this only applies to tables created from now on; existing databases keep their current schema.

Primary keys

A model whose primary key is a single Int or Long field gets a database-assigned key: insert leaves the PK column out of the statement and returns the new rowid as a Long.

(deftype Item [id Int text String done Bool])
(derive-model Item SQLiteBackend [id Int])

(Item.insert &db &(Item.init 0 @"buy milk" false))
; => (Success 1)

Every other primary key is user-supplied, so insert writes all fields — including the PK — and returns (Result () String). That covers composite keys:

(deftype Enrollment [sid Int cid Int grade String])
(derive-model Enrollment SQLiteBackend [sid Int cid Int])

(ignore (Enrollment.insert &db &(Enrollment.init 1 101 @"A")))
(Enrollment.find-by-id &db &1 &101)
(Enrollment.delete-by-id &db &1 &101)

and single keys of a non-integer type, the natural shape for slugs, usernames, or ISO codes:

(deftype Country [code String name String population Int])
(derive-model Country SQLiteBackend [code String])

(ignore (Country.insert &db &(Country.init @"de" @"Germany" 84)))
(Country.find-by-id &db "de")
; => (Success (Country @"de" @"Germany" 84))

find-by-id and delete-by-id always accept one argument per PK field.

Creating and dropping the table

(let-do [db (Result.unsafe-from-success (SQLite3.open "app.db"))]
  (ignore (Item.create-table &db))
  (SQLite3.close db))

create-table runs CREATE TABLE IF NOT EXISTS, so it is safe to call on every startup. It returns (Result () String) — an error if the statement fails (e.g. the database is read-only).

(ignore (Item.drop-table &db))

drop-table runs DROP TABLE IF EXISTS and also returns (Result () String), so dropping a table that is not there succeeds.

Inserting

(match (Item.insert &db &(Item.init 0 @"buy milk" false))
  (Result.Success new-id) (println* new-id)
  (Result.Error e) (IO.errorln &e))

insert writes the non-PK fields and returns the auto-assigned rowid as a Long, wrapped in a Result. The PK field on the input row is ignored, which is why we pass 0. This applies to models with a single Int or Long primary key; see Primary keys for the other shapes, where insert writes the PK too and returns (Result () String).

To insert many rows at once, batch-insert sends a single INSERT ... VALUES (...), (...), ... statement instead of one statement per row:

(ignore (Item.batch-insert &db &[(Item.init 0 @"buy milk" false)
                                 (Item.init 0 @"write carp" true)]))

It returns (Result () String) — no rowids — and an empty array is a successful no-op. It writes the same columns as insert: the PK is omitted for a single Int/Long PK and written for every other shape.

upsert inserts a row, or overwrites the non-PK fields of the row that is already there, using INSERT ... ON CONFLICT DO UPDATE:

(ignore (Item.upsert &db &(Item.init 1 @"buy oat milk" true)))

Unlike insert, upsert always writes every column, so you have to supply the PK yourself even for a model whose insert would have the database assign one. It returns (Result () String).

Reading

; All rows
(match (Item.find-all &db)
  (Result.Success items) (println* &items)
  (Result.Error e) (IO.errorln &e))

; By primary key
(match (Item.find-by-id &db &1)
  (Result.Success item) (println* &item)
  (Result.Error _) (println* "not found"))

The PK argument is passed as a reference even for value types like Int.

; By WHERE clause with parameterized values
(match (Item.find-where &db "done = ?1" &[(to-sqlite3 0)])
  (Result.Success items) (println* &items)
  (Result.Error e) (IO.errorln &e))

; Multiple conditions
(match (Item.find-where &db "done = ?1 AND text = ?2"
                        &[(to-sqlite3 1) (to-sqlite3 @"buy milk")])
  (Result.Success items) (println* &items)
  (Result.Error e) (IO.errorln &e))

find-where takes a SQL WHERE clause as a string and an array of SQLite3.Type parameter values. Parameters use positional placeholders (?1, ?2, …) and are bound safely — they are never interpolated into the SQL string. The function returns all matching rows.

; One row, no primary key needed
(match (Item.find-first &db)
  (Result.Success item) (println* &item)
  (Result.Error _) (println* "table is empty"))

; One row matching a WHERE clause
(match (Item.find-first-where &db "done = ?1" &[(to-sqlite3 0)])
  (Result.Success item) (println* &item)
  (Result.Error _) (println* "nothing left to do"))

find-first and find-first-where add LIMIT 1 and return a single (Result T String) rather than an array. Neither adds an ORDER BY, so which row you get is up to the database; use find-page with a limit of 1 when it matters. Both are an Error when nothing matches, so they cannot tell an empty result from a failed query — reach for find-where if you need to.

; Is there anything left to do?
(match (Item.exists? &db "done = ?1" &[(to-sqlite3 0)])
  (Result.Success any?) (println* any?)
  (Result.Error e) (IO.errorln &e))

exists? returns (Result Bool String) and takes the same WHERE clause and parameters as find-where. It runs a COUNT(*), so no rows are marshalled.

Ordering and pagination

; All rows, sorted
(match (Item.find-ordered &db "text ASC")
  (Result.Success items) (println* &items)
  (Result.Error e) (IO.errorln &e))

; Filtered rows, sorted
(match (Item.find-where-ordered &db "done = ?1" &[(to-sqlite3 0)] "text ASC")
  (Result.Success items) (println* &items)
  (Result.Error e) (IO.errorln &e))

; Paginated: page 1 (first 10 rows, sorted by id)
(match (Item.find-page &db "id ASC" 10 0)
  (Result.Success page) (println* &page)
  (Result.Error e) (IO.errorln &e))

; Paginated: page 2
(match (Item.find-page &db "id ASC" 10 10)
  (Result.Success page) (println* &page)
  (Result.Error e) (IO.errorln &e))

; Filtered + paginated
(match (Item.find-where-page &db "done = ?1" &[(to-sqlite3 0)]
                             "text ASC" 10 0)
  (Result.Success page) (println* &page)
  (Result.Error e) (IO.errorln &e))

find-ordered and find-where-ordered accept an ORDER BY clause as a string. find-page and find-where-page add LIMIT/OFFSET pagination. All four return (Result (Array T) String).

Updating

(ignore (Item.update &db &(Item.init 1 @"bought milk" true)))

update writes all non-PK fields, using the PK on the row for the WHERE clause and returns (Result () String). Partial updates are not supported, so the typical pattern is find-by-id then mutate then update.

update-where is the set-based counterpart: it takes a SQL SET fragment and a WHERE fragment, and touches only the columns you name.

; Mark everything that is not done as done
(ignore (Item.update-where &db "done = ?1" "done = ?2"
                           &[(to-sqlite3 1) (to-sqlite3 0)]))

; Several columns at once
(ignore (Item.update-where &db "text = ?1, done = ?2" "id = ?3"
                           &[(to-sqlite3 @"bought milk") (to-sqlite3 1)
                             (to-sqlite3 1)]))

A single parameter array covers both fragments, so keep the placeholder numbers distinct across them. It returns (Result () String).

Deleting

(ignore (Item.delete-by-id &db &1))

delete-where deletes every row matching a WHERE clause instead of one row by primary key:

(ignore (Item.delete-where &db "done = ?1" &[(to-sqlite3 1)]))

Both return (Result () String), and neither reports how many rows were removed — deleting nothing is a success, not an error.

Aggregates

; How many rows are there?
(match (Item.count &db)
  (Result.Success n) (println* n)
  (Result.Error e) (IO.errorln &e))

; How many match a WHERE clause?
(match (Item.count-where &db "done = ?1" &[(to-sqlite3 0)])
  (Result.Success n) (println* n)
  (Result.Error e) (IO.errorln &e))

count and count-where return (Result Int String) — an Int, not the Double the other aggregates use. An empty table or an unmatched WHERE clause counts 0; a missing table is an error.

; Sum of a numeric column
(match (Item.sum &db "id")
  (Result.Success total) (println* total)
  (Result.Error e) (IO.errorln &e))

; Average with a WHERE clause
(match (Item.avg-where &db "id" "done = ?1" &[(to-sqlite3 0)])
  (Result.Success average) (println* average)
  (Result.Error e) (IO.errorln &e))

; Min and max
(ignore (Item.min-val &db "id"))
(ignore (Item.max-val &db "id"))

sum, avg, min-val, and max-val take a column name and return (Result Double String). Each has a -where variant that accepts a WHERE clause and bound parameters. These four are always returned as Double, even for integer columns. Empty tables or unmatched WHERE clauses return 0.0.

Transactions

The ORM provides transaction support through macros that work with any backend.

Manual control

(ignore (ORM.begin SQLiteBackend &db))
(ignore (Todo.insert &db &(Todo.init 0 @"buy milk" false)))
(ignore (ORM.commit SQLiteBackend &db))

ORM.begin, ORM.commit, and ORM.rollback each take a backend module and a database reference, returning (Result () String).

Automatic rollback

ORM.with-transaction begins a transaction, evaluates a body expression, and commits on success or rolls back on error:

(ORM.with-transaction SQLiteBackend &db
  (do
    (ignore (Todo.insert &db &item1))
    (Todo.insert &db &item2)))

The body must evaluate to (Result a String). On Success the transaction is committed and the value is returned. On Error (or if the commit itself fails) the transaction is rolled back and the error is propagated.

Generated functions

Given (derive-model T Backend [pk-field Pk]), the macro adds the following functions to the T module:

Function	Type
create-table	(Fn [&Backend.Db] (Result () String))
drop-table	(Fn [&Backend.Db] (Result () String))
insert	(Fn [&Backend.Db &T] (Result Long String))
batch-insert	(Fn [&Backend.Db &(Array T)] (Result () String))
upsert	(Fn [&Backend.Db &T] (Result () String))
find-all	(Fn [&Backend.Db] (Result (Array T) String))
find-by-id	(Fn [&Backend.Db &Pk] (Result T String))
find-first	(Fn [&Backend.Db] (Result T String))
find-where	(Fn [&Backend.Db &String &(Array Backend.Type)] (Result (Array T) String))
find-first-where	(Fn [&Backend.Db &String &(Array Backend.Type)] (Result T String))
find-ordered	(Fn [&Backend.Db &String] (Result (Array T) String))
find-where-ordered	(Fn [&Backend.Db &String &(Array Backend.Type) &String] (Result (Array T) String))
find-page	(Fn [&Backend.Db &String Int Int] (Result (Array T) String))
find-where-page	(Fn [&Backend.Db &String &(Array Backend.Type) &String Int Int] (Result (Array T) String))
exists?	(Fn [&Backend.Db &String &(Array Backend.Type)] (Result Bool String))
update	(Fn [&Backend.Db &T] (Result () String))
update-where	(Fn [&Backend.Db &String &String &(Array Backend.Type)] (Result () String))
delete-by-id	(Fn [&Backend.Db &Pk] (Result () String))
delete-where	(Fn [&Backend.Db &String &(Array Backend.Type)] (Result () String))
count	(Fn [&Backend.Db] (Result Int String))
count-where	(Fn [&Backend.Db &String &(Array Backend.Type)] (Result Int String))
sum	(Fn [&Backend.Db &String] (Result Double String))
sum-where	(Fn [&Backend.Db &String &String &(Array Backend.Type)] (Result Double String))
avg	(Fn [&Backend.Db &String] (Result Double String))
avg-where	(Fn [&Backend.Db &String &String &(Array Backend.Type)] (Result Double String))
min-val	(Fn [&Backend.Db &String] (Result Double String))
min-val-where	(Fn [&Backend.Db &String &String &(Array Backend.Type)] (Result Double String))
max-val	(Fn [&Backend.Db &String] (Result Double String))
max-val-where	(Fn [&Backend.Db &String &String &(Array Backend.Type)] (Result Double String))

The insert signature above is the one for a single Int/Long PK. For every other primary key — composite [pk1 T1 pk2 T2 ...], or a single field of any other type — insert returns (Result () String) (no rowid). find-by-id/delete-by-id accept one argument per PK field (&Pk1 &Pk2 ...).

batch-insert follows insert: it omits the PK column for a single Int/Long PK and writes it for every other shape. upsert always writes every column, so its signature does not vary.

Backends

A backend is a module that defines six defndynamic helpers. The ORM macro calls them at expansion time to build SQL strings and row marshalling code.

(defmodule MyBackend
  (defndynamic sql-type [t] ...)           ; Carp type -> SQL type string
  (defndynamic placeholder [n] ...)        ; parameter placeholder, 1-indexed
  (defndynamic query-fn [] ...)            ; static function the generated code calls
  (defndynamic last-insert-id-sql [] ...)  ; SQL to fetch the last inserted id
  (defndynamic extract-col [t var] ...)    ; form extracting a value from an owned col variable
  (defndynamic bind-value [t expr] ...)    ; form converting an expression for binding
  (defndynamic begin-sql [] ...)           ; SQL to begin a transaction
  (defndynamic commit-sql [] ...)          ; SQL to commit a transaction
  (defndynamic rollback-sql [] ...)        ; SQL to roll back a transaction
)

The t passed to sql-type, extract-col, and bind-value is the field's declared type, so it is a list (Maybe T) for a nullable field and a bare symbol otherwise.

backends/sqlite3.carp is the reference implementation. It is small (under 150 lines) and a good starting point for a new backend.

Limitations

• At least one non-PK field must be present, since insert and update bind data columns. A table of only PK fields raises a macro error.
• update overwrites all non-PK fields. The typical workflow is find-by-id, mutate the result, then update.
• The macro only understands the Carp value types the chosen backend registers. The SQLite backend supports Int, Long, Double, Float, String, and Bool, plus (Maybe T) of any of those. Anything else raises a macro error at expansion time.
• with-transaction requires the body to return a (Result a String). Use do to group multiple expressions.

Testing

carp -x test/orm.carp

---

Have fun!