Reading tables¶

Every table and view is exposed at /<name>. Global temporary tables are included; system tables are not.

Names¶

Firebird stores unquoted identifiers in upper case. The API shows those in lower case, so table BOOKS is /books and column AUTHOR_ID is author_id. Identifiers created with quotes keep their exact spelling: a table "Events Log" is /Events%20Log, with a column "When" as When. Lookups also accept the database spelling (/BOOKS).

Selecting columns¶

Request	Meaning
`GET /books`	all columns (`select=*`)
`GET /books?select=id,title`	chosen columns, in this order
`GET /books?select=book:title`	rename: `alias:column`
`GET /books?select=price::text,published::int`	cast: `column::type`
`GET /books?select="Odd Name"`	quote names that contain special characters

Casts: text, varchar, integer, smallint, bigint, int128, numeric, double, decfloat, boolean, date, time, timestamp.

Filtering¶

A query parameter named after a column filters on it: column=operator.value. Several filters are combined with AND, and one column may be filtered several times (?price=gt.5&price=lt.10).

Operator	SQL	Example
`eq`, `neq`	`=`, `<>`	`?status=eq.new`
`gt`, `gte`, `lt`, `lte`	`>`, `>=`, `<`, `<=`	`?price=lt.10`
`like`	`LIKE`, with `*` as the wildcard	`?title=like.The*`
`ilike`	case-insensitive `LIKE`	`?title=ilike.god`
`similar`	`SIMILAR TO`	`?code=similar.[A-Z]{3}`
`containing`	`CONTAINING` (case-insensitive substring)	`?summary=containing.dragon`
`starting`	`STARTING WITH`	`?title=starting.The`
`in`	`IN (...)`	`?id=in.(1,2,3)`, `?name=in.("a,b",c)`
`is`	`IS NULL`, `IS NOT NULL` (`not_null`), `IS TRUE/FALSE/UNKNOWN`	`?born=is.null`
`isdistinct`	`IS DISTINCT FROM`	`?a=isdistinct.5`
`not.<op>`	`NOT (...)`	`?id=not.in.(1,2)`

containing, starting and similar are Firebird operators that PostgREST does not have. starting can use an index on the column; like/ilike patterns, containing and similar scan the rows.

Values are converted to the column type: booleans accept true/false/t/f/1/0, dates and timestamps accept ISO 8601 (2024-05-06, 2024-05-06T07:08:09Z). Wrap values that contain commas or parentheses in double quotes inside in.(...).

Logic trees¶

or and and take a parenthesised list of conditions written as column.operator.value. They nest, and not. negates a group:

?or=(price.lt.8,and(published.gte.1990,in_stock.is.true))
?not.and=(id.gt.2,id.lt.5)
?or=(title.ilike.*sea*,title.ilike.*star*)
?or=(born.is.null,country.not.eq.UK)

Inside a tree, negate a single condition with column.not.operator.value.

Ordering¶

?order=price.desc
?order=published.desc.nullslast,title
?order=price.asc.nullsfirst

Without order, rows come in whatever order Firebird returns them.

Ordering by an embedded resource¶

Order by a column of a to-one (many-to-one) embed with embed(column). The embed must be in select; use its alias if it has one:

GET /books?select=title,authors(name)&order=authors(name).desc,title
GET /books?select=title,writer:authors(name,born)&order=writer(born)
GET /books?select=title,...authors(name)&order=authors(name)

It works for embedded rows too. This orders each tag's books by their author's name:

GET /tags?select=name,books(title,authors(name))&books.order=authors(name)

Filters on the embed apply: a row whose embed is filtered out, and so returned as null, sorts as NULL. Place those rows with nullsfirst or nullslast.

To-many embeds (one-to-many, many-to-many) have no single value to order by and are refused with FBRST126; order their rows with embed.order= instead (see Resource embedding).

Paging¶

?limit=25&offset=50

or the Range header (items, zero-based, inclusive):

Range: 50-74

Firebird runs these as OFFSET n ROWS FETCH NEXT m ROWS ONLY. Large offsets get slower, because Firebird has to skip those rows. For deep paging, filter on an indexed key instead (?id=gt.<last id>&order=id&limit=25).

db-max-rows caps every read, whatever the request asks for.

A large page is encoded as its rows are read, so a result costs about its JSON size in memory on the server (a 100 MB response peaks near 200 MB of process memory); the response is sent once complete, because Content-Range needs the number of rows. Reads with embedded resources keep their rows until the children are attached and cost more. For exports, page with limit and offset or a Range header.

Counting¶

Send Prefer: count=exact to get the total in Content-Range:

GET /books?select=id&limit=2&offset=1
Prefer: count=exact

HTTP/1.1 206 Partial Content
Content-Range: 1-2/5

The status is 206 Partial Content when the page doesn't contain every matching row, and 200 otherwise. Counting runs an extra SELECT COUNT(*), which reads every matching row; only ask for it when you need it.

Estimated counts¶

On large tables an exact count is slow, because Firebird has to visit every row. Two cheaper modes return an estimate instead:

`Prefer`	Total in `Content-Range`
`count=exact`	the exact number of matching rows
`count=planned`	the table's row count estimated from its index statistics, without reading the table
`count=estimated`	exact up to `db-max-rows` (or 1000 when it is not set), the `planned` estimate above that

The estimate comes from the statistics of the table's primary key or another unique index: their selectivity is 1 / number of rows. Firebird computes index statistics when an index is created and when you run SET STATISTICS INDEX, so the estimate is as old as that. Recompute them after large loads or deletes, for example in a nightly job. This recomputes every index of the database (in isql, wrap it in SET TERM):

EXECUTE BLOCK AS
  DECLARE name VARCHAR(63);
BEGIN
  FOR SELECT TRIM(RDB$INDEX_NAME) FROM RDB$INDICES WHERE COALESCE(RDB$SYSTEM_FLAG, 0) = 0 INTO :name DO
    EXECUTE STATEMENT 'SET STATISTICS INDEX "' || name || '"';
END

An estimate only describes a whole table. When it doesn't apply, the total is exact and Preference-Applied says count=exact:

the request has filters (an estimate of a filtered count would need the optimizer's guesses, which Firebird does not expose);
the resource is a view or a procedure;
the table has no unique index, or its unique indexes have no statistics yet (an index created on an empty table has none until the next SET STATISTICS);
with count=estimated, the estimate is below the rows already counted.

The total is never smaller than the rows in the response: when an old estimate is behind, the rows actually returned win.

HEAD¶

HEAD runs the same query and returns only the headers, so HEAD with Prefer: count=exact gives the count without a body.

Transactions¶

Every request runs in its own transaction. GET and HEAD use a read-only transaction, so a view or selectable procedure that tries to write fails instead of changing data.