# Tinqer Documentation

This file contains the complete documentation for Tinqer, a type-safe query builder for TypeScript.
---

## README

# Tinqer

A type-safe query builder for TypeScript. Queries are expressed as inline arrow functions, parsed into an expression tree, and compiled into SQL for PostgreSQL or SQLite. The API is similar to DotNet's LINQ-based frameworks.

## Installation

Install the core library and adapter for your database:

```bash
# Core library
npm install @tinqerjs/tinqer

# PostgreSQL adapter (pg-promise)
npm install @tinqerjs/pg-promise-adapter

# SQLite adapter (better-sqlite3)
npm install @tinqerjs/better-sqlite3-adapter
```

## Quick Start

### PostgreSQL Example

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeSelect } from "@tinqerjs/pg-promise-adapter";
import pgPromise from "pg-promise";

interface Schema {
  users: {
    id: number;
    name: string;
    email: string;
    age: number;
  };
}

const pgp = pgPromise();
const db = pgp("postgresql://user:pass@localhost:5432/mydb");
const schema = createSchema<Schema>();

const results = await executeSelect(
  db,
  schema,
  (q, params: { minAge: number }) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .orderBy((u) => u.name)
      .select((u) => ({ id: u.id, name: u.name })),
  { minAge: 18 },
);
// results: [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }]
```

**The same query works with SQLite** - just change the adapter and database connection:

```typescript
import Database from "better-sqlite3";
import { createSchema } from "@tinqerjs/tinqer";
import { executeSelect } from "@tinqerjs/better-sqlite3-adapter";

// Same schema definition
interface Schema {
  users: {
    id: number;
    name: string;
    email: string;
    age: number;
  };
}

const db = new Database("./data.db");
const schema = createSchema<Schema>();

// Identical query logic
const results = executeSelect(
  db,
  schema,
  (q, params: { minAge: number }) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .orderBy((u) => u.name)
      .select((u) => ({ id: u.id, name: u.name })),
  { minAge: 18 },
);
// results: [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }]
```

### SQL Generation Without Execution

**`execute*` functions** execute queries and return results. **`toSql` function** generates SQL and parameters without executing - useful for debugging, logging, or custom execution:

```typescript
import {
  createSchema,
  defineSelect,
  defineInsert,
  defineUpdate,
  defineDelete,
} from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; age: number };
}

const schema = createSchema<Schema>();

// SELECT - returns { sql, params }
const select = toSql(
  defineSelect(schema, (q, params: { minAge: number }) =>
    q.from("users").where((u) => u.age >= params.minAge),
  ),
  { minAge: 18 },
);
// select.sql: SELECT * FROM "users" WHERE "age" >= $(minAge)
// select.params: { minAge: 18 }

// INSERT
const insert = toSql(
  defineInsert(schema, (q, params: { name: string; age: number }) =>
    q.insertInto("users").values({ name: params.name, age: params.age }),
  ),
  { name: "Alice", age: 30 },
);
// insert.sql: INSERT INTO "users" ("name", "age") VALUES ($(name), $(age))

// UPDATE
const update = toSql(
  defineUpdate(schema, (q, params: { newAge: number; userId: number }) =>
    q
      .update("users")
      .set({ age: params.newAge })
      .where((u) => u.id === params.userId),
  ),
  { newAge: 31, userId: 1 },
);
// update.sql: UPDATE "users" SET "age" = $(newAge) WHERE "id" = $(userId)

// DELETE
const del = toSql(
  defineDelete(schema, (q, params: { minAge: number }) =>
    q.deleteFrom("users").where((u) => u.age < params.minAge),
  ),
  { minAge: 18 },
);
// del.sql: DELETE FROM "users" WHERE "age" < $(minAge)
```

## Core Features

### Type-Safe Query Building

```typescript
const schema = createSchema<Schema>();

// Full TypeScript type inference
const query = (q) =>
  q
    .from("users")
    .where((u) => u.age >= 18 && u.email.includes("@company.com"))
    .orderBy((u) => u.name)
    .select((u) => ({ id: u.id, name: u.name, email: u.email }));

// The query builder returns a Queryable whose result type is inferred as
// { id: number; name: string; email: string }
```

### Query Composition

Query plans are **immutable and composable** - you can chain operations onto plan handles to create reusable base queries and branch into specialized variations.

#### Chaining Operations on Plans

```typescript
import { defineSelect } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

const schema = createSchema<Schema>();

// Start with base query
const plan = defineSelect(schema, (q) => q.from("users"))
  .where((u) => u.age > 18)
  .where((u) => u.isActive)
  .orderBy((u) => u.name)
  .select((u) => ({ id: u.id, name: u.name }));

const { sql, params } = toSql(plan, {});
```

#### Reusable Base Queries

Plans are immutable - each operation returns a new plan without modifying the original. This enables creating base queries and branching:

```typescript
type DeptParams = { dept: number };

// Reusable base query
const usersInDept = defineSelect(schema, (q, p: DeptParams) =>
  q.from("users").where((u) => u.departmentId === p.dept),
);

// Branch 1: Active users only
const activeUsers = usersInDept
  .where((u) => u.isActive === true)
  .where<{ minAge: number }>((u, p) => u.age >= p.minAge);

// Branch 2: Inactive users only
const inactiveUsers = usersInDept
  .where((u) => u.isActive === false)
  .where<{ maxAge: number }>((u, p) => u.age <= p.maxAge);

// Execute branches with different parameters
toSql(activeUsers, { dept: 1, minAge: 25 });
toSql(inactiveUsers, { dept: 1, maxAge: 65 });
```

#### Parameter Accumulation

Parameters from the builder function and chained operations are merged:

```typescript
type BuilderParams = { baseAge: number };
type ChainParams = { maxAge: number };

const plan = defineSelect(schema, (q, p: BuilderParams) =>
  q.from("users").where((u) => u.age > p.baseAge),
).where<ChainParams>((u, p) => u.age < p.maxAge);

// Must provide both parameter types
toSql(plan, { baseAge: 18, maxAge: 65 });
```

Composition works with all operations: `defineSelect`, `defineInsert`, `defineUpdate`, `defineDelete`.

### Joins

Tinqer mirrors LINQ semantics. Inner joins have a dedicated operator; left outer and cross joins follow the familiar `groupJoin`/`selectMany` patterns from C#.

#### Inner Join

```typescript
interface Schema {
  users: { id: number; name: string; deptId: number };
  departments: { id: number; name: string };
}

const schema = createSchema<Schema>();

const query = (q) =>
  q
    .from("users")
    .join(
      q.from("departments"),
      (user) => user.deptId,
      (department) => department.id,
      (user, department) => ({
        userName: user.name,
        departmentName: department.name,
      }),
    )
    .orderBy((row) => row.userName);
```

#### Left Outer Join

```typescript
const query = (q) =>
  q
    .from("users")
    .groupJoin(
      q.from("departments"),
      (user) => user.deptId,
      (department) => department.id,
      (user, deptGroup) => ({ user, deptGroup }),
    )
    .selectMany(
      (group) => group.deptGroup.defaultIfEmpty(),
      (group, department) => ({
        user: group.user,
        department,
      }),
    )
    .select((row) => ({
      userId: row.user.id,
      departmentName: row.department ? row.department.name : null,
    }));
```

#### Cross Join

```typescript
const query = (q) =>
  q
    .from("departments")
    .selectMany(
      () => q.from("users"),
      (department, user) => ({ department, user }),
    )
    .select((row) => ({
      departmentId: row.department.id,
      userId: row.user.id,
    }));
```

Right and full outer joins still require manual SQL, just as in LINQ-to-Objects.

### Grouping and Aggregation

```typescript
const query = (q) =>
  q
    .from("orders")
    .groupBy((o) => o.product_id)
    .select((g) => ({
      productId: g.key,
      totalQuantity: g.sum((o) => o.quantity),
      avgPrice: g.avg((o) => o.price),
      orderCount: g.count(),
    }))
    .orderByDescending((row) => row.totalQuantity);
```

### Window Functions

Window functions enable calculations across rows related to the current row. Tinqer supports `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` with optional partitioning and ordering.

```typescript
// Get top earner per department (automatically wrapped in subquery)
const topEarners = await executeSelect(
  db,
  schema,
  (q, params, h) =>
    q
      .from("employees")
      .select((e) => ({
        ...e,
        rank: h
          .window(e)
          .partitionBy((r) => r.department)
          .orderByDescending((r) => r.salary)
          .rowNumber(),
      }))
      .where((e) => e.rank === 1), // Filtering on window function result
  {},
);

// Generated SQL (automatically wrapped):
// SELECT * FROM (
//   SELECT *, ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
//   FROM "employees"
// ) AS "employees"
// WHERE "rank" = 1
```

**Automatic Subquery Wrapping**: Tinqer automatically detects when `where()` clauses reference window function columns and wraps the query in a subquery, since SQL doesn't allow filtering on window functions at the same level where they're defined.

See the [Window Functions Guide](docs/guide.md#8-window-functions) for detailed examples of `RANK()`, `DENSE_RANK()`, complex ordering, and [filtering on window results](docs/guide.md#85-filtering-on-window-function-results).

### CRUD Operations

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeInsert, executeUpdate, executeDelete } from "@tinqerjs/pg-promise-adapter";

const schema = createSchema<Schema>();

// INSERT
const insertedRows = await executeInsert(
  db,
  schema,
  (q) =>
    q.insertInto("users").values({
      name: "Alice",
      email: "alice@example.com",
    }),
  {},
);

// UPDATE with RETURNING
const inactiveUsers = await executeUpdate(
  db,
  schema,
  (q, params: { cutoffDate: Date }) =>
    q
      .update("users")
      .set({ status: "inactive" })
      .where((u) => u.lastLogin < params.cutoffDate)
      .returning((u) => u.id),
  { cutoffDate: new Date("2023-01-01") },
);

// Tip: undefined values in .set() or .values() are ignored; explicit null sets NULL.

// DELETE
const deletedCount = await executeDelete(
  db,
  schema,
  (q) => q.deleteFrom("users").where((u) => u.status === "deleted"),
  {},
);

// SQLite note: executeInsert/executeUpdate ignore RETURNING clauses at runtime; run a follow-up SELECT if you need the affected rows.
```

### Parameters and Auto-Parameterisation

All literal values are automatically parameterized to prevent SQL injection:

```typescript
// External parameters via params object
const schema = createSchema<Schema>();

const sample = toSql(
  defineSelect(schema, (q, params: { minAge: number }) =>
    q.from("users").where((u) => u.age >= params.minAge),
  ),
  { minAge: 18 },
);
// SQL (PostgreSQL): SELECT * FROM "users" WHERE "age" >= $(minAge)
// params: { minAge: 18 }

// Literals auto-parameterized automatically
const literals = toSql(
  defineSelect(schema, (q) => q.from("users").where((u) => u.age >= 18)),
  {},
);
// params: { __p1: 18 }
```

### Case-Insensitive String Operations

```typescript
import { createSchema } from "@tinqerjs/tinqer";

const schema = createSchema<Schema>();

const query = (q, params, helpers) =>
  q.from("users").where((u) => helpers.contains(u.name, params.searchTerm)); // Case-insensitive substring match

// PostgreSQL: WHERE u.name ILIKE $(searchTerm) (param: "%alice%")
// SQLite: WHERE LOWER(u.name) LIKE LOWER(?) (param: "%alice%")
```

## Key Concepts

### Query Lifecycle

1. **Build Query** - Construct fluent chain using `Queryable` API
2. **Parse Lambda** - Lambda expressions are parsed into expression tree (never executed)
3. **Auto-Parameterize** - Literal values extracted as parameters
4. **Generate SQL** - Adapter converts expression tree to database-specific SQL

### Expression Support

Tinqer supports a focused set of JavaScript/TypeScript expressions:

- **Comparison**: `===`, `!==`, `>`, `>=`, `<`, `<=`
- **Logical**: `&&`, `||`, `!`
- **Arithmetic**: `+`, `-`, `*`, `/`, `%`
- **String**: `.includes()`, `.startsWith()`, `.endsWith()`, `.toLowerCase()`, `.toUpperCase()`
- **Null handling**: `??` (null coalescing), `?.` (optional chaining)
- **Arrays**: `.includes()` for IN queries
- **Helper functions**: `ilike()`, `contains()`, `startsWith()`, `endsWith()` (case-insensitive)
- **Window functions**: `h.window(row).rowNumber()`, `h.window(row).rank()`, `h.window(row).denseRank()` with `partitionBy()`, `orderBy()`, `orderByDescending()`, `thenBy()`, `thenByDescending()`

## Database Support

### PostgreSQL

- Native boolean type (`true`/`false`)
- Case-insensitive matching with `ILIKE`
- Full JSONB support
- Window functions: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`
- Parameter placeholders: `$1`, `$2`, etc.

### SQLite

- Boolean values use INTEGER (0/1)
- Case-insensitive via `LOWER()` function
- JSON functions support
- Window functions: `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()` (requires SQLite 3.25+)
- Parameter placeholders: `?`, `?`, etc.

See [Database Adapters](docs/adapters.md) for detailed comparison.

## Differences from .NET LINQ to SQL

- Lambdas cannot capture external variables; use params object
- Join operations (`join`, `groupJoin`, `selectMany`) must be composed inside the defineSelect builder; they cannot be chained on plan handles
- Left outer joins and cross joins supported via LINQ patterns (right/full joins still require manual SQL)
- No deferred execution; SQL generated on demand
- Grouping supports `count`, `sum`, `avg`, `min`, `max`

## Documentation

- **[Query Operations Guide](docs/guide.md)** - Complete reference for all query operations, parameters, and CRUD
- **[API Reference](docs/api-reference.md)** - Execution functions, type utilities, and helper APIs
- **[Database Adapters](docs/adapters.md)** - PostgreSQL and SQLite specifics, differences, limitations
- **[Development Guide](docs/development.md)** - Contributing, testing, troubleshooting

## Packages

| Package                            | Purpose                                                  |
| ---------------------------------- | -------------------------------------------------------- |
| `@tinqerjs/tinqer`                 | Core expression tree and types (re-exported by adapters) |
| `@tinqerjs/pg-promise-adapter`     | PostgreSQL adapter with pg-promise                       |
| `@tinqerjs/better-sqlite3-adapter` | SQLite adapter with better-sqlite3                       |

## Credits

Tinqer uses [OXC](https://oxc.rs/) - a fast JavaScript/TypeScript parser written in Rust - to parse lambda expressions at runtime. OXC's speed and reliability make Tinqer's runtime lambda parsing practical and performant.

## For AI/LLMs

A consolidated documentation file for AI assistants is available at [`llms.txt`](llms.txt). This file includes all documentation in a single text format optimized for Large Language Model consumption.

## License

MIT

---

## Adapters


# Database Adapters

Tinqer ships dedicated adapters for PostgreSQL (`@tinqerjs/pg-promise-adapter`) and SQLite (`@tinqerjs/better-sqlite3-adapter`). Both share the same builder APIs while handling dialect-specific SQL generation and parameter formatting.

## Table of Contents

- [1. PostgreSQL Adapter](#1-postgresql-adapter)
  - [1.1 Installation](#11-installation)
  - [1.2 Setup & Query Execution](#12-setup--query-execution)
  - [1.3 PostgreSQL Dialect Notes](#13-postgresql-dialect-notes)
- [2. SQLite Adapter](#2-sqlite-adapter)
  - [2.1 Installation](#21-installation)
  - [2.2 Setup & Query Execution](#22-setup--query-execution)
  - [2.3 SQLite Dialect Notes](#23-sqlite-dialect-notes)
- [3. Key Differences](#3-key-differences)
  - [3.1 Parameter Placeholders](#31-parameter-placeholders)
  - [3.2 Data Types](#32-data-types)
  - [3.3 Case-Insensitive Matching](#33-case-insensitive-matching)
  - [3.4 RETURNING Behaviour](#34-returning-behaviour)

---

## 1. PostgreSQL Adapter

### 1.1 Installation

```bash
npm install @tinqerjs/pg-promise-adapter pg-promise
```

### 1.2 Setup & Query Execution

```typescript
import pgPromise from "pg-promise";
import { createSchema, defineSelect } from "@tinqerjs/tinqer";
import {
  executeSelect,
  executeInsert,
  executeUpdate,
  executeDelete,
  toSql,
} from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; email: string; age: number; active: boolean };
}

const pgp = pgPromise();
const db = pgp("postgresql://user:pass@localhost:5432/mydb");
const schema = createSchema<Schema>();

// Execute with params
const activeUsers = await executeSelect(
  db,
  schema,
  (q, params: { minAge: number }) =>
    q
      .from("users")
      .where((u) => u.active && u.age >= params.minAge)
      .orderBy((u) => u.name),
  { minAge: 18 },
);

// Execute with params
const matchingUsers = await executeSelect(
  db,
  schema,
  (q, params: { minAge: number }) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .select((u) => ({ id: u.id, name: u.name })),
  { minAge: 21 },
);

// INSERT with RETURNING
const createdUsers = await executeInsert(
  db,
  schema,
  (q) =>
    q
      .insertInto("users")
      .values({ name: "Alice", email: "alice@example.com", age: 30, active: true })
      .returning((u) => ({ id: u.id, createdAt: u.createdAt })),
  {},
);

// UPDATE
const updatedCount = await executeUpdate(
  db,
  schema,
  (q) =>
    q
      .update("users")
      .set({ active: false })
      .where((u) => u.age > 65),
  {},
);

// DELETE
const deletedCount = await executeDelete(
  db,
  schema,
  (q) => q.deleteFrom("users").where((u) => !u.active),
  {},
);

// Generate SQL without executing
const { sql, params } = toSql(
  defineSelect(schema, (q) => q.from("users").where((u) => u.email.endsWith("@example.com"))),
  {},
);
```

### 1.3 PostgreSQL Dialect Notes

- Booleans map to `BOOLEAN` values (`true`/`false`).
- Case-insensitive helper functions generate `LOWER()` comparisons for portable SQL.
- RETURNING clauses are fully supported on INSERT, UPDATE, and DELETE through the execution helpers.
- Parameter placeholders use the `$()` syntax expected by pg-promise (e.g., `$(minAge)`).
- Window functions (`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`) are fully supported.

---

## 2. SQLite Adapter

### 2.1 Installation

```bash
npm install @tinqerjs/better-sqlite3-adapter better-sqlite3
```

### 2.2 Setup & Query Execution

```typescript
import Database from "better-sqlite3";
import { createSchema, defineSelect } from "@tinqerjs/tinqer";
import {
  executeSelect,
  executeInsert,
  executeUpdate,
  executeDelete,
  toSql,
} from "@tinqerjs/better-sqlite3-adapter";

interface Schema {
  users: { id: number; name: string; email: string; age: number; isActive: number };
}

const db = new Database(":memory:");
const schema = createSchema<Schema>();

db.exec(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    age INTEGER,
    isActive INTEGER NOT NULL DEFAULT 1
  );
`);

const inserted = executeInsert(
  db,
  schema,
  (q) => q.insertInto("users").values({ name: "Sam", email: "sam@example.com", age: 28 }),
  {},
);
// inserted === 1

const users = executeSelect(
  db,
  schema,
  (q, params: { active: number }) =>
    q
      .from("users")
      .where((u) => u.isActive === params.active)
      .orderBy((u) => u.name),
  { active: 1 },
);

const updated = executeUpdate(
  db,
  schema,
  (q) =>
    q
      .update("users")
      .set({ isActive: 0 })
      .where((u) => u.age > 60),
  {},
);

const removed = executeDelete(
  db,
  schema,
  (q, params: { cutoff: number }) => q.deleteFrom("users").where((u) => u.age < params.cutoff),
  { cutoff: 18 },
);

// Need the SQL text for custom execution?
const { sql, params } = toSql(
  defineSelect(schema, (q) => q.from("users").where((u) => u.name.startsWith("S"))),
  {},
);
const rows = db.prepare(sql).all(params);
```

### 2.3 SQLite Dialect Notes

- SQLite has no native boolean type; represent booleans as `INTEGER` 0/1 in your schema.
- All parameters are passed as named values (e.g., `@__p1`, `@minAge`). The adapter converts booleans and dates to SQLite-friendly values automatically.
- The execution helpers return row counts. SQLite currently ignores RETURNING clauses when running through the helpers; use a follow-up SELECT if you need inserted rows.
- Window functions (`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`) require **SQLite 3.25 or later**.

---

## 3. Key Differences

### 3.1 Parameter Placeholders

| Database   | Placeholder Format    | Example                    |
| ---------- | --------------------- | -------------------------- |
| PostgreSQL | `$(name)` / `$(__p1)` | `WHERE "age" >= $(minAge)` |
| SQLite     | `@name` / `@__p1`     | `WHERE "age" >= @minAge`   |

### 3.2 Data Types

| Type      | PostgreSQL                 | SQLite                           | Schema Recommendation                 |
| --------- | -------------------------- | -------------------------------- | ------------------------------------- |
| Boolean   | `BOOLEAN` (`true`/`false`) | `INTEGER` (0/1)                  | `boolean` for PG, `number` for SQLite |
| Integer   | `INTEGER`, `BIGINT`        | `INTEGER`                        | `number` or `bigint`                  |
| Decimal   | `NUMERIC`, `DECIMAL`       | `REAL`                           | `number`                              |
| String    | `TEXT`, `VARCHAR`          | `TEXT`                           | `string`                              |
| Date/Time | `TIMESTAMP`, `DATE`        | `TEXT` / `INTEGER` (ISO strings) | `Date` or `string`                    |
| JSON      | `JSONB`, `JSON`            | `TEXT` (with JSON functions)     | `unknown` / structured type           |

### 3.3 Case-Insensitive Matching

Use query helpers for portable case-insensitive comparisons:

```typescript
import { createSchema, defineSelect } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; email: string };
}

const schema = createSchema<Schema>();

const { sql } = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("users").where((u) => helpers.functions.icontains(u.name, "alice")),
  ),
  {},
);
// PostgreSQL: WHERE LOWER("name") LIKE '%' || LOWER($(__p1)) || '%'
// SQLite: WHERE LOWER("name") LIKE '%' || LOWER(@__p1) || '%'
```

### 3.4 RETURNING Behaviour

- **PostgreSQL**: `executeInsert`, `executeUpdate`, and `executeDelete` return the projected values when a `.returning()` clause is present.
- **SQLite**: `executeInsert`, `executeUpdate`, and `executeDelete` return the row count. To inspect affected rows, issue a follow-up SELECT.

---

## Api Reference


# Tinqer API Reference

Reference for adapter execution helpers, typed contexts, and query utilities.

## Table of Contents

- [1. Execution APIs](#1-execution-apis)
  - [1.1 defineSelect, toSql & executeSelect](#11-defineselect-tosql--executeselect)
  - [1.2 defineInsert, toSql & executeInsert](#12-defineinsert-tosql--executeinsert)
  - [1.3 defineUpdate, toSql & executeUpdate](#13-defineupdate-tosql--executeupdate)
  - [1.4 defineDelete, toSql & executeDelete](#14-definedelete-tosql--executedelete)
  - [1.5 ExecuteOptions & SqlResult](#15-executeoptions--sqlresult)
- [2. Type-Safe Contexts](#2-type-safe-contexts)
  - [2.1 createSchema](#21-createschema)
- [3. Helper Utilities](#3-helper-utilities)
  - [3.1 createQueryHelpers](#31-createqueryhelpers)

---

## 1. Execution APIs

Tinqer uses a two-step API:

1. **Plan definition** (`define*` functions from `@tinqerjs/tinqer`) - Creates type-safe query plans
2. **Execution or SQL generation** (`execute*` or `toSql` from adapter packages) - Executes plans or generates SQL

Adapter packages live in `@tinqerjs/pg-promise-adapter` (PostgreSQL) and `@tinqerjs/better-sqlite3-adapter` (SQLite).

### 1.1 defineSelect, toSql & executeSelect

Creates SELECT query plans, generates SQL, or executes queries.

**Signatures**

```typescript
// Plan definition (from @tinqerjs/tinqer)
function defineSelect<TSchema, TParams, TResult>(
  schema: DatabaseSchema<TSchema>,
  queryBuilder: (
    q: QueryBuilder<TSchema>,
    params: TParams,
    helpers: QueryHelpers,
  ) => Queryable<TResult> | OrderedQueryable<TResult> | TerminalQuery<TResult>,
  paramDefaults?: TParams,
): SelectPlanHandle<TResult, TParams> | SelectTerminalHandle<TResult, TParams>;

// SQL generation (from adapter packages)
function toSql<TParams>(
  plan: SelectPlanHandle<unknown, TParams> | SelectTerminalHandle<unknown, TParams>,
  params: TParams,
): { sql: string; params: TParams & Record<string, unknown> };

// Execution (from adapter packages)
async function executeSelect<TResult, TParams>(
  db: PgDatabase | BetterSqlite3Database,
  plan: SelectPlanHandle<TResult, TParams> | SelectTerminalHandle<TResult, TParams>,
  params: TParams,
  options?: ExecuteOptions,
): Promise<TResult[] | TResult>;
```

**Example - SQL Generation**

```typescript
import { createSchema, defineSelect } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; age: number };
}

const schema = createSchema<Schema>();

const { sql, params } = toSql(
  defineSelect(schema, (q, params: { minAge: number }) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .select((u) => ({ id: u.id, name: u.name })),
  ),
  { minAge: 18 },
);
// sql: SELECT "id" AS "id", "name" AS "name" FROM "users" WHERE "age" >= $(minAge)
// params: { minAge: 18 }
```

**Example - Execution**

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeSelect } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; age: number };
}

const schema = createSchema<Schema>();

const users = await executeSelect(
  db,
  schema,
  (q, params: { minAge: number }) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .orderBy((u) => u.name),
  { minAge: 21 },
);
// Returns: Array of user objects
```

### 1.2 defineInsert, toSql & executeInsert

Creates INSERT query plans, generates SQL, or executes queries with optional RETURNING clauses.

**Signatures**

```typescript
// Plan definition (from @tinqerjs/tinqer)
function defineInsert<TSchema, TParams, TTable, TReturning = never>(
  schema: DatabaseSchema<TSchema>,
  queryBuilder: (
    q: QueryBuilder<TSchema>,
    params: TParams,
    helpers: QueryHelpers,
  ) => Insertable<TTable> | InsertableWithReturning<TTable, TReturning>,
  paramDefaults?: TParams,
): InsertPlanHandle<TTable, TReturning, TParams>;

// SQL generation (from adapter packages)
function toSql<TParams>(
  plan: InsertPlanHandle<unknown, unknown, TParams>,
  params: TParams,
): { sql: string; params: TParams & Record<string, unknown> };

// Execution (from adapter packages)
async function executeInsert<TTable, TReturning, TParams>(
  db: PgDatabase | BetterSqlite3Database,
  plan: InsertPlanHandle<TTable, TReturning, TParams>,
  params: TParams,
  options?: ExecuteOptions,
): Promise<TReturning extends never ? number : TReturning[]>;
```

**Example - SQL Generation**

```typescript
import { createSchema, defineInsert } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string };
}

const schema = createSchema<Schema>();

const { sql, params } = toSql(
  defineInsert(schema, (q, params: { name: string }) =>
    q.insertInto("users").values({ name: params.name }),
  ),
  { name: "Alice" },
);
// sql: INSERT INTO "users" ("name") VALUES ($(name))
// params: { name: "Alice" }
```

**Example - Execution**

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeInsert } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string };
}

const schema = createSchema<Schema>();

// Without RETURNING - returns number of rows inserted
const rowCount = await executeInsert(
  db,
  schema,
  (q, params: { name: string }) => q.insertInto("users").values({ name: params.name }),
  { name: "Alice" },
);

// With RETURNING - returns inserted rows
const createdUsers = await executeInsert(
  db,
  schema,
  (q, params: { name: string }) =>
    q
      .insertInto("users")
      .values({ name: params.name })
      .returning((u) => ({ id: u.id, name: u.name })),
  { name: "Bob" },
);
```

### 1.3 defineUpdate, toSql & executeUpdate

Creates UPDATE query plans, generates SQL, or executes queries with optional RETURNING clauses.

**Signatures**

```typescript
// Plan definition (from @tinqerjs/tinqer)
function defineUpdate<TSchema, TParams, TTable, TReturning = never>(
  schema: DatabaseSchema<TSchema>,
  queryBuilder: (
    q: QueryBuilder<TSchema>,
    params: TParams,
    helpers: QueryHelpers,
  ) =>
    | UpdatableWithSet<TTable>
    | UpdatableComplete<TTable>
    | UpdatableWithReturning<TTable, TReturning>,
  paramDefaults?: TParams,
): UpdatePlanHandle<TTable, TReturning, TParams>;

// SQL generation (from adapter packages)
function toSql<TParams>(
  plan: UpdatePlanHandle<unknown, unknown, TParams>,
  params: TParams,
): { sql: string; params: TParams & Record<string, unknown> };

// Execution (from adapter packages)
async function executeUpdate<TTable, TReturning, TParams>(
  db: PgDatabase | BetterSqlite3Database,
  plan: UpdatePlanHandle<TTable, TReturning, TParams>,
  params: TParams,
  options?: ExecuteOptions,
): Promise<TReturning extends never ? number : TReturning[]>;
```

**Example - SQL Generation**

```typescript
import { createSchema, defineUpdate } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; status: string; lastLogin: Date };
}

const schema = createSchema<Schema>();

const { sql, params } = toSql(
  defineUpdate(schema, (q, params: { cutoff: Date }) =>
    q
      .update("users")
      .set({ status: "inactive" })
      .where((u) => u.lastLogin < params.cutoff),
  ),
  { cutoff: new Date("2024-01-01") },
);
// sql: UPDATE "users" SET "status" = 'inactive' WHERE "lastLogin" < $(cutoff)
// params: { cutoff: Date }
```

**Example - Execution**

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeUpdate } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; lastLogin: Date; status: string };
}

const schema = createSchema<Schema>();

// Without RETURNING - returns number of rows updated
const updatedRows = await executeUpdate(
  db,
  schema,
  (q, params: { cutoff: Date }) =>
    q
      .update("users")
      .set({ status: "inactive" })
      .where((u) => u.lastLogin < params.cutoff),
  { cutoff: new Date("2024-01-01") },
);

// With RETURNING - returns updated rows
const updatedUsers = await executeUpdate(
  db,
  schema,
  (q, params: { cutoff: Date }) =>
    q
      .update("users")
      .set({ status: "inactive" })
      .where((u) => u.lastLogin < params.cutoff)
      .returning((u) => ({ id: u.id, status: u.status })),
  { cutoff: new Date("2024-01-01") },
);
```

### 1.4 defineDelete, toSql & executeDelete

Creates DELETE query plans, generates SQL, or executes queries.

**Signatures**

```typescript
// Plan definition (from @tinqerjs/tinqer)
function defineDelete<TSchema, TParams>(
  schema: DatabaseSchema<TSchema>,
  queryBuilder: (
    q: QueryBuilder<TSchema>,
    params: TParams,
    helpers: QueryHelpers,
  ) => Deletable<unknown> | DeletableComplete<unknown>,
  paramDefaults?: TParams,
): DeletePlanHandle<TParams>;

// SQL generation (from adapter packages)
function toSql<TParams>(
  plan: DeletePlanHandle<TParams>,
  params: TParams,
): { sql: string; params: TParams & Record<string, unknown> };

// Execution (from adapter packages)
async function executeDelete<TParams>(
  db: PgDatabase | BetterSqlite3Database,
  plan: DeletePlanHandle<TParams>,
  params: TParams,
  options?: ExecuteOptions,
): Promise<number>;
```

**Example - SQL Generation**

```typescript
import { createSchema, defineDelete } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; status: string };
}

const schema = createSchema<Schema>();

const { sql, params } = toSql(
  defineDelete(schema, (q, params: { status: string }) =>
    q.deleteFrom("users").where((u) => u.status === params.status),
  ),
  { status: "inactive" },
);
// sql: DELETE FROM "users" WHERE "status" = $(status)
// params: { status: "inactive" }
```

**Example - Execution**

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeDelete } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; status: string };
}

const schema = createSchema<Schema>();

const deletedCount = await executeDelete(
  db,
  schema,
  (q, params: { status: string }) => q.deleteFrom("users").where((u) => u.status === params.status),
  { status: "inactive" },
);
```

### 1.5 ExecuteOptions & SqlResult

Both adapters expose `ExecuteOptions` and `SqlResult` for inspection and typing.

```typescript
interface ExecuteOptions {
  onSql?: (result: SqlResult<Record<string, unknown>, unknown>) => void;
}

interface SqlResult<TParams, TResult> {
  sql: string;
  params: TParams;
  _resultType?: TResult; // phantom type information
}
```

Use `onSql` for logging, testing, or debugging without changing execution flow.

---

## 2. Type-Safe Contexts

### 2.1 createSchema

Creates a phantom-typed `DatabaseSchema` that ties table names to row types. The schema is passed to execution functions, which provide a type-safe query builder through the lambda's first parameter.

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeSelect } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; email: string };
  posts: { id: number; userId: number; title: string };
}

const schema = createSchema<Schema>();

// Schema is passed to executeSelect, which provides the query builder 'q' parameter
const results = await executeSelect(
  db,
  schema,
  (q) => q.from("users").where((u) => u.email.endsWith("@example.com")),
  {},
);
```

---

## 3. Helper Utilities

### 3.1 createQueryHelpers

Provides helper functions for case-insensitive comparisons and string searches. Helpers are automatically passed as the third parameter to query builder functions.

```typescript
import { createSchema, defineSelect } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string };
}

const schema = createSchema<Schema>();

const result = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("users").where((u) => helpers.functions.icontains(u.name, "alice")),
  ),
  {},
);
```

**Available Helper Functions**

Helpers expose the following functions that adapt per database dialect:

- `ilike(field, pattern)` - Case-insensitive LIKE comparison
- `contains(field, substring)` - Check if field contains substring (case-sensitive)
- `icontains(field, substring)` - Check if field contains substring (case-insensitive)
- `startsWith(field, prefix)` - Check if field starts with prefix (case-sensitive)
- `istartsWith(field, prefix)` - Check if field starts with prefix (case-insensitive)
- `endsWith(field, suffix)` - Check if field ends with suffix (case-sensitive)
- `iendsWith(field, suffix)` - Check if field ends with suffix (case-insensitive)

**Creating Custom Helpers**

You can create helpers with custom functions:

```typescript
const helpers = createQueryHelpers<Schema>();
// Use helpers in your queries through the third parameter
```

---

## Development


# Development Guide

Guide for contributing to Tinqer, running tests, and troubleshooting.

## Table of Contents

- [1. Getting Started](#1-getting-started)
  - [1.1 Prerequisites](#11-prerequisites)
  - [1.2 Installation](#12-installation)
  - [1.3 Project Structure](#13-project-structure)
- [2. Building](#2-building)
  - [2.1 Build Commands](#21-build-commands)
  - [2.2 Clean Build](#22-clean-build)
- [3. Testing](#3-testing)
  - [3.1 Running Tests](#31-running-tests)
  - [3.2 Test Organization](#32-test-organization)
  - [3.3 Writing Tests](#33-writing-tests)
- [4. Code Quality](#4-code-quality)
  - [4.1 Linting](#41-linting)
  - [4.2 Formatting](#42-formatting)
- [5. Contributing](#5-contributing)
  - [5.1 Coding Standards](#51-coding-standards)
  - [5.2 Commit Guidelines](#52-commit-guidelines)
  - [5.3 Pull Requests](#53-pull-requests)
- [6. Troubleshooting](#6-troubleshooting)
  - [6.1 Common Issues](#61-common-issues)
  - [6.2 Parser Errors](#62-parser-errors)
  - [6.3 Type Errors](#63-type-errors)

---

## 1. Getting Started

### 1.1 Prerequisites

- Node.js 18+ (for ESM support)
- npm 8+
- TypeScript 5.3+
- PostgreSQL 12+ (for PostgreSQL adapter development)
- SQLite 3.35+ (for SQLite adapter development)

### 1.2 Installation

```bash
# Clone the repository
git clone https://github.com/tinqerjs/tinqer.git
cd tinqer

# Install dependencies
npm install

# Build all packages
./scripts/build.sh
```

### 1.3 Project Structure

```
tinqer/
├── packages/
│   ├── tinqer/                          # Core library
│   │   ├── src/
│   │   │   ├── parser/                  # Lambda expression parser (OXC)
│   │   │   ├── converter/               # AST to expression tree converter
│   │   │   ├── queryable/               # Queryable API
│   │   │   ├── visitors/                # SQL generation visitors
│   │   │   └── types/                   # TypeScript type definitions
│   │   └── tests/                       # Core library tests
│   │
│   ├── pg-promise-adapter/           # PostgreSQL adapter
│   │   ├── src/
│   │   │   ├── adapter.ts               # PostgreSQL SQL adapter
│   │   │   ├── execute.ts               # Execution functions
│   │   │   └── visitors/                # PostgreSQL-specific visitors
│   │   └── tests/                       # Integration tests
│   │
│   ├── better-sqlite3-adapter/       # SQLite adapter
│   │   ├── src/
│   │   │   ├── adapter.ts               # SQLite SQL adapter
│   │   │   ├── execute.ts               # Execution functions
│   │   │   └── visitors/                # SQLite-specific visitors
│   │   └── tests/                       # Integration tests
│   │
│   └── tinqer-sql-*/                    # Integration test packages
│
├── scripts/                             # Build and utility scripts
│   ├── build.sh                         # Main build script
│   ├── clean.sh                         # Clean build artifacts
│   ├── lint-all.sh                      # Lint all packages
│   └── format-all.sh                    # Format with Prettier
│
└── docs/                                # Documentation
```

---

## 2. Building

### 2.1 Build Commands

```bash
# Standard build with formatting
./scripts/build.sh

# Build without formatting (faster during development)
./scripts/build.sh --no-format

# Build specific package
cd packages/tinqer
npm run build
```

**Build Process:**

1. Runs TypeScript compiler for each package
2. Generates ES modules with `.js` extensions
3. Runs Prettier formatting (unless `--no-format` is used)
4. Outputs to `dist/` directories

### 2.2 Clean Build

```bash
# Remove build artifacts
./scripts/clean.sh

# Remove build artifacts and node_modules
./scripts/clean.sh --all
```

---

## 3. Testing

### 3.1 Running Tests

```bash
# Run all tests
npm test

# Run tests in watch mode
npm run test:watch

# Run specific tests by pattern
npm run test:grep -- "WHERE operations"
npm run test:grep -- "INSERT"
npm run test:grep -- "JOIN"

# Run tests for specific package
cd packages/tinqer
npm test
```

### 3.2 Test Organization

**Core Library Tests** (`packages/tinqer/tests/`):

- Parser tests: Lambda expression parsing
- Converter tests: AST to expression tree conversion
- Queryable tests: Query builder API
- Type tests: TypeScript type inference

**Integration Tests** (`packages/tinqer-sql-*/tests/`):

- PostgreSQL integration: `pg-promise-adapter-integration/tests/`
- SQLite integration: `better-sqlite3-adapter-integration/tests/`
- Full end-to-end query execution tests
- Database-specific feature tests

### 3.3 Writing Tests

**Unit Test Example:**

```typescript
import { describe, it } from "mocha";
import { strict as assert } from "assert";
import { createSchema, defineSelect } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

describe("SQL Generation", () => {
  it("should generate SQL with WHERE clause", () => {
    interface Schema {
      users: { id: number; name: string; age: number };
    }

    const schema = createSchema<Schema>();
    const result = toSql(
      defineSelect(schema, (q) => q.from("users").where((u) => u.age >= 18)),
      {},
    );

    // Assert SQL and parameters
    assert.ok(result.sql.includes("WHERE"));
    assert.ok(result.params);
  });
});
```

**Integration Test Example:**

```typescript
import { describe, it, beforeEach } from "mocha";
import { strict as assert } from "assert";
import { createSchema } from "@tinqerjs/tinqer";
import { executeSelect } from "@tinqerjs/pg-promise-adapter";
import { db } from "./shared-db.js";

const schema = createSchema<Schema>();

describe("PostgreSQL Integration", () => {
  beforeEach(async () => {
    await db.none("TRUNCATE TABLE users RESTART IDENTITY CASCADE");
    await db.none("INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25)");
  });

  it("should execute SELECT query", async () => {
    const results = await executeSelect(
      db,
      schema,
      (q, params: { minAge: number }) =>
        q
          .from("users")
          .where((u) => u.age >= params.minAge)
          .select((u) => u.name),
      { minAge: 25 },
    );

    assert.deepEqual(results, ["Alice", "Bob"]);
  });
});
```

**Test Database Setup:**

PostgreSQL tests use shared connection (`packages/pg-promise-adapter-integration/tests/shared-db.ts`):

```typescript
import pgPromise from "pg-promise";

const pgp = pgPromise();
export const db = pgp({
  host: "localhost",
  port: 5432,
  database: "tinqer_test",
  user: "tinqer_test",
  password: "tinqer_test",
});
```

SQLite tests use isolated in-memory databases:

```typescript
import Database from "better-sqlite3";

describe("SQLite Tests", () => {
  let db: Database.Database;

  beforeEach(() => {
    db = new Database(":memory:");
    // Create schema and seed data
  });

  afterEach(() => {
    db.close();
  });
});
```

---

## 4. Code Quality

### 4.1 Linting

```bash
# Lint all packages
./scripts/lint-all.sh

# Lint with auto-fix
./scripts/lint-all.sh --fix

# Lint specific package
cd packages/tinqer
npm run lint
npm run lint:fix
```

**ESLint Configuration:**

- `@typescript-eslint/no-explicit-any`: error (no `any` types allowed)
- `@typescript-eslint/prefer-const`: error
- Strict type checking enabled

### 4.2 Formatting

```bash
# Format all files with Prettier
./scripts/format-all.sh

# Check formatting without changes
./scripts/format-all.sh --check

# Format specific package
cd packages/tinqer
npm run format
```

**IMPORTANT:** Always run `./scripts/format-all.sh` before committing.

---

## 5. Contributing

### 5.1 Coding Standards

**TypeScript Guidelines:**

- **No `any` types**: All code must be strictly typed
- **Prefer `type` over `interface`**: Use `interface` only for extensible contracts
- **ESM imports**: Always include `.js` extension in imports

  ```typescript
  // Correct
  import { Queryable } from "./queryable/queryable.js";

  // Incorrect
  import { Queryable } from "./queryable/queryable";
  ```

- **Pure functions**: Prefer stateless functions with explicit dependency injection
- **No dynamic imports**: Always use static imports

**Code Organization:**

- Export functions from modules when possible
- Use classes only for stateful connections or complex state management
- Keep files focused and single-purpose
- Write comprehensive JSDoc comments for public APIs

### 5.2 Commit Guidelines

```bash
# Before committing:
./scripts/format-all.sh  # Format code
./scripts/lint-all.sh    # Check linting
./scripts/build.sh       # Build all packages
npm test                 # Run all tests

# Commit with descriptive message
git add .
git commit -m "feat: add support for window functions"
```

**Commit Message Format:**

- `feat:` - New features
- `fix:` - Bug fixes
- `refactor:` - Code refactoring
- `test:` - Test additions or changes
- `docs:` - Documentation changes
- `chore:` - Build process or tooling changes

### 5.3 Pull Requests

1. **Create feature branch:**

   ```bash
   git checkout -b feat/my-feature
   ```

2. **Make changes and test:**

   ```bash
   ./scripts/format-all.sh
   ./scripts/lint-all.sh
   ./scripts/build.sh
   npm test
   ```

3. **Push and create PR:**

   ```bash
   git push -u origin feat/my-feature
   # Create PR on GitHub
   ```

4. **PR Requirements:**
   - All tests passing
   - Code formatted and linted
   - Documentation updated
   - Clear description of changes
   - Type safety maintained

---

## 6. Troubleshooting

### 6.1 Common Issues

**Issue: Build Fails with Module Resolution Errors**

```
Error: Cannot find module './queryable.js'
```

**Solution:** Ensure all imports include `.js` extension:

```typescript
// Incorrect
import { Queryable } from "./queryable";

// Correct
import { Queryable } from "./queryable.js";
```

**Issue: Tests Fail with Connection Pool Destroyed**

```
Error: Connection pool has been destroyed
```

**Solution:** Use shared database connection, don't call `pgp.end()` in tests:

```typescript
// Correct
import { db } from "./shared-db.js";

// Incorrect - don't create new pgp instances in tests
const pgp = pgPromise();
const db = pgp({...});
pgp.end(); // This destroys the global pool!
```

**Issue: SQLite Boolean Type Errors**

```
TypeError: SQLite3 can only bind numbers, strings, bigints, buffers, and null
```

**Solution:** Use `number` type (0/1) for boolean columns in SQLite schemas:

```typescript
// Correct for SQLite
interface Schema {
  users: {
    is_active: number; // Use 0 for false, 1 for true
  };
}

// Incorrect for SQLite
interface Schema {
  users: {
    is_active: boolean; // SQLite doesn't have boolean type
  };
}
```

### 6.2 Parser Errors

**Issue: Unsupported AST Node Type**

```
Error: Unsupported AST node type: TemplateLiteral
```

**Solution:** Use params pattern for dynamic values:

```typescript
// Incorrect - template literal in lambda
.where(u => u.name === `User ${userId}`)

// Correct - use params with executeSelect
await executeSelect(
  db,
  schema,
  (q, params: { name: string }) =>
    q.from("users").where((u) => u.name === params.name),
  { name: `User ${userId}` },
);
```

**Issue: Unknown Identifier**

```
Error: Unknown identifier 'externalVar'
```

**Solution:** Pass external variables via params object:

```typescript
// Incorrect - closure variable
const minAge = 18;
.where(u => u.age >= minAge)

// Correct - params pattern with executeSelect
await executeSelect(
  db,
  schema,
  (q, params: { minAge: number }) =>
    q.from("users").where((u) => u.age >= params.minAge),
  { minAge: 18 },
);
```

### 6.3 Type Errors

**Issue: Type Inference Not Working**

```typescript
// Type inference fails without schema context
const schema = createSchema(); // No schema type provided

// Types will be 'unknown' without schema
const result = await executeSelect(
  db,
  schema,
  (q) => q.from("users"), // Type is Queryable<unknown>
  {},
);
```

**Solution:** Provide explicit schema type to createSchema:

```typescript
interface Schema {
  users: { id: number; name: string };
}

const schema = createSchema<Schema>();

// Now fully typed from schema
const result = await executeSelect(
  db,
  schema,
  (q) => q.from("users"), // Fully typed: Queryable<{ id: number; name: string }>
  {},
);
```

**Issue: Property Does Not Exist**

```
Property 'email' does not exist on type '{ id: number; name: string }'
```

**Solution:** Ensure schema definition includes all columns:

```typescript
interface Schema {
  users: {
    id: number;
    name: string;
    email: string; // Add missing column
  };
}
```

---

## Development Workflow

**Typical Development Cycle:**

1. **Make changes** to source files
2. **Run linter**: `./scripts/lint-all.sh --fix`
3. **Build**: `./scripts/build.sh --no-format` (skip formatting for speed)
4. **Run specific tests**: `npm run test:grep -- "your feature"`
5. **Iterate** until tests pass
6. **Run full test suite**: `npm test`
7. **Format code**: `./scripts/format-all.sh`
8. **Final build**: `./scripts/build.sh`
9. **Commit changes**

**Debugging Tips:**

- Use `npm run test:grep -- "pattern"` to focus on specific tests
- Check `.tests/` directory for saved test output (gitignored)
- Use TypeScript's `tsc --noEmit` to check types without building
- Enable verbose logging in tests with `DEBUG=* npm test`

---


---

## Guide


# Tinqer Query Operations Guide

Complete reference for all query operations, parameters, and CRUD functionality in Tinqer.

## Table of Contents

- [1. Filtering Operations](#1-filtering-operations)
  - [1.1 Basic Comparison](#11-basic-comparison)
  - [1.2 Multiple Predicates](#12-multiple-predicates)
  - [1.3 Logical Nesting and Arithmetic](#13-logical-nesting-and-arithmetic)
  - [1.4 Null Checks and Null Coalescing](#14-null-checks-and-null-coalescing)
  - [1.5 String Operations](#15-string-operations)
  - [1.6 Case-Insensitive Helpers](#16-case-insensitive-helpers)
  - [1.7 Array Membership (IN)](#17-array-membership-in)
  - [1.8 Combined Filter Example](#18-combined-filter-example)
- [2. Projections](#2-projections)
  - [2.1 Full Row Projection](#21-full-row-projection)
  - [2.2 Object Projection](#22-object-projection)
  - [2.3 Projection with Null Coalescing and Arithmetic](#23-projection-with-null-coalescing-and-arithmetic)
- [3. Ordering](#3-ordering)
  - [3.1 Single Key Ascending](#31-single-key-ascending)
  - [3.2 Mixed Ordering](#32-mixed-ordering)
- [4. Distinct Operations](#4-distinct-operations)
- [5. Pagination](#5-pagination)
  - [5.1 Offset/Limit Pattern](#51-offsetlimit-pattern)
  - [5.2 Pagination with Filtering](#52-pagination-with-filtering)
- [6. Joins](#6-joins)
  - [6.1 Simple Inner Join](#61-simple-inner-join)
  - [6.2 Join with Additional Filter](#62-join-with-additional-filter)
  - [6.3 Join with Grouped Results](#63-join-with-grouped-results)
  - [6.4 Left Outer Join](#64-left-outer-join)
  - [6.5 Cross Join](#65-cross-join)
- [7. Grouping and Aggregation](#7-grouping-and-aggregation)
  - [7.1 Basic Grouping](#71-basic-grouping)
  - [7.2 Group with Multiple Aggregates](#72-group-with-multiple-aggregates)
  - [7.3 Group with Post-Filter](#73-group-with-post-filter)
- [8. Window Functions](#8-window-functions)
  - [8.1 ROW_NUMBER](#81-row_number)
  - [8.2 RANK](#82-rank)
  - [8.3 DENSE_RANK](#83-dense_rank)
  - [8.4 Multiple Window Functions](#84-multiple-window-functions)
  - [8.5 Filtering on Window Function Results](#85-filtering-on-window-function-results)
- [9. Scalar Aggregates on Root Queries](#9-scalar-aggregates-on-root-queries)
- [10. Quantifiers](#10-quantifiers)
  - [10.1 Any Operation](#101-any-operation)
  - [10.2 All Operation](#102-all-operation)
- [11. Element Retrieval](#11-element-retrieval)
- [12. Materialisation](#12-materialisation)
- [13. Parameters and Auto-Parameterisation](#13-parameters-and-auto-parameterisation)
  - [13.1 External Parameter Objects](#131-external-parameter-objects)
  - [13.2 Literal Auto-Parameterisation](#132-literal-auto-parameterisation)
  - [13.3 Array Membership](#133-array-membership)
  - [13.4 Case-Insensitive Helper Functions](#134-case-insensitive-helper-functions)
- [14. CRUD Operations](#14-crud-operations)
  - [14.1 INSERT Statements](#141-insert-statements)
  - [14.2 UPDATE Statements](#142-update-statements)
  - [14.3 DELETE Statements](#143-delete-statements)
  - [14.4 Safety Features](#144-safety-features)
  - [14.5 Executing CRUD Operations](#145-executing-crud-operations)
- [15. Query Composition and Reusability](#15-query-composition-and-reusability)
  - [15.1 Chaining Operations on Plans](#151-chaining-operations-on-plans)
  - [15.2 Immutability and Base Queries](#152-immutability-and-base-queries)
  - [15.3 Branching from Base Queries](#153-branching-from-base-queries)
  - [15.4 Parameter Accumulation](#154-parameter-accumulation)
  - [15.5 Composition with All Operations](#155-composition-with-all-operations)
  - [15.6 Practical Patterns](#156-practical-patterns)

---

## 1. Filtering Operations

The `where` method applies predicates to filter query results. Multiple `where` calls are combined with AND logic.

### 1.1 Basic Comparison

```typescript
import { createSchema, defineSelect } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; age: number; email: string; active: boolean };
}

const schema = createSchema<Schema>();

const adults = toSql(
  defineSelect(schema, (q) => q.from("users").where((u) => u.age >= 18)),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE "age" >= $(__p1)
```

```sql
-- SQLite
SELECT * FROM "users" WHERE "age" >= @__p1
```

```json
{ "__p1": 18 }
```

### 1.2 Multiple Predicates

```typescript
const activeRange = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .where((u) => u.age >= 21)
      .where((u) => u.age <= 60)
      .where((u) => u.active === true),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users"
WHERE "age" >= $(__p1) AND "age" <= $(__p2) AND "active" = $(__p3)
```

```sql
-- SQLite
SELECT * FROM "users"
WHERE "age" >= @__p1 AND "age" <= @__p2 AND "active" = @__p3
```

```json
{ "__p1": 21, "__p2": 60, "__p3": true }
```

### 1.3 Logical Nesting and Arithmetic

```typescript
const premium = toSql(
  defineSelect(schema, (q) =>
    q.from("users").where((u) => (u.salary * 0.9 > 150_000 && u.age < 55) || u.active === false),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users"
WHERE ((("salary" * $(__p1)) > $(__p2) AND "age" < $(__p3)) OR "active" = $(__p4))
```

```sql
-- SQLite
SELECT * FROM "users"
WHERE ((("salary" * @__p1) > @__p2 AND "age" < @__p3) OR "active" = @__p4)
```

```json
{ "__p1": 0.9, "__p2": 150000, "__p3": 55, "__p4": false }
```

### 1.4 Null Checks and Null Coalescing

```typescript
const preferredName = toSql(
  defineSelect(schema, (q) => q.from("users").where((u) => (u.nickname ?? u.name) === "anonymous")),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE COALESCE("nickname", "name") = $(__p1)
```

```sql
-- SQLite
SELECT * FROM "users" WHERE COALESCE("nickname", "name") = @__p1
```

```json
{ "__p1": "anonymous" }
```

### 1.5 String Operations

```typescript
const emailFilters = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .where((u) => u.email.startsWith("admin"))
      .where((u) => u.email.endsWith("@example.com"))
      .where((u) => u.name.toLowerCase() === "john"),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users"
WHERE "email" LIKE $(__p1) || '%'
  AND "email" LIKE '%' || $(__p2)
  AND LOWER("name") = $(__p3)
```

```sql
-- SQLite
SELECT * FROM "users"
WHERE "email" LIKE @__p1 || '%'
  AND "email" LIKE '%' || @__p2
  AND LOWER("name") = @__p3
```

```json
{ "__p1": "admin", "__p2": "@example.com", "__p3": "john" }
```

### 1.6 Case-Insensitive Helpers

```typescript
const insensitive = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("users").where((u) => helpers.functions.iequals(u.name, "ALICE")),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE LOWER("name") = LOWER($(__p1))
```

```sql
-- SQLite
SELECT * FROM "users" WHERE LOWER("name") = LOWER(@__p1)
```

```json
{ "__p1": "ALICE" }
```

### 1.7 Array Membership (IN)

```typescript
const allowed = toSql(
  defineSelect(schema, (q) =>
    q.from("users").where((u) => ["admin", "support", "auditor"].includes(u.role)),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE "role" IN ($(__p1), $(__p2), $(__p3))
```

```sql
-- SQLite
SELECT * FROM "users" WHERE "role" IN (@__p1, @__p2, @__p3)
```

```json
{ "__p1": "admin", "__p2": "support", "__p3": "auditor" }
```

Negating the predicate (`!array.includes(...)`) yields `NOT IN`.

### 1.8 Combined Filter Example

```typescript
const advancedFilter = toSql(
  defineSelect(schema, (q, params: { minAge: number; categories: string[] }, helpers) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .where((u) => params.categories.includes(u.departmentId.toString()))
      .where((u) => helpers.functions.icontains(u.email, "company")),
  ),
  { minAge: 25, categories: ["10", "11"] },
);
```

```sql
-- PostgreSQL
SELECT * FROM "users"
WHERE "age" >= $(minAge)
  AND "departmentId" IN ($(categories_0), $(categories_1))
  AND LOWER("email") LIKE '%' || LOWER($(__p1)) || '%'
```

```sql
-- SQLite
SELECT * FROM "users"
WHERE "age" >= @minAge
  AND "departmentId" IN (@categories_0, @categories_1)
  AND LOWER("email") LIKE '%' || LOWER(@__p1) || '%'
```

```json
{
  "minAge": 25,
  "categories": ["10", "11"],
  "categories_0": "10",
  "categories_1": "11",
  "__p1": "company"
}
```

---

## 2. Projections

The `select` method transforms query results by projecting columns or computed expressions.

### 2.1 Full Row Projection

```typescript
const fullRow = toSql(
  defineSelect(schema, (q) => q.from("users")),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users"
```

```sql
-- SQLite
SELECT * FROM "users"
```

### 2.2 Object Projection

```typescript
const summary = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .where((u) => u.active)
      .select((u) => ({
        id: u.id,
        name: u.name,
        contact: {
          email: u.email,
        },
      })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "id" AS "id", "name" AS "name", "email" AS "contact.email" FROM "users" WHERE "active"
```

```sql
-- SQLite
SELECT "id" AS "id", "name" AS "name", "email" AS "contact.email" FROM "users" WHERE "active"
```

### 2.3 Projection with Null Coalescing and Arithmetic

```typescript
interface ProductSchema {
  products: { id: number; name: string; price: number; discount: number | null };
}
const schema = createSchema<ProductSchema>();

const pricing = toSql(
  defineSelect(schema, (q) =>
    q.from("products").select((p) => ({
      id: p.id,
      name: p.name,
      effectivePrice: p.price - (p.discount ?? 0),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "id" AS "id", "name" AS "name", ("price" - COALESCE("discount", $(__p1))) AS "effectivePrice" FROM "products"
```

```sql
-- SQLite
SELECT "id" AS "id", "name" AS "name", ("price" - COALESCE("discount", @__p1)) AS "effectivePrice" FROM "products"
```

```json
{ "__p1": 0 }
```

---

## 3. Ordering

Methods `orderBy`, `orderByDescending`, `thenBy`, and `thenByDescending` control result ordering.

### 3.1 Single Key Ascending

```typescript
const alphabetical = toSql(
  defineSelect(schema, (q) => q.from("users").orderBy((u) => u.name)),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" ORDER BY "name" ASC
```

```sql
-- SQLite
SELECT * FROM "users" ORDER BY "name" ASC
```

### 3.2 Mixed Ordering

```typescript
const ordered = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .orderBy((u) => u.departmentId)
      .thenByDescending((u) => u.salary)
      .thenBy((u) => u.name),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" ORDER BY "departmentId" ASC, "salary" DESC, "name" ASC
```

```sql
-- SQLite
SELECT * FROM "users" ORDER BY "departmentId" ASC, "salary" DESC, "name" ASC
```

---

## 4. Distinct Operations

```typescript
const departments = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .select((u) => u.departmentId)
      .distinct(),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT DISTINCT "departmentId" AS "departmentId" FROM "users"
```

```sql
-- SQLite
SELECT DISTINCT "departmentId" AS "departmentId" FROM "users"
```

---

## 5. Pagination

Methods `skip` and `take` implement OFFSET and LIMIT clauses.

### 5.1 Offset/Limit Pattern

```typescript
const page = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .orderBy((u) => u.id)
      .skip(30)
      .take(15),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" ORDER BY "id" ASC LIMIT $(__p2) OFFSET $(__p1)
```

```sql
-- SQLite
SELECT * FROM "users" ORDER BY "id" ASC LIMIT @__p2 OFFSET @__p1
```

```json
{ "__p1": 30, "__p2": 15 }
```

### 5.2 Pagination with Filtering

```typescript
const filteredPage = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .where((u) => u.active)
      .orderBy((u) => u.name)
      .skip(50)
      .take(25),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE "active" ORDER BY "name" ASC LIMIT $(__p3) OFFSET $(__p2)
```

```sql
-- SQLite
SELECT * FROM "users" WHERE "active" ORDER BY "name" ASC LIMIT @__p3 OFFSET @__p2
```

```json
{ "__p1": true, "__p2": 50, "__p3": 25 }
```

---

## 6. Joins

The `join` method creates INNER JOIN operations. Left outer joins and cross joins follow the same LINQ patterns used in .NET: `groupJoin` + `selectMany(...defaultIfEmpty())` for left joins, and `selectMany` with a query-returning collection selector for cross joins.

### 6.1 Simple Inner Join

```typescript
interface JoinSchema {
  users: { id: number; name: string; departmentId: number };
  departments: { id: number; name: string };
}
const schema = createSchema<JoinSchema>();

const userDepartments = toSql(
  defineSelect(schema, (q) =>
    q.from("users").join(
      q.from("departments"),
      (u) => u.departmentId,
      (d) => d.id,
      (u, d) => ({ userName: u.name, departmentName: d.name }),
    ),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "t0"."name" AS "userName", "t1"."name" AS "departmentName"
FROM "users" AS "t0"
INNER JOIN "departments" AS "t1" ON "t0"."departmentId" = "t1"."id"
```

```sql
-- SQLite
SELECT "t0"."name" AS "userName", "t1"."name" AS "departmentName"
FROM "users" AS "t0"
INNER JOIN "departments" AS "t1" ON "t0"."departmentId" = "t1"."id"
```

### 6.2 Join with Additional Filter

```typescript
interface OrderSchema {
  users: { id: number; name: string };
  orders: { id: number; userId: number; total: number };
}
const schema = createSchema<OrderSchema>();

const regionOrders = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .where((u) => u.id > 100)
      .join(
        q.from("orders"),
        (u) => u.id,
        (o) => o.userId,
        (u, o) => ({ userName: u.name, total: o.total }),
      )
      .where((row) => row.total > 500),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "t0"."name" AS "userName", "t1"."total" AS "total"
FROM "users" AS "t0"
INNER JOIN "orders" AS "t1" ON "t0"."id" = "t1"."userId"
WHERE "t0"."id" > $(__p1) AND "t1"."total" > $(__p2)
```

```sql
-- SQLite
SELECT "t0"."name" AS "userName", "t1"."total" AS "total"
FROM "users" AS "t0"
INNER JOIN "orders" AS "t1" ON "t0"."id" = "t1"."userId"
WHERE "t0"."id" > @__p1 AND "t1"."total" > @__p2
```

```json
{ "__p1": 100, "__p2": 500 }
```

### 6.3 Join with Grouped Results

```typescript
const totalsByDepartment = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .join(
        q.from("orders"),
        (u) => u.id,
        (o) => o.userId,
        (u, o) => ({ u, o }),
      )
      .groupBy((joined) => joined.u.departmentId)
      .select((g) => ({
        departmentId: g.key,
        totalOrders: g.count(),
        revenue: g.sum((row) => row.o.total),
      })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "t0"."departmentId" AS "departmentId", COUNT(*) AS "totalOrders", SUM("t1"."total") AS "revenue"
FROM "users" AS "t0"
INNER JOIN "orders" AS "t1" ON "t0"."id" = "t1"."userId"
GROUP BY "t0"."departmentId"
```

```sql
-- SQLite
SELECT "t0"."departmentId" AS "departmentId", COUNT(*) AS "totalOrders", SUM("t1"."total") AS "revenue"
FROM "users" AS "t0"
INNER JOIN "orders" AS "t1" ON "t0"."id" = "t1"."userId"
GROUP BY "t0"."departmentId"
```

### 6.4 Left Outer Join

Model the classic LINQ pattern: start with `groupJoin`, then expand the grouped results with `selectMany(...defaultIfEmpty())`. Any missing matches appear as `null` in the projection.

```typescript
const usersWithDepartments = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .groupJoin(
        q.from("departments"),
        (user) => user.departmentId,
        (department) => department.id,
        (user, deptGroup) => ({ user, deptGroup }),
      )
      .selectMany(
        (g) => g.deptGroup.defaultIfEmpty(),
        (g, department) => ({ user: g.user, department }),
      )
      .select((row) => ({
        userId: row.user.id,
        departmentName: row.department ? row.department.name : null,
      })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "t0"."id" AS "userId", CASE WHEN "t1"."id" IS NOT NULL THEN "t1"."name" ELSE NULL END AS "departmentName"
FROM "users" AS "t0"
LEFT OUTER JOIN "departments" AS "t1" ON "t0"."departmentId" = "t1"."id"
```

```sql
-- SQLite
SELECT "t0"."id" AS "userId", CASE WHEN "t1"."id" IS NOT NULL THEN "t1"."name" ELSE NULL END AS "departmentName"
FROM "users" AS "t0"
LEFT OUTER JOIN "departments" AS "t1" ON "t0"."departmentId" = "t1"."id"
```

### 6.5 Cross Join

Return a `Queryable` from the collection selector passed to `selectMany`. Because we skip `defaultIfEmpty`, the parser normalizes the operation into a `CROSS JOIN`.

```typescript
const departmentUsers = toSql(
  defineSelect(schema, (q) =>
    q
      .from("departments")
      .selectMany(
        () => q.from("users"),
        (department, user) => ({ department, user }),
      )
      .select((row) => ({
        departmentId: row.department.id,
        userId: row.user.id,
      })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "t0"."id" AS "departmentId", "t1"."id" AS "userId"
FROM "departments" AS "t0"
CROSS JOIN "users" AS "t1"
```

```sql
-- SQLite
SELECT "t0"."id" AS "departmentId", "t1"."id" AS "userId"
FROM "departments" AS "t0"
CROSS JOIN "users" AS "t1"
```

Right and full outer joins still require manual SQL, mirroring the .NET APIs.

---

## 7. Grouping and Aggregation

The `groupBy` method groups results and enables aggregate functions: `count`, `sum`, `avg`, `min`, `max`.

### 7.1 Basic Grouping

```typescript
const byDepartment = toSql(
  defineSelect(schema, (q) => q.from("users").groupBy((u) => u.departmentId)),
  {},
);
```

```sql
-- PostgreSQL
SELECT "departmentId" FROM "users" GROUP BY "departmentId"
```

```sql
-- SQLite
SELECT "departmentId" FROM "users" GROUP BY "departmentId"
```

### 7.2 Group with Multiple Aggregates

```typescript
const departmentStats = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .groupBy((u) => u.departmentId)
      .select((g) => ({
        departmentId: g.key,
        headcount: g.count(),
        totalSalary: g.sum((u) => u.salary),
        averageSalary: g.avg((u) => u.salary),
        maxSalary: g.max((u) => u.salary),
      }))
      .orderByDescending((row) => row.totalSalary),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "departmentId" AS "departmentId", COUNT(*) AS "headcount", SUM("salary") AS "totalSalary", AVG("salary") AS "averageSalary", MAX("salary") AS "maxSalary"
FROM "users"
GROUP BY "departmentId"
ORDER BY "totalSalary" DESC
```

```sql
-- SQLite
SELECT "departmentId" AS "departmentId", COUNT(*) AS "headcount", SUM("salary") AS "totalSalary", AVG("salary") AS "averageSalary", MAX("salary") AS "maxSalary"
FROM "users"
GROUP BY "departmentId"
ORDER BY "totalSalary" DESC
```

### 7.3 Group with Post-Filter

```typescript
const largeDepartments = await executeSelect(
  db,
  schema,
  (q) =>
    q
      .from("users")
      .groupBy((u) => u.departmentId)
      .select((g) => ({ departmentId: g.key, headcount: g.count() }))
      .where((row) => row.headcount > 5),
  {},
);
```

The adapter emits the `WHERE` on the grouped projection; explicit HAVING clauses are not generated.

---

## 8. Window Functions

Window functions perform calculations across rows related to the current row without collapsing the result set. Tinqer supports `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` for ranking operations with optional partitioning and required ordering.

All window functions are accessed via the helpers parameter (second parameter in query builders) and support:

- **`partitionBy(...selectors)`**: Optional partitioning (0 or more selectors)
- **`orderBy(selector)`** / **`orderByDescending(selector)`**: Required ordering (at least one)
- **`thenBy(selector)`** / **`thenByDescending(selector)`**: Additional ordering

### 8.1 ROW_NUMBER

`ROW_NUMBER()` assigns sequential numbers to rows within a partition, starting from 1. The numbering resets for each partition.

```typescript
interface EmployeeSchema {
  employees: { id: number; name: string; department: string; salary: number };
}
const schema = createSchema<EmployeeSchema>();

const rankedEmployees = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("employees").select((e) => ({
      name: e.name,
      department: e.department,
      salary: e.salary,
      rank: helpers
        .window(e)
        .partitionBy((r) => r.department)
        .orderByDescending((r) => r.salary)
        .rowNumber(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "name", "department", "salary",
  ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
FROM "employees"
```

```sql
-- SQLite
SELECT "name", "department", "salary",
  ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
FROM "employees"
```

#### Without Partition

```typescript
interface OrderTimeSchema {
  orders: { id: number; createdAt: Date };
}
const schema = createSchema<OrderTimeSchema>();

const chronological = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("orders").select((o) => ({
      orderId: o.id,
      rowNum: helpers
        .window(o)
        .orderBy((r) => r.createdAt)
        .rowNumber(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL and SQLite
SELECT "id" AS "orderId", ROW_NUMBER() OVER (ORDER BY "createdAt" ASC) AS "rowNum"
FROM "orders"
```

#### Multiple Partitions

```typescript
interface RegionEmployeeSchema {
  employees: { name: string; region: string; department: string; salary: number };
}
const schema = createSchema<RegionEmployeeSchema>();

const multiPartition = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("employees").select((e) => ({
      name: e.name,
      rank: helpers
        .window(e)
        .partitionBy(
          (r) => r.region,
          (r) => r.department,
        )
        .orderByDescending((r) => r.salary)
        .rowNumber(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL and SQLite
SELECT "name",
  ROW_NUMBER() OVER (PARTITION BY "region", "department" ORDER BY "salary" DESC) AS "rank"
FROM "employees"
```

#### Secondary Ordering with thenBy

```typescript
const ranked = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("employees").select((e) => ({
      name: e.name,
      rank: helpers
        .window(e)
        .partitionBy((r) => r.department)
        .orderByDescending((r) => r.salary)
        .thenBy((r) => r.name)
        .rowNumber(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL and SQLite
SELECT "name",
  ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC, "name" ASC) AS "rank"
FROM "employees"
```

### 8.2 RANK

`RANK()` assigns ranks with gaps for tied values. If two rows have the same rank, the next rank skips numbers.

```typescript
const rankedSalaries = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("employees").select((e) => ({
      name: e.name,
      salary: e.salary,
      rank: helpers
        .window(e)
        .partitionBy((r) => r.department)
        .orderByDescending((r) => r.salary)
        .rank(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "name", "salary",
  RANK() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
FROM "employees"
```

```sql
-- SQLite
SELECT "name", "salary",
  RANK() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
FROM "employees"
```

Example result with gaps:

| name  | salary | rank |
| ----- | ------ | ---- |
| Alice | 90000  | 1    |
| Bob   | 90000  | 1    |
| Carol | 85000  | 3    |

Notice rank 2 is skipped because two employees share rank 1.

#### RANK Without Partition

```typescript
interface PlayerSchema {
  players: { name: string; score: number };
}
const schema = createSchema<PlayerSchema>();

const globalRank = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("players").select((p) => ({
      player: p.name,
      score: p.score,
      rank: helpers
        .window(p)
        .orderByDescending((r) => r.score)
        .rank(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL and SQLite
SELECT "name" AS "player", "score", RANK() OVER (ORDER BY "score" DESC) AS "rank"
FROM "players"
```

### 8.3 DENSE_RANK

`DENSE_RANK()` assigns ranks without gaps. Tied values receive the same rank, and the next rank is consecutive.

```typescript
const denseRanked = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("employees").select((e) => ({
      name: e.name,
      salary: e.salary,
      rank: helpers
        .window(e)
        .partitionBy((r) => r.department)
        .orderByDescending((r) => r.salary)
        .denseRank(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "name", "salary",
  DENSE_RANK() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
FROM "employees"
```

```sql
-- SQLite
SELECT "name", "salary",
  DENSE_RANK() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
FROM "employees"
```

Example result without gaps:

| name  | salary | rank |
| ----- | ------ | ---- |
| Alice | 90000  | 1    |
| Bob   | 90000  | 1    |
| Carol | 85000  | 2    |

#### Complex thenBy Chain

```typescript
interface EmployeeAgeSchema {
  employees: { name: string; department: string; salary: number; age: number };
}
const schema = createSchema<EmployeeAgeSchema>();

const complexRanking = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("employees").select((e) => ({
      name: e.name,
      rank: helpers
        .window(e)
        .partitionBy((r) => r.department)
        .orderByDescending((r) => r.salary)
        .thenByDescending((r) => r.age)
        .thenBy((r) => r.name)
        .denseRank(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL and SQLite
SELECT "name",
  DENSE_RANK() OVER (
    PARTITION BY "department"
    ORDER BY "salary" DESC, "age" DESC, "name" ASC
  ) AS "rank"
FROM "employees"
```

### 8.4 Multiple Window Functions

Combine multiple window functions in a single SELECT:

```typescript
const allRankings = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("employees").select((e) => ({
      name: e.name,
      department: e.department,
      salary: e.salary,
      rowNum: helpers
        .window(e)
        .partitionBy((r) => r.department)
        .orderByDescending((r) => r.salary)
        .rowNumber(),
      rank: helpers
        .window(e)
        .partitionBy((r) => r.department)
        .orderByDescending((r) => r.salary)
        .rank(),
      denseRank: helpers
        .window(e)
        .partitionBy((r) => r.department)
        .orderByDescending((r) => r.salary)
        .denseRank(),
    })),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT "name", "department", "salary",
  ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rowNum",
  RANK() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank",
  DENSE_RANK() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "denseRank"
FROM "employees"
```

```sql
-- SQLite
SELECT "name", "department", "salary",
  ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rowNum",
  RANK() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank",
  DENSE_RANK() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "denseRank"
FROM "employees"
```

### 8.5 Filtering on Window Function Results

Window function results can be filtered using `where()` clauses. Tinqer automatically wraps queries in subqueries when window function columns are referenced in WHERE conditions, since SQL does not allow filtering on window functions in the same query level where they're defined.

#### Top-N Per Group

Get the top earner from each department:

```typescript
const topEarners = await executeSelect(
  db,
  schema,
  (q, params, helpers) =>
    q
      .from("employees")
      .select((e) => ({
        ...e,
        rank: helpers
          .window(e)
          .partitionBy((r) => r.department)
          .orderByDescending((r) => r.salary)
          .rowNumber(),
      }))
      .where((r) => r.rank === 1)
      .orderBy((r) => r.department),
  {},
);
```

```sql
-- PostgreSQL (automatically wrapped in subquery)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
  FROM "employees"
) AS "employees"
WHERE "rank" = 1
ORDER BY "department" ASC
```

```sql
-- SQLite (automatically wrapped in subquery)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "rank"
  FROM "employees"
) AS "employees"
WHERE "rank" = 1
ORDER BY "department" ASC
```

#### Top-3 Per Group

Get the top 3 highest-paid employees from a specific department:

```typescript
interface EmployeeDeptSchema {
  employees: { name: string; salary: number; department_id: number };
}
const schema = createSchema<EmployeeDeptSchema>();

const top3Engineering = await executeSelect(
  db,
  schema,
  (q, params: { deptId: number }, helpers) =>
    q
      .from("employees")
      .select((e) => ({
        name: e.name,
        salary: e.salary,
        rank: helpers
          .window(e)
          .partitionBy((r) => r.department_id)
          .orderByDescending((r) => r.salary)
          .rowNumber(),
      }))
      .where((r) => r.rank <= 3 && r.department_id === params.deptId)
      .orderBy((r) => r.rank),
  { deptId: 1 },
);
```

```sql
-- PostgreSQL and SQLite
SELECT * FROM (
  SELECT "name", "salary", "department_id",
    ROW_NUMBER() OVER (PARTITION BY "department_id" ORDER BY "salary" DESC) AS "rank"
  FROM "employees"
) AS "employees"
WHERE "rank" <= 3 AND "department_id" = $(deptId)
ORDER BY "rank" ASC
```

#### Filtering with Spread Operator

The spread operator (`...e`) includes all original columns along with window function results:

```typescript
interface PerformanceSchema {
  employees: { id: number; name: string; performance_score: number };
}
const schema = createSchema<PerformanceSchema>();

const topPerformers = await executeSelect(
  db,
  schema,
  (q, params, helpers) =>
    q
      .from("employees")
      .select((e) => ({
        ...e, // All original columns
        performance_rank: helpers
          .window(e)
          .orderByDescending((r) => r.performance_score)
          .rowNumber(),
      }))
      .where((r) => r.performance_rank <= 10),
  {},
);
```

```sql
-- PostgreSQL and SQLite
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY "performance_score" DESC) AS "performance_rank"
  FROM "employees"
) AS "employees"
WHERE "performance_rank" <= 10
```

#### Combined Filters

Combine window function filters with regular WHERE conditions:

```typescript
interface ActiveEmployeeSchema {
  employees: { name: string; department: string; salary: number; is_active: boolean };
}
const schema = createSchema<ActiveEmployeeSchema>();

const activeTopEarners = await executeSelect(
  db,
  schema,
  (q, params, helpers) =>
    q
      .from("employees")
      .select((e) => ({
        name: e.name,
        department: e.department,
        salary: e.salary,
        is_active: e.is_active,
        dept_rank: helpers
          .window(e)
          .partitionBy((r) => r.department)
          .orderByDescending((r) => r.salary)
          .rowNumber(),
      }))
      .where((r) => r.dept_rank <= 2 && r.is_active === true)
      .orderBy((r) => r.department)
      .thenBy((r) => r.dept_rank),
  {},
);
```

```sql
-- PostgreSQL and SQLite (note: is_active filter can be applied before or after window function)
SELECT * FROM (
  SELECT "name", "department", "salary", "is_active",
    ROW_NUMBER() OVER (PARTITION BY "department" ORDER BY "salary" DESC) AS "dept_rank"
  FROM "employees"
) AS "employees"
WHERE "dept_rank" <= 2 AND "is_active" = TRUE
ORDER BY "department" ASC, "dept_rank" ASC
```

**Note**: Tinqer automatically detects when WHERE clauses reference window function columns and wraps the query in a subquery. This transformation is transparent—you write natural TypeScript code, and Tinqer generates the correct SQL structure.

**Note**: SQLite window function support requires SQLite 3.25 or later.

---

## 9. Scalar Aggregates on Root Queries

Aggregate methods can be called directly on queries to return single values.

```typescript
const totals = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .where((u) => u.active === true)
      .sum((u) => u.salary),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT SUM("salary") FROM "users" WHERE "active" = $(__p1)
```

```sql
-- SQLite
SELECT SUM("salary") FROM "users" WHERE "active" = @__p1
```

```json
{ "__p1": true }
```

Methods `count`, `average`, `min`, and `max` follow the same structure. The `count` method also accepts a predicate:

```typescript
const activeCount = toSql(
  defineSelect(schema, (q) => q.from("users").count((u) => u.active)),
  {},
);
```

```sql
-- PostgreSQL
SELECT COUNT(*) FROM "users" WHERE "active"
```

```sql
-- SQLite
SELECT COUNT(*) FROM "users" WHERE "active"
```

---

## 10. Quantifiers

Methods `any` and `all` test whether elements satisfy conditions.

### 10.1 Any Operation

```typescript
const hasAdults = toSql(
  defineSelect(schema, (q) => q.from("users").any((u) => u.age >= 18)),
  {},
);
```

```sql
-- PostgreSQL
SELECT CASE WHEN EXISTS(SELECT 1 FROM "users" WHERE "age" >= $(__p1)) THEN 1 ELSE 0 END
```

```sql
-- SQLite
SELECT CASE WHEN EXISTS(SELECT 1 FROM "users" WHERE "age" >= @__p1) THEN 1 ELSE 0 END
```

```json
{ "__p1": 18 }
```

### 10.2 All Operation

The `all` method emits a `NOT EXISTS` check:

```typescript
const allActive = toSql(
  defineSelect(schema, (q) => q.from("users").all((u) => u.active === true)),
  {},
);
```

```sql
-- PostgreSQL
SELECT CASE WHEN NOT EXISTS(SELECT 1 FROM "users" WHERE NOT ("active" = $(__p1))) THEN 1 ELSE 0 END
```

```sql
-- SQLite
SELECT CASE WHEN NOT EXISTS(SELECT 1 FROM "users" WHERE NOT ("active" = @__p1)) THEN 1 ELSE 0 END
```

---

## 11. Element Retrieval

Methods `first`, `firstOrDefault`, `single`, `singleOrDefault`, `last`, and `lastOrDefault` retrieve single elements.

```typescript
const newestUser = toSql(
  defineSelect(schema, (q) =>
    q
      .from("users")
      .orderBy((u) => u.createdAt)
      .last(),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" ORDER BY "createdAt" ASC LIMIT 1
```

```sql
-- SQLite
SELECT * FROM "users" ORDER BY "createdAt" ASC LIMIT 1
```

- `single` ensures at most one result
- `firstOrDefault` / `singleOrDefault` return `NULL` when no rows match
- `last` and `lastOrDefault` automatically reverse ordering when no explicit `orderBy` exists

---

## 12. Materialisation

Queries are executed directly without requiring a materialization method. The query builder returns results as arrays by default.

```typescript
const activeUsers = await executeSelect(
  db,
  schema,
  (q) =>
    q
      .from("users")
      .where((u) => u.active)
      .orderBy((u) => u.name),
  {},
);
```

The generated SQL matches the entire query chain.

---

## 13. Parameters and Auto-Parameterisation

Tinqer automatically parameterizes all values to prevent SQL injection and enable prepared statements.

### 13.1 External Parameter Objects

```typescript
const filtered = toSql(
  defineSelect(schema, (q, params: { minAge: number; role: string }) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .where((u) => u.role === params.role),
  ),
  { minAge: 30, role: "manager" },
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE "age" >= $(minAge) AND "role" = $(role)
```

```sql
-- SQLite
SELECT * FROM "users" WHERE "age" >= @minAge AND "role" = @role
```

```json
{ "minAge": 30, "role": "manager" }
```

Nested properties and array indices are preserved (`params.filters.departments[0]`).

### 13.2 Literal Auto-Parameterisation

```typescript
const autoParams = toSql(
  defineSelect(schema, (q) =>
    q.from("users").where((u) => u.departmentId === 7 && u.name.startsWith("A")),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE "departmentId" = $(__p1) AND "name" LIKE $(__p2) || '%'
```

```sql
-- SQLite
SELECT * FROM "users" WHERE "departmentId" = @__p1 AND "name" LIKE @__p2 || '%'
```

```json
{ "__p1": 7, "__p2": "A" }
```

### 13.3 Array Membership

```typescript
const membership = toSql(
  defineSelect(schema, (q) => q.from("users").where((u) => [1, 2, 3].includes(u.id))),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE "id" IN ($(__p1), $(__p2), $(__p3))
```

```sql
-- SQLite
SELECT * FROM "users" WHERE "id" IN (@__p1, @__p2, @__p3)
```

```json
{ "__p1": 1, "__p2": 2, "__p3": 3 }
```

Parameterized array example:

```typescript
const dynamicMembership = toSql(
  defineSelect(schema, (q, params: { allowed: number[] }) =>
    q.from("users").where((u) => params.allowed.includes(u.id)),
  ),
  { allowed: [5, 8] },
);
```

```json
{ "allowed[0]": 5, "allowed[1]": 8 }
```

### 13.4 Case-Insensitive Helper Functions

```typescript
const ic = toSql(
  defineSelect(schema, (q, params, helpers) =>
    q.from("users").where((u) => helpers.functions.icontains(u.email, "support")),
  ),
  {},
);
```

```sql
-- PostgreSQL
SELECT * FROM "users" WHERE LOWER("email") LIKE '%' || LOWER($(__p1)) || '%'
```

```sql
-- SQLite
SELECT * FROM "users" WHERE LOWER("email") LIKE '%' || LOWER(@__p1) || '%'
```

```json
{ "__p1": "support" }
```

---

## 14. CRUD Operations

Tinqer provides full CRUD support with the same type-safety and lambda expression support as SELECT queries. All CRUD operations follow the same pattern: builder functions return operation chains, statement functions generate SQL, and execute functions run the queries.

### 14.1 INSERT Statements

The `insertInto` function creates INSERT operations. Values are specified using direct object syntax (no lambda wrapping required).

#### Basic INSERT

```typescript
import { createSchema, defineInsert } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; age: number; email: string };
}
const schema = createSchema<Schema>();

// Insert with literal values - direct object syntax
const insert = toSql(
  defineInsert(schema, (q) =>
    q.insertInto("users").values({
      name: "Alice",
      age: 30,
      email: "alice@example.com",
    }),
  ),
  {},
);
```

Generated SQL:

```sql
-- PostgreSQL
INSERT INTO "users" ("name", "age", "email")
VALUES ($(__p1), $(__p2), $(__p3))

-- SQLite
INSERT INTO "users" ("name", "age", "email")
VALUES (@__p1, @__p2, @__p3)
```

#### INSERT with External Parameters

External variables must be passed via the params object - closure variables are not supported:

```typescript
const insert = toSql(
  defineInsert(schema, (q, params: { name: string; age: number }) =>
    q.insertInto("users").values({
      name: params.name,
      age: params.age,
      email: "default@example.com",
    }),
  ),
  { name: "Bob", age: 25 },
);
```

Generated SQL uses external parameters directly:

```sql
INSERT INTO "users" ("name", "age", "email")
VALUES ($(name), $(age), $(__p1))  -- PostgreSQL
VALUES (@name, @age, @__p1)        -- SQLite
```

> **Tip:** When a property in `.values()` evaluates to `undefined`, the column is omitted from the INSERT. Explicit `null` values still emit `NULL`. The insert throws if every value is `undefined`.

#### INSERT with RETURNING Clause

Both PostgreSQL and SQLite (3.35.0+) support the RETURNING clause to retrieve values from inserted rows:

```typescript
// Return specific columns
const insertWithReturn = toSql(
  defineInsert(schema, (q) =>
    q
      .insertInto("users")
      .values({ name: "Charlie", age: 35 })
      .returning((u) => ({ id: u.id, createdAt: u.createdAt })),
  ),
  {},
);

// Return all columns
const insertReturnAll = toSql(
  defineInsert(
    schema,
    (q) =>
      q
        .insertInto("users")
        .values({ name: "David", age: 40 })
        .returning((u) => u), // Returns *
  ),
  {},
);
```

#### NULL Values in INSERT

```typescript
const insert = toSql(
  defineInsert(schema, (q) =>
    q.insertInto("users").values({
      name: "Eve",
      email: null, // Generates NULL, not parameterized
      phone: undefined, // Column omitted from INSERT
    }),
  ),
  {},
);
```

### 14.2 UPDATE Statements

The `update` function creates UPDATE operations. The `.set()` method uses direct object syntax (no lambda wrapping required).

#### Basic UPDATE

```typescript
import { createSchema, defineUpdate } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

const schema = createSchema<Schema>();

const updateStmt = toSql(
  defineUpdate(schema, (q) =>
    q
      .update("users")
      .set({ age: 31, lastModified: new Date() })
      .where((u) => u.id === 1),
  ),
  {},
);
```

Generated SQL:

```sql
-- PostgreSQL
UPDATE "users"
SET "age" = $(__p1), "lastModified" = $(__p2)
WHERE "id" = $(__p3)

-- SQLite
UPDATE "users"
SET "age" = @__p1, "lastModified" = @__p2
WHERE "id" = @__p3
```

> **Tip:** If any property in the `.set()` object evaluates to `undefined` (for example because a parameter was omitted), Tinqer simply skips that column. Explicit `null` values still generate `SET column = NULL`. The query builder throws if every assignment resolves to `undefined`.

#### UPDATE with External Parameters

External variables must be passed via the params object:

```typescript
const updateStmt = toSql(
  defineUpdate(schema, (q, params: { newAge: number }) =>
    q
      .update("users")
      .set({ age: params.newAge })
      .where((u) => u.id === 1),
  ),
  { newAge: 32 },
);
```

#### UPDATE with Complex WHERE

```typescript
const updateStmt = toSql(
  defineUpdate(schema, (q) =>
    q
      .update("users")
      .set({ status: "inactive" })
      .where((u) => u.lastLogin < new Date("2023-01-01") && u.role !== "admin"),
  ),
  {},
);
```

#### UPDATE with RETURNING Clause

```typescript
const updateWithReturn = toSql(
  defineUpdate(schema, (q) =>
    q
      .update("users")
      .set({ age: 32 })
      .where((u) => u.id === 2)
      .returning((u) => ({ id: u.id, age: u.age, updatedAt: u.updatedAt })),
  ),
  {},
);
```

#### Full Table UPDATE (Requires Explicit Permission)

```typescript
// UPDATE without WHERE requires explicit permission
const updateAll = toSql(
  defineUpdate(
    schema,
    (q) => q.update("users").set({ isActive: true }).allowFullTableUpdate(), // Required flag
  ),
  {},
);
```

Without the flag, attempting an UPDATE without WHERE throws an error:

```
Error: UPDATE requires a WHERE clause or explicit allowFullTableUpdate().
Full table updates are dangerous and must be explicitly allowed.
```

### 14.3 DELETE Statements

The `deleteFrom` function creates DELETE operations with optional WHERE conditions.

#### Basic DELETE

```typescript
import { createSchema, defineDelete } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

const schema = createSchema<Schema>();

const del = toSql(
  defineDelete(schema, (q) => q.deleteFrom("users").where((u) => u.age > 100)),
  {},
);
```

Generated SQL:

```sql
DELETE FROM "users" WHERE "age" > $(__p1)  -- PostgreSQL
DELETE FROM "users" WHERE "age" > @__p1    -- SQLite
```

#### DELETE with Complex Conditions

```typescript
const del = toSql(
  defineDelete(schema, (q) =>
    q
      .deleteFrom("users")
      .where((u) => u.isDeleted === true || (u.age < 18 && u.role !== "admin") || u.email === null),
  ),
  {},
);
```

#### DELETE with IN Clause

```typescript
const del = toSql(
  defineDelete(schema, (q, params: { userIds: number[] }) =>
    q.deleteFrom("users").where((u) => params.userIds.includes(u.id)),
  ),
  { userIds: [1, 2, 3, 4, 5] },
);
```

Generated SQL:

```sql
-- PostgreSQL
DELETE FROM "users"
WHERE "id" IN ($(userIds_0), $(userIds_1), $(userIds_2), $(userIds_3), $(userIds_4))

-- SQLite
DELETE FROM "users"
WHERE "id" IN (@userIds_0, @userIds_1, @userIds_2, @userIds_3, @userIds_4)
```

```json
{
  "userIds": [1, 2, 3, 4, 5],
  "userIds_0": 1,
  "userIds_1": 2,
  "userIds_2": 3,
  "userIds_3": 4,
  "userIds_4": 5
}
```

#### Full Table DELETE (Requires Explicit Permission)

```typescript
// DELETE without WHERE requires explicit permission
const deleteAll = toSql(
  defineDelete(schema, (q) => q.deleteFrom("users").allowFullTableDelete()), // Required flag
  {},
);
```

### 14.4 Safety Features

Tinqer includes multiple safety guards for CRUD operations:

#### Mandatory WHERE Clauses

UPDATE and DELETE operations require WHERE clauses by default to prevent accidental full-table operations:

```typescript
// This throws an error
toSql(
  defineDelete(schema, (q) => q.deleteFrom("users")),
  {},
);
// Error: DELETE requires a WHERE clause or explicit allowFullTableDelete()

// This works
toSql(
  defineDelete(schema, (q) => q.deleteFrom("users").allowFullTableDelete()),
  {},
);
```

#### Type Safety

All CRUD operations maintain full TypeScript type safety:

```typescript
interface UserSchema {
  users: { id: number; name: string; email: string | null; age: number };
}
const schema = createSchema<UserSchema>();

// Type error: 'username' doesn't exist on users table
toSql(
  defineInsert(schema, (q) =>
    q.insertInto("users").values({
      username: "Alice", // ❌ Type error
    }),
  ),
  {},
);

// Type error: age must be number
toSql(
  defineUpdate(schema, (q) =>
    q.update("users").set({
      age: "30", // ❌ Type error - must be number
    }),
  ),
  {},
);
```

#### Parameter Sanitization

All values are automatically parameterized to prevent SQL injection:

```typescript
const maliciousName = "'; DROP TABLE users; --";
const insert = toSql(
  defineInsert(schema, (q) =>
    q.insertInto("users").values({
      name: maliciousName, // Safely parameterized
    }),
  ),
  {},
);
// Generates: INSERT INTO "users" ("name") VALUES ($(__p1))
// Parameters: { __p1: "'; DROP TABLE users; --" }
```

### 14.5 Executing CRUD Operations

The adapter packages provide execution functions for all CRUD operations:

#### PostgreSQL (pg-promise)

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeInsert, executeUpdate, executeDelete } from "@tinqerjs/pg-promise-adapter";

const schema = createSchema<Schema>();

// Execute INSERT with RETURNING
const insertedUsers = await executeInsert(
  db,
  schema,
  (q) =>
    q
      .insertInto("users")
      .values({ name: "Frank", age: 28 })
      .returning((u) => ({ id: u.id, name: u.name })),
  {},
);
// Returns: [{ id: 123, name: "Frank" }]

// Execute UPDATE - returns affected row count
const updateCount = await executeUpdate(
  db,
  schema,
  (q) =>
    q
      .update("users")
      .set({ age: 29 })
      .where((u) => u.id === 123),
  {},
);
// Returns number of affected rows

// Execute DELETE - returns affected row count
const deleteCount = await executeDelete(
  db,
  schema,
  (q) => q.deleteFrom("users").where((u) => u.id === 123),
  {},
);
```

#### SQLite (better-sqlite3)

```typescript
import { createSchema } from "@tinqerjs/tinqer";
import { executeInsert, executeUpdate, executeDelete } from "@tinqerjs/better-sqlite3-adapter";

const schema = createSchema<Schema>();

// Execute INSERT - returns row count
const insertCount = executeInsert(
  db,
  schema,
  (q) => q.insertInto("users").values({ name: "Grace", age: 30 }),
  {},
);
// Returns number of inserted rows

// Execute UPDATE - returns row count
const updateCount = executeUpdate(
  db,
  schema,
  (q) =>
    q
      .update("users")
      .set({ age: 33 })
      .where((u) => u.name === "Henry"),
  {},
);

// Execute DELETE - returns row count
const deleteCount = executeDelete(
  db,
  schema,
  (q) => q.deleteFrom("users").where((u) => u.age > 100),
  {},
);
```

SQLite helpers always return the number of affected rows. To inspect row data after an insert or update, run a follow-up SELECT query using `executeSelect`.

#### Transaction Support

Both adapters support transactions through their respective database drivers:

```typescript
interface TxSchema {
  users: { id: number; name: string; lastLogin: Date };
  user_logs: { userId: number; action: string };
}
const schema = createSchema<TxSchema>();

// PostgreSQL transactions
await db.tx(async (t) => {
  const users = await executeInsert(
    t,
    schema,
    (q) =>
      q
        .insertInto("users")
        .values({ name: "Ivy" })
        .returning((u) => u.id),
    {},
  );

  await executeInsert(
    t,
    schema,
    (q) =>
      q.insertInto("user_logs").values({
        userId: users[0]!.id,
        action: "created",
      }),
    {},
  );
});

// SQLite transactions
const transaction = sqliteDb.transaction(() => {
  executeInsert(db, schema, (q) => q.insertInto("users").values({ name: "Jack" }), {});
  executeUpdate(
    db,
    schema,
    (q) =>
      q
        .update("users")
        .set({ lastLogin: new Date() })
        .where((u) => u.name === "Jack"),
    {},
  );
});
transaction();
```

---

## 15. Query Composition and Reusability

Tinqer query plans are **immutable and composable**. Plan handles returned by `define*` functions support method chaining - each operation returns a new plan without modifying the original. This enables powerful query composition patterns: reusable base queries, branching specialized variations, and parameter accumulation.

### 15.1 Chaining Operations on Plans

All plan handles support chaining operations like `where()`, `orderBy()`, `select()`, etc.:

```typescript
import { createSchema, defineSelect } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; age: number; isActive: boolean };
}

const schema = createSchema<Schema>();

// Chain operations on the plan handle
const plan = defineSelect(schema, (q) => q.from("users"))
  .where((u) => u.age > 18)
  .where((u) => u.isActive === true)
  .orderBy((u) => u.name)
  .select((u) => ({ id: u.id, name: u.name }));

const { sql, params } = toSql(plan, {});
// SELECT "id" AS "id", "name" AS "name" FROM "users"
// WHERE "age" > $(__p1) AND "isActive" = $(__p2)
// ORDER BY "name" ASC
```

### 15.2 Immutability and Base Queries

Plans are immutable - calling a method returns a **new plan** without modifying the original:

```typescript
const basePlan = defineSelect(schema, (q) => q.from("users"));

const withAge = basePlan.where((u) => u.age > 18);
const withStatus = basePlan.where((u) => u.isActive === true);

// basePlan is unchanged
// withAge and withStatus are independent plans
```

This immutability enables creating **reusable base queries**:

```typescript
interface Schema {
  orders: {
    id: number;
    userId: number;
    total: number;
    status: string;
    createdAt: Date;
  };
}

const schema = createSchema<Schema>();

// Reusable base query
type UserOrdersParams = { userId: number };

const userOrders = defineSelect(schema, (q, p: UserOrdersParams) =>
  q
    .from("orders")
    .where((o) => o.userId === p.userId)
    .orderBy((o) => o.createdAt),
);

// Use the base query in different contexts
const recentUserOrders = userOrders.take(10);
const highValueUserOrders = userOrders.where((o) => o.total > 1000);

// Execute with parameters
toSql(recentUserOrders, { userId: 42 });
toSql(highValueUserOrders, { userId: 42 });
```

### 15.3 Branching from Base Queries

Since plans are immutable, you can branch multiple specialized queries from a single base:

```typescript
interface Schema {
  users: {
    id: number;
    name: string;
    age: number;
    isActive: boolean;
    departmentId: number;
  };
}

const schema = createSchema<Schema>();

type DeptParams = { departmentId: number };

// Base query: users in a department
const usersInDept = defineSelect(schema, (q, p: DeptParams) =>
  q.from("users").where((u) => u.departmentId === p.departmentId),
);

// Branch 1: Active users only
const activeUsersInDept = usersInDept
  .where((u) => u.isActive === true)
  .where<{ minAge: number }>((u, p) => u.age >= p.minAge)
  .orderBy((u) => u.name);

// Branch 2: Inactive users only
const inactiveUsersInDept = usersInDept
  .where((u) => u.isActive === false)
  .where<{ maxAge: number }>((u, p) => u.age <= p.maxAge)
  .orderBy((u) => u.age);

// Branch 3: Senior staff
const seniorStaffInDept = usersInDept
  .where((u) => u.age >= 50)
  .select((u) => ({ id: u.id, name: u.name, age: u.age }));

// Execute branches with different parameters
toSql(activeUsersInDept, { departmentId: 1, minAge: 25 });
toSql(inactiveUsersInDept, { departmentId: 1, maxAge: 65 });
toSql(seniorStaffInDept, { departmentId: 1 });
```

### 15.4 Parameter Accumulation

Parameters from the builder function and chained operations are **merged automatically**:

```typescript
interface Schema {
  products: {
    id: number;
    name: string;
    price: number;
    category: string;
    inStock: boolean;
  };
}

const schema = createSchema<Schema>();

// Builder function defines initial params
type BuilderParams = { category: string };

const plan = defineSelect(schema, (q, p: BuilderParams) =>
  q.from("products").where((prod) => prod.category === p.category),
);

// Chained operations add more params
type ChainParams1 = { minPrice: number };
type ChainParams2 = { maxPrice: number };

const refinedPlan = plan
  .where<ChainParams1>((prod, p) => prod.price >= p.minPrice)
  .where<ChainParams2>((prod, p) => prod.price <= p.maxPrice)
  .where((prod) => prod.inStock === true);

// Must provide ALL accumulated parameters
const { sql, params } = toSql(refinedPlan, {
  category: "electronics",
  minPrice: 100,
  maxPrice: 1000,
});

// params contains: { category, minPrice, maxPrice, __p1: true }
```

**Type safety**: TypeScript enforces that all accumulated parameters are provided at execution time.

### 15.5 Composition with All Operations

Composition works with all CRUD operations, not just SELECT:

#### INSERT Composition

```typescript
interface Schema {
  posts: {
    id: number;
    userId: number;
    title: string;
    content: string;
    isPublished: boolean;
  };
}

const schema = createSchema<Schema>();

const insertPost = defineInsert(schema, (q) => q.insertInto("posts"))
  .values({
    userId: 1,
    title: "My Post",
    content: "Post content",
    isPublished: false,
  })
  .returning((p) => ({ id: p.id, title: p.title }));

toSql(insertPost, {});
```

#### UPDATE Composition

```typescript
const updatePlan = defineUpdate(schema, (q) => q.update("posts"))
  .set({ isPublished: true })
  .where<{ minViews: number }>((p, params) => p.viewCount > params.minViews)
  .returning((p) => p.id);

toSql(updatePlan, { minViews: 1000 });
```

#### DELETE Composition

```typescript
const deletePlan = defineDelete(schema, (q) => q.deleteFrom("posts"))
  .where((p) => p.isPublished === false)
  .where<{ beforeDate: Date }>((p, params) => p.createdAt < params.beforeDate);

toSql(deletePlan, { beforeDate: new Date("2024-01-01") });
```

### 15.6 Practical Patterns

#### Pattern 1: Pagination Helper

```typescript
interface Schema {
  articles: {
    id: number;
    title: string;
    createdAt: Date;
    viewCount: number;
  };
}

const schema = createSchema<Schema>();

type PageParams = { page: number; pageSize: number };

function paginate<TPlan extends { skip: Function; take: Function }>(
  plan: TPlan,
  page: number,
  pageSize: number,
): TPlan {
  return plan.skip((page - 1) * pageSize).take(pageSize) as TPlan;
}

const allArticles = defineSelect(schema, (q) => q.from("articles").orderBy((a) => a.createdAt));

// Page 1
const page1 = paginate(allArticles, 1, 20);
toSql(page1, {});

// Page 2
const page2 = paginate(allArticles, 2, 20);
toSql(page2, {});
```

#### Pattern 2: Common Filters

```typescript
const activeArticles = defineSelect(schema, (q) =>
  q.from("articles").where((a) => a.isActive === true),
);

const popularActiveArticles = activeArticles.where((a) => a.viewCount > 1000);

const recentActiveArticles = activeArticles
  .where<{ since: Date }>((a, p) => a.createdAt >= p.since)
  .orderBy((a) => a.createdAt);

toSql(popularActiveArticles, {});
toSql(recentActiveArticles, { since: new Date("2024-01-01") });
```

#### Pattern 3: Repository Pattern

```typescript
class UserRepository {
  private baseQuery = defineSelect(schema, (q) => q.from("users"));

  findActive() {
    return this.baseQuery.where((u) => u.isActive === true);
  }

  findByDepartment(deptId: number) {
    return this.baseQuery.where((u) => u.departmentId === deptId);
  }

  findActiveInDepartment(deptId: number) {
    return this.findActive().where((u) => u.departmentId === deptId);
  }
}

const repo = new UserRepository();
toSql(repo.findActive(), {});
toSql(repo.findActiveInDepartment(5), {});
```

**Key Takeaway**: Query composition enables building reusable, composable query fragments that can be combined and specialized without duplication.

---


---