FenecQL

The query language you use to create collections, write documents and search them by vector, by text or by filter.

FenecQL is a small language: one statement reads or writes a collection of documents, and the same text runs in the browser, in an app, in the shell and over HTTP. This page is a tour of it and the grammar; the reference is a page for each family of statements, listed below the tour. Jump to the grammar.

An example of the reference with tabs shows the same statement written with each language's query builder, db a client of the server or the database in an app, and the statement that code sends. Each tab's code is run by that language's tests, as shown, and held to the statement.

A tour

One database, built in the shell a step at a time. Each step is a few statements as written with the shell's answer under them; the timings are from one run, and yours differ. To follow along, open a file with fenec books.fenec (Install has the download, and The shell the first steps) and type each line at the prompt. A write ends at a ;.

Make a collection and write to it

A collection has typed fields. put writes documents, and the database gives each an id.

fenec> create collection books (title text, year int, author int, copies int);
collection `books` created
fenec> put books [{title: "Desert Dunes", year: 2019, author: 1, copies: 3}, {title: "Night Oasis", year: 2024, author: 2, copies: 1}, {title: "Fennec Ears", year: 2021, author: 1, copies: 0}, {title: "Oasis Maps", year: 2016, author: 2, copies: 5}];
4 records (132.88µs)

More in Writing data.

Read it back

get with nothing after it is every row.

fenec> get books
id  title         year  author  copies
--  ------------  ----  ------  ------
1   Desert Dunes  2019  1       3
2   Night Oasis   2024  2       1
3   Fennec Ears   2021  1       0
4   Oasis Maps    2016  2       5
(4 rows, 102.58µs)

More in Reading data.

Filter and order

select names the fields to return, where keeps the rows, order sorts them and limit takes a page.

fenec> get books select title, year where year >= 2020 order year desc
title        year
-----------  ----
Night Oasis  2024
Fennec Ears  2021
(2 rows, 147.79µs)
fenec> get books select title, copies where copies > 0 order year limit 2
title         copies
------------  ------
Oasis Maps    5
Desert Dunes  3
(2 rows, 157.21µs)

More in Reading data.

Change it

A set can read the row it writes, and require 1 refuses the statement unless exactly one row changed. insert … returning id answers the id it made.

fenec> set books {copies: copies + 2} where title = "Fennec Ears" require 1;
1 records (156.21µs)
fenec> insert books {title: "Salt Roads", year: 2022, author: 1, copies: 4} returning id;
id
--
5
(1 rows, 110.04µs)

More in Writing data.

Index it

A filter works without an index, by reading the rows; an index makes one kind of question fast. explain runs a query and says how it ran: here the ordered index is walked and stops at the page.

fenec> create index on books (year) @sorted;
index built on `books.year`
fenec> explain get books where year >= 2020 order year desc limit 2
plan
----------------------------------------------------
order: walked the ordered index on year desc, 2 rows
rows: 2
(2 rows, 250.79µs)

More in Indexes.

A @text index lets match rank rows by how well their words match. fuzzy finds a word typed wrong, and highlight() says where in the text the words are.

fenec> create index on books (title) @text;
index built on `books.title`
fenec> get books select title match title "oasis"
title        _score
-----------  ------
Night Oasis  0.8755
Oasis Maps   0.8755
(2 rows, 258.04µs)
fenec> get books select title, highlight(title) match title "oasys" fuzzy
title        highlight(title)  _score
-----------  ----------------  ------
Night Oasis  [[6, 11]]         0.8755
Oasis Maps   [[0, 5]]          0.8755
(2 rows, 404.92µs)

More in Text search.

Follow a key into another collection

lookup attaches another collection's rows to each row, by a key that collection indexes. The shell prints them as joined rows; over JSON they nest under books.

fenec> create collection authors (name text, country text);
collection `authors` created
fenec> put authors [{name: "Ada", country: "UK"}, {name: "Bo", country: "TR"}];
2 records (92.46µs)
fenec> create index on books (author) @hash;
index built on `books.author`
fenec> get authors select name lookup books on author select title order year
name  books.title
----  ------------
Ada   Desert Dunes
Ada   Fennec Ears
Ada   Salt Roads
Bo    Oasis Maps
Bo    Night Oasis
(5 rows, 181.67µs)

More in Lookup and subqueries.

Count and group

An aggregate in the select list folds rows into one, and group makes one row per key.

fenec> get books select author, count(*) as books, sum(copies) as copies group author order books desc
author  books  copies
------  -----  ------
1       3      9
2       2      6
(2 rows, 154.25µs)

More in Aggregates and grouping.

Not in the tour: vector search, which finds the rows whose embedding is closest to one you pass, and types such as json, timestamp and geo.

The reference pages

A page for each family of statements: what it is for, a run you can repeat, then every rule, with the measurements and the internals folded under a line that says what is inside.

  • Reading data

    get, where, order, limit; pages by parameters, columns worked out, explain.

  • Writing data

    put, insert, set, del; upserts, require, returning, writes that pick their rows.

  • Collections and types

    json fields and paths, points, time, collation, nocase, alter collection.

  • Indexes

    Hash, ordered and unique indexes, indexes over several fields, rows that expire.

  • Lookup and subqueries

    Attach another collection's rows to each row, or filter by them.

  • Aggregates and grouping

    count, sum, group, having, bucket and time zones.

  • Text search

    match, typos, highlights, facets and hybrid search.

  • Vector search

    near, quantized indexes and sparse vectors.

Statements

FenecQL is a small language with vectors in the core. It is not SQL and does not try to be, but it reads SQL's everyday spellings as the statements they mean — select … from, CREATE TABLE, INSERT INTO … VALUES, UPDATE … SET a = …, JOIN as a lookup, IN (…), EXISTS, DISTINCT, BETWEEN, LIKE, ON CONFLICT — and refuses the rest saying what to write: see From SQL. These are all its statements.

create collection [if not exists] <name> ( <field> <type> [required] [collate und|tr] [nocase] [@index], ...
                                         [, @sorted(<field> [desc], ...) | @unique(<field>, ...)] )
drop collection [if exists] <name>
create index [if not exists] on <name> (<field> | <path>) @index
create index [if not exists] on <name> (<field> [desc], <field> [desc], ...) @sorted | @unique
drop index [if exists] on <name> (<field>, <field>, ...)
alter collection <name> add field <field> <type> [collate und|tr] [nocase] [@index]
alter collection <name> drop field <field>
alter collection <name> rename field <field> to <new name>
alter collection <name> alter field <field> @ttl(<duration>) | @sorted

put <name> { field: value, ... }              -- or [ {...}, {...} ]
insert [into] <name> { field: value, ... }     -- as put, refusing an id that is taken
put <name> { field: value, ... } if absent     -- a held id or @unique value passed over, and not counted
put <name> { ... } if absent else set { n: n + new.n }   -- an upsert: the row holding it set, new.f the document's
put <name> $1 [if absent ...]                -- the documents a parameter holds: an object, or a list of them
insert into <name> (a, b) values (1, 'x'), ... [on conflict do nothing | do update set a = ...]  -- SQL's spelling
update <name> set a = <expr>, b = <expr> [where ...]   -- SQL's spelling of set
get <name> [select a, b] [where <expr>]
           [near <field> <vector> [ef N] [exact]]
           [match <field> <text> [fuzzy [N]] [prefix]] [rerank <field> <vector> [candidates N]]
           [fuse [k N] [candidates N]]             -- match and near, both ranking
           [order <key> [collate und|tr] [asc|desc], ...] [limit N] [offset N]   -- N or a parameter, $2
                                                   -- a key: a field, or an expression, distance(loc, $1)
           [facet <field> [top N | ranges [..]] [disjunctive], ...]   -- value counts over every matched row
           [lookup <name> on <child> [= <parent>] [required] <clauses...>]...
           [lookup <name> on (<child>, ...) = (<parent>, ...) ...]   -- a key over several fields
get <name> select a, highlight(f [, pre, post]), snippet(f, N [, ellipsis [, pre, post]])
           match <field> <text> ...           -- where the words matched
get <name> where <field> in (get <name> select <field> ...)  -- a subquery's list
get <name> [where <expr>] count               -- number of matching rows
get <name> select [<key>,] count(*), count(distinct e), sum(e), avg(e), min(e), max(e),
           first(e [by k]), last(e [by k]), approx_count_distinct(e),
           hll_accumulate(e), hll_combine(sketch) [as <name>], ... [where <expr>]
           [group <key>, ... [having <expr>] [order <column> [desc]] [limit N] [offset N] [count]]
get <name> select a, <expr> as <name> ...     -- a column worked out over each row
explain get <name> ...                         -- the path the query took
select a, b from <name> [where ...]           -- the classic SQL order works too
set <name> { field: value, path: value } [where <expr>]  -- a value may read the row: {n: n + 1}
del <name> [where <expr>]
<set | del ...> [order <key> [desc], ...] [limit N] [returning * | a, b]   -- the page a get answers, written and answered
<put | insert ...> [returning * | a, b]       -- the rows written, ids and all, in the documents' order
<put | insert | set | del ...> require N    -- refused, and its batch put back, unless it wrote N rows
get <name> ... require N [lookup ...]       -- refused, and its batch put back, unless it answered N rows

collections | describe <name> | compact [<name>]

-- a path, meta.source.rank, reads into a json field wherever a field is read
  • Every document gets an id on its own. Give one in a put and it writes over the document with that id.
  • insert never writes over. If an id is taken, the whole statement is refused; put … if absent passes over it instead, and the answer counts what was written; put … if absent else set {n: n + new.n} sets the row holding it instead, an upsert in one statement, new.f the document's field.
  • A set can read the row it writes. set hits {n: n + 1} where key = "ip1" is an increment, made under the write lock — see Writes that read the row.
  • A set or a del can pick its rows and answer them. order and limit write the page a get with them would answer, and returning hands it back: a job queue's claim in one statement — see Writes that pick their rows. A put or an insert answers the rows it wrote with returning, the ids it made among them.
  • A page's numbers take a parameter. limit $2 offset $3, and a facet's top $4: every page of a list is one statement, which a server parses once — see Pages by parameters.
  • require N makes a count a condition. A write that does not write exactly N rows, or a get that does not answer exactly N, is refused, and the batch it is in is put back whole — see Writes that must find their rows.
  • alter collection rewrites no document. A field added reads null in the documents already there; a field's type does not change — see Changing a collection.
  • A statement ends where the next one begins, or at a ;. No separator is needed, and a statement may run over several lines: the parser reads where each ends. A ; ends the statement before it, so no clause goes on past one — del notes; where id = 3 is refused, not read as one statement, and so is a ; inside brackets, in (get … ;). A clause that is a statement too begins one after it: get a lookup b on k; select z is two statements, the second a read of z. A query string's select=, group= and facet= take a ; after their list as they always did. How the shell runs statements written over lines is in The shell.
How insert and put treat an id

The id field is automatic. Supplying id inside a put turns it into an upsert. insert makes documents and never writes over one: a document naming an id that is taken refuses the statement, and nothing of it is written — 409 over HTTP, as PostgreSQL's INSERT refuses a taken key. Without an id the two are the same. A retried write that must not be made twice either way sends an Idempotency-Key.

Reference

Types, indexes, operators and functions at a glance.

Typesbool int float text bytes timestamp vector<N[, f16]> sparse<N> [type] json geo (a point, [lon, lat] — see Points)
Caseemail text nocase compares by its case fold wherever it is compared — =, its indexes, order, group — and keeps what was written — see Case-insensitive text
Pathsmeta.source.rank reads into a json field in where, select, order and set, and takes @hash, @unique and @sorted — see Objects and paths
Indexes@sorted(owner, created desc) and @unique(org, slug) over several fields (see Indexes over several fields), @hash, @unique, @sorted, @ttl(30m) (a timestamp field's rows expire — see Rows that expire), @text(k1=.., b=.., prefix=.., chars), @hnsw(metric, m=.., ef_construction=.., ef_search=.., quant=int8|bit), @inverted (a sparse<N> field's), @geo (a geo field's)
Defaults@hnsw(cosine, m=16, ef_construction=200, ef_search=100); ef_search=200 with quant=bit
Metricscosine l2 dot
Operators= != (or <>) < <= > >=, ~ text contains (case-insensitive), has list contains, in [..] (or in (..)), in (get …) — see Subqueries, is null, between a and b, like and ilike with % and _, not in, not like, not between — see From SQL
Arithmetic+ - * / on numbers, a timestamp moved by milliseconds, and + (or ||) joining two texts — in a set's values and in where; see Writes that read the row
Logicand or not — and binds tighter
Parameters$1, $2, … as in PostgreSQL, wherever a value goes, and as limit, offset and a facet's top — see Pages by parameters
Functionslower upper len coalesce greatest least now expired (rows past their @ttl) timestamp bucket(at, 15m [, zone]) cosine l2 dot norm normalize distance(loc, p) within(loc, box) (Points), case when … then … else … end, plus plugins — see Functions over a row
Orderingorder year desc, title asc — a tie on the first key is decided by the second; id can be ordered too; a key may be any expression over the row, order distance(loc, $1), order price * qty desc (see Ordering by an expression); order name collate und puts text in Unicode's order for every language and collate tr in Turkish order, and a field declared name text collate und is in it wherever it compares — see Collation
Pickingset … order run_at limit 10 returning * writes the rows a get with the clauses answers and hands them back, as written; del … returning as they were; insert … returning id the rows it made — see Writes that pick their rows
Explainexplain get ... runs the query and returns the path it took, one row a step — see Explain
Typosmatch body $1 fuzzy matches each word within the edits its length allows, a word as typed ranking first; prefix matches the last word as the start of one, as a search box is typed into — see Typos and words begun
Highlightshighlight(body) and snippet(body, 20) in the select list mark the words a match found, as UTF-16 offsets or between tags you pass — see Highlights and snippets
Facetsfacet brand, color top 10 counts each value over every row the query matches, beside the page — see Facets
Countingcount returns one row with one column; it does not combine with select, near, match, order, limit or offset
Aggregatescount(*) count(…) (the rows it is not null in) count(distinct …) sum avg min max first(… by …) last(… by …) approx_count_distinct hll_accumulate hll_combine over fields, paths or expressions, whole or per group of one key or several, the groups kept by having — see Aggregates and the analytics and market-data recipes
Columnsselect px * qty as notional: an expression over the row, named with as — see Columns worked out
Ceilingsnear, match and fuse at most 10 000 rows (limit + offset, a parameter's as a number written), rerank at most 10 000 candidates, fuse 10 000 a side; a word of a fuzzy or prefix match 10 000 words of the index; expression depth 512 levels; a json value 64 levels deep, a path 64 keys; in (get …) 100 000 values and 4 levels — see Limits