# fenecdb

> Minimal embedded document database: one file, live queries an app's
> screens follow, a replica on the device that syncs with a server, a server
> over HTTP with a file per tenant, and search built in -- BM25 over text,
> with typos, highlights and facets, and HNSW vectors for meaning. It runs in
> the browser as WebAssembly, in an app, and as a server. Its query language
> is FenecQL, which is not SQL but reads SQL's everyday spellings.

Writing FenecQL -- the reference below spells it out in full:

- It is not SQL, but it reads SQL's everyday spellings as the statements
  they mean: `CREATE TABLE` and `CREATE INDEX`, `INSERT INTO t (a, b)
  VALUES (1, 'x')`, `UPDATE t SET a = 1 WHERE ...`, `IN (1, 2)`,
  `BETWEEN`, `LIKE` and `ILIKE`, `ON CONFLICT DO NOTHING` and `DO UPDATE
  SET`, `DISTINCT`, `EXISTS`, single quotes and `||`
  (https://fenecdb.com/docs/sql). A double-quoted word is text, never a
  column: a field is written bare, `where name = 'Ada'`. Every row has an
  `id` (an int the database makes; no `rowid`, no `_id`). A JOIN on a
  key is read as `lookup`, which FenecQL writes `get orders lookup users on
  id = user_id` -- each order with its user nested; the clauses after
  `lookup` are the looked-up collection's -- and a filter by another
  collection's rows is an uncorrelated
  `in (get <collection> select <field> where ...)`, or `not in (...)` for
  the rows it does not name. There is no interactive
  transaction: a `/batch` of statements (in the page, a `run` of several)
  lands whole or not at all, and `require <n>` after a write makes its count
  a condition that puts the whole batch back.
- The aggregates -- `count(*)`, `count(f)` (the rows where `f` is not
  null), `count(distinct f)`, `sum`, `avg`, `min`, `max`, `first(f by k)`,
  `last(f by k)` -- go in the select list, over every match or per
  `group <key>`, and `having` keeps groups. Distinct values are a group:
  `select city group city`.
- Text: `like 'a%'` and `ilike` take SQL's `%` and `_`; `~` is a
  case-insensitive *contains* (`email ~ "@example.org"`), never a regular
  expression, and `match` ranks words over an `@text` field.
- Square brackets are list literals and nothing else: `tags: ["a", "b"]`,
  `tags [text]`. Nothing here marks an optional part with them; a clause you
  do not need is simply left out.
- Strings take double or single quotes. Parameters are `$1`, `$2`, ...; a vector
  parameter is a list of numbers. A timestamp is written as text,
  `"2026-09-01T10:00:00Z"`, or `"2026-01-01"` in a comparison.
- Types: `bool int float text bytes timestamp`, lists such as `[text]`,
  `json` (any JSON value; a path such as `meta.source` reads into it),
  `geo` (a point, `[lon, lat]`), `vector<N>`, `vector<N, f16>` and
  `sparse<N>`. A typed field holds no object; a `json` field does. There is
  no decimal -- money is an `int` of cents -- and no UUID type (use
  `text @hash`).
- Indexes: `@hash` for equality, `@unique` for a value only one row may hold,
  `@sorted` for ranges and for `order` with a `limit`, `@ttl(30m)` for rows
  that expire, `@text` for `match`, `@hnsw(cosine)` (or `l2`, `dot`) for
  `near`. Only an `and` chain uses an index: `=` and `in [...]` on a `@hash`
  field or on `id`, `<`, `<=`, `>`, `>=` and `=` on a `@sorted` field;
  everything else scans. `explain get ...` runs a query and returns the path
  it took.
- Text is ordered by its bytes; `order title collate tr` orders one query's
  text as Turkish does (ICU's `tr`: `ç` after `c`, `ı` before `i`). A field
  declared `name text collate tr` compares by it wherever its text is ordered
  -- `order`, `<` and `>` in `where`, `min`, `max`, its `@sorted` index --
  while `=` compares bytes.
- A text field declared `nocase` (`email text nocase @unique`) compares by
  its case fold everywhere -- `=`, `in`, its indexes, `order`, `group`,
  `facet` -- and keeps what was written: `Alice@x.com` is `alice@X.COM`.
- `match` ranks by BM25 and `near` by distance; each decides the order, so
  neither combines with `order`, and the two together need `fuse`, which
  ranks by both. `match ... fuzzy` finds a word typed wrong and
  `match ... prefix` one half typed. Each returns at most 10 000 rows
  (`limit + offset`) and adds a `_score` column.
- After `lookup`, every clause belongs to the child collection and `limit`
  counts children per parent. `on <field>` names the child's field that
  holds the parent's `id` (`get orders lookup items on order_id`); `on <its
  field> = <this one's>` runs the other way (`get items lookup orders on
  id = order_id` attaches each item's order). `required` goes right after
  `on <field>` and keeps only the parents with a matching child -- SQL's
  `exists`; `count` goes before `lookup`.

Every statement, by example:

```fenecql
create collection articles (title text @hash, views int, tags [text], published timestamp @sorted, body text @text, embed vector<4> @hnsw(cosine))
put articles {title: "Rust", views: 10, tags: ["lang"], published: "2026-09-01T10:00:00Z", body: "Ownership and borrowing", embed: [0.1, 0.2, 0.3, 0.4]}
put articles [{title: "Zig", views: 3}, {title: "Go", views: 7}]
get articles select title, views where views < 100 and tags has "lang" order views desc limit 5 offset 5
get articles select title order title collate tr, views desc limit 20
get articles where title in ["Rust", "Go"] count
get articles select title, sum(views), max(published) where tags has "lang" group title order sum(views) desc limit 5
get articles order published desc limit 20 lookup comments on article_id where score >= 4 order published desc limit 3
get articles count lookup comments on article_id required where score = 5
get comments where score >= 4 lookup articles on id = article_id select title
insert into articles (title, views) values ('Nim', 4), ('Odin', 2)
get articles where title like 'R%' and views between 1 and 100 and title not in ["Zig"]
get articles select title, highlight(body) match body "borowing" fuzzy facet tags limit 10
get articles select title where published >= "2026-01-01" near embed $1 limit 3
get articles match body "borrowing" near embed $1 fuse limit 10
get articles match body "borrowing" rerank embed $1 candidates 200 limit 10
explain get articles where views < 100 order published desc limit 5
set articles {views: views + 1} where title = "Rust" require 1
del articles where views > 100000
create index on articles (views) @hash
drop collection articles
```

From JavaScript, `db.from('articles').where('views', '<', 100).order('published', 'desc')
.limit(10).rows()` builds the same FenecQL with every value a parameter, and
`db.live(query, draw)` runs it again after every write to what it reads.

## Docs

- [Overview](https://fenecdb.com/docs/): An embedded document database, from a browser tab to a server of many tenants, with search built in: what it is, what it gave up, and which guide to read first.
- [Quickstart](https://fenecdb.com/docs/quickstart): Start fenec-server, make a collection, write two rows, query and search them with curl and from your language, and see them in fenec studio. About five minutes.
- [Quickstart: in the browser](https://fenecdb.com/docs/quickstart-browser): Run fenecdb in a page with no server: a Vite project, one npm package and a script that opens a database, writes two rows, queries and searches them, then keeps the database in IndexedDB between visits.
- [Install](https://fenecdb.com/docs/install): Every way to install fenecdb: the install script, Homebrew, a .deb or .rpm, a release tarball, the Docker image, the package for your language, or a build from source.
- [Your language](https://fenecdb.com/docs/languages): Use fenecdb from Python, JavaScript, Go, Rust, .NET, Java, PHP or Ruby over HTTP, with the HTTP client your language already has, or embedded in a Swift, Kotlin or Dart app.
- [JavaScript and TypeScript](https://fenecdb.com/docs/javascript): Run fenecdb in the page with one ES module: open a database, query it with the builder, keep it in IndexedDB or a file, use it as the app's state with live queries, and declare its schema in code, Drizzle's way, or type it from the live one.
- [Mobile and native apps](https://fenecdb.com/docs/mobile): Keep a fenecdb database in a file on the device from Swift, Kotlin or Dart and Flutter, with the query builder and live queries driving the UI.
- [HTTP endpoint](https://fenecdb.com/docs/http): Start fenec-server over HTTP and send it FenecQL from curl, fetch or any language's standard library: REST routes derived from the schema, raw statements with parameters, batches and writes that are safe to retry.
- [Embedded Rust](https://fenecdb.com/docs/embedding): Use fenec-core as a Rust library inside your own program: opening a file, running statements, durability and plugins.
- [The shell](https://fenecdb.com/docs/shell): fenec, the fenecdb shell: open a database file, run FenecQL a statement at a time, script it from a file or a pipe, and what the same program does besides: import, backup, types and MCP.
- [Integrations](https://fenecdb.com/docs/integrations): Vector stores for LangChain, LlamaIndex and LangChain.js, retrieval for the Vercel AI SDK, useLiveQuery for React, the Go and .NET SDKs, and fenecdb embedded in Swift, Kotlin and Dart apps.
- [Agents and MCP](https://fenecdb.com/docs/mcp): fenecdb as an agent's search and memory tool over the Model Context Protocol: Streamable HTTP on fenec-server, stdio with fenec mcp, schema, search, query, get, write and a memory that outlives the conversation — each tool a statement held to the token's rules.
- [Sync](https://fenecdb.com/docs/sync): A local replica that reads without the network, writes optimistically and catches up from the server's change stream.
- [Instead of Redis](https://fenecdb.com/docs/redis): Counters, rate limits, locks with fencing tokens, sessions, caches, leaderboards, sets, unique visitors, queues, delayed jobs, streams, pub/sub, idempotency keys and geo, in the database you already run — each a recipe a test runs, with what it guarantees, what it costs, and what fenecdb does not do that Redis does.
- [Job queues](https://fenecdb.com/docs/queues): Workers taking jobs from a collection: claimed by one worker each under a lease, acked, extended, retried with a backoff and dead-lettered, delayed and by priority — a statement each, with what it costs over a million jobs.
- [Points and distances](https://fenecdb.com/docs/geo): Redis's GEOADD and GEOSEARCH in a collection: a geo field, distances in metres, rows within a radius or a box, the nearest first, positions that expire — each a statement, with what it costs over a million points against PostGIS and Redis.
- [Analytics and market data](https://fenecdb.com/docs/analytics): Rollups by the minute, distinct users, funnels and retention over events; bars, VWAP and the latest price over ticks — each a statement, with what it costs over a million rows.
- [Services and events](https://fenecdb.com/docs/microservices): Give each service its own database and token, and follow its writes as a stream of events in place of an outbox table.
- [Serverless and Cloudflare](https://fenecdb.com/docs/serverless): Run fenecdb inside a Cloudflare Worker with a database per Durable Object, or have the function talk to a server instead.
- [Import](https://fenecdb.com/docs/import): Build a collection from SQLite or a live PostgreSQL table in one command, and keep it following the table's changes.
- [FenecQL](https://fenecdb.com/docs/fenecql): FenecQL, the query language of fenecdb: a tour from the first write to a lookup and a total, run in the shell, then the reference by family of statements and the full grammar.
- [Reading data](https://fenecdb.com/docs/fenecql-reading): Reading with FenecQL: get, select, where, order, limit and offset, pages that take parameters, ordering by an expression, columns worked out, functions over a row (bucket and time zones, greatest, least, case), count and explain.
- [Writing data](https://fenecdb.com/docs/fenecql-writing): Writing with FenecQL: put, insert, set and del, upserts with if absent else set, values that read the row, require N, returning, and writes that pick their rows with order and limit.
- [Collections and types](https://fenecdb.com/docs/fenecql-schema): Collections and types in FenecQL: json fields and paths, points, time, collation, case-insensitive text, alter collection and describe.
- [Indexes](https://fenecdb.com/docs/fenecql-indexes): Indexes in FenecQL: declaring one on a field or with create index, @hash, @unique and @sorted, @text's prefix and chars, indexes over several fields, and rows that expire with @ttl and the reaper.
- [Lookup and subqueries](https://fenecdb.com/docs/fenecql-lookup): Lookup and subqueries in FenecQL: lookup attaches another collection's documents to each row, chains, keys of several fields and levels that fold; in (get ...) filters by another collection's rows.
- [Aggregates and grouping](https://fenecdb.com/docs/fenecql-aggregates): Aggregates in FenecQL: count, sum, avg, min, max, first and last, group and having, totals per group and bars by the minute.
- [Text search](https://fenecdb.com/docs/fenecql-search): Text search in FenecQL: match ranks by BM25, rerank, fuzzy and prefix for typos and words begun, highlight() and snippet(), facets, and fuse for words and meaning in one ranking.
- [Vector search](https://fenecdb.com/docs/fenecql-vector): Vector search in FenecQL: near finds the documents whose vector is closest, with a filter, ef and exact; quantized vector indexes, sparse vectors and a hybrid query.
- [From SQL](https://fenecdb.com/docs/sql): The SQL spellings FenecQL reads as written — CREATE TABLE and CREATE INDEX, INSERT … VALUES, UPDATE … SET, JOIN, IN (…), EXISTS, DISTINCT, BETWEEN, LIKE and ILIKE, ON CONFLICT, pgvector's <-> — each the statement it means, what is refused with the FenecQL to write instead, and where the meaning differs from SQL's: nulls, double quotes, text, joins.
- [HTTP reference](https://fenecdb.com/docs/http-reference): Every route of the HTTP endpoint, the filters of the query string, vector search and lookups over REST, the headers that hold a write or mark a read, the statuses a refusal answers with, subscriptions, polling and the change stream.
- [HTTP security and scoped access](https://fenecdb.com/docs/http-access): A bearer token, CORS and a read-only endpoint, JSON Web Tokens held to a policy file down to the rows, and the keys that verify them, with rotation and identity providers.
- [Limits](https://fenecdb.com/docs/limits): What fenecdb deliberately does not do, every ceiling baked into the code, memory and scale characteristics, and the behaviour worth knowing before it surprises you.
- [File format](https://fenecdb.com/docs/file-format): The layout of a fenecdb file — its record kinds, their bodies, and why each exception to the plain layout exists.
- [Server](https://fenecdb.com/docs/server): Run fenecdb as a server with fenec-server: its flags, durability, security with tokens, JWT policies and the audit log, limits, health checks and containers.
- [Studio](https://fenecdb.com/docs/studio): fenec studio, the admin pages fenec-server serves with --studio: collections, rows, edits, a query editor, the schema, live rows and the server's numbers in a browser, with the token you paste and no authority of its own. The site's playground is the same studio over a database in your tab, and a Mac app (beta) opens a database file in it.
- [Replication](https://fenecdb.com/docs/replication): Read replicas that follow a primary's writes, promotion when the primary is gone, and backups you can restore to any moment.
- [Monitoring](https://fenecdb.com/docs/monitoring): Prometheus metrics at /_metrics, ready Grafana dashboards of the metrics and of the data, a slow-statement log, per-statement counts, an audit log, a request id in every line, and OpenTelemetry traces.
- [Datadog and others](https://fenecdb.com/docs/datadog): fenecdb under Datadog, New Relic, Elastic and Grafana Cloud: the OpenMetrics check for /_metrics, the JSON logs and their pipeline, a ready dashboard, the request id that joins them, and OpenTelemetry traces through the Agent's OTLP receiver.
- [Grafana](https://fenecdb.com/docs/grafana): fenecdb in Grafana: the server's metrics through Prometheus, and the database's own rows through fenecdb's data source plugin — FenecQL with the time range and variables bound as parameters, alert rules, annotations, columns in the select list's order — or through the Infinity plugin with nothing to install.
- [Tenants and sharding](https://fenecdb.com/docs/sharding): Start two nodes and a router, make a tenant, stop its node and fail it over to the other, with what each command prints; and where each part of tenants and sharding is explained.
- [Tenants](https://fenecdb.com/docs/sharding-tenants): How to give a tenant its schema and a quota, move it to another node, archive a node's tenants and ask one question of all of them through the router.
- [Replicas and failover](https://fenecdb.com/docs/sharding-failover): How to keep a copy of every tenant on another node, fail a node over by hand or on a lease, and run a second router that follows the first.
- [Regions](https://fenecdb.com/docs/sharding-regions): How to put nodes in regions and zones, keep a tenant and its replica in one region, and give a region a directory of its own so that it goes on taking writes when it is cut off from the others.
- [The global entry](https://fenecdb.com/docs/sharding-entry): How to give every tenant one URL, whichever region it lives in, with a Cloudflare Worker in front of the regions' routers.
- [Read replicas](https://fenecdb.com/docs/sharding-read-replicas): How to answer a client's reads from a replica in its own region while its writes go to the tenant's home.
- [Sharding reference](https://fenecdb.com/docs/sharding-reference): The routes of a tenant node and of the router, the router's flags, what each quota bounds, the tokens, a lease's timings, the statuses a client meets, and what the deploy kit holds.
- [Sharding design](https://fenecdb.com/docs/sharding-design): Why each tenant is a file of its own, how the router forwards a request, how a lease keeps a tenant from having two primaries, and what it costs and does not do.
- [How it works](https://fenecdb.com/docs/concepts): The design decisions under fenecdb — no buffer pool of its own, a mapped file, vectors in the language, one writer — and what each one costs.
- [Compare](https://fenecdb.com/docs/compare): fenecdb beside PostgreSQL, SQLite and PGlite: when to choose each, feature by feature, with sources and measured numbers.
- [vs PostgreSQL](https://fenecdb.com/docs/vs-postgres): When to choose PostgreSQL, with pgvector for vectors, and when fenecdb, feature by feature, with the measured numbers and how to move a table across.
- [vs SQLite](https://fenecdb.com/docs/vs-sqlite): When to choose SQLite, with FTS5, its official WebAssembly build and sqlite-vec, and when fenecdb, feature by feature, with what is measured and what is not.
- [vs PGlite](https://fenecdb.com/docs/vs-pglite): When to choose PGlite, PostgreSQL in the browser with pgvector, and when fenecdb, feature by feature, with what each downloads and how PGlite's SQL maps to FenecQL and the builder.
- [Benchmarks](https://fenecdb.com/docs/benchmarks): fenecdb against SQLite, PostgreSQL, MongoDB and Redis on YCSB, writers beside readers, requests over HTTP, search and vectors: what is measured, how to read the tables, the headline numbers, and a page for each family with the method behind every number.
- [YCSB benchmarks](https://fenecdb.com/docs/benchmarks-ycsb): fenecdb as a general database on YCSB's six core workloads over a million records, in process against SQLite and fenec-server over HTTP against PostgreSQL, MongoDB and Redis, with every cell and the method behind them.
- [Concurrency and request benchmarks](https://fenecdb.com/docs/benchmarks-requests): Many writers and the readers beside them against SQLite, what one request to fenec-server costs and where its time goes, and what loading rows costs against PostgreSQL with pgvector.
- [Text search benchmarks](https://fenecdb.com/docs/benchmarks-text): What a search box costs (match ranked by BM25, marks, facets, typos and half-typed words), and how good the answers are on two BEIR datasets, with the method behind every number.
- [Vector search benchmarks](https://fenecdb.com/docs/benchmarks-vector): Search by meaning against SQLite and PostgreSQL with pgvector, in process and over HTTP, at a million vectors, with the ef and recall trade-off and half precision, and the method behind every number.
- [Size and memory benchmarks](https://fenecdb.com/docs/benchmarks-size): What the browser downloads, how large each build is, what each feature adds to the WebAssembly module, and how the memory fenecdb counts compares with what the process uses.
- [Build from source](https://fenecdb.com/docs/build): Clone the repository, build the shell, the server and the browser module with cargo and make, run the test suites, and build the site.

## Optional

- [Every page above as one text file](https://fenecdb.com/llms-full.txt)