ExSQL

A SQLite implementation in pure Elixir — no NIFs, no ports, no C.

ExSQL follows the architecture of SQLite's C source (tokenizer → parser → execution → storage) but reshapes each stage for the BEAM: the engine is a pure functional core over immutable data, with an optional GenServer connection for stateful, sqlite3-style use.

Usage

{:ok, conn} = ExSQL.open()

ExSQL.execute!(conn, """
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER);
INSERT INTO users (name, age) VALUES ('alice', 34), ('bob', 29), ('carol', 41);
""")

result = ExSQL.query!(conn, "SELECT name, age FROM users WHERE age > 30 ORDER BY age DESC")
result.columns #=> ["name", "age"]
result.rows    #=> [["carol", 41], ["alice", 34]]

Or skip the process entirely and thread the database value yourself:

db = ExSQL.Database.new()
{:ok, _, db} = ExSQL.Executor.run(db, "CREATE TABLE t (x INTEGER)")
{:ok, _, db} = ExSQL.Executor.run(db, "INSERT INTO t VALUES (1), (2), (3)")
{:ok, [result], _db} = ExSQL.Executor.run(db, "SELECT sum(x) FROM t")
result.rows #=> [[6]]

Using with Ecto

ExSQL ships an Ecto adapter, Ecto.Adapters.ExSQL, so it can back an Ecto repo as a drop-in SQL adapter — application code stays standard Ecto, and a database can be in-memory or persisted to a real SQLite file.

# config/config.exs
config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.ExSQL,
  database: "priv/my_app.db", # or :memory for an in-memory database
  pool_size: 1                # required — see below

# lib/my_app/repo.ex
defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.ExSQL
end

With a schema, ordinary Ecto queries work:

import Ecto.Query

MyApp.Repo.insert!(%MyApp.User{name: "alice", age: 34})

MyApp.Repo.all(from u in MyApp.User, where: u.age > 30, order_by: [desc: u.age])

pool_size: 1 is recommended. Each connection holds its own immutable database value. File-backed repos default to a shared redo log (persist: :log): committed transactions append whole log records, other connections detect the new commit version and reload, so a pool no longer clobbers writes — but two connections committing read-modify-write transactions concurrently still resolve by replay order, not by SQLite-style locking. A single writer with lock-free snapshot reads remains the intended model. The legacy whole-file mode (persist: :file, which rewrites the base file after every committed write) genuinely requires pool_size: 1.

Query codegen (experimental, opt-in)

For repeated queries, ExSQL can compile a query shape into a native BEAM function (via Module.create/3, which the JIT turns into machine code) instead of walking the parsed AST row by row. Turn it on with an environment variable:

EXSQL_CODEGEN=1

It is transparent and gated. A query is keyed by its shape — its structure with literal values lifted out to parameters — so where: u.age > 30 and where: u.age > 40 share one compiled module. A shape is only compiled after it has been seen a few times (EXSQL_CODEGEN_THRESHOLD, default 2): a one-shot ad-hoc query never pays the (~few-ms) compile cost, while a repeated or parameterized query — the norm when an app drives ExSQL through Ecto — amortizes it after the first couple of calls. Anything the compiler doesn't support falls back to the tree-walking executor automatically, so results are always identical.

Supported shapes today: single-table scan / filter / project / sort, and two-table LEFT JOIN on an equi-key (hash-joined, with NULL-filled non-matches, residual ON filters, and COALESCE projections) — the bread-and-butter reads of a typical app.

import Ecto.Query

# A hot, repeated query — "episodes for this podcast, with the current user's
# play state" — runs the same shape on every page load, for any id values:
from(e in Episode,
  left_join: s in EpisodeState,
  on: s.episode_id == e.id and s.user_id == ^user_id,
  where: e.podcast_id == ^podcast_id,
  order_by: [desc: e.published_at],
  select: %{
    id: e.id,
    title: e.title,
    played: coalesce(s.played, false),
    position: coalesce(s.play_position_seconds, 0)
  }
)
|> Repo.all()

With EXSQL_CODEGEN=1, once the shape compiles this runs against a generated function that reads rows positionally and hash-joins the right side — measured at roughly 10× faster end-to-end through Ecto than the tree walker on a ~750-row left join, with byte-identical results. Every subsequent call reuses the same module, regardless of the user_id / podcast_id bound in.

Codegen is opt-in and experimental. It is verified against the tree-walking executor across the full test suite — every supported query runs the generated code and is checked to match — but it is off by default; enable it per deployment with EXSQL_CODEGEN=1.

Compared with Exqlite

Exqlite is the mature, production-oriented choice when you want the real SQLite engine from Elixir. It binds to sqlite3 through NIFs, so it inherits SQLite's full feature set, planner, file locking, and performance profile. ExSQL has a different goal: it is a SQLite-compatible engine written in Elixir, with ordinary BEAM data structures and no native boundary. That makes some tradeoffs better for BEAM applications, while other SQLite/exqlite limits remain or are not solved yet.

In short: ExSQL can improve on Exqlite's native-boundary and mutable-handle constraints, and it can use immutable snapshots as a BEAM-native read model. It does not yet match SQLite/exqlite for maturity, full SQL coverage, or query planning — but on raw engine speed it is closer than you might expect.

Performance. Run through the same Elixir harness over the sqllogictest corpus (~620 files), ExSQL lands around parity with exqlite (the C SQLite NIF) at the median, within 2× on ~93% of files, matching or beating the C engine on a large share — with no timeouts and no result mismatches. The slowest files are unindexed full scans (a measured ~4.9× floor for any pure-Elixir engine) and a few insert-heavy DML cases. Full method and current numbers: BENCHMARKS.md.

On-disk format. ExSQL reads and writes the real SQLite file format — databases round-trip with the sqlite3 CLI and exqlite (PRAGMA integrity_check passes), including multi-level B-trees, secondary indexes, overflow pages for large rows, and AUTOINCREMENT sequences. (UTF-8 databases; reads reject UTF-16 with a clear error.)

Architecture

The module layout mirrors SQLite's pipeline:

SQLite semantics implemented so far:

• Type affinity (INTEGER/TEXT/REAL/NUMERIC/BLOB) with SQLite's declared-type derivation rules — VARCHAR(40) is TEXT affinity, DECIMAL is NUMERIC, untyped columns are BLOB — and §4.2 comparison affinity: a numeric-affinity operand coerces the other side to NUMERIC, a TEXT one coerces a no-affinity side to TEXT, literals compared to literals stay untouched (so '500' = 500 is false but t = 500 matches a TEXT column); applied across =/</IS/IN/BETWEEN/CASE/USING, with IN (SELECT ...) using the subquery column's affinity
• Storage classes mapped to native terms: nil, integers, floats, binaries (TEXT), {:blob, binary} — with SQLite's cross-class comparison ordering (NULL < numeric < TEXT < BLOB)
• Three-valued logic: NULL AND 0 is 0, NULL OR 0 is NULL, x = NULL never matches, IS [NOT] is the NULL-safe comparison
• INTEGER PRIMARY KEY is the rowid: inserting NULL auto-assigns, explicit values re-seed the counter, rowid/oid/_rowid_ resolve to it
• Constraints: NOT NULL, UNIQUE, PRIMARY KEY, DEFAULT, with statement-level atomicity on violation (ABORT semantics)
• Statements: CREATE TABLE [IF NOT EXISTS], DROP TABLE [IF EXISTS], INSERT (multi-row VALUES, INSERT INTO ... SELECT, DEFAULT VALUES, explicit rowid targets), REPLACE INTO / INSERT OR REPLACE/IGNORE / UPDATE OR ... conflict handling, bare VALUES (...) selects, SELECT (WHERE, GROUP BY/HAVING — including alias and position terms, ORDER BY — column / alias / position / aggregate expression, LIMIT/OFFSET including the LIMIT x, y form, DISTINCT), UPDATE, DELETE
• Transactions: BEGIN/COMMIT/ROLLBACK and SAVEPOINT/RELEASE/ROLLBACK TO — a snapshot stack on the immutable database value, with SQLite's error messages (cannot start a transaction within a transaction, …)
• Compound selects: UNION [ALL], INTERSECT, EXCEPT, with SQLite's rules — ORDER BY/LIMIT only after the last component, terms resolved against output columns (by position, name, or any component's column name), distinct ops emerging sorted as from the temp B-tree

• Joins: comma, [INNER | CROSS] JOIN, NATURAL, LEFT [OUTER] JOIN, ON/USING, table aliases, subqueries in FROM, qualified t.* — with SQLite's column-hiding rules for NATURAL/USING in * expansion, its join-type validation (unknown join type: INNER OUTER), and ambiguous column detection

• Subqueries: scalar (SELECT ...), EXISTS, IN (SELECT ...), all correlated — free columns resolve against the enclosing row, including in UPDATE/DELETE WHERE clauses
• Expressions: full operator precedence, LIKE/GLOB, IN, BETWEEN, CASE, CAST (§4.1 forced-conversion rules, longest-numeric-prefix text parsing), bitwise &/|/<</>>/~ (int64 wrap, negative shifts reverse), string/blob/hex literals, comments
• Functions: aggregates (count(*), count, sum, total, avg, min, max, group_concat/string_agg) and scalars (abs, length, octet_length, lower, upper, coalesce, ifnull, nullif, substr/substring, round, typeof, multi-arg min/max, replace, trim/ltrim/rtrim, instr, hex, quote, char, unicode, sign, iif, zeroblob, concat/concat_ws, function-form like()/glob()), with SQLite's "no such function" vs "wrong number of arguments" errors

Tests

test/sqlite/ holds ExUnit translations of SQLite's own TCL test suite (sqlite/test/*.test), one module per source file with the original test ids preserved — currently select3.test (aggregates, GROUP BY/HAVING), select4.test (compound selects), join.test, in.test, subquery.test, cast.test, expr.test (operators), func.test, insert.test, trans.test, savepoint.test, and types2.test (comparison affinity). ExSQL.SQLiteCase provides execsql/execsql2/catchsql helpers mirroring tester.tcl, so a TCL case translates nearly word for word. As features from the roadmap land, the matching test file gets translated alongside.

Benchmarks

BENCHMARKS.md measures ExSQL against exqlite (the C SQLite NIF) driven from the same Elixir harness over the sqllogictest corpus — the fairest "what does ExSQL cost vs SQLite, from Elixir" comparison — with the current results, methodology, and commands to reproduce. The harness lives in bench/slt_compare/.

Roadmap

Landed: ALTER TABLE / CHECK constraints / composite PK & UNIQUE; views; secondary indexes with a query planner (rowid/index seeks, incremental O(n) index maintenance for INSERT/UPDATE/DELETE); the on-disk file format (read and write, interchangeable with real SQLite); positional tuple-based row storage; and runtime query codegen — compiling repeated query shapes to native BEAM functions (opt-in via EXSQL_CODEGEN=1, gated by a shape-keyed sighting cache; see Query codegen).

Next:

1. Broaden codegen coverage — more join kinds, aggregates, and expression forms. Today it compiles single-table scans and two-table left joins and falls back to the tree walker for everything else; widening the supported shape extends the JIT win to more of a real app's query mix.
2. Prepared-statement / plan cache — skip re-parsing repeated (parameterized) queries. The codegen shape cache already compiles repeated queries; caching the parse would cut the remaining per-call text-parsing overhead that both engines still pay.

Development

mix test       # run the suite
mix precommit  # format + compile with warnings-as-errors + test

License

GNU General Public License v3.0 or later (GPL-3.0-or-later). See LICENSE.