sqlite #

The SQLite file format and a SQL engine over it, in pure OCaml.

Overview #

Most programs that embed a database use SQLite. From OCaml that means linking the SQLite C library, which is not possible on targets without a libc, such as MirageOS unikernels. This package is a SQL engine written in OCaml that works on real SQLite database files. It reads and writes the file format of sqlite3, and the two recovery files sqlite3 keeps next to a database: the rollback journal and the write-ahead log.

Before overwriting a page in place, a database in rollback-journal mode copies the page's old contents into the -journal file. After a crash in the middle of a commit, copying them back undoes the commit. In write-ahead-log mode the database file is left alone; new page images are appended to the -wal file, followed by a commit frame. Readers look in the log first, a crash loses only the frames after the last commit, and a checkpoint later copies the logged pages into the database file. This engine commits by appending to the log and calling fsync on it, and it checkpoints when the database is closed. It reads either kind of file left by sqlite3. A database created here opens in the sqlite3 shell, and one created by that shell opens here. The pager, the B-tree, the SQL parser, the planner and the register VM are all written in OCaml, so the engine builds and runs on every OCaml target, unikernels included.

The engine runs SELECT, INSERT, UPDATE and DELETE with joins, subqueries, CTEs and window functions. A planner chooses index seeks and hash joins. On the schema side it supports triggers, foreign keys, generated columns, STRICT and WITHOUT ROWID tables, ALTER TABLE, ANALYZE, PRAGMA introspection and PRAGMA integrity_check.

The SQL dialect is that of sqlite3. Where the engine behaves differently, the documentation of the function concerned in lib/sqlite.mli says so. The differences are recorded there and nowhere else.

One writer, many readers #

Within one process, the engine allows one writer and any number of read-only handles (open_ ~read_only:true). Each handle belongs to one domain. A domain is OCaml 5's unit of parallelism: a system thread with its own minor heap, running OCaml code in parallel with the other domains. A read-only handle writes nothing to the database or to its -wal or -journal file; like a sqlite3 reader, it writes its read mark into the -shm wal-index. It can therefore be opened, used and closed at any time while a writer is active, and it always sees a committed snapshot. If a checkpoint finds an active reader, it postpones itself instead of waiting, so a commit never waits for a read.

Between processes, a WAL database is shared as sqlite3 shares it: readers and a writer run at once (wal.html, section 2.2). Both engines keep the wal-index, the -shm file next to the database, in sqlite3's format. Its header names the last commit in the log. A reader records how far into the log its snapshot reaches as a read mark and reads that snapshot while a writer appends later frames, and a checkpoint copies no frame past a mark a reader still holds. A write transaction takes the wal-index's WRITE lock, so a second writer, of this engine or of sqlite3, gets Busy, and a reader waits for nothing. The -shm file is read and written with pread and pwrite, a bounded window at a time; sqlite3 maps it, and both see the same bytes.

SQLite locks with POSIX byte-range locks: the wal-index's locks are bytes of the -shm file, and the database file's are bytes just past 1 GiB, where no page is stored: a PENDING byte, then a RESERVED byte, then a SHARED range. A WAL handle holds SHARED for each transaction. In rollback-journal mode a writable handle takes SQLite's EXCLUSIVE lock, a write lock on all three, for each write transaction, so a reader of another process waits for the commit, as it does in sqlite3 (lockingv3.html, section 3).

This engine departs from sqlite3 in three ways. A handle holds no lock between transactions, where a sqlite3 WAL connection holds SHARED for its whole life, so a sqlite3 connection closing last may checkpoint and remove the log while a handle of this engine is idle; the handle reads the database again at its next transaction, and opens the log at the name again, where otherwise it keeps its log open from its first transaction to its close, as sqlite3 does. A writable handle takes the WRITE lock for its reads as well as its writes. And a log that starts over takes salts one greater than the last, where sqlite3 draws the second at random.

Two writable handles take turns whether they are in one process or two: a write session refuses another while it stands, and with a busy timeout the other waits for it. A single handle cannot be shared between domains, because a read also changes its page cache. The full rules are in the Concurrency section of lib/sqlite.mli.

Installation #

The package needs OCaml 5.1 or later and does its I/O through Eio. Install it with opam:

$ opam install sqlite

If opam cannot find the package, it is not in the public opam-repository yet. Add the overlay repository first:

$ opam repo add samoht https://tangled.org/gazagnaire.org/opam-overlay.git
$ opam update
$ opam install sqlite

Usage #

The main API is a typed DSL for queries and writes. Each column has an OCaml type, so the compiler rejects a type mismatch, a wrong number of result columns or a nullability error. run db q returns all the rows of q as a list, and iter db q ~f calls f on each row as the scan finds it.

open Sqlite

let demo ~fs =
  Eio.Switch.run @@ fun sw ->
  let db = open_ ~sw Eio.Path.(fs / "data.db") in

  let users = table "users" in
  let id = field users "id" int in
  let name = field users "name" text in
  create_table db users [ coldef ~primary_key:true id; coldef name ];

  insert db users [ id <-- 1L; name <-- "alice" ];
  insert db users [ id <-- 2L; name <-- "bob" ];

  iter db (from users |> where (id =. 1L) |> select1 name) ~f:print_endline;

  sync db;
  close db

Aggregates and grouping are typed as well. SUM, AVG, MIN and MAX return an option, because SQLite gives NULL for an empty group, and TOTAL returns 0.:

let by_region db usage region population demand =
  from usage
  |> select4 region count_star (sum_int population)
       (avg_nullable_float demand)
  |> group_by region
  |> having (count_star >=. 10L)
  |> order_by region `Asc
  |> run db
(* rows of (string * int64 * int64 option * float option) *)

Numeric expressions say whether they use integer, floating-point or nullable arithmetic. A total weighted by population, for example, is written:

let weighted_total population demand =
  total_float (mul_float (as_float population) (coalesce demand 0.))

Raw SQL #

Sqlite.Raw runs SQL given as a string, for queries the DSL cannot express or that are only known at run time. Statements take ? parameters, and rows come back as lists of untyped values:

let print_matching db pattern =
  Raw.exec db "CREATE INDEX IF NOT EXISTS users_by_name ON users (name)"
    ~params:[];
  Raw.iter db "SELECT id, name FROM users WHERE name LIKE ?"
    ~params:[ Text pattern ]
    ~f:(fun row -> print_endline (String.concat "|" (List.map Value.to_text row)))

Key-value store #

Sqlite.Kv stores string keys and values in the same file. Kv.Table gives separate namespaces:

let store db =
  Kv.put db "key1" "value1";
  let blocks = Kv.Table.v db ~name:"blocks" in
  Kv.Table.put blocks "cid1" "data1";
  (Kv.find db "key1", Kv.Table.find blocks "cid1")

Command line #

The package installs a sql command that works on the same databases:

$ sql write mydb.db hello=world foo=bar
$ sql query mydb.db "SELECT key, value FROM kv ORDER BY key"
foo|bar
hello|world
$ sql tables mydb.db
kv (key TEXT, value BLOB)
$ sql script mydb.db < schema.sql

sql read prints the key-value table and sql delete removes keys. Every subcommand accepts --help.

Documentation #

The API has three parts:

• the top-level module is the typed DSL for queries and writes shown above (typed column handles, run/iter, insert/update/delete, create_table);
• Sqlite.Raw takes SQL as strings (query / exec / iter) and returns rows as lists of untyped values;
• Sqlite.Kv is the string key-value store, with separate Kv.Table namespaces.

doc/design.mld describes the file format the engine writes and what writing it in OCaml makes possible.

To build the full interface documentation:

dune build @doc

If the package is installed in a switch that has odig:

odig doc sqlite

Related work #

• btree is the storage layer this engine uses: pages, B-trees and the record format.
• sqlite3-ocaml binds the SQLite C library at a low level.
• ezsqlite is another set of C bindings, with extensions.
• Limbo reimplements SQLite in Rust.
• irmin is a distributed database modelled on Git, for a different kind of application.

Licence #

ISC License. See LICENSE.md for details.