# 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): Build fenecdb, make a collection, write a row and query it — in the browser, against a server, or in the shell.
- [How it works](https://fenecdb.com/docs/concepts): The design decisions under fenecdb — no page cache, a mapped file, vectors in the language, one writer — and what each one costs.
- [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.
- [FenecQL](https://fenecdb.com/docs/fenecql): The FenecQL reference: statements, types, indexes, operators, parameters, time, the vector, text and lookup clauses, highlights and facets.
- [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.
- [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): Query and write fenecdb over plain HTTP and JSON — REST routes from the schema, vector search, raw FenecQL, batches, live subscriptions and a change stream.
- [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.
- [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.
- [Recipes](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.
- [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.
- [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.
- [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.
- [Tenants and sharding](https://fenecdb.com/docs/sharding): Split data by tenant, one file each, across many nodes behind a router that knows which node holds which.
- [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.
- [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.
- [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.
- [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.
- [Overview](https://fenecdb.com/docs/compare): fenecdb beside PostgreSQL, SQLite and PGlite: when to choose each, feature by feature, with sources and measured numbers.
- [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.
- [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.
- [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, with the method behind every number.
- [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.
- [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.

## Optional

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