Couchbase: structured N1QL queries

Source: src/nosql/couchbase/n1ql.ts · src/nosql/couchbase/index.ts · Tests: tests/nosql/couchbase-n1ql.test.ts

CouchbaseStore originally exposed key/value CRUD addressed by document id — get / insert / upsert / replace / remove — plus query(n1ql). Fetching a known document was structured; searching was a hand-written N1QL string. This page documents the structured query path that closes that gap.

query() has not gone anywhere. It is the escape hatch for the things N1QL can express and this compiler deliberately will not, and every refusal below names the feature and points back at it.

The surface

Method	Compiles to
`findDocuments(spec)`	`SELECT … FROM <keyspace> AS d [USE KEYS …] [WHERE …] [ORDER BY …] [LIMIT/OFFSET]`
`findDocument(spec)`	the same with `LIMIT 1`, returning the first row or `undefined`
`countDocuments(spec)`	`SELECT COUNT(*) AS \`count` …`, returning the number
`updateDocuments(spec, values)`	`UPDATE … SET … [UNSET …] WHERE … RETURNING …`
`destroyDocuments(spec)`	`DELETE FROM … WHERE … RETURNING …`
`createPrimaryIndex(spec)`	`CREATE PRIMARY INDEX ON <keyspace>`
`createIndex(spec)`	`CREATE INDEX <name> ON <keyspace>(<keys>)`
`dropIndex(spec)`	`DROP INDEX <name> ON <keyspace>` / `DROP PRIMARY INDEX`
`getN1qlCompiler()`	the compile-only `N1qlCompiler`, for inspecting generated N1QL

import { Op } from 'ts-prorm-orm';
import { CouchbaseStore } from 'ts-prorm-orm/nosql/couchbase';

const store = new CouchbaseStore({
  connectionString: 'couchbase://localhost',
  username: 'Administrator',
  password: 'password',
  bucket: 'travel-sample',
  scope: 'inventory',
  collection: 'airline',
});
await store.connect();

const rows = await store.findDocuments({
  where: { country: 'France', 'meta.rank': { [Op.gte]: 3 } },
  attributes: ['name', 'callsign'],
  order: [['name', 'ASC']],
  limit: 20,
});

with { p0: 'France', p1: 3 } bound as named parameters. No caller-supplied value ever reaches the statement text.

More calls, side by side

Each prorm call on the left compiles to the N1QL on the right; the parameters are shown as a comment. store.getN1qlCompiler() gives you the same output without a connection.

import { Op } from 'ts-prorm-orm';
import { CouchbaseStore } from 'ts-prorm-orm/nosql/couchbase';

const store = new CouchbaseStore({ connectionString: 'couchbase://localhost', username: 'Administrator', password: 'password', bucket: 'travel-sample', scope: 'inventory', collection: 'airline' });
await store.connect();

// 1. count with IN
const n = await store.countDocuments({ where: { country: { [Op.in]: ['France', 'Spain'] } } });

// 2. OR, LIKE, paging
const page = await store.findDocuments({
  where: { name: { [Op.like]: 'Air%' }, [Op.or]: [{ country: 'France' }, { 'meta.rank': { [Op.gte]: 4 } }] },
  order: [['name', 'ASC']],
  limit: 5,
  offset: 10,
});

// 3. by key, no index needed
const two = await store.findDocuments({ keys: ['airline_10', 'airline_137'], attributes: ['name', 'country'] });

// 4. update: a null filter also matches MISSING fields
await store.updateDocuments({ where: { country: 'France', callsign: null } }, { callsign: 'UNKNOWN', 'meta.reviewed': true });

// 5. bounded delete
await store.destroyDocuments({ where: { 'meta.rank': { [Op.lt]: 1 } }, limit: 100 });

// 6. an index for (1) and (2)
await store.createIndex({ name: 'idx_airline_country_rank', fields: ['country', ['meta.rank', 'DESC']], ifNotExists: true });

Which path a call takes

Key/value calls and USE KEYS never touch an index; everything else goes through the query service and needs one:

The spec shape

N1qlMatchSpec is field-for-field the findAll shape the rest of the library uses — where, attributes, order, limit, offset, count, distinct — with the document-store parts a relational table has no room for:

Field	Meaning
`bucket` / `scope` / `collection`	Override the store's default keyspace for this call.
`keys`	Document keys for the `USE KEYS` fast path.
`metaId`	Also project `META(d).id`. `true` names it `id`; a string names it that.
`alias`	Keyspace alias inside the statement. Defaults to `d`.

N1qlMutationSpec is the same minus projection, plus returning (true → the affected document keys as { id }, a field list, or false).

Keyspaces are quoted, always

A Couchbase keyspace is bucket.scope.collection, and bucket names routinely contain - and .. This is the single most dangerous thing to get wrong, because it does not produce an error:

That is valid N1QL. The query runs, matches nothing, and nothing anywhere reports a problem. So every part of the keyspace is validated against Couchbase's own naming rules and backtick-quoted:

A part containing a backtick is rejected, not escaped — Couchbase does not permit one in a name, so it can only be a typo or an injection attempt.

When scope and collection are both _default, the bucket alone is emitted (`travel-sample`) — the same shortcut store.collection() takes for key/value access, and the only form a pre-7.0 server understands. Any non-default part produces the full three-part path.

Projection uses d.*, not *: a bare * in N1QL wraps each document under the keyspace name ({ "airline": { … } }), which is almost never what a caller wants back.

`USE KEYS` — the index-free path

When you already know the document keys, pass them:

await store.findDocuments({ keys: ['airline_10', 'airline_10123'] });
// SELECT d.* FROM `travel-sample`.`inventory`.`airline` AS d USE KEYS $p0

USE KEYS reads straight from the key/value layer and never consults an index, so it works on a collection with no GSI at all. A where alongside it still applies, as a filter over the fetched documents. A single key is normalised to a one-element array so the statement text is identical for one key and for many — one more plan-cache hit.

Indexes are mandatory, and the error says so

This is the trap that makes a N1QL builder dangerous. Couchbase refuses a query with no usable GSI rather than falling back to a scan:

No index available on keyspace `travel-sample`.`inventory`.`airline` that matches your query

The statement the compiler emitted is perfectly valid, so nothing at compile time can catch it. What the store does instead is turn that refusal into the fix. On a query error carrying Couchbase's planning-failure code (4000), it re-derives the index the spec would have needed — from the fields the where actually filtered on plus the order fields — and puts it in the error:

Couchbase query failed: No index available on keyspace … .
No index serves this query. Couchbase refuses an unindexed query rather than scanning, so this
fails until one exists: CREATE INDEX `ix_airline_country_name` ON
`travel-sample`.`inventory`.`airline`(`country`, `name`). A primary index (createPrimaryIndex())
makes any filter run but scans the whole keyspace, and passing `keys` uses USE KEYS, which needs
no index at all.

The same derivation is available directly as getN1qlCompiler().suggestIndex(spec), which returns null for a USE KEYS read (it needs none).

Index management is structured too, so fixing it does not mean dropping back to a string:

await store.createPrimaryIndex({ ifNotExists: true });          // dev and tests
await store.createIndex({ name: 'ix_country', fields: ['country', ['name', 'DESC']] });
await store.dropIndex({ name: 'ix_country' });

Index keys are written unaliased and carry no parameters, because N1QL will not accept a placeholder in an index definition. That is also why partial indexes (CREATE INDEX … WHERE …) are not offered: their predicate would have to be inlined, which is the one thing this compiler exists to avoid. Use query().

MISSING is not NULL

In a document store an absent field is MISSING, and d.deletedAt IS NULL is false for a document that has no deletedAt at all. The ORM's where: { deletedAt: null } means "has no usable value", so it compiles to both:

and Op.isNotNull to (d.\email` IS NOT NULL AND d.`email` IS NOT MISSING). Emitting the obvious IS NULL` alone is the classic Couchbase zero-rows bug.

Op.ne keeps SQL's three-valued semantics: d.f != $p0 does not match a document where f is missing, the same way <> 'x' does not match a SQL NULL.

Operators

The ~80 Op.* and $string operators are normalised by the shared normalizeWhere() from src/graph/types.ts — the same single definition the graph dialects use — and rendered here. Notable translations:

Operator	N1QL
`eq` `ne` `gt` `gte` `lt` `lte`	`=` `!=` `>` `>=` `<` `<=`
`in` / `notIn`	`d.f IN $p0` (the array is one bound parameter)
`between` / `notBetween`	`BETWEEN $p0 AND $p1`
`isNull` / `isNotNull`	null and missing, as above
`like` / `notLike`	`LIKE $p0` — the pattern is yours, wildcards intended
`iLike` / `notILike`	`LOWER(d.f) LIKE $p0` with the pattern lowered too
`startsWith`	`POSITION(d.f, $p0) = 0`
`endsWith`	`POSITION(REVERSE(d.f), $p0) = 0`, the value reversed
`substring`	`CONTAINS(d.f, $p0)`
`regexp` / `notRegexp`	`REGEXP_CONTAINS(d.f, $p0)`

Two of those are deliberate departures from the obvious translation:

startsWith / endsWith / substring do not become LIKE. Their argument is literal text, not a pattern. LIKE '<value>%' on a value containing % or _ would silently turn it into a wildcard and widen the result set. POSITION and CONTAINS compare the bound string literally, with nothing to escape.

regexp uses REGEXP_CONTAINS, not REGEXP_LIKE. Every SQL dialect's regexp operator is a search; REGEXP_LIKE anchors the pattern to the whole string.

Nested paths work as where keys and as projections: 'address.city' → d.`address`.`city` , and literal array subscripts too: 'tags[0]' → d.`tags`[0].

Mutations require a filter

updateDocuments and destroyDocuments refuse a spec with neither where nor keys. An unfiltered UPDATE keyspace SET … rewrites every document in the collection, and no ORM should make that the consequence of forgetting an argument. If you genuinely want it, write it and run it through query().

An undefined value unsets the field rather than setting it to null:

await store.updateDocuments({ where: { id: 7 } }, { note: null, nickname: undefined });
// UPDATE `app` AS d SET d.`note` = $p0 UNSET d.`nickname` WHERE d.`id` = $p1
//   RETURNING META(d).id AS `id`

MISSING and NULL are observably different in a document store, so the two spellings must not collapse into one.

What is refused, and why

Each of these throws N1qlCapabilityError — a typed error naming the feature, following the GraphCapabilityError pattern — rather than emitting a statement the server would reject or, worse, answer wrongly.

Refused	Reason
`attributes: { exclude: [...] }`	Needs `SELECT * EXCEPT`, which only exists on Couchbase Server 7.6+ and cannot be emulated without knowing the document shape. List what you want instead.
Array wildcard paths (`tags[*]`)	Matching any element of an array needs `UNNEST` or `ANY … SATISFIES`, which restructures the `FROM` clause, not just one expression.
`order: [[f, 'RANDOM']]`	N1QL has no per-row random function that is stable within a sort, so there is no `ORDER BY random()`.
`count` + `distinct` with no single attribute	N1QL counts distinct values of an expression, not of a whole document. Pass exactly one field.
Partial index predicates	Cannot be parameterised; inlining them is the thing this compiler exists to prevent.
Joins, `UNNEST`, subqueries, `GROUP BY` / `HAVING`, window functions	Out of scope for a `findAll`-shaped spec. `query()`.

ValidationError covers the malformed rather than the impossible: a non-integer limit/offset, a backtick or illegal character in a keyspace or field name, an empty keys array, a non-array operand for in/between, an unknown sort direction, an unusable alias.

Parameters

Every value is bound as a named parameter ($p0, $p1, …) and passed to the SDK as an object:

cluster.query(text, { parameters: { p0: 'France', p1: 3 } });

Named rather than positional because the compiler allocates names as it walks the predicate tree; positional binding would make the order load-bearing across nested $or branches. The Couchbase SDK accepts an array for $1-style positional binding, which this compiler never emits.

limit and offset are the only caller-supplied values written into the statement text, and they are safe to inline precisely because they are refused unless they are non-negative integers — what reaches the text is a number this process produced, never the caller's string.

Design note: why not `BaseGraphDialect`?

OrientDbStore wires itself to a compile-only BaseGraphDialect subclass, and that is the closest existing pattern. The split is reused here — a compiler that holds no connection, a store that owns the cluster and executes { text, params } — but not the base class.

BaseGraphDialect models a table as a node label, a row as a vertex and an association as an edge, and carries a graph DDL surface (CREATE VERTEX, edge types, traversals). Couchbase has none of those: N1QL is SQL over JSON documents in a keyspace, not a graph language. Inheriting it would have meant implementing a dozen compile*Edge methods that could only ever throw.

What is reused is the part of src/graph/types.ts that is about the ORM's query shape rather than about graphs: normalizeWhere() (the single definition of the operator surface — re-parsing WhereOptions here would create a second, drifting copy), ParamBag (ordered, collision-free parameter naming) and the GraphStatement { text, params } contract. N1qlMatchSpec mirrors NodeMatchSpec field for field so the two read the same.

Verification status

Unit / mock-verified only, like the rest of CouchbaseStore: tests/nosql/couchbase-n1ql.test.ts injects a mock cluster, so the real couchbase driver is never loaded and no network is touched.

The tests assert the compiled statement and its parameters, and — for every value-carrying case — that the value appears in the parameters and nowhere in the statement text. A test that only checked "the N1QL contains the value" would pass against a builder that interpolates, which is the bug worth catching. Keyspace quoting is covered directly, since an unquoted keyspace returns zero rows instead of an error.

Not verified: behaviour against a real Couchbase cluster — in particular the exact PlanningFailureError shape the SDK raises for error 4000, the availability of IF NOT EXISTS on the target server version, and real index-selection behaviour.

CouchbaseStore reference — connection lifecycle and key/value CRUD

Query operators — the Op.* surface being compiled

ArangoDB AQL — the same structured-query treatment for another document store

Graph databases — the graph dialects whose predicate normaliser is reused here

Couchbase: structured N1QL queries

The surface#

More calls, side by side#

Which path a call takes#

The spec shape#

Keyspaces are quoted, always#

USE KEYS — the index-free path#

Indexes are mandatory, and the error says so#

MISSING is not NULL#

Operators#

Mutations require a filter#

What is refused, and why#

Parameters#

Design note: why not BaseGraphDialect?#

Verification status#

Related reading#