Reading data

get a collection's documents, filtered, ordered and paged.

get reads a collection: select names the fields to return, where the rows to keep, order their order, and limit and offset the page. Every clause is optional, so get films alone is every row.

Run in the shell, answers and all; the timings are from one run.

fenec> create collection films (title text, year int, rating float, tags [text]);
collection `films` created
fenec> put films [{title: "Dune Run", year: 1999, rating: 7.1, tags: ["sand", "chase"]}, {title: "Night Heist", year: 2008, rating: 8.2, tags: ["heist", "night"]}, {title: "The Quiet Oasis", year: 2015, rating: 7.8, tags: ["sand", "drama"]}, {title: "Neon Heist", year: 2021, rating: 6.9, tags: ["heist", "neon"]}, {title: "Desert Fox", year: 2023, rating: 8.6, tags: ["sand", "drama"]}];
5 records (163.25µs)
fenec> get films select title, year where year >= 2000 and rating >= 7.5 order rating desc
title            year
---------------  ----
Desert Fox       2023
Night Heist      2008
The Quiet Oasis  2015
(3 rows, 168.96µs)
fenec> get films select title where tags has "heist" order year desc
title
-----------
Neon Heist
Night Heist
(2 rows, 138.79µs)
fenec> get films select title, year order year desc limit 2 offset 1
title            year
---------------  ----
Neon Heist       2021
The Quiet Oasis  2015
(2 rows, 126.12µs)
fenec> get films where rating >= 7 count
count
-----
4
(1 rows, 85.92µs)

A row has its id unless select leaves it out, and count answers one row with one column. A get can also be held to a count, require N, which refuses it and its batch when it answers another (A read that must find its rows). Only filters joined by and reach an index (Indexes), and explain, below, says which one a query used. The operators, logic and ordering are in the reference table. Reading across collections, totals and search have pages of their own: Lookup and subqueries, Aggregates and grouping and Text search.

Pages by parameters

limit, offset and a facet's top take a parameter wherever they take a number: a get's, a lookup level's, an inner get's, and a set's or a del's that picks its rows.

get articles where year = $1 order published desc limit $2 offset $3
get products limit $1 lookup reviews on product_id order created desc limit $2
get products match title $1 limit $2 facet brand top $3
set jobs {owner: $1} where run_at <= now() order run_at asc limit $2 returning *

import { connect, raw } from '@fenecdb/web/client'

await db.from('articles')
  .where('year', 2024)
  .order('published', 'desc')
  .limit(20)
  .offset(40)
  .rows();

await db.from('products')
  .limit(20)
  .lookup(
    'reviews',
    { on: 'product_id', order: [['created', 'desc']], limit: 3 }
  )
  .rows();

await db.from('products')
  .match('title', 'tent')
  .limit(20)
  .facet('brand', { top: 10 })
  .rows();

await db.from('jobs')
  .where(raw('run_at <= now()'))
  .order('run_at')
  .limit(10)
  .update({ owner: 'w1' }, { returning: true });

from fenecdb import Client, raw

(db.collection("articles")
    .where("year", 2024)
    .order("published", "desc")
    .limit(20)
    .offset(40)
    .rows())

(db.collection("products")
    .limit(20)
    .lookup("reviews", on="product_id", order=[("created", "desc")], limit=3)
    .rows())

(db.collection("products")
    .match("title", "tent")
    .limit(20)
    .facet("brand", top=10)
    .rows())

(db.collection("jobs")
    .where(raw("run_at <= now()"))
    .order("run_at")
    .limit(10)
    .update({"owner": "w1"}, returning=True))

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("articles").
	Where("year", "=", 2024).
	Order("published", "desc").
	Limit(20).
	Offset(40).
	Rows(ctx)

db.From("products").
	Limit(20).
	Lookup(
		"reviews",
		fenecdb.On("product_id"),
		fenecdb.Sort("created", "desc"),
		fenecdb.Limit(3),
	).
	Rows(ctx)

db.From("products").
	Match("title", "tent").
	Limit(20).
	Facet("brand", fenecdb.Top(10)).
	Rows(ctx)

db.From("jobs").
	WhereCond(fenecdb.Raw("run_at <= now()")).
	Order("run_at", "asc").
	Limit(10).
	Update(ctx, fenecdb.D("owner", "w1"), fenecdb.Returning("*"))

using FenecDb;

await db.From("articles")
    .Where("year", 2024)
    .Order("published", "desc")
    .Limit(20)
    .Offset(40)
    .RowsAsync();

await db.From("products")
    .Limit(20)
    .Lookup(
        "reviews",
        on: "product_id",
        order: [new SortKey("created", "desc")],
        limit: 3
    )
    .RowsAsync();

await db.From("products")
    .Match("title", "tent")
    .Limit(20)
    .Facet("brand", top: 10)
    .RowsAsync();

await db.From("jobs")
    .Where(Cond.Raw("run_at <= now()"))
    .Order("run_at")
    .Limit(10)
    .UpdateAsync(new { owner = "w1" }, returning: ["*"]);

import FenecDB

try await db.from("articles")
    .where("year", 2024)
    .order("published", "desc")
    .limit(20)
    .offset(40)
    .rows()

try await db.from("products")
    .limit(20)
    .lookup(
        "reviews",
        on: "product_id",
        order: [SortKey("created", "desc")],
        limit: 3
    )
    .rows()

try await db.from("products")
    .match("title", "tent")
    .limit(20)
    .facet("brand", top: 10)
    .rows()

try await db.from("jobs")
    .where(.raw("run_at <= now()"))
    .order("run_at")
    .limit(10)
    .updateReturning(["owner": "w1"] as Value)

import com.fenecdb.*

db.from("articles")
    .where("year", 2024)
    .order("published", "desc")
    .limit(20)
    .offset(40)
    .rows()

db.from("products")
    .limit(20)
    .lookup(
        "reviews",
        on = "product_id",
        order = listOf(SortKey("created", "desc")),
        limit = 3
    )
    .rows()

db.from("products")
    .match("title", "tent")
    .limit(20)
    .facet("brand", top = 10)
    .rows()

db.from("jobs")
    .where(Cond.raw("run_at <= now()"))
    .order("run_at")
    .limit(10)
    .updateReturning(mapOf("owner" to "w1"))

import 'package:fenecdb/fenecdb.dart';

await db.from('articles')
    .where('year', 2024)
    .order('published', 'desc')
    .limit(20)
    .offset(40)
    .rows();

await db.from('products')
    .limit(20)
    .lookup(
        'reviews',
        on: 'product_id',
        order: [SortKey('created', 'desc')],
        limit: 3
    )
    .rows();

await db.from('products')
    .match('title', 'tent')
    .limit(20)
    .facet('brand', top: 10)
    .rows();

await db.from('jobs')
    .where(Cond.raw('run_at <= now()'))
    .order('run_at')
    .limit(10)
    .updateReturning({'owner': 'w1'});

sends

get articles where year = $1 order published desc limit $2 offset $3
-- [2024, 20, 40]
get products limit $1 lookup reviews on product_id order created desc limit $2
-- [20, 3]
get products match title $1 limit $2 facet brand top $3
-- ["tent", 20, 10]
set jobs {owner: $1} where run_at <= now() order run_at asc limit $2 returning *
-- ["w1", 10]

Every page is one statement. Written into the text, each page size and each page was a text of its own, which a server parses unless its parse cache still holds it; as parameters a list is one text whatever page is asked. Every builder sends them so.
A parameter is the number it holds. It is checked as the statement runs, before the plan: limit $1 handed 10 answers what limit 10 answers, row for row, through the same plan, and explain names the number.
It is refused as the number written would be, in the same words: a count is a whole number from 0 (20.0, a float with no fraction, is 20), and -1, 2.5, a text or null is `limit` takes a whole number from 0, not -1. Past a ceiling it meets the check the number written meets: near and match at 10 000 rows of limit + offset, a facet's top from 1 to 10 000. A count past what memory could hold is every row.
require N stays a number written. Its count is what the statement guards — require 1, "this row and no other" — a constant of the code, which never makes a text of its own; a page's size and place are a request's data.

How a limit or offset parameter is read

The statement keeps the parameter, so what reads a statement by its shape reads limit $2 as a limit: count and an aggregate without group refuse offset $1 as they refuse offset 5, and a sync replica refuses a synced set … limit $1 as it refuses limit 1. What reads it by its number reads the number it is handed: a long read is pinned or not by the page it asks for, as a number written is. /_stats/statements counted a page size written in as a place already, limit $3; the parse cache (POST /query, 16 shards of 64, a shard emptied when full) kept a text a page.

Ordering by an expression

A key of order is a field, or any expression over the row: a distance, a product, a case. The rows a get answers, and those a set or a del picks, are put in its order as by a field's, ties in id order:

get orders order n * 2 desc, item limit 10
get orders order case when customer = "bo" then 0 else 1 end, id

Every match's key is worked out and the page put in order: the cost follows the matches, as an order over a field without @sorted does. A field beside an expression keeps its own collation and fold; an expression's text orders by its bytes, or in the collation collate names.
distance(<field>, <point>) ascending over a field with @geo, with a limit, walks the point index nearest first and stops at the page, as near does — where no row the filter passes lacks a point, whose null distance comes first: the index holds every row, or the filter puts a radius or a box on the field (Nearest first).
Not in every place an order goes: over groups a key names a column of the list (give the expression a name with as); a lookup level orders its children by fields; a key holds no inner get, aggregate or expired().

Columns worked out

An item of the select list can be an expression over the row: its fields and paths, parameters and functions, with + - * /. It answers under the name as gives it; a field can be named again the same way.

get ticks select sym, px * qty as notional where qty > 100
get ticks select px as price, bucket(at, 1m) as minute, greatest(px, $1) as floor
get orders select id, case when total >= 100 then "large" else "small" end as size

import { connect, expr, bucket } from '@fenecdb/web/client'

await db.from('ticks')
  .select('sym', expr('px * qty').as('notional'))
  .where('qty', '>', 100)
  .rows();

await db.from('ticks')
  .select(
    expr('px').as('price'),
    bucket('at', '1m').as('minute'),
    expr('greatest(px, ?)', 100).as('floor')
  )
  .rows();

await db.from('orders')
  .select(
    'id',
    expr('case when total >= ? then ? else ? end', 100, 'large', 'small').as('size')
  )
  .rows();

from fenecdb import Client, expr, bucket

(db.collection("ticks")
    .select("sym", expr("px * qty").as_("notional"))
    .where("qty", ">", 100)
    .rows())

(db.collection("ticks")
    .select(
        expr("px").as_("price"),
        bucket("at", "1m").as_("minute"),
        expr("greatest(px, ?)", 100).as_("floor")
    )
    .rows())

(db.collection("orders")
    .select(
        "id",
        expr("case when total >= ? then ? else ? end", 100, "large", "small").as_("size")
    )
    .rows())

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("ticks").
	Select("sym", fenecdb.Expr("px * qty").As("notional")).
	Where("qty", ">", 100).
	Rows(ctx)

db.From("ticks").
	Select(
		fenecdb.Expr("px").As("price"),
		fenecdb.Bucket("at", "1m").As("minute"),
		fenecdb.Expr("greatest(px, ?)", 100).As("floor"),
	).
	Rows(ctx)

db.From("orders").
	Select(
		"id",
		fenecdb.Expr("case when total >= ? then ? else ? end", 100, "large", "small").As("size"),
	).
	Rows(ctx)

using FenecDb;

await db.From("ticks")
    .Select("sym", Computed.Expr("px * qty").As("notional"))
    .Where("qty", ">", 100)
    .RowsAsync();

await db.From("ticks")
    .Select(
        Computed.Expr("px").As("price"),
        Computed.Bucket("at", "1m").As("minute"),
        Computed.Expr("greatest(px, ?)", 100).As("floor")
    )
    .RowsAsync();

await db.From("orders")
    .Select(
        "id",
        Computed.Expr("case when total >= ? then ? else ? end", 100, "large", "small").As("size")
    )
    .RowsAsync();

import FenecDB

try await db.from("ticks")
    .select("sym", .expr("px * qty").as("notional"))
    .where("qty", ">", 100)
    .rows()

try await db.from("ticks")
    .select(
        .expr("px").as("price"),
        .bucket("at", "1m").as("minute"),
        .expr("greatest(px, ?)", 100).as("floor")
    )
    .rows()

try await db.from("orders")
    .select(
        "id",
        .expr("case when total >= ? then ? else ? end", 100, "large", "small").as("size")
    )
    .rows()

import com.fenecdb.*

db.from("ticks")
    .select("sym", Computed.expr("px * qty").alias("notional"))
    .where("qty", ">", 100)
    .rows()

db.from("ticks")
    .select(
        Computed.expr("px").alias("price"),
        Computed.bucket("at", "1m").alias("minute"),
        Computed.expr("greatest(px, ?)", 100).alias("floor")
    )
    .rows()

db.from("orders")
    .select(
        "id",
        Computed.expr("case when total >= ? then ? else ? end", 100, "large", "small").alias("size")
    )
    .rows()

import 'package:fenecdb/fenecdb.dart';

await db.from('ticks')
    .select(['sym', Computed.expr('px * qty').as('notional')])
    .where('qty', '>', 100)
    .rows();

await db.from('ticks')
    .select([
        Computed.expr('px').as('price'),
        Computed.bucket('at', '1m').as('minute'),
        Computed.expr('greatest(px, ?)', [100]).as('floor')
    ])
    .rows();

await db.from('orders')
    .select([
        'id',
        Computed.expr('case when total >= ? then ? else ? end', [100, 'large', 'small']).as('size')
    ])
    .rows();

sends

get ticks select sym, px * qty as notional where qty > $1
-- [100]
get ticks select px as price, bucket(at, 1m) as minute, greatest(px, $1) as floor
-- [100]
get orders select id, case when total >= $1 then $2 else $3 end as size
-- [100, "large", "small"]

How a select expression is typed and named

The types are a set's (Writes that read the row): two ints make an int, and one past 64 bits is refused, never wrapped; a null is null. An expression is bound to the collection once a query — each field it reads by its place, every field the list reads decoded in one pass a row — and its error comes at the first row it fails on. An item that is neither a field nor an aggregate of one has no name of its own: select px * qty is refused, and asks for as.

Functions over a row

Functions work wherever an expression goes: where, a set's values, a select list's items and a group's keys.

get events select bucket(at, 15m) as q, count(*) group q
get events where bucket(at, 1d) = "2026-05-03" count
set bars {high: greatest(high, $1), low: least(low, $1)} where key = $2
get orders select id, case when total is null then 0 when total < 0 then -1 else 1 end as sign

bucket(ts, interval) is the start of the interval a timestamp falls in, in UTC: ms, s, m, h and d count from the epoch, w from a Monday as ISO's weeks begin, and mo and y by the calendar — bucket(at, 3mo) is a quarter. The interval is written as a duration, 15m, or passed as a parameter holding one as text or a number of milliseconds. An int of epoch milliseconds gives an int.
bucket(ts, interval, zone) is the same by a zone's clock: an IANA name, bucket(at, 1d, 'Europe/Istanbul'), in any case, or an offset written out — '+03:00', '-0530', '+03', 'Z', 'UTC' — or a parameter holding either, which is how a page passes its own. A day, a week, a month or a year starts at local midnight — the first instant whose clock reads that date, where a clock skips midnight the instant it jumps past it — so where daylight time begins or ends a day is 23 or 25 hours. A shorter interval starts each time the clock reads its start or jumps past it: the hour a clock shows twice is two buckets, and one it skips none. The rules are IANA's from 2000 on, of the tzdata the build was made with (2026c), each zone's transitions and the rule after them; a time before 2000 in a named zone is refused rather than answered by rules the build does not hold, and an offset answers any time. A named zone's table is 31 KB that native builds carry and the browser module is handed beside its collation data the first time a statement names one.
greatest(a, b, …) and least(a, b, …) are the largest and smallest of their values, as PostgreSQL's are: a null is passed over, and the answer is null only when every value is.
case when c then v … else e end is the value of the first condition that holds, or the else, or null. Only the branch taken is worked out: case when qty = 0 then null else px / qty end divides only where it may.

Explain

Put explain in front of a get to see how the query ran: which index it used and how many rows each step read.

explain get events where cat = "c7" order created desc limit 20

plan
order: the ordered index on created not walked, an equality names fewer rows
filter: the hash index on cat, 9753 rows, which is the answer
order: created desc, every key read, 9753 matches, the first 20 put in order
rows: 20

It runs the query, because some choices depend on the data.
The wording is for reading, not parsing; it may change between versions.

A worked explain over a million events

explain in front of a get runs the query and answers with the path it took instead of its rows: one row a step, in the order the steps ran, in a single plan column. The last row is the size of the answer.

explain get events where cat ~ "c77" order created desc limit 20

plan
order: walked the ordered index on created desc, 2153 rows tested, 20 kept
rows: 20

Both are over a million events, cat carrying @hash and created @sorted. The first filter names a hundredth of the rows through an index, so they are sorted; the second is one no index narrows, so the walk tests rows newest first until twenty pass.

It runs the query because several choices depend on the data rather than the text: whether a filter's rows outnumber the ANN's budget, whether a walk along an ordered index fills the page before it gives up, whether the ANN came up short and fell back. The steps name each of them.

A step that starts	says
`filter:`	which index narrowed the filter — the id index, a hash index, an `in` over one, an ordered index's range — or that it was a full scan, how many rows were tested, and whether the scan stopped at the page
`order:`	a walk along an ordered index and how many rows it tested, why one was not walked, or the sort: how many matches, how many put in order
`near:`	the ANN and its `ef`, the filter's set searched exactly, the probe that stopped past the budget, the fallback when the ANN came up short, and the wider beam or exact search when tombstones cut it short
`match:`, `rerank:`	the text index and how many rows it ranked, the vectors read for the rerank
`fuse:`	the rank offset, and how many candidates each side brought — after its own `match:` and `near:` steps
`lookup:`, `required:`	the probe per parent and how many children it attached — over several fields the index and whether its runs were walked in the level's order or probed and sorted; which side `required` was answered from

The same statement goes through every transport: the shell prints the rows, POST /query returns them as JSON, and the JavaScript builder has .explain(), which resolves to the lines. The steps are for reading, not parsing — their wording may change between versions.

Counting

get articles where year >= 2024 count
-- one row, one column

import { connect } from '@fenecdb/web/client'

await db.from('articles').where('year', '>=', 2024).count();

from fenecdb import Client

db.collection("articles").where("year", ">=", 2024).count()

import fenecdb "github.com/fenecdb/fenecdb/integrations/go"

db.From("articles").Where("year", ">=", 2024).Count(ctx)

using FenecDb;

await db.From("articles").Where("year", ">=", 2024).CountAsync();

import FenecDB

try await db.from("articles").where("year", ">=", 2024).count()

import com.fenecdb.*

db.from("articles").where("year", ">=", 2024).count()

import 'package:fenecdb/fenecdb.dart';

await db.from('articles').where('year', '>=', 2024).count();

sends

get articles where year >= $1 count
-- [2024]