Francis SQLite

A tiny, deliberately-capped SQLite dataset builder for the Francis micro-framework.

This is the Sequel dataset idea, not Ecto. Chainable queries, plain string-key maps out, and exactly one runtime dependency — exqlite, the SQLite NIF. No schemas, no changesets, no mix ecto.migrate.

import FrancisSqlite

from("chirps")
|> where("at > ?", [cutoff])
|> order_by(desc: :at)
|> limit(100)
|> all()
#=> [%{"handle" => "ada", "text" => "hi", "at" => 1_717_142_400}, ...]

Why it exists

Elixir's ecosystem consolidated on Ecto, so there is no lightweight middle rung between raw SQL strings and a full ORM — the slot Sequel fills for Sinatra. For a Francis app (the "Sinatra of Elixir") that wants real durability and SQL without standing up Postgres or adopting a framework, this is that rung.

The cap is the feature

The dataset covers single-table SELECT (where / order_by / limit / select) and INSERT. That's it. A query builder's surface area is infinite, and pretending otherwise is how these libraries rot into a half-built Ecto. The moment you want a join, subquery, GROUP BY, or window function, drop to raw SQL:

all("SELECT handle, count(*) AS n FROM chirps GROUP BY handle ORDER BY n DESC")

Raw SQL is a first-class citizen here, not an escape hatch you should feel bad about. The dataset just removes the boilerplate from the 90% of queries that are boring CRUD.

Design choices

Choice	What	Why
One connection	A single GenServer owns one exqlite handle; all queries serialize through it	Same single-writer shape as a hand-rolled GenServer — impossible to get a torn read, nothing to pool. Upgrade to a read-only WAL pool if serialization ever hurts.
WAL mode	PRAGMA journal_mode=WAL on boot	Durability is the database's job — no term_to_binary dump file, no flush timer, no "lose the last 10 seconds" window.
Plain maps	Rows come back as %{"col" => val}	Zero config, zero schema. You already know your columns.
PRAGMA user_version migrations	An ordered list of DDL strings; the Nth is "version N"	The minimal safe migrator — no schema_migrations table, no timestamped files. Append-only.

Usage

Add the dep (path dep while co-developing, Hex once published):

{:francis_sqlite, path: "../francis_sqlite"}

Start it in your supervision tree:

children = [
  {FrancisSqlite,
     database: System.get_env("DB_PATH") || "priv/app.db",
     migrations: [
       "CREATE TABLE chirps (id INTEGER PRIMARY KEY, handle TEXT, text TEXT, at INTEGER)",
       "CREATE INDEX chirps_at ON chirps (at DESC)"
     ]}
]

Then import FrancisSqlite and:

insert("chirps", handle: "ada", text: "hi", at: System.os_time(:second))
#=> {:ok, 42}   # last_insert_rowid

from("chirps") |> order_by(desc: :at) |> limit(100) |> all()
from("chirps") |> where("handle = ?", ["ada"]) |> one()
all("SELECT ...", [param1, param2])   # raw, when the cap bites

License

MIT