defmodule ExSQL do
@moduledoc """
A SQLite implementation in pure Elixir.
ExSQL follows SQLite's architecture — tokenizer, parser, executor, storage —
reshaped for the BEAM: the engine is a pure functional core over immutable
data, with an optional GenServer connection for stateful use.
## Quick start
{: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);
\"\"\")
{:ok, result} = ExSQL.query(conn, "SELECT name FROM users WHERE age > 30")
result.rows
#=> [["alice"]]
## Purely functional use
The engine itself never touches a process; you can thread the database
value yourself:
db = ExSQL.Database.new()
{:ok, _, db} = ExSQL.Executor.run(db, "CREATE TABLE t (x)")
{:ok, [result], _db} = ExSQL.Executor.run(db, "SELECT count(*) FROM t")
## Pipeline
| stage | SQLite (C) | ExSQL |
|-------|------------|-------|
| lexing | `tokenize.c` | `ExSQL.Tokenizer` |
| parsing | `parse.y` (Lemon) | `ExSQL.Parser` |
| execution | codegen + VDBE (`vdbe.c`) | `ExSQL.Executor` (tree-walking) |
| storage | `btree.c` + `pager.c` | `ExSQL.Table` / `ExSQL.Database` (in-memory) |
"""
alias ExSQL.{Connection, Error, Result}
@doc """
Opens a new in-memory database connection (the equivalent of
`sqlite3_open(":memory:")`).
"""
@spec open(keyword()) :: GenServer.on_start()
def open(opts \\ []), do: Connection.start_link(opts)
@doc "Stops a connection."
@spec close(GenServer.server()) :: :ok
def close(conn), do: GenServer.stop(conn)
@doc """
Registers or replaces a connection-local scalar function.
The callback arity must match the SQL arity exactly. It receives evaluated
SQL values as positional arguments and may return a SQL value, `{:ok, value}`,
or `{:error, message}`.
:ok = ExSQL.create_function(conn, "double", 1, fn x -> x * 2 end)
ExSQL.query!(conn, "SELECT double(21)").rows
#=> [[42]]
"""
@spec create_function(GenServer.server(), String.t(), non_neg_integer(), function()) ::
:ok | {:error, Error.t()}
def create_function(conn, name, arity, callback),
do: Connection.create_function(conn, name, arity, callback)
@doc """
Registers or replaces a connection-local aggregate function.
`arity` is the SQL arity. The callback itself receives one argument: a list
of evaluated, non-NULL argument rows. For example, a one-argument aggregate
receives `[[value], ...]`.
:ok = ExSQL.create_aggregate(conn, "product", 1, fn rows ->
rows |> Enum.map(&hd/1) |> Enum.product()
end)
"""
@spec create_aggregate(GenServer.server(), String.t(), non_neg_integer(), function()) ::
:ok | {:error, Error.t()}
def create_aggregate(conn, name, arity, callback),
do: Connection.create_aggregate(conn, name, arity, callback)
@doc """
Registers or replaces a connection-local aggregate window function.
The callback contract matches `create_aggregate/4`: it receives one list of
evaluated, non-NULL argument rows. When used with `OVER (...)`, ExSQL calls
it with the rows in the current window frame.
:ok = ExSQL.create_window_function(conn, "frame_count", 1, fn rows ->
length(rows)
end)
"""
@spec create_window_function(GenServer.server(), String.t(), non_neg_integer(), function()) ::
:ok | {:error, Error.t()}
def create_window_function(conn, name, arity, callback),
do: Connection.create_window_function(conn, name, arity, callback)
@doc """
Registers or replaces a connection-local incremental aggregate window function.
The callback map must contain `:init`, `:step`, `:inverse`, `:value`, and
`:final` functions. `init.()` returns the initial state, `step.(state, args)`
adds one frame row, `inverse.(state, args)` removes one frame row,
`value.(state)` returns the current window value, and `final.(state)` returns
the aggregate value when the function is used without `OVER`.
:ok = ExSQL.create_incremental_window_function(conn, "running_sum", 1, %{
init: fn -> 0 end,
step: fn total, [x] -> total + (x || 0) end,
inverse: fn total, [x] -> total - (x || 0) end,
value: fn total -> total end,
final: fn total -> total end
})
"""
@spec create_incremental_window_function(
GenServer.server(),
String.t(),
non_neg_integer(),
map()
) :: :ok | {:error, Error.t()}
def create_incremental_window_function(conn, name, arity, callbacks),
do: Connection.create_incremental_window_function(conn, name, arity, callbacks)
@doc """
Registers or replaces a connection-local collation.
The callback receives two text values and may return `:lt`, `:eq`, `:gt`, a
negative/zero/positive integer, `{:ok, result}`, or `{:error, message}`.
:ok = ExSQL.create_collation(conn, "reverse", fn a, b ->
cond do
a < b -> :gt
a > b -> :lt
true -> :eq
end
end)
ExSQL.query!(conn, "SELECT name FROM users ORDER BY name COLLATE reverse")
"""
@spec create_collation(GenServer.server(), String.t(), function()) ::
:ok | {:error, Error.t()}
def create_collation(conn, name, callback),
do: Connection.create_collation(conn, name, callback)
@doc """
Executes one or more `;`-separated statements, returning one
`ExSQL.Result` per statement.
Bind parameters are bound from `params`: a list for positional parameters
(`?`, `?NNN`; 1-based) or a map for named ones (`:name`, `@name`, `$name`;
keys may include or omit the sigil, and integer keys bind by index).
ExSQL.query(conn, "SELECT * FROM users WHERE age > ?", [30])
ExSQL.query(conn, "SELECT * FROM users WHERE name = :name", %{name: "alice"})
"""
@spec execute(GenServer.server(), String.t(), [ExSQL.Value.t()] | map()) ::
{:ok, [Result.t()]} | {:error, Error.t()}
def execute(conn, sql, params \\ []), do: Connection.execute(conn, sql, params)
@doc "Like `execute/3`, but raises `ExSQL.Error` on failure."
@spec execute!(GenServer.server(), String.t(), [ExSQL.Value.t()] | map()) :: [Result.t()]
def execute!(conn, sql, params \\ []) do
case execute(conn, sql, params) do
{:ok, results} -> results
{:error, error} -> raise error
end
end
@doc """
Executes a single statement and returns its `ExSQL.Result`.
Errors if `sql` contains more than one statement — use `execute/3` for
scripts. Takes bind parameters like `execute/3`.
"""
@spec query(GenServer.server(), String.t(), [ExSQL.Value.t()] | map()) ::
{:ok, Result.t()} | {:error, Error.t()}
def query(conn, sql, params \\ []) do
case execute(conn, sql, params) do
{:ok, [result]} ->
{:ok, result}
{:ok, results} ->
{:error, %Error{message: "expected one statement, got #{length(results)}"}}
{:error, _} = error ->
error
end
end
@doc "Like `query/3`, but raises `ExSQL.Error` on failure."
@spec query!(GenServer.server(), String.t(), [ExSQL.Value.t()] | map()) :: Result.t()
def query!(conn, sql, params \\ []) do
case query(conn, sql, params) do
{:ok, result} -> result
{:error, error} -> raise error
end
end
end