# @qrvey/query-builder

> Build one query, run it on any engine.

![license](https://img.shields.io/badge/license-MIT-blue)
![types](https://img.shields.io/badge/types-included-blue)
![modules](https://img.shields.io/badge/modules-ESM%20%2B%20CJS-blue)
![dependencies](https://img.shields.io/badge/runtime%20deps-0-brightgreen)

`@qrvey/query-builder` turns a **normalized, engine-agnostic query definition**
into an **engine-specific query** — SQL for PostgreSQL and ClickHouse, search DSL
for Elasticsearch and OpenSearch — plus consistent output metadata.

It is a **pure transform**: no I/O, no engine clients, no product coupling. You
give it an input and a target engine; it returns a query object. A consumer (for
example `@qrvey/enginex`) executes the result.

```ts
import { QueryBuilder } from '@qrvey/query-builder';

const { query, values } = new QueryBuilder(
  {
    source: { name: 'orders' },
    select: [{ field: 'id', type: 'string' }, { field: 'amount', type: 'number' }],
    filter: { field: 'status', type: 'string', operator: 'eq', value: 'active' },
    orderBy: [{ field: 'created', type: 'date', direction: 'desc' }],
    limit: 20,
  },
  { engine: 'postgresql' },
).transpile();

// query  → SELECT "id", "amount" FROM "orders" WHERE "status" = $1 ORDER BY "created" DESC LIMIT 20
// values → ['active']
```

---

## Table of contents

- [Why](#why)
- [Install](#install)
- [Quick start](#quick-start)
- [Supported engines](#supported-engines)
- [Features](#features)
- [Guides](#guides)
- [Result shapes](#result-shapes)
- [Validation & errors](#validation--errors)
- [Scope](#scope)
- [Architecture](#architecture)

---

## Why

Applications that must talk to several data stores usually end up with one query
layer per engine and duplicated business logic. `query-builder` gives you a
**single, typed query definition** that compiles to each engine's native query,
so callers write the query once and only choose an engine at execution time.

- **One input, many engines** — the same `QueryInput` compiles to SQL or search DSL.
- **Safe by construction** — values are always parameterized; identifiers and LIKE/wildcard metacharacters are escaped.
- **Typed** — the input and output contracts are fully typed.
- **Extensible** — adding an engine means adding a dialect, not changing the contract.

---

## Install

```bash
npm install @qrvey/query-builder
# or
yarn add @qrvey/query-builder
```

Ships CommonJS, ESM and type declarations. No runtime dependencies.

---

## Quick start

### Read a query

```ts
import { QueryBuilder } from '@qrvey/query-builder';

const builder = new QueryBuilder(
  {
    source: { name: 'orders' },
    groupBy: [{ field: 'country', type: 'string' }],
    aggregations: [
      { function: 'sum', field: 'amount', type: 'number' },
      { function: 'count' }, // COUNT(*) / total hits
    ],
    having: {
      aggregation: { function: 'sum', field: 'amount', type: 'number' },
      operator: 'gt',
      value: 1000,
    },
  },
  { engine: 'clickhouse' },
);

const result = builder.transpile();
```

### Write a mutation

```ts
import { MutationBuilder } from '@qrvey/query-builder';

const result = new MutationBuilder(
  {
    operation: 'update',
    source: { name: 'orders' },
    set: [{ field: 'status', type: 'string', value: 'archived' }],
    filter: { field: 'amount', type: 'number', operator: 'lt', value: 10 },
  },
  { engine: 'postgresql' },
).transpile();
```

### Choose the engine per call

The engine can be set on the constructor or per call:

```ts
const builder = new QueryBuilder(input);
builder.transpile({ engine: 'postgresql' });
builder.transpile({ engine: 'elasticsearch' });
```

---

## Supported engines

| Engine | Family | Identifiers | Parameters |
| ------ | ------ | ----------- | ---------- |
| `postgresql` | SQL | `"col"` | positional `$1, $2, …` |
| `clickhouse` | SQL | `` `col` `` | named `{pN:Type}` |
| `elasticsearch` | Search DSL | dotted field names | request `body` |
| `opensearch` | Search DSL | dotted field names | request `body` |

---

## Features

- **Projections** — select typed fields, or aggregate.
- **Filters** — nested `and`/`or`/`not` trees with a closed operator set.
- **Aggregations** — `count`, `sum`, `avg`, `min`, `max` (`count` without a field = `COUNT(*)` / total hits).
- **Grouping** — group by fields, including calendar date grouping (`hour … year`).
- **Having** — filter over aggregation results.
- **Ordering** — by field or by aggregation.
- **Pagination** — offset (`skip`) or cursor (`search_after`) with output metadata.
- **Nested fields** — read JSON/object sub-fields via `path`.
- **Mutations** — `insert` (single/batch), `upsert`, `update` (with granular `increment`/`append`/`removeField`), `delete`.
- **Safety** — parameterized values, escaped identifiers and LIKE/wildcard metacharacters, and a guard against unfiltered mutations.

---

## Guides

| Guide | What it covers |
| ----- | -------------- |
| [Queries](docs/QUERIES.md) | Building read queries: projections, filters, operators, aggregations, grouping, having, ordering, pagination, nested fields. |
| [Mutations](docs/MUTATIONS.md) | `insert`, `upsert`, `update` (+ granular actions), `delete`, and safety rules. |
| [API reference](docs/API.md) | Every exported class, method, type and error code. |
| [Integration](docs/INTEGRATION.md) | How an executor (e.g. `@qrvey/enginex`) interprets and runs each result per engine. |
| [Architecture](docs/ARCHITECTURE.md) | Contributor guide: internals, and how to add an engine or validation rule. |

---

## Result shapes

`.transpile()` returns a discriminated union — check `kind`:

```ts
// SQL engines (postgresql, clickhouse)
{
  engine, kind: 'sql',
  query,        // parameterized statement — run this
  values,       // positional params
  namedValues,  // named params (ClickHouse)
  plainQuery,   // literals inlined — logging/debug only, never execute
  metadata,     // output field descriptors + pagination
}

// Search engines (elasticsearch, opensearch)
{
  engine, kind: 'search-dsl',
  index,        // target index
  body,         // request body (_source/query/aggs/sort/size/…)
  metadata,
}
```

See the [Integration guide](docs/INTEGRATION.md) for how to execute each shape,
and the [API reference](docs/API.md) for the full type definitions.

---

## Validation & errors

```ts
const { valid, errors } = new QueryBuilder(input, { engine: 'postgresql' }).validate();
```

- `.validate()` returns `{ valid, errors }` — errors as data, no throw.
- `.plan()` / `.transpile()` throw the first `QueryBuilderError` on invalid input.

Every error is a `QueryBuilderError` with a stable `code` and a `path` pointing at
the offending input location (e.g. `$.groupBy[0].limit`). See the
[error codes](docs/API.md#errors).

---

## Scope

**Supported:** one source; typed fields (`string | number | boolean | date`);
nested `and`/`or`/`not` filters; the operator set above; `count`/`sum`/`avg`/`min`/`max`
aggregations; group by (incl. date grouping) and per-group limits; having; order
by field/aggregation; offset/cursor pagination (default limit `25`, max limit/skip
`10_000`, max `50` selected fields); nested field paths; and portable
`insert`/`upsert`/`update`/`delete` mutations.

**Out of scope:** joins; multiple sources; raw SQL/DSL passthrough; input aliases;
calculated columns; `distinct`; full-text search; regex; median/percentiles; and
normalization of ES/OS aggregation responses.

---

## Architecture

The library separates a **stable public input contract** from engine-specific
compilation:

```
QueryInput ─▶ validate shape ─▶ normalize ─▶ QueryPlan (AST) ─▶ validate plan ─▶ compile ─▶ result
```

The `QueryPlan` is the internal logical model (intent, not syntax); compilers
consume a validated plan, never the raw input. SQL engines share `SqlCompiler`;
search engines share `SearchDslCompiler`. The rule of thumb is
**compiler = shared flow, dialect = engine syntax**, so adding an engine means
implementing a dialect — not changing the contract.

See the [API reference](docs/API.md) for the full public surface.

---

## Changelog

See [CHANGELOG.md](CHANGELOG.md).

---

## License

MIT