Spire

A SQL query engine for Elixir. Parses SELECT statements, validates them against a policy, and runs them against in-memory data, Ecto-backed databases, or any source you bring.

0.1.0 — M1 (read-only SELECT). See SPEC.md for the full scope.

policy = Spire.Policy.new(
  tables: %{
    "users" => %Spire.Policy.Table{
      columns: ["id", "name", "email"],
      filter_only_columns: ["tenant_id"],
      enforced_where: Spire.Policy.where("tenant_id = :tenant_id")
    }
  }
)

{:ok, rows} = Spire.query(
  customer_supplied_sql,
  Spire.Source.Ecto.new(repo: MyApp.Repo),
  policy: policy,
  params: %{tenant_id: current_user.tenant_id}
)

The customer can write any SELECT against users. Columns outside the allowlist are rejected. Rows from other tenants are filtered out by the enforced WHERE, which is structurally distinct from a regular filter and cannot be removed or reordered by any optimizer.

What's inside

• Parser: hand-written lexer + Pratt expression parser + recursive descent for SELECT. Rejects everything else at parse time.
• Analyzer: policy enforcement — table/column/function allowlists, filter-only columns, enforced-WHERE injection, complexity caps (max_joins, max_result_limit, require_limit).
• Planner: AST → flat plan-op list (SPEC.md §5).
• Spire.IR: canonical relational intermediate form. One place owns binding-index assignment, filter lifting, LEFT JOIN ON fusion, and aggregate-alias substitution. Backend compilers are pure renderers from IR.
• Engine: in-memory evaluator with three-valued NULL logic routed through Spire.Engine.Null (Elixir's == / and / or are never used to evaluate SQL expressions).
• Compilers: Spire.Compiler.Ecto emits parametrized SQL strings; Spire.Compiler.EctoQuery emits %Ecto.Query{} structs composable with preload, where, etc.
• Sources: in-memory maps, Ecto-backed (any ecto_sql adapter), plus test-only Spire.Source.DuckDB and Spire.Source.ClickHouse for differential testing.

Operators

• Statements: SELECT only.
• Clauses: DISTINCT, FROM, INNER/LEFT/RIGHT/CROSS JOIN, WHERE, GROUP BY, HAVING, ORDER BY (NULLS FIRST / LAST), LIMIT, OFFSET.
• Expressions: literals, named/positional params, column refs, arithmetic, comparisons, AND / OR / NOT, IS [NOT] NULL, [NOT] BETWEEN, [NOT] IN, LIKE / ILIKE / NOT LIKE, CASE WHEN, CAST(... AS type) / ::, allowlisted function calls.
• Aggregates: count(*), count(col), count(DISTINCT col), sum, avg, min, max.
• Built-in functions: lower, upper, length, trim, ltrim, rtrim, concat, replace, substring, abs, ceil, floor, round, sign, coalesce, nullif, greatest, least, now.

Subqueries, CTEs, window functions, and DML aren't in M1.

Safe-by-default policy

Spire.query/3 requires an explicit :policy option.

# For SQL from anywhere untrusted:
Spire.query!(sql, src, policy: %Spire.Policy{...}, params: ...)

# For SQL you wrote yourself (internal tools, scripts, IEx):
Spire.query!(sql, src, policy: Spire.Policy.permissive())

Forgetting :policy returns a :policy_required error. The library doesn't pick a mode for you.

The threat model is in SECURITY.md: what Spire defends against (cross-tenant reads, column exfiltration, atom-table exhaustion, LIKE regex backtracking, deep nesting, huge IN lists) and what's out of scope (timing channels, infra-layer DoS, error-message disclosure).

A worked example: multi-tenant SaaS

defmodule MyApp.Reports do
  @policy Spire.Policy.new(
    tables: %{
      "users" => %Spire.Policy.Table{
        columns: ["id", "name", "email"],
        filter_only_columns: ["tenant_id"],
        enforced_where: Spire.Policy.where("tenant_id = :tenant_id")
      },
      "orders" => %Spire.Policy.Table{
        columns: ["id", "user_id", "total", "status"],
        filter_only_columns: ["tenant_id"],
        enforced_where: Spire.Policy.where("tenant_id = :tenant_id")
      }
    },
    max_joins: 3,
    max_result_limit: 1000,
    require_limit: true
  )

  defp source, do: Spire.Source.Ecto.new(repo: MyApp.Repo)

  def run(customer_sql, %{tenant_id: tid}) do
    Spire.query(customer_sql, source(),
      policy: @policy,
      params: %{tenant_id: tid}
    )
  end
end

tenant_id is bound server-side from the current user. The customer can't reach it through their SQL.

A live walkthrough is in examples/multi_tenant_saas.livemd (in-process SQLite) and bench/multi_tenant_demo.exs (against a 10M-row ClickHouse fixture).

Other ways to use it

# Lazy stream for filter/project/limit pipelines. Sort/aggregate/join
# still buffer internally.
{:ok, stream} = Spire.stream(sql, src, policy: @policy, params: ...)

# Prepared plan — parse + analyze run once.
@list_users Spire.prepare!(
  "SELECT id, name FROM users WHERE active = :active",
  policy: @policy
)
{:ok, rows} = Spire.execute(@list_users, src, params: %{active: true})

# Inspect what Spire would send to your backend.
{:ok, {sql, params}} = Spire.to_sql(customer_sql, src,
  policy: @policy, params: %{tenant_id: 3})

# Or get a real %Ecto.Query{} you can compose with hand-written Ecto.
{:ok, q} = Spire.to_ecto_query(customer_sql, src, policy: @policy)
q |> MyApp.Repo.preload(:posts) |> MyApp.Repo.all()

Telemetry

Events for parse, plan, analyze, engine.scan, engine.materialize, pushdown, and query.start / query.stop (with :duration). See Spire.Telemetry.events/0.

Correctness

Per CLAUDE.md, the in-memory engine is the reference. Every other backend must agree with it. SEMANTICS.md documents operator semantics formally; the truth tables there are executable in test/spire/engine/null_truth_tables_test.exs.

Spire.SourceCase runs a canonical query corpus through every configured backend (engine, SQLite-SQL, Ecto.Query, DuckDB, ClickHouse, Postgres) and asserts they all return identical rows. Random-plan property tests do the same with generated queries.

Development

mix test                       # default suite (excludes :stress)
mix test --only conformance    # multi-backend differential corpus
mix test --include stress      # heavy properties × 60K-row fixture
mix format --check-formatted
mix credo --strict
mix dialyzer
mix coveralls

Local backends for differential testing

DuckDB and SQLite run in-process. ClickHouse and Postgres need servers — docker-compose handles both:

docker compose up -d
mix test                       # picks up CH/PG automatically when reachable
docker compose down -v

Tests skip cleanly when a server isn't reachable. CI runs both via service containers (.github/workflows/ci.yml); the nightly cron runs the stress properties (.github/workflows/nightly-stress.yml).

Benchmarks

docker compose up -d clickhouse
MIX_ENV=test LABEL=baseline mix run bench/clickhouse_10m.exs

The harness times plan / compile / execute separately and verifies results against running the SQL directly through ClickHouse.

Reading order

• SPEC.md — surface API and architecture
• SEMANTICS.md — operator semantics
• SECURITY.md — threat model
• CHANGELOG.md — release notes
• CONTRIBUTING.md — adding tests, operators, backends

License

MIT.