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 required. Each connection holds its own immutable database value, so a second writer connection would clobber the first's writes. The intended model is a single writer with lock-free snapshot reads (each read runs against a consistent, immutable point-in-time value).

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.

Exqlite limitation / behaviorExSQL status
Prepared statements are not cachedNot solved yet. ExSQL's DBConnection query object is an immutable Elixir struct, but the engine still parses and executes the SQL text for each call. A parser/plan cache is a future optimization.
Prepared statements are mutable handles and must not be manipulated concurrentlyImproved by design. ExSQL has no mutable sqlite3_stmt* handle; query structs and parsed data are normal immutable Elixir values. The stateful connection still serializes execution, but there is no shared native statement object to corrupt.
Simultaneous writes are not supportedSame practical limit. The Ecto adapter requires pool_size: 1. ExSQL can provide immutable snapshot reads, but there is still one authoritative writer for a database path.
Native calls run through the Dirty NIF schedulerAvoided. ExSQL is pure Elixir: no NIFs, no ports, and no C calls. Long queries consume BEAM reductions like other Elixir code instead of occupying Dirty NIF scheduler work.
Datetimes are stored without offsetsImproved for Ecto values. The Ecto adapter encodes DateTime values with DateTime.to_iso8601/1 and decodes :utc_datetime values with DateTime.from_iso8601/1, so offset-bearing ISO-8601 text round-trips through ExSQL. SQLite-compatible SQL date/time functions still follow SQLite semantics and normalize timezone offsets during calculation.
BLOB values require {:blob, binary} or they are treated as stringsSame explicit representation. ExSQL also represents TEXT as plain Elixir binaries and BLOB as {:blob, binary}. Use SQL blob literals such as x'CAFE' or the tagged tuple when binding/returning BLOB values.

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:

stageSQLite (C)ExSQLapproach
lexingtokenize.c (char-class table + keyword hash)ExSQL.Tokenizerbinary pattern matching
parsingparse.y (Lemon LALR)ExSQL.Parserrecursive descent, precedence climbing
valuesvdbemem.c (Mem cells)ExSQL.Valuestorage classes as native terms
executioncodegen + VDBE bytecode VM (vdbe.c)ExSQL.Executortree-walking interpreter
storagebtree.c + pager.cExSQL.Table / ExSQL.Databaseimmutable maps keyed by rowid; each row a positional tuple (≈63% smaller than a per-row key map)
connectionsqlite3* handle + mutexExSQL.ConnectionGenServer serializing statements

SQLite semantics implemented so far:

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); and positional tuple-based row storage.

Next:

  1. Bytecode VM — compile a query to a specialized routine à la VDBE (vdbe.c has 190 opcodes; a register machine over positional cursors maps cleanly onto the tuple-based rows now in place). This is the path toward C speed on scan-bound queries and the substrate for a prepared-statement cache.
  2. Prepared-statement / plan cache — skip re-parsing repeated (parameterized) queries; the biggest win for real apps driving ExSQL through Ecto.

Development

mix test       # run the suite

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

License

BSD 3-Clause. See LICENSE.