---
url: /bigal/reference/api.md
description: >-
  Complete API reference for BigAl — initialize(), Repository,
  ReadonlyRepository, query builder methods, subquery(), decorators, and types.
---

# API Reference

All public exports from `bigal`.

## initialize()

Creates repositories for all provided models.

```ts
import { initialize } from 'bigal';

const repos = initialize({
  models: [Product, Store],
  pool,
  readonlyPool,
  connections,
  expose,
});
```

**Parameters:** `InitializeOptions`

| Option         | Type                          | Required | Description                                   |
| -------------- | ----------------------------- | -------- | --------------------------------------------- |
| `models`       | `EntityStatic<Entity>[]`      | Yes      | Model classes decorated with `@table()`       |
| `pool`         | `PoolLike`                    | Yes      | Primary connection pool                       |
| `readonlyPool` | `PoolLike`                    | No       | Pool for read operations (defaults to `pool`) |
| `connections`  | `Record<string, IConnection>` | No       | Named connections for multi-database setups   |
| `expose`       | `(repo, metadata) => void`    | No       | Callback invoked for each created repository  |

**Returns:** `Record<string, IReadonlyRepository<Entity> | IRepository<Entity>>`

## Repository

Full CRUD repository returned by `initialize()` for non-readonly models.

### find()

```ts
repository.find(options?): FindQuery<T>
```

Returns a query builder for multiple records. Options: `{ select?, pool? }`.

### findOne()

```ts
repository.findOne(options?): FindOneQuery<T>
```

Returns a query builder for a single record or `null`. Options: `{ select?, pool? }`.

### count()

```ts
repository.count(options?): CountQuery<T>
```

Returns a query builder that resolves to a number. Options: `{ pool? }`.

### create()

```ts
repository.create(values, options?): Promise<QueryResult<T>>
repository.create(values[], options?): Promise<QueryResult<T>[]>
```

Insert one or multiple records. Options: `{ returnRecords?, returnSelect?, onConflict? }`.

### update()

```ts
repository.update(where, values, options?): Promise<QueryResult<T>[]>
```

Update matching records. Options: `{ returnRecords?, returnSelect? }`.

### destroy()

```ts
repository.destroy(where, options?): Promise<QueryResult<T>[]>
```

Delete matching records. Options: `{ returnRecords?, returnSelect? }`.

## ReadonlyRepository

Read-only repository returned for models with `readonly: true`. Exposes `find()`, `findOne()`, and `count()` only.

## Query builder methods

All query types support fluent chaining. Each method returns a new immutable instance.

| Method                             | Available on         | Description                      |
| ---------------------------------- | -------------------- | -------------------------------- |
| `.where(query)`                    | find, findOne, count | Filter records                   |
| `.sort(value)`                     | find, findOne        | Order results                    |
| `.limit(n)`                        | find                 | Limit rows returned              |
| `.skip(n)`                         | find                 | Skip rows                        |
| `.paginate(page, pageSize)`        | find                 | Shorthand for skip + limit       |
| `.withCount()`                     | find                 | Return `{ results, totalCount }` |
| `.populate(relation, options?)`    | find, findOne        | Load related entities            |
| `.join(relation, alias?, on?)`     | find, findOne        | INNER JOIN                       |
| `.leftJoin(relation, alias?, on?)` | find, findOne        | LEFT JOIN                        |
| `.distinctOn(columns)`             | find                 | PostgreSQL DISTINCT ON           |
| `.toJSON()`                        | find, findOne        | Return plain objects             |

## subquery()

```ts
import { subquery } from 'bigal';

const sub = subquery(repository);
```

Returns a `SubqueryBuilder` with methods: `select()`, `where()`, `sort()`, `limit()`, `groupBy()`, `having()`, `distinctOn()`.

Scalar aggregate shortcuts: `sub.count()`, `sub.sum(col)`, `sub.avg(col)`, `sub.max(col)`, `sub.min(col)`.

## Decorators

### @table(options)

Binds a class to a database table or view.

| Option       | Type      | Description                            |
| ------------ | --------- | -------------------------------------- |
| `name`       | `string`  | Table or view name                     |
| `schema`     | `string`  | PostgreSQL schema (default: `public`)  |
| `readonly`   | `boolean` | Returns `ReadonlyRepository` if `true` |
| `connection` | `string`  | Named connection key                   |

### @primaryColumn(options)

Marks the primary key. Options: `{ type }`.

### @column(options)

Defines a column. See [Models > Column options](/guide/models#column-options) for all options.

### @createDateColumn()

Auto-set on insert.

### @updateDateColumn()

Auto-set on update.

### @versionColumn()

Auto-incrementing version for optimistic locking.

## Types

### Entity

Base class for all models.

### NotEntity\<T>

Wrapper type for JSON column objects that have an `id` field. Prevents BigAl's type system from treating them as entities.

### QueryResult\<T>

Narrows relationship fields from union types to foreign key types. See [Relationships > QueryResult](/guide/relationships#queryresult-type-narrowing).

### QueryResultPopulated\<T, K>

Type for entities with specific relationships populated.

### TypedAggregateExpression\<Alias>

Return type annotation for aggregate callbacks that enables type-safe sorting on subquery join columns.

### PoolLike

Interface for compatible connection pools. Supported: `postgres-pool`, `pg`, `@neondatabase/serverless`.

### IConnection

```ts
interface IConnection {
  pool: PoolLike;
  readonlyPool?: PoolLike;
}
```

### IRepository\<T>

Interface for full CRUD repositories.

### IReadonlyRepository\<T>

Interface for read-only repositories.

---

---
url: /bigal/advanced/bigal-vs-raw-sql.md
description: >-
  When to use BigAl vs raw SQL, with a side-by-side translation table mapping
  common SQL queries to BigAl's fluent API.
---

# BigAl vs Raw SQL

## When to use BigAl

BigAl is a good fit for standard CRUD operations and queries that map naturally to its fluent API:

* Simple to moderately complex WHERE clauses
* Joins on defined relationships
* Pagination, sorting, and counting
* Subqueries with aggregates
* DISTINCT ON queries
* Upserts with ON CONFLICT

## When to use raw SQL

Drop to raw SQL (via your pool directly) when:

* You need CTEs (WITH clauses)
* Window functions beyond what DISTINCT ON provides
* Complex recursive queries
* Bulk operations with custom locking (SELECT FOR UPDATE)
* Database-specific features BigAl does not wrap

## Translation reference

### Basic queries

| SQL                                                   | BigAl                                                        |
| ----------------------------------------------------- | ------------------------------------------------------------ |
| `SELECT * FROM products WHERE id = 1`                 | `productRepo.findOne().where({ id: 1 })`                     |
| `SELECT name FROM products WHERE id = 1`              | `productRepo.findOne({ select: ['name'] }).where({ id: 1 })` |
| `SELECT * FROM products WHERE name ILIKE '%widget%'`  | `productRepo.find().where({ name: { contains: 'widget' } })` |
| `SELECT * FROM products WHERE price >= 100`           | `productRepo.find().where({ price: { '>=': 100 } })`         |
| `SELECT * FROM products WHERE status IN ('a','b')`    | `productRepo.find().where({ status: ['a', 'b'] })`           |
| `SELECT * FROM products WHERE status <> 'x'`          | `productRepo.find().where({ status: { '!': 'x' } })`         |
| `SELECT * FROM products WHERE deleted_at IS NOT NULL` | `productRepo.find().where({ deletedAt: { '!': null } })`     |
| `SELECT * FROM products ORDER BY name LIMIT 10`       | `productRepo.find().where({}).sort('name asc').limit(10)`    |
| `SELECT COUNT(*) FROM products WHERE active = true`   | `productRepo.count().where({ active: true })`                |

### CRUD

| SQL                                                         | BigAl                                          |
| ----------------------------------------------------------- | ---------------------------------------------- |
| `INSERT INTO products (name) VALUES ('Widget') RETURNING *` | `productRepo.create({ name: 'Widget' })`       |
| `UPDATE products SET name = 'X' WHERE id = 1 RETURNING *`   | `productRepo.update({ id: 1 }, { name: 'X' })` |
| `DELETE FROM products WHERE id = 1 RETURNING *`             | `productRepo.destroy({ id: 1 })`               |

### Subqueries, joins, and advanced

| SQL                                                                      | BigAl                                                                                   |
| ------------------------------------------------------------------------ | --------------------------------------------------------------------------------------- |
| `WHERE store_id IN (SELECT id FROM stores WHERE active)`                 | `.where({ store: { in: subquery(storeRepo).select(['id']).where({ active: true }) } })` |
| `INNER JOIN stores s ON p.store_id = s.id WHERE s.name = 'Acme'`         | `.join('store').where({ store: { name: 'Acme' } })`                                     |
| `SELECT DISTINCT ON (store_id) * ... ORDER BY store_id, created_at DESC` | `.distinctOn(['store']).sort('store').sort('createdAt desc')`                           |
| `ON CONFLICT (sku) DO NOTHING`                                           | `{ onConflict: { action: 'ignore', targets: ['sku'] } }`                                |
| `ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name`                   | `{ onConflict: { action: 'merge', targets: ['sku'], merge: ['name'] } }`                |

## Mixing BigAl and raw SQL

BigAl does not lock you in. Use the same pool for raw queries:

```ts
const { rows } = await pool.query('SELECT * FROM products WHERE tsv @@ plainto_tsquery($1)', ['search term']);
```

Use BigAl for the 90% of queries that are straightforward, and raw SQL for the rest.

---

---
url: /bigal/reference/configuration.md
description: >-
  Configure connection pools (postgres-pool, pg, Neon), read replicas, multiple
  databases, and debug logging.
---

# Configuration

## Connection pools

BigAl requires a PostgreSQL connection pool that implements `PoolLike`. Three drivers are supported:

### postgres-pool (recommended)

```ts
import { Pool } from 'postgres-pool';
import { initialize } from 'bigal';

const pool = new Pool({
  connectionString: 'postgres://user:pass@localhost/mydb',
});

const repos = initialize({ models, pool });
```

### node-postgres (pg)

```ts
import pg from 'pg';

const pool = new pg.Pool({
  connectionString: 'postgres://user:pass@localhost/mydb',
});

const repos = initialize({ models, pool });
```

### Neon serverless

```ts
import { Pool } from '@neondatabase/serverless';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

const repos = initialize({ models, pool });
```

## Read replicas

Separate read and write pools by passing `readonlyPool`:

```ts
const pool = new Pool('postgres://localhost/mydb');
const readonlyPool = new Pool('postgres://readonly-host/mydb');

const repos = initialize({
  models,
  pool,
  readonlyPool,
});
```

`find()`, `findOne()`, and `count()` use `readonlyPool`. `create()`, `update()`, and `destroy()` use `pool`.

Individual queries can override the pool:

```ts
const product = await productRepository
  .findOne({
    pool: writePool,
  })
  .where({ id: 42 });
```

## Multiple databases

Use named connections for models that live in different databases:

```ts
@table({ name: 'audit_logs', connection: 'audit' })
export class AuditLog extends Entity {
  // ...
}

const repos = initialize({
  models: [Product, AuditLog],
  pool: mainPool,
  connections: {
    audit: {
      pool: auditPool,
      readonlyPool: auditReadonlyPool,
    },
  },
});
```

Models without a `connection` option use the top-level `pool`.

## Expose callback

The `expose` callback is invoked for each repository after creation:

```ts
const repos = initialize({
  models,
  pool,
  expose(repository, tableMetadata) {
    console.log(`Initialized ${tableMetadata.name}`);
  },
});
```

## Debugging

Set the `DEBUG_BIGAL` environment variable to log generated SQL:

```sh
DEBUG_BIGAL=true node app.js
```

---

---
url: /bigal/guide/crud-operations.md
description: >-
  Create, update, and destroy records with RETURNING support, query projection,
  and ON CONFLICT upserts.
---

# CRUD Operations

BigAl repositories provide `create()`, `update()`, and `destroy()` methods. All three return affected records
by default (using `RETURNING *`), and all support `returnRecords` and `returnSelect` options.

## Create

### Single record

```ts
const product = await productRepository.create({
  name: 'Widget',
  priceCents: 999,
});
// product = { id: 42, name: 'Widget', priceCents: 999, createdAt: ... }
```

### Multiple records

```ts
const products = await productRepository.create([
  { name: 'Widget', priceCents: 999 },
  { name: 'Gadget', priceCents: 1499 },
]);
// products = [{ id: 42, ... }, { id: 43, ... }]
```

### Skip returning records

```ts
await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnRecords: false });
```

### Query projection (returnSelect)

Return only specific columns. The primary key is always included.

```ts
const product = await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnSelect: ['name'] });
// product = { id: 42, name: 'Widget' }
```

Pass an empty array to return only the primary key:

```ts
const product = await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnSelect: [] });
// product = { id: 42 }
```

## onConflict (Upsert)

Handle constraint violations with PostgreSQL's `ON CONFLICT` clause.

### Ignore (DO NOTHING)

```ts
const product = await productRepository.create(
  { name: 'Widget', sku: 'WDG-001' },
  {
    onConflict: {
      action: 'ignore',
      targets: ['sku'],
    },
  },
);
```

### Merge (DO UPDATE) - all columns

```ts
const product = await productRepository.create(
  { name: 'Widget', sku: 'WDG-001', priceCents: 999 },
  {
    onConflict: {
      action: 'merge',
      targets: ['sku'],
    },
  },
);
```

### Merge - specific columns

```ts
const product = await productRepository.create(
  { name: 'Widget', sku: 'WDG-001', priceCents: 999 },
  {
    onConflict: {
      action: 'merge',
      targets: ['sku'],
      merge: ['name', 'priceCents'],
    },
  },
);
```

## Update

`update()` takes a where clause object and a values object. Returns an array of affected records.

```ts
// Update a single record
const products = await productRepository.update({ id: 42 }, { name: 'Super Widget' });
// products = [{ id: 42, name: 'Super Widget', ... }]

// Update multiple records
const products = await productRepository.update({ id: [42, 43] }, { priceCents: 1299 });
// products = [{ id: 42, ... }, { id: 43, ... }]
```

> `update()` always returns an array, regardless of how many records were affected.

Without returning records:

```ts
await productRepository.update({ id: 42 }, { name: 'Super Widget' }, { returnRecords: false });
```

With query projection:

```ts
const products = await productRepository.update({ id: [42, 43] }, { priceCents: 1299 }, { returnSelect: ['id'] });
// products = [{ id: 42 }, { id: 43 }]
```

## Destroy

`destroy()` takes a where clause object. Returns an array of deleted records.

```ts
// Delete a single record
const products = await productRepository.destroy({ id: 42 });
// products = [{ id: 42, name: 'Super Widget', ... }]

// Delete multiple records
const products = await productRepository.destroy({ id: [42, 43] });
```

> `destroy()` always returns an array, regardless of how many records were affected.

Without returning records:

```ts
await productRepository.destroy({ id: 42 }, { returnRecords: false });
```

With query projection:

```ts
const products = await productRepository.destroy({ id: [42, 43] }, { returnSelect: ['name'] });
// products = [{ id: 42, name: 'Widget' }, { id: 43, name: 'Gadget' }]
```

> The primary key is always included. Pass an empty array to return only the primary key.

---

---
url: /bigal/getting-started.md
description: >-
  Install BigAl, define your first model with decorators, initialize a
  repository, and run type-safe PostgreSQL queries.
---

# Getting Started

## Install

```sh
npm install bigal
```

You also need a PostgreSQL driver:

```sh
# Option 1: postgres-pool (recommended)
npm install postgres-pool

# Option 2: node-postgres
npm install pg

# Option 3: Neon serverless
npm install @neondatabase/serverless
```

## Define a model

Models extend `Entity` and use decorators to map to database tables.

```ts
import { column, primaryColumn, table, Entity } from 'bigal';

@table({ name: 'products' })
export class Product extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ type: 'string', required: true })
  public name!: string;

  @column({ type: 'string' })
  public sku?: string;

  @column({ type: 'integer', required: true, name: 'price_cents' })
  public priceCents!: number;
}
```

## Initialize repositories

Pass your models and a connection pool to `initialize()`. It returns a map of repositories keyed by model name.

```ts
import { initialize, Repository } from 'bigal';
import { Pool } from 'postgres-pool';
import { Product } from './Product';

const pool = new Pool('postgres://localhost/mydb');

const repos = initialize({
  models: [Product],
  pool,
});

const productRepository = repos.Product as Repository<Product>;
```

## Run your first query

Queries use a fluent builder and are `PromiseLike` — just `await` the chain.

```ts
// Find all products with price >= 1000 cents, sorted by name
const products = await productRepository
  .find()
  .where({ priceCents: { '>=': 1000 } })
  .sort('name asc')
  .limit(10);

// Find one product by ID
const product = await productRepository.findOne().where({ id: 42 });

// Count matching records
const count = await productRepository.count().where({ sku: { '!': null } });
```

## Using with AI assistants

BigAl provides an agent skill for AI-powered development tools. Install it in your project to give
your AI assistant BigAl-specific guidance:

```sh
npx skills add bigalorm/bigal
```

Machine-readable documentation is also available:

* [llms.txt](/llms.txt) — structured overview
* [llms-full.txt](/llms-full.txt) — complete documentation in a single file

## Next steps

* [Models](/guide/models) — decorators, relationships, and Entity types
* [Querying](/guide/querying) — operators, pagination, JSONB, and more
* [CRUD Operations](/guide/crud-operations) — create, update, and destroy
* [API Reference](/reference/api) — all exports and method signatures

---

---
url: /bigal/advanced/known-issues.md
description: >-
  Known issues and workarounds — optional collections, NotEntity for JSON
  objects with id fields, and DEBUG_BIGAL logging.
---

# Known Issues

## Entity collections must be optional

Collection properties (one-to-many, many-to-many) must be declared as optional. They are only present after `.populate()` and will cause `QueryResult` type errors if required:

```ts
// Correct
@column({ collection: () => 'Product', via: 'store' })
public products?: Product[];

// Incorrect — causes type issues
@column({ collection: () => 'Product', via: 'store' })
public products!: Product[];
```

## Non-entity objects with id fields

If a JSON column contains objects with an `id` property, TypeScript may mistake them for BigAl entities. Wrap the type with `NotEntity<T>`:

```ts
import type { NotEntity } from 'bigal';

interface IMyJsonType {
  id: string;
  foo: string;
}

@column({ type: 'json' })
public metadata?: NotEntity<IMyJsonType>;
```

Without `NotEntity`, BigAl's type system treats the type as an entity relationship, which leads to incorrect type narrowing in `QueryResult`.

## Debugging queries

Set the `DEBUG_BIGAL` environment variable to see generated SQL:

```sh
DEBUG_BIGAL=true node app.js
```

This logs all SQL statements and parameter values to the console.

---

---
url: /bigal/guide/models.md
description: >-
  Define PostgreSQL tables as TypeScript classes with decorators for columns,
  primary keys, relationships, and automatic timestamps.
---

# Models

Models map TypeScript classes to PostgreSQL tables. Every model extends `Entity` and uses decorators for table and column configuration.

## Table decorator

Use `@table()` to bind a class to a database table:

```ts
import { table, Entity } from 'bigal';

@table({ name: 'products' })
export class Product extends Entity {
  // columns go here
}
```

Options:

| Option       | Type      | Description                                              |
| ------------ | --------- | -------------------------------------------------------- |
| `name`       | `string`  | Database table or view name                              |
| `schema`     | `string`  | PostgreSQL schema (default: `public`)                    |
| `readonly`   | `boolean` | If `true`, `initialize()` returns a `ReadonlyRepository` |
| `connection` | `string`  | Named connection key (for multi-database setups)         |

## Column decorators

### `@primaryColumn()`

Marks the primary key column:

```ts
import { primaryColumn } from 'bigal';

@primaryColumn({ type: 'integer' })
public id!: number;
```

### `@column()`

Defines a regular column:

```ts
import { column } from 'bigal';

@column({ type: 'string', required: true })
public name!: string;

@column({ type: 'string' })
public sku?: string;
```

### `@createDateColumn()`

Automatically set on insert:

```ts
import { createDateColumn } from 'bigal';

@createDateColumn()
public createdAt!: Date;
```

### `@updateDateColumn()`

Automatically set on update:

```ts
import { updateDateColumn } from 'bigal';

@updateDateColumn()
public updatedAt!: Date;
```

### `@versionColumn()`

Auto-incrementing version for optimistic locking:

```ts
import { versionColumn } from 'bigal';

@versionColumn()
public version!: number;
```

## Column options

| Option       | Type           | Description                                                                                                                                             |
| ------------ | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `type`       | `string`       | Column type: `'string'`, `'integer'`, `'float'`, `'boolean'`, `'date'`, `'datetime'`, `'json'`, `'string[]'`, `'integer[]'`, `'float[]'`, `'boolean[]'` |
| `name`       | `string`       | Database column name (if different from property name)                                                                                                  |
| `required`   | `boolean`      | If `true`, value must not be null                                                                                                                       |
| `defaultsTo` | `any`          | Default value                                                                                                                                           |
| `model`      | `() => string` | Foreign key relationship (many-to-one)                                                                                                                  |
| `collection` | `() => string` | Inverse relationship (one-to-many or many-to-many)                                                                                                      |
| `through`    | `() => string` | Join table for many-to-many                                                                                                                             |
| `via`        | `string`       | Property on related model that holds the foreign key                                                                                                    |

## Relationships

### Many-to-one

Use `model` when the current entity holds the foreign key:

```ts
import { column, Entity, primaryColumn, table } from 'bigal';
import type { Store } from './Store';

@table({ name: 'products' })
export class Product extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ model: () => 'Store', name: 'store_id' })
  public store!: number | Store;
}
```

The property type is `number | Store` — it holds the foreign key when not populated, or the full entity after `.populate()`.

### One-to-many

Use `collection` on the inverse side:

```ts
@column({ collection: () => 'Product', via: 'store' })
public products?: Product[];
```

Collections **must** be optional (`?`) since they are only present after `.populate()`.

### Many-to-many

Use `collection` with `through` for join tables:

```ts
@column({
  collection: () => 'Category',
  through: () => 'ProductCategory',
  via: 'product',
})
public categories?: Category[];
```

See [Relationships](/guide/relationships) for complete examples including join tables and self-referencing models.

## Entity base class

All models extend `Entity`, which provides no properties by itself — it serves as a marker for BigAl's type system to distinguish ORM entities from plain objects.

## NotEntity\<T>

If a JSON column contains objects with an `id` field, TypeScript may mistake them for BigAl entities. Wrap the type with `NotEntity<T>`:

```ts
import type { NotEntity } from 'bigal';

interface IMyJsonType {
  id: string;
  foo: string;
}

@column({ type: 'json' })
public metadata?: NotEntity<IMyJsonType>;
```

---

---
url: /bigal/guide/querying.md
description: >-
  Fluent query builder for find, findOne, and count with WHERE operators, JSONB
  querying, pagination, sorting, DISTINCT ON, and populate.
---

# Querying

BigAl provides `findOne()`, `find()`, and `count()` methods on repositories. Queries use a fluent builder pattern —
each method returns a new immutable instance, and queries are `PromiseLike` so you can `await` them directly.

## findOne

Returns a single record or `null`:

```ts
const product = await productRepository.findOne().where({ id: 42 });
```

### Query projection

Select specific columns:

```ts
const product = await productRepository
  .findOne({
    select: ['name', 'sku'],
  })
  .where({ id: 42 });
```

### Pool override

Use an explicit connection pool:

```ts
const product = await productRepository
  .findOne({
    pool: poolOverride,
  })
  .where({ id: 42 });
```

## find

Returns an array of records:

```ts
const products = await productRepository.find().where({ store: storeId });
```

## count

Returns the number of matching records:

```ts
const count = await productRepository.count().where({
  name: { like: 'Widget%' },
});
```

## Where operators

### String matching

All string operators use case-insensitive matching (`ILIKE`) and accept arrays for OR conditions.

| Operator     | Description       | SQL Pattern |
| ------------ | ----------------- | ----------- |
| `like`       | Raw ILIKE pattern | As provided |
| `contains`   | Substring match   | `%value%`   |
| `startsWith` | Prefix match      | `value%`    |
| `endsWith`   | Suffix match      | `%value`    |

```ts
await productRepository.find().where({ name: { contains: 'widget' } });
// SQL: WHERE name ILIKE '%widget%'

await productRepository.find().where({ name: { startsWith: 'Pro' } });
// SQL: WHERE name ILIKE 'Pro%'
```

### Comparison operators

| Operator | Description           |
| -------- | --------------------- |
| `<`      | Less than             |
| `<=`     | Less than or equal    |
| `>`      | Greater than          |
| `>=`     | Greater than or equal |

```ts
await productRepository.find().where({ price: { '>=': 100 } });

// Multiple operators on same field (AND)
await productRepository.find().where({
  createdAt: { '>=': startDate, '<': endDate },
});
```

### Array values (IN)

```ts
await personRepository.find().where({ age: [22, 23, 24] });
// SQL: WHERE age IN ($1, $2, $3)
```

### Negation (`!`)

```ts
await productRepository.find().where({ status: { '!': 'discontinued' } });
// SQL: WHERE status <> $1

await productRepository.find().where({ status: { '!': ['a', 'b'] } });
// SQL: WHERE status NOT IN ($1, $2)

await productRepository.find().where({ deletedAt: { '!': null } });
// SQL: WHERE deleted_at IS NOT NULL
```

### OR conditions

```ts
await personRepository.find().where({
  or: [{ firstName: 'Walter' }, { lastName: 'White' }],
});
// SQL: WHERE (first_name = $1) OR (last_name = $2)
```

### AND with nested OR

```ts
await personRepository.find().where({
  and: [{ or: [{ firstName: 'Walter' }, { lastName: 'White' }] }, { or: [{ firstName: 'Jesse' }, { lastName: 'Pinkman' }] }],
});
```

## JSONB querying

BigAl supports querying properties within JSON/JSONB columns using PostgreSQL's `->>` operator.

### Property equality

```ts
await repo.find().where({ bar: { theme: 'dark' } });
// SQL: WHERE "bar"->>'theme'=$1
```

### Comparisons on JSON properties

Numeric and boolean values are automatically cast:

```ts
await repo.find().where({ bar: { retryCount: { '>=': 3 } } });
// SQL: WHERE ("bar"->>'retryCount')::numeric>=$1

await repo.find().where({ bar: { active: true } });
// SQL: WHERE ("bar"->>'active')::boolean=$1
```

### Nested paths

Intermediate segments use `->`, final segment uses `->>`:

```ts
await repo.find().where({ bar: { failure: { stage: 'transcription' } } });
// SQL: WHERE "bar"->'failure'->>'stage'=$1

await repo.find().where({ bar: { a: { b: { c: 'value' } } } });
// SQL: WHERE "bar"->'a'->'b'->>'c'=$1
```

### Null checks

Check if a JSONB property is null or not null:

```ts
await repo.find().where({ bar: { theme: null } });
// SQL: WHERE "bar"->>'theme' IS NULL

await repo.find().where({ bar: { theme: { '!': null } } });
// SQL: WHERE "bar"->>'theme' IS NOT NULL
```

Note that `IS NULL` on a JSONB property is true both when the key is missing from the object and when it is
explicitly set to `null`. This matches PostgreSQL's behavior — the `->>` operator returns `NULL` in both cases.

Properties set to `undefined` in a where clause are silently ignored (standard JavaScript — `undefined` values are
dropped by `Object.entries`). To query for missing or null properties, always use `null` explicitly.

### JSONB containment

Combine `contains` with property access:

```ts
await repo.find().where({
  bar: { contains: { type: 'recovery' }, retryCount: { '<': 3 } },
});
// SQL: WHERE "bar"@>$1::jsonb AND ("bar"->>'retryCount')::numeric<$2
```

## Sorting

### String syntax

```ts
await productRepository.find().where({}).sort('name asc');
await productRepository.find().where({}).sort('name asc, createdAt desc');
```

### Object syntax

```ts
await productRepository.find().where({}).sort({ name: 1 }); // ASC
await productRepository.find().where({}).sort({ name: 1, createdAt: -1 }); // ASC, DESC
```

## Pagination

### skip and limit

```ts
await productRepository.find().where({}).skip(20).limit(10);
```

### paginate

```ts
const page = 2;
const pageSize = 25;
await productRepository.find().where({}).paginate(page, pageSize);
```

### withCount

Get paginated results with total count in a single query using `COUNT(*) OVER()`:

```ts
const { results, totalCount } = await productRepository.find().where({ store: storeId }).sort('name').limit(10).skip(20).withCount();

const totalPages = Math.ceil(totalCount / 10);
```

## DISTINCT ON

PostgreSQL's `DISTINCT ON` returns one row per unique combination of columns:

```ts
// Most recently created product per store
const latest = await productRepository.find().distinctOn(['store']).sort('store').sort('createdAt desc');
```

Requirements:

* `ORDER BY` is required and must start with the `DISTINCT ON` columns
* Cannot be combined with `withCount()`

## Populate

Load related entities:

```ts
const product = await productRepository
  .findOne()
  .where({ id: 42 })
  .populate('store', { select: ['name'] });

// product.store is the full Store entity
console.log(product.store.name);
```

## toJSON

Return plain objects without class prototypes:

```ts
const product = await productRepository.findOne().where({ id: 42 }).toJSON();
```

---

---
url: /bigal/guide/relationships.md
description: >-
  Many-to-one, one-to-many, and many-to-many relationships with decorators,
  QueryResult type narrowing, and populate options.
---

# Relationships

BigAl supports three relationship patterns via the `@column` decorator: many-to-one, one-to-many, and many-to-many.

## Many-to-one (model)

Use `model` when the current entity holds the foreign key:

```ts
import { column, Entity, primaryColumn, table } from 'bigal';
import type { Store } from './Store';

@table({ name: 'products' })
export class Product extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ type: 'string', required: true })
  public name!: string;

  @column({ model: () => 'Store', name: 'store_id' })
  public store!: number | Store;
}
```

* The property type is `number | Store` — foreign key when not populated, full entity after `.populate()`
* Use `name: 'store_id'` when the database column differs from the property name
* Reference the model by string name (`'Store'`) to avoid circular imports
* Model names are case-insensitive

## One-to-many (collection)

Use `collection` on the inverse side:

```ts
import { column, Entity, primaryColumn, table } from 'bigal';
import type { Product } from './Product';

@table({ name: 'stores' })
export class Store extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ type: 'string' })
  public name?: string;

  @column({ collection: () => 'Product', via: 'store' })
  public products?: Product[];
}
```

* `via` references the property name on the related model (not the database column)
* Collections **must** be optional (`?`) — they are only present after `.populate()`

## Many-to-many (through)

Use `through` for relationships that require a join table:

```ts
// Product.ts
@table({ name: 'products' })
export class Product extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ type: 'string', required: true })
  public name!: string;

  @column({
    collection: () => 'Category',
    through: () => 'ProductCategory',
    via: 'product',
  })
  public categories?: Category[];
}
```

```ts
// Category.ts
@table({ name: 'categories' })
export class Category extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ type: 'string', required: true })
  public name!: string;

  @column({
    collection: () => 'Product',
    through: () => 'ProductCategory',
    via: 'category',
  })
  public products?: Product[];
}
```

```ts
// ProductCategory.ts (join table)
@table({ name: 'product__category' })
export class ProductCategory extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ model: () => 'Product', name: 'product_id' })
  public product!: number | Product;

  @column({ model: () => 'Category', name: 'category_id' })
  public category!: number | Category;
}
```

* `through` specifies the join table model
* `via` references the property on the join table that points back to this entity
* The join table must have `model` relationships to both sides

## Self-referencing relationships

Entities can reference themselves for hierarchical data:

```ts
@table({ name: 'categories' })
export class Category extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ type: 'string', required: true })
  public name!: string;

  @column({ model: () => 'Category', name: 'parent_id' })
  public parent?: number | Category | null;

  @column({ collection: () => 'Category', via: 'parent' })
  public children?: Category[];
}
```

## QueryResult type narrowing

When you query entities, BigAl returns `QueryResult<T>` which automatically narrows relationship fields:

```ts
const product = await productRepository.findOne().where({ id: 1 });

// product.store is `number`, not `number | Store`
// QueryResult narrows the union automatically
console.log(product.store); // number (the foreign key ID)
```

The narrowing rules:

| Entity property type      | QueryResult type     |
| ------------------------- | -------------------- |
| `number \| Store`         | `number`             |
| `number \| Store \| null` | `number \| null`     |
| `Product[]` (collection)  | Excluded from result |

### Using QueryResult in type definitions

Use `Pick<QueryResult<T>, ...>` instead of `Pick<T, ...>` for derived types:

```ts
import type { QueryResult } from 'bigal';

// Correct: store is `number`
type ProductSummary = Pick<QueryResult<Product>, 'id' | 'name' | 'store'>;

// Wrong: store is `number | Store`
type ProductSummaryWrong = Pick<Product, 'id' | 'name' | 'store'>;
```

## QueryResultPopulated

For type safety with populated relations:

```ts
import type { QueryResultPopulated } from 'bigal';

// store is QueryResult<Store>
type ProductWithStore = QueryResultPopulated<Product, 'store'>;
```

## Populate with junction table filtering

For many-to-many relationships, you can filter and sort by columns on the junction table:

```ts
const compilation = await compilationRepository
  .findOne()
  .where({ id: compilationId })
  .populate('tracks', {
    select: ['name', 'duration'],
    where: { isPublished: true },
    through: {
      where: { revisionDeleted: null },
      sort: 'ordering asc',
    },
  });
```

* `through.where` filters junction table records
* `through.sort` orders populated items by junction table columns
* When `through.sort` is specified, it takes precedence over the target entity sort

## Best practices

1. **Use `QueryResult<T>` for return types** — avoids union type ambiguity
2. **Use string references for model names** — prevents circular imports
3. **Mark collections as optional** — they are `undefined` unless populated
4. **Avoid type assertions** — `QueryResult` narrows types automatically
5. **Use `.toJSON()` for serializable results** — strips class prototypes

---

---
url: /bigal/guide/subqueries-and-joins.md
description: >-
  Type-safe subqueries for WHERE IN, EXISTS, and scalar comparisons. Model
  joins, subquery joins, aggregates, GROUP BY, and HAVING.
---

# Subqueries and Joins

BigAl supports subqueries for WHERE clauses, scalar comparisons, and joins. All subqueries are type-safe and composable.

## Creating subqueries

Use the `subquery()` function:

```ts
import { subquery } from 'bigal';

const activeStores = subquery(storeRepository).select(['id']).where({ isActive: true });
```

`SubqueryBuilder` methods:

| Method                | Description                         |
| --------------------- | ----------------------------------- |
| `select(columns)`     | Columns and/or aggregates to select |
| `where(query)`        | Filter rows                         |
| `sort(value)`         | Order results                       |
| `limit(n)`            | Limit rows                          |
| `groupBy(columns)`    | Group for aggregation               |
| `having(condition)`   | Filter groups by aggregate values   |
| `distinctOn(columns)` | PostgreSQL DISTINCT ON              |

## WHERE IN / NOT IN

```ts
const activeStores = subquery(storeRepository).select(['id']).where({ isActive: true });

// WHERE IN
const products = await productRepository.find().where({
  store: { in: activeStores },
});
// SQL: WHERE "store_id" IN (SELECT "id" FROM "stores" WHERE "is_active"=$1)

// WHERE NOT IN
const products = await productRepository.find().where({
  store: { '!': { in: activeStores } },
});
```

## WHERE EXISTS / NOT EXISTS

```ts
const hasProducts = subquery(productRepository).where({ name: { like: 'Widget%' } });

// EXISTS
const stores = await storeRepository.find().where({
  exists: hasProducts,
});

// NOT EXISTS
const stores = await storeRepository.find().where({
  '!': { exists: hasProducts },
});
```

If no columns are selected in the subquery, it defaults to `SELECT 1`.

## Scalar subquery comparisons

Compare column values against single-value subquery results:

```ts
const avgPrice = subquery(productRepository).avg('price');

const expensiveProducts = await productRepository.find().where({
  price: { '>': avgPrice },
});
// SQL: WHERE "price">(SELECT AVG("price") FROM "products")
```

Supported operators: `>`, `>=`, `<`, `<=`, `'!'` (not equal), or direct equality.

```ts
// Equal to max price
.where({ price: subquery(productRepository).max('price') })

// Not equal to min price
.where({ price: { '!': subquery(productRepository).min('price') } })
```

## Model joins

Join to related entities defined in your model:

```ts
// Inner join
const products = await productRepository
  .find()
  .join('store')
  .where({ store: { name: 'Acme' } });
// SQL: SELECT "products".* FROM "products"
//   INNER JOIN "stores" ON "products"."store_id"="stores"."id"
//   WHERE "stores"."name"=$1

// Left join
const products = await productRepository
  .find()
  .leftJoin('store')
  .where({ store: { name: 'Acme' } });

// Custom alias
const products = await productRepository
  .find()
  .join('store', 'primaryStore')
  .where({ primaryStore: { name: 'Acme' } });

// Additional ON conditions (left join only)
const products = await productRepository.find().leftJoin('store', 'activeStore', { isActive: true });
```

## Subquery joins

Join to subquery results:

```ts
const productCounts = subquery(productRepository)
  .select(['store', (sb) => sb.count().as('productCount')])
  .groupBy(['store']);

// Inner join
const stores = await storeRepository.find().join(productCounts, 'stats', { on: { id: 'store' } });
// SQL: SELECT "stores".* FROM "stores"
//   INNER JOIN (
//     SELECT "store_id" AS "store", COUNT(*) AS "productCount"
//     FROM "products" GROUP BY "store_id"
//   ) AS "stats" ON "stores"."id"="stats"."store"

// Left join
const stores = await storeRepository.find().leftJoin(productCounts, 'stats', { on: { id: 'store' } });
```

Multiple ON conditions:

```ts
const categoryStats = subquery(productRepository)
  .select(['store', 'category', (sb) => sb.count().as('count')])
  .groupBy(['store', 'category']);

const stores = await storeRepository.find().join(categoryStats, 'stats', { on: { id: 'store', categoryId: 'category' } });
```

## Sorting on joined columns

Use dot notation to sort by joined table columns:

```ts
// Model join
const products = await productRepository.find().join('store').sort('store.name asc');

// Subquery join
const stores = await storeRepository
  .find()
  .join(productCounts, 'stats', { on: { id: 'store' } })
  .sort('stats.productCount desc');
```

## Aggregate functions

Available in subquery selects:

| Function                   | Description            |
| -------------------------- | ---------------------- |
| `count()`                  | Count all rows         |
| `count(column)`            | Count non-null values  |
| `count(column).distinct()` | Count distinct values  |
| `sum(column)`              | Sum numeric values     |
| `avg(column)`              | Average numeric values |
| `max(column)`              | Maximum value          |
| `min(column)`              | Minimum value          |

```ts
const stats = subquery(productRepository)
  .select(['store', (sb) => sb.count().as('totalProducts'), (sb) => sb.sum('price').as('totalValue'), (sb) => sb.avg('price').as('avgPrice'), (sb) => sb.count('name').distinct().as('uniqueNames')])
  .groupBy(['store']);
```

If `.as()` is not called, aggregates use their function name as the alias (e.g. `count`, `sum`).

## GROUP BY and HAVING

```ts
const popularCategories = subquery(productRepository)
  .select(['category', (sb) => sb.count().as('productCount')])
  .groupBy(['category'])
  .having({ productCount: { '>': 10 } });
// SQL: ... GROUP BY "category_id" HAVING COUNT(*)>10
```

HAVING operators:

| Syntax                   | SQL                |
| ------------------------ | ------------------ |
| `{ alias: 5 }`           | `HAVING AGG(*)=5`  |
| `{ alias: { '>': 5 } }`  | `HAVING AGG(*)>5`  |
| `{ alias: { '>=': 5 } }` | `HAVING AGG(*)>=5` |
| `{ alias: { '<': 5 } }`  | `HAVING AGG(*)<5`  |
| `{ alias: { '<=': 5 } }` | `HAVING AGG(*)<=5` |
| `{ alias: { '!=': 5 } }` | `HAVING AGG(*)<>5` |

Multiple conditions:

```ts
.having({ productCount: { '>=': 5, '<=': 100 } })
// SQL: HAVING COUNT(*)>=5 AND COUNT(*)<=100
```

## Type-safe subquery sorting

Annotate aggregate callbacks with `TypedAggregateExpression` for compile-time column validation:

```ts
import type { TypedAggregateExpression } from 'bigal';

const productCounts = subquery(productRepository)
  .select([
    'store',
    (sb): TypedAggregateExpression<'productCount'> => sb.count().as('productCount'),
  ])
  .groupBy(['store']);

const stores = await storeRepository.find()
  .join(productCounts, 'stats', { on: { id: 'store' } })
  .sort('stats.productCount desc'); // Type-safe!

// @ts-expect-error - 'invalidColumn' is not a selected column
.sort('stats.invalidColumn desc');
```

## DISTINCT ON in subqueries

Use `distinctOn()` for "greatest-per-group" patterns:

```ts
const latestProducts = subquery(productRepository).select(['store', 'name', 'createdAt']).distinctOn(['store']).sort('store').sort('createdAt desc');

const stores = await storeRepository.find().join(latestProducts, 'latestProduct', { on: { id: 'store' } });
```

See [Querying > DISTINCT ON](/guide/querying#distinct-on) for constraints and usage with top-level queries.

---

---
url: /bigal/guide/views.md
description: >-
  Map PostgreSQL views to readonly models with ReadonlyRepository. Supports
  inheritance, schema options, and all query features.
---

# Views and Readonly Repositories

BigAl does not distinguish between tables and views. Both use the `@table()` decorator. Setting `readonly: true` causes
`initialize()` to return a `ReadonlyRepository` — which omits `create`, `update`, and `destroy` methods,
catching accidental writes at compile time.

BigAl does not create or manage views. Create them in PostgreSQL via your migration tool.

## Defining a view model

### Standalone model

```ts
import { column, primaryColumn, table, Entity } from 'bigal';

@table({
  name: 'product_summaries',
  readonly: true,
})
export class ProductSummary extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({ type: 'string', required: true })
  public name!: string;

  @column({ type: 'string', required: true, name: 'store_name' })
  public storeName!: string;

  @column({ type: 'integer', required: true, name: 'category_count' })
  public categoryCount!: number;
}
```

Corresponding view in PostgreSQL:

```sql
CREATE VIEW product_summaries AS
SELECT
  p.id,
  p.name,
  s.name AS store_name,
  COUNT(pc.category_id) AS category_count
FROM products p
JOIN stores s ON s.id = p.store_id
LEFT JOIN product_categories pc ON pc.product_id = p.id
GROUP BY p.id, p.name, s.name;
```

### Inheriting from an existing model

If the view has the same columns as an existing table, extend the model to reuse column definitions:

```ts
import { table } from 'bigal';
import { Product } from './Product';

@table({
  name: 'readonly_products',
  readonly: true,
})
export class ReadonlyProduct extends Product {}
```

### Schema option

For views in a non-default schema:

```ts
@table({
  schema: 'reporting',
  name: 'product_summaries',
  readonly: true,
})
export class ProductSummary extends Entity {
  // ...
}
```

## Initializing the repository

Include the view model in `initialize()`. BigAl creates a `ReadonlyRepository` automatically:

```ts
import { initialize, ReadonlyRepository } from 'bigal';
import { Product, Store, ProductSummary } from './models';

const repos = initialize({
  models: [Product, Store, ProductSummary],
  pool,
  readonlyPool,
});

const productSummaryRepository = repos.ProductSummary as ReadonlyRepository<ProductSummary>;
```

## Querying

Readonly repositories support the same query methods as regular repositories:

```ts
const summaries = await productSummaryRepository
  .find()
  .where({ storeName: { contains: 'Acme' } })
  .sort('categoryCount desc')
  .limit(10);

const summary = await productSummaryRepository.findOne().where({ id: 42 });

const count = await productSummaryRepository.count().where({ categoryCount: { '>': 5 } });
```

All query features work: `where` operators, `sort`, `skip`, `limit`, `paginate`, `populate`, `join`, `withCount`, and `distinctOn`.

## Available methods

| Method      | Repository | ReadonlyRepository |
| ----------- | ---------- | ------------------ |
| `findOne()` | Yes        | Yes                |
| `find()`    | Yes        | Yes                |
| `count()`   | Yes        | Yes                |
| `create()`  | Yes        | No                 |
| `update()`  | Yes        | No                 |
| `destroy()` | Yes        | No                 |