Efsql

Work in progress.

Efsql is a SQL CLI for FoundationDB, built on top of EctoFoundationDB.

Requirements

efsql needs the FoundationDB client library (libfdb_c) on the machine where it runs. It is not bundled, and Homebrew does not package it — install it from the FoundationDB releases. Released efsql binaries are compiled against FDB API version 730, so the client must be 7.3 or newer.

Building from source additionally needs Elixir and Erlang/OTP. mix.exs requires Elixir ~> 1.17; efsql is developed and tested on Elixir 1.19 / OTP 28, which is also what the release builds use.

Installation

Released binaries bundle their own Erlang runtime, so Elixir and OTP are only needed to build from source.

Note: the download links below become live with the first tagged release. Until then, build from source.

macOS

Install the FoundationDB client (there is no client-only package for macOS, so this installs the server too; you do not have to run it):

curl -LO https://github.com/apple/foundationdb/releases/download/7.3.69/FoundationDB-7.3.69_arm64.pkg

sudo installer -pkg FoundationDB-7.3.69_arm64.pkg -target /

On an Intel Mac, use the _x86_64.pkg asset instead. Then install efsql:

brew install foundationdb-beam/tap/efsql

Linux

Install the FoundationDB client, then efsql. On x86_64:

curl -LO https://github.com/apple/foundationdb/releases/download/7.3.69/foundationdb-clients_7.3.69-1_amd64.deb

sudo dpkg -i foundationdb-clients_7.3.69-1_amd64.deb

VERSION=<latest_version_from_releases> # e.g. "0.1.1" \
  && curl -LO https://github.com/foundationdb-beam/efsql/releases/download/v${VERSION}/efsql_${VERSION}_amd64.deb

sudo dpkg -i efsql_$VERSION_amd64.deb

On arm64, substitute aarch64 in the FoundationDB asset name and arm64 in the efsql one.

Other platforms, or no package manager

Tarballs are published for macOS and Linux on both architectures. They unpack to a self-contained directory; put bin/efsql on your PATH (a symlink is fine — it resolves its own location):

VERSION=<latest_version_from_releases> # e.g. "0.1.1" \
  && curl -LO https://github.com/foundationdb-beam/efsql/releases/download/v${VERSION}/efsql-${VERSION}-linux-x86_64.tar.gz

tar xzf efsql-$VERSION-linux-x86_64.tar.gz

Verify the installation

efsql --check

This loads the FoundationDB client and reports whether it is usable, which separates a packaging problem from a connection problem.

Build from source

mix deps.get

MIX_ENV=prod mix release

This produces a self-contained release at _build/prod/rel/efsql/bin/efsql.

The build compiles the erlfdb NIF, which detects the FoundationDB API version by running fdbcli. If fdbcli is not on your PATH, set the version explicitly:

ERLFDB_COMPILE_API_VERSION=730 MIX_ENV=prod mix release

To run against a database without building a release:

mix run -e 'Efsql.Cli.main([])'

Note: A fully self-contained escript is not possible because of the erlfdb NIF.

Usage

_build/prod/rel/efsql/bin/efsql [-C cluster_file] [--storage-id id] [--debug]

The default cluster file is chosen using the same logic as fdbcli:

1. $FDB_CLUSTER_FILE environment variable
2. ./fdb.cluster in the current directory
3. /usr/local/etc/foundationdb/fdb.cluster

Options

Flag	Description
-C, --cluster-file PATH	Path to fdb.cluster file
--storage-id ID	FoundationDB storage ID
--debug	Print the computed Repo call before each result
--no-tui	Use the line-based REPL instead of the full-screen TUI
--check	Verify the FoundationDB client library loads, then exit
-V, --version	Show the version
-h, --help	Show help

Example session

$ _build/prod/rel/efsql/bin/efsql -C /etc/foundationdb/fdb.cluster
Connected to /etc/foundationdb/fdb.cluster
[Ctrl+D to exit]
> select id, product, status from acme.orders;
╭──────────────────────┬─────────────┬───────────╮
│ id                   │ product     │ status    │
├──────────────────────┼─────────────┼───────────┤
│ 22348699227647901699 │ Gadget Plus │ cancelled │
╰──────────────────────┴─────────────┴───────────╯
(1 rows)

Supported SQL

All queries require at minimum a tenant_id.table_name form in the FROM clause.

Keywords and unquoted names are case-insensitive; double-quote a name to keep its case ("CreatedAt"). Most SQL keywords work as bare names, so columns like day, user, date or value need no quoting. The few that can't, such as order, select, limit or and, must be quoted ("order"). The error says so if you forget.

Comments (-- ... and /* ... */) can go anywhere. A syntax error reports the line and column it was found at, and features efsql doesn't support (joins, HAVING, functions other than aggregates) are rejected by name.

Storage IDs

FoundationDB data is organized by storage ID. When multiple storage IDs are in use (e.g. one per product tier or user class), you can address them within a single session using a three-part storage_id.tenant_id.table_name form:

select * from customer.acme.orders;
select * from admins.engineering.users;

The two-part tenant_id.table_name form continues to use the storage ID set at startup via --storage-id (or the default if none was given).

Query every tenant

The tenants of one storage ID share a schema, so a query can read all of them at once. Write * for the tenant:

select _tenant, count(*) from customer.*.orders group by _tenant;
select id, total, _tenant from *.orders where status = 'paid' order by total desc limit 20;
select * from *.orders where _tenant like 'acme%';

*.table uses the startup storage ID (in the TUI, the one you're browsing), and storage_id.*.table names one. Every row gets a _tenant field with its tenant's name, which you can select, filter, group and sort by like any other.

A condition on _tenant decides which tenants are read at all, so where _tenant = 'acme' never touches the others. Everything else runs in each tenant, with that tenant's own indexes, and grouping, ordering and LIMIT then apply to all the rows together.

The tenants are read in a single FoundationDB transaction, so the result is one consistent snapshot. That also means the whole read has to finish within FoundationDB's five-second transaction limit; a query that reads too much says so, and a _tenant or WHERE condition narrows it. One transaction reads at most 100 tenants unless the :efsql, :max_tenants setting says otherwise.

When the tenants don't fit in one transaction, read them in batches:

\set tenant_batch 25

Each batch of 25 tenants is then its own transaction, and there is no tenant limit. Batches are processed as they arrive, so a batched query keeps only what its result needs: a LIMIT without ORDER BY stops reading once it has its rows, ORDER BY ... LIMIT n keeps the best n so far, and GROUP BY keeps a running total per group. Up to four batches are read at once (the :efsql, :batch_concurrency setting, or batch_concurrency: from Elixir). Grouping, ordering and LIMIT still apply to all the rows together, but the result is no longer one snapshot: each batch sees the database at a slightly different moment, and the result says how many transactions it came from. \set tenant_batch off goes back to one transaction. (From Elixir, pass tenant_batch: 25 to Efsql.qall/3.)

Select rows

select col_a, col_b from tenant_id.table_name;

Filter by primary key

-- exact match
select col_a, col_b from tenant_id.table_name where _ = 'foobar';

-- range
select col_a, col_b from tenant_id.table_name where _ >= 'bar' and _ < 'foo';
select col_a, col_b from tenant_id.table_name where _ > 'bar';
select col_a, col_b from tenant_id.table_name where _ < 'foo';
select col_a, col_b from tenant_id.table_name where _ between 'bar' and 'foo';

Filter by partitioned versionstamp primary key

For schemas with a versionstamp primary key partitioned by a field (e.g. partition_by: :user_id), use a tuple ('partition-value', ...) syntax:

-- scan all rows in a partition (select * is supported here)
select * from tenant_id.table_name where _ = ('user-uuid', *);
select col_a, col_b from tenant_id.table_name where _ = ('user-uuid', *);

-- range within a partition (N is a versionstamp integer from the id column)
select col_a, col_b from tenant_id.table_name
  where _ >= ('user-uuid', 22348699227647901699)
    and _ <  ('user-uuid', 22348699227647901800);

SELECT * is supported for any query that doesn't use an index (full table scans, primary key lookups, and partition range scans). It is not supported for index queries.

Filter by index

-- exact match on an indexed column
select col_a, col_b from tenant_id.table_name where index_col = 'baz';

-- range on an indexed column
select col_a, col_b from tenant_id.table_name where index_col >= 'baz' and index_col < 'zaz';
select col_a, col_b from tenant_id.table_name where index_col between 'baz' and 'zaz';

Since efsql doesn't have access to the Ecto schema, type checking is loosened: a value must be written as the type the column stores. For datetime columns, use a typed literal.

OR

select col_a from tenant_id.table_name where status = 'paid' or total > 100;
select col_a from tenant_id.table_name where city = 'Osaka' and (age < 20 or age > 60);
select col_a from tenant_id.table_name where status = 'paid' or status = 'shipped';

AND binds tighter than OR, so a and b or c is (a and b) or c. An OR is checked on the rows read, while the conditions AND-ed with it still narrow what is read: in the second query, an index on city serves city = 'Osaka'. OR-ed equalities on one field are an IN (status in ('paid', 'shipped')), read as one lookup per value when an index or the primary key serves the field. As in SQL, a NULL field fails only its own branch: a row whose status is NULL still matches status = 'paid' or total > 100 when its total is over 100.

In a query across tenants, an OR on _tenant alone picks the tenants, but one can't mix _tenant with other fields.

Negation and ILIKE

select col_a from tenant_id.table_name where status <> 'paid';          -- or !=
select col_a from tenant_id.table_name where status not in ('paid', 'refunded');
select col_a from tenant_id.table_name where total not between 10 and 20;
select col_a from tenant_id.table_name where name not like 'test%';
select col_a from tenant_id.table_name where name ilike 'al%';           -- ignores case
select col_a from tenant_id.table_name where not (status = 'paid' or total > 100);

NOT works on any condition, and is read as its opposite: not (a < 1) is a >= 1, not (a = 1 or b = 2) is a <> 1 and b <> 2, and not (a and b) is not a or not b.

As in SQL, a NULL field matches no comparison, negated ones included: a row whose status is NULL matches neither status = 'paid' nor status <> 'paid'. Negations and ILIKE are checked on the rows read, as no index can serve them; the rest of the WHERE clause still narrows what is read.

Filter by NULL

select col_a from tenant_id.table_name where col_b is null;
select col_a from tenant_id.table_name where col_b is not null;

A field that is nil or absent from the stored record is NULL. The PostgreSQL shorthands col_b isnull and col_b notnull also work. As in SQL, col_b = null would never be true, so efsql rejects it with a pointer to IS NULL.

Typed literals

A quoted literal is always a string. To compare against an Elixir term that has no SQL literal, annotate the literal with a type using the PostgreSQL cast operator ::, or the standard CAST(... AS ...):

-- atoms
select col_a from tenant_id.table_name where status = 'active'::atom;
select col_a from tenant_id.table_name where status = cast('active' as atom);
select col_a from tenant_id.table_name where status in ('active'::atom, 'pending'::atom);

-- module names are atoms too
select col_a from tenant_id.table_name where kind = 'Elixir.MyApp.Widget'::atom;

-- NaiveDateTime, for :naive_datetime / :naive_datetime_usec fields
select col_a from tenant_id.table_name where inserted_at >= '2024-03-01 12:00:00'::timestamp;
select col_a from tenant_id.table_name where inserted_at >= '2024-03-01'::timestamp;

-- DateTime, for :utc_datetime / :utc_datetime_usec fields
select col_a from tenant_id.table_name where seen_at >= '2024-03-01T12:00:00Z'::timestamptz;
select col_a from tenant_id.table_name where seen_at >= '2024-03-01T14:00:00+02:00'::timestamptz;

-- Date and Time, for :date and :time / :time_usec fields
select col_a from tenant_id.table_name where birthday = '2024-03-01'::date;
select col_a from tenant_id.table_name where opens_at < '09:30:00'::time;

Type	Elixir term	Alias
atom	Atom
timestamp	NaiveDateTime	naive_datetime
timestamptz	DateTime (UTC)	utc_datetime
date	Date
time	Time

Elixir has two datetime types and Ecto stores whichever the field declares, so pick the literal type that matches the column: a NaiveDateTime never equals a DateTime, and index lookups compare the exact encoding. A timestamp literal with a time zone offset is rejected rather than having the offset silently dropped; a timestamptz literal without one is taken as UTC. A bare date means midnight. Fractional seconds are optional, and values compare equal regardless of precision.

Numbers need no type: a numeric literal compares by value against a :decimal field, so where price < 100 and where price = 0.1 work. This holds for filtering and sorting, but not for an index on a decimal field, which the adapter keys by the Decimal's term encoding rather than its value.

Ecto stores Ecto.Enum fields as strings, so query those with a plain string literal, not an atom.

Typed literals work anywhere a value does, including IN and BETWEEN:

select col_a from tenant_id.table_name where inserted_at between '2024-01-01'::timestamp and '2025-01-01'::timestamp;

Group and aggregate

-- one row for the whole table
select count(*) from tenant_id.table_name;
select min(inserted_at), max(inserted_at) from tenant_id.table_name where status = 'paid';

-- one row per group
select status, count(*) as n, sum(total) from tenant_id.table_name group by status;
select status, avg(total) from tenant_id.table_name group by status order by avg(total) desc limit 3;

The aggregates are count(*), count(field), sum, min, max and avg, and AS names one. As in SQL, count(*) counts rows while every other aggregate skips NULLs, and NULLs form one group. sum and avg take numbers and Decimals; min and max also work on strings and datetimes.

A selected field must be in GROUP BY. ORDER BY can name a group field, an aggregate or its alias, and LIMIT limits the groups. WHERE is applied before grouping and gets the usual index and primary key pushdown; the grouping itself happens after the rows are read. HAVING is not supported yet.

Limit

select col_a, col_b from tenant_id.table_name limit 100;

If no LIMIT is specified, efsql caps results at 15 rows and indicates when more are available.

Running the demo

EFSQL_SANDBOX=1 boots a throwaway FoundationDB inside the same BEAM and seeds it with demo tenants, so it never touches a real cluster:

EFSQL_SANDBOX=1 ELIXIR_ERL_OPTIONS='+Bi' mix run -e 'Efsql.Cli.main([])'

The demo has two storage ids and several tenants; demo holds ~760 rows across users, orders, products, sessions (a partitioned versionstamp key) and events. Press ? inside the TUI for the query reference.

Data persists under .erlfdb_sandbox/; delete that directory to reseed. The +Bi flag stops Ctrl-C from dropping the VM into its BREAK menu, which raw mode would otherwise expose.