# typeorm-extensions

OData v4 query support for TypeORM on Postgres: filtering, ordering, relation
expansion, projection, pagination, and row-level-security context propagation —
driven by a URL query string, applied to a `SelectQueryBuilder`.

```ts
import { executeOData } from "@ballistix.digital/typeorm-extensions";

// From a request query string or an Express-style req.query object:
const qb = repo.createQueryBuilder("project");
const { data, total } = await executeOData(qb, req.query);
```

```
GET /projects
  ?$filter=status eq 'active' and budget gt 1000
  &$orderby=name desc
  &$expand=owner,attachments($select=id,filename)
  &$select=id,name,budget
  &$top=20&$skip=40&$count=true
```

Version 3 is a ground-up rewrite around the official **OData v4** grammar. If
you are coming from v2's `?q=`/`?embed=`/`?sort=`/`?page=` dialect, read
**[MIGRATION.md](./MIGRATION.md)** — the query language changed, and a few
behaviours (notably string `eq`) changed with it.

Requires **TypeORM `^1.1.0`** and Postgres. TypeORM is a peer dependency: the
library reads your `EntityMetadata` and builds onto your `SelectQueryBuilder`,
so it must resolve to the same TypeORM instance the application uses.

Developer documentation — the pipeline, the areas, the conventions, and how to
add to each — is in **[docs/](./docs/README.md)**.

## Why OData

REST collection querying has no single dominant standard, but OData v4 (OASIS)
is the only one that specifies filtering, ordering, expansion, projection,
paging and counting as one coherent grammar — and the only one with a
maintained, spec-grade TypeScript parser
([`@balena/odata-parser`](https://github.com/balena-io-modules/odata-parser)).
v3 implements a documented subset of that grammar and rejects everything
outside it with a clear 400, which is exactly what the spec's partial-conformance
rules allow.

## Architecture

A query travels through three layers, each independent and each debuggable on
its own:

```
query string ──parse──▶ ODataQuery ──bind──▶ (join plan + bound paths) ──emit──▶ SelectQueryBuilder
```

- **parse** (`src/module/odata/service/parse/parse.facade.ts`, over
  `odataParser.service.ts`) wraps
  `@balena/odata-parser` and normalizes its output into the library's own AST
  (`ODataQuery`). This is the *only* module that touches the parser
  dependency, so it can be swapped without touching anything downstream. The
  AST is plain serializable data.
- **bind** (`src/module/odata/service/bind/binder.service.ts`,
  `src/module/odata/service/plan/joinPlan.service.ts`) resolves every property path against TypeORM
  `EntityMetadata` — validating columns, relations and embeddeds, and
  planning the joins. A path that does not resolve is a 400 here, before any
  SQL exists.
- **emit** (`src/module/odata/service/emit/emit.facade.ts`, over
  `filterEmitter.service.ts`, `lambdaEmitter.service.ts` and
  `searchEmitter.service.ts`) turns the bound
  query into `SelectQueryBuilder` calls. Every value is a bound parameter;
  every identifier is escaped through TypeORM's own driver. Postgres is the
  one supported dialect, and all dialect-specific SQL lives here.

There is **no prototype patching**. The API is plain functions you import.

Every identifier the library generates in SQL — join aliases, lambda aliases,
parameter names, the search rank alias — starts with `__` so it cannot
collide with an alias the application chose.

Three entry files sit at `src/`: `index.ts` (the root export), `nestjs.ts`
(`./nestjs`), and `compatV2.ts` (`./compat/v2`). Each is a plain re-export
list — no logic lives there.

### Inspecting a query

There is no debug hook. Call `parseOData` to see the AST before binding, and
read `qb.getQueryAndParameters()` after `applyOData` for the final SQL. Both
are plain data, safe to log:

```ts
const ast = parseOData(req.query);
logger.debug({ ast });

applyOData(qb, ast);
const [sql, params] = qb.getQueryAndParameters();
logger.debug({ sql, params });
```

## Supported OData surface

### `$filter`

| Group | Supported |
|---|---|
| Comparison | `eq ne gt ge lt le` (with spec null semantics), `in`, `null` literals |
| Logical | `and or not`, grouping `( )` |
| String functions | `contains startswith endswith` (indexable LIKE), `tolower toupper trim length indexof substring concat` |
| Date/time functions | `year month day hour minute second fractionalseconds date time now maxdatetime mindatetime totaloffsetminutes totalseconds` |
| Arithmetic | `add sub mul div mod`, `ceiling floor round` |
| Type | `cast` (to the Edm scalar types) |
| Lambdas | `any` / `all` over to-many relations **and** primitive array columns |
| Navigation | to-one paths (`owner/email`), embedded columns (`address/city`), nested (`owner/organisation/code`) |

Filtering *across* a to-many relation requires an `any`/`all` lambda, per spec:
`attachments/any(a:a/filename eq 'x.pdf')`. Write no space after the lambda's
colon — the grammar does not take one.

**A uuid is a quoted value here**: `$filter=id eq '0f3e…'`, not the bare
`id eq 0f3e…` that OData's own guid literal allows. The parser reads an
unquoted uuid as a number and rejects it at the first hex letter. The compat
layer quotes a bare uuid for v2 callers, so a `?q=` URL keeps working.

**Null semantics follow OData, not SQL.** `budget ne 1000` matches rows where
`budget` is null (null *is* "not equal"); negated comparisons include nulls the
same way. The generated SQL uses `COALESCE(..., FALSE)` only where an operand
can actually be null, and plain index-friendly operators everywhere else.

### `$orderby`, `$top`, `$skip`, `$count`

`$orderby=name desc,owner/email` — comma-separated property paths, each with an
optional `asc`/`desc`. `$top`/`$skip` are offset pagination; `$count=true` runs
the query with a total. A query with no `$top` is unbounded — this library
never invents a page size. An explicit `$top` is always honoured, however
large: a caller asking for everything is trusted. An application that wants a
default page size sets `$top` on the query itself before calling `applyOData`
or `executeOData`. Cursor pagination is not implemented — `$skiptoken` is
reserved for it.

### `$expand` and `$select`

`$expand=owner,attachments($select=id,filename)` left-joins and selects the
relations into the payload; nested `$filter`, `$select` and `$expand` are
supported. `$select=id,name` narrows the root columns (primary keys are always
kept).
Relations joined only to satisfy `$filter` or `$orderby` are **not** added to
the payload. Eager relations (TypeORM `eager: true`) are always joined,
mirroring `repository.find()`, with no opt-out.

A nested `$filter` chooses which expanded rows appear:

```
GET /projects?$expand=attachments($filter=sizeBytes gt 2048;$select=filename)
```

It never changes the root row set. A project whose attachments all fail the
filter still comes back, with an empty `attachments`; a filtered to-one
expansion comes back `null`; and a `$count=true` total counts the same roots
either way. The nested filter takes the whole root `$filter` grammar, bound
against the expanded entity — its own columns, paths to its to-one relations,
and `any`/`all` lambdas from it — and nests to any depth.

A filtered expansion owns its relation in the payload. A join of the same
relation made by the caller, and an unfiltered `$expand` of it, stay in the
query but stop contributing to the payload — otherwise the two would overwrite
each other and the filter would silently do nothing. Naming one relation twice,
each time with a nested `$filter`, is a 400: both entries write the same
property, so one of the two filters could only be lost.

### `$search`

`$search` is spec-defined as implementation-specific free-text matching; here it
is Postgres `pg_trgm` trigram similarity over the columns named in
`$searchColumns`, a required, paired system option:

```
GET /projects?$search=apollo&$searchColumns=name,owner/email
```

`$searchColumns` is comma-separated property paths, `/` between segments for a
to-one navigation, and every path must resolve to a string column. `$search`
without `$searchColumns` is a 400, and so is `$searchColumns` without
`$search` — neither option means anything alone.

**Security.** `$searchColumns` comes from the request, so whoever can call the
endpoint chooses which columns `$search` reads. Every string column of the
entity, and of every to-one relation reachable from it, is searchable this
way — the library places no restriction of its own. An endpoint that must
limit this validates `req.query.$searchColumns` itself before the query
reaches `applyOData` or `executeOData`.

When `$search` is present and no `$orderby` is given, results order by
relevance. The term is always a bound parameter.

The whole value is one keyword, matched as plain text. Surrounding double
quotes are stripped; `AND`, `OR` and `NOT` are not operators, so `$search=and`
looks for the word "and" and `$search=a AND b` looks for that phrase.

### Deliberately rejected (HTTP 400)

Valid OData that this library does not implement fails loudly, naming the
construct and the alternative where one exists.

- **System options** — `$apply`, `$compute`, `$format`, `$skiptoken`,
  `$deltatoken`, `$inlinecount`, `$index`, `$schemaversion`.
- **Filter constructs** — `geo.*` functions and `geography`/`geometry` literals
  (need PostGIS), `has` (enum flags), `hassubset`, `hassubsequence`,
  `matchesPattern`, `divby`, `case` and `isof`.
- **Nested options inside `$expand`** — everything except `$filter`, `$select`
  and `$expand`, which covers `$orderby`, `$top`, `$skip` and `$count`
  (`$levels` is not in the grammar at all, so it fails as a syntax error).
- **`$count` inside a path** (`tags/$count`) — use `$count=true` for the
  collection total.
- **`$search` without `$searchColumns`** — name the columns to match against.

Each of these throws `UnsupportedFeatureError`; see its message for the
specifics. A `$`-prefixed name that is no OData system option at all throws
`QuerySyntaxError` instead, and **`$searchColumns` without `$search`** throws
`InvalidQueryError`, since the columns then have nothing to match.

## Row-level security

`runInRlsContext` opens a transaction, applies an RLS context via `SET LOCAL`,
runs your callback, and returns its value:

```ts
import { runInRlsContext, RlsContext } from "@ballistix.digital/typeorm-extensions";

const projects = await runInRlsContext(
  dataSource,
  RlsContext.withRole("app_user", { user: { id, role: "app_user" } }),
  (tx) => applyOData(tx.getRepository(Project).createQueryBuilder("project"), req.query).getMany(),
  { allowedRoles: ["app_user"] },   // required allowlist
);
```

The role is validated against `allowedRoles` and emitted as a quoted
identifier — a request-supplied role can never inject SQL or escalate. Running
without switching roles is **explicit**: pass `RlsContext.withoutRole()`, not an
absent context. `createRlsRunner(dataSource, config)` binds the config once.

The config carries two options. `allowedRoles` lists the roles a context may
request — the comparison is case-insensitive, and a role outside the list
throws `InvalidContextError`. `settingName` names the session setting the
context is serialized into for `current_setting()` (default `request.context`);
Postgres accepts a custom setting only under a `prefix.name` two-part name, so
a name without a `.` throws `InvalidContextError` too.

## Errors

Every error this library throws extends `LibraryException`: one envelope with
a stable code, a suggested HTTP status, and structured detail.

| Field     | Type            | Meaning                                       |
|-----------|-----------------|------------------------------------------------|
| `status`  | `number`        | Suggested HTTP status (`statusCode` is an alias) |
| `code`    | `ExceptionCode` | Stable machine-readable code                   |
| `message` | `string`        | Human-readable title, built from `errors[0]`   |
| `errors`  | `object[]`      | Structured detail behind the message           |

Each subclass narrows `errors` to its own detail type: `QuerySyntaxError.errors`
is `QuerySyntaxDetail[]`, and the other three subclasses narrow it the same way.

`ExceptionCode` values, their status, and their detail shape:

| Code                  | Status | Detail                                              |
|-----------------------|--------|------------------------------------------------------|
| `QUERY_SYNTAX`        | 400    | `{ option: string; reason: string }`                 |
| `UNSUPPORTED_FEATURE` | 400    | `{ feature: string; alternative?: string }`          |
| `UNKNOWN_PROPERTY`    | 400    | `{ entity: string; path: string; reason?: string }`  |
| `INVALID_QUERY`       | 400    | `{ message: string }`                                |
| `INVALID_CONTEXT`     | 403    | `{ message: string }`                                |

`ODataQueryError` is the shared base of the first four codes. `InvalidContextError`
(a rejected RLS context) carries `INVALID_CONTEXT` on its own.

`@ballistix.digital/typeorm-extensions/nestjs` exports `toHttpException`. It
maps any `LibraryException` to an `HttpException` whose body is `{ status,
code, title, errors }`, using the exception's own status. An unrecognised
error passes through untouched, so a caller always rethrows the result.

An error thrown after the pipe has run — a query applied inside a service, or
a rejected RLS context — reaches your own exception filter, which owns the
response shape:

```ts
@Catch(LibraryException)
export class LibraryErrorFilter implements ExceptionFilter {
  catch(error: LibraryException<unknown>, host: ArgumentsHost) {
    const body: ApiErrorDto = {
      status: error.status,
      code: error.code,
      title: error.message,
      errors: error.errors,
    };
    host.switchToHttp().getResponse().status(error.status).json(body);
  }
}
```

## Framework adapters

`@ballistix.digital/typeorm-extensions/nestjs` provides an `ODataQueryPipe`
(parses `req.query` into an `ODataQuery`, mapping parse errors to `400`s) and
`toHttpException` (see Errors above). `@nestjs/common` is an optional peer —
the core has no framework dependency.

## Upgrading from v2

`@ballistix.digital/typeorm-extensions/compat/v2` translates the old
`page`/`pagesize`/`sort`/`embed`/`q`/shorthand query shapes into a v3
`ODataQuery` and runs them through the fixed engine, so you can upgrade the
library first and your public URLs later. It translates; it does not reimplement
— translated queries get the corrected semantics too. See
[MIGRATION.md](./MIGRATION.md).

## Testing

The suite is split into Jest projects, each runnable on its own.

| Command | Needs Postgres | What it covers |
|---|---|---|
| `npm run test:unit` | no | Parser, AST normalization, error mapping, compat translation, emitter guards |
| `npm run test:sql` | yes* | The SQL each construct generates (metadata-only DataSource) |
| `npm run test:integration` | yes | Queries executed against seeded data, plus RLS |
| `npm run test:packaging` | no | `npm pack` ships only `dist/**` (builds first) |
| `npm test` | yes | unit + sql + integration |
| `npm run test:coverage` | yes | ...with coverage thresholds enforced |
| `npm run lint` | no | ESLint over `src/` |

\* The `sql` specs never execute a query: they build entity metadata and read
`qb.getQuery()`. The tier still needs Postgres reachable, because it shares the
`globalSetup` that creates the extensions and the RLS role. The `integration`
tier needs the same Postgres with `pg_trgm` (for `$search` and the RLS
policies).

### Getting a Postgres

Inside the devcontainer this is already running. On the host:

```sh
docker compose -f .devcontainer/docker-compose.yml up -d postgres
```

`.devcontainer/` is a per-developer folder and is not tracked in this
repository, so a fresh clone may not have it. Any Postgres 17 works, because
the official image ships the extensions `globalSetup` creates:

```sh
docker run -d --name typeorm-extensions-pg \
  -e POSTGRES_PASSWORD=dev123 -e POSTGRES_DB=postgres -e TZ=UTC \
  -p 5556:5432 postgres:17-alpine
```

Connection settings come from the environment, defaulting to that sidecar
(`POSTGRES_HOST=localhost`, `POSTGRES_PORT=5556`, `POSTGRES_DB=postgres`,
`POSTGRES_USER=postgres`, `POSTGRES_PASSWORD=dev123`). Each Jest worker gets its
own schema (`test_w<N>`), created and dropped per run. Set `TYPEORM_LOGGING=true`
to see the SQL.

### TypeScript version constraint

**Stay on TypeScript 5.x** — 6 and 7 break the ts-jest toolchain. The
repository pins `typescript@^5.9.3` and `ts-jest@^29`; raising the major
version of either needs both to move together.

### Layout

```
src/index.ts                     root entry: applyOData, executeOData, RlsContext, ...
src/nestjs.ts                    "./nestjs" entry: ODataQueryPipe, toHttpException
src/compatV2.ts                  "./compat/v2" entry: applyV2Query, translateV2Query, ...
src/module/odata/domain/         ODataQuery AST, ODataResult, and the per-stage interchange types
src/module/odata/exception/      ODataQueryError and its subclasses
src/module/odata/service/        one facade per stage, over parser, binder, join plan and the emitters
src/module/odata/util/           AST normalization, SQL function mapping, per-stage pure helpers
src/module/rls/                  RlsContext, RlsRunnerService, transactional RLS context
src/module/compat/               v2 query-shape translation and adapter services
src/module/nestjs/               NestJS pipe, HTTP exception mapping
src/module/common/               what two or more areas share: the error envelope, identifier escaping
test/unit/        no database
test/sql/         generated SQL only (metadata-only DataSource)
test/integration/ executes queries, asserts rows, RLS
test/packaging/   tarball guard
```

## Known issues

See [FINDINGS.md](./FINDINGS.md) — the v2 defect ledger, now annotated with how
each was resolved in v3.