---
url: /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
```

## Runtimes

BigAl runs on Node.js 22.11+, Bun, and Deno 2. Models use TypeScript legacy decorators, so enable them in your
compiler options. For Node.js and Bun, add this to `tsconfig.json`:

```json
{
  "compilerOptions": {
    "experimentalDecorators": true
  }
}
```

For Deno, put the same `compilerOptions` in `deno.json`.

## 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: /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;
```

### Vector (pgvector)

Declare a `VECTOR(n)` column with `type: 'vector'`. Values are `number[] | null`:

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

@column({ type: 'vector', dimensions: 1536 })
public embedding?: number[];
```

Requires the [pgvector](https://github.com/pgvector/pgvector) extension. The `dimensions` option is
informational - BigAl does not issue DDL. See
[Querying > Vector distance queries](/guide/querying#vector-distance-queries) for sorting and
filtering by similarity.

### `@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[]'`, `'vector'` |
| `name`       | `string`       | Database column name (if different from property name)                                                                                                              |
| `required`   | `boolean`      | If `true`, value must not be null                                                                                                                                   |
| `defaultsTo` | `any`          | Default value                                                                                                                                                       |
| `dimensions` | `number`       | Number of dimensions for `'vector'` columns. Informational only - BigAl does not issue DDL                                                                          |
| `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: /guide/querying.md
description: >-
  Fluent query builder for find, findOne, and count with filters, pagination,
  sorting, opt-in row locks, DISTINCT ON, and populate.
---

# Querying

BigAl provides `findOne()`, `find()`, and `count()` methods on repositories. Queries use a fluent builder pattern -
chained methods build up one query object, and queries are `PromiseLike` so you can `await` the chain directly.
Each call to `find()`, `findOne()`, or `count()` starts a fresh query. See the
[API reference](/reference/api#query-builder-methods) for the full list of chainable methods.

## findOne

Returns a single record or `null`:

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

### Query projection

Pass `select` to return only the columns you need instead of every column (the default).
This shrinks the SELECT list, reduces bytes transferred, and lowers hydration cost.
It is a large win for wide rows, big JSON blobs, or vector/embedding columns you do not need on a given path.
`find()` and `populate()` accept the same option.

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

// find() takes the same option
const products = await productRepository.find({ select: ['name', 'sku'] }).where({ store: storeId });
```

`.select()` is also available as a chained method and narrows the result type to the picked columns:

```ts
const products = await productRepository.find().select(['name', 'sku']).where({ store: storeId });
// products: Pick<QueryResult<Product>, 'name' | 'sku'>[]
```

The primary key column is always included in the generated SQL, even when it is not in the `select` list.

### 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%' },
});
```

If you only need to know whether a match exists, use `count()` instead of `findOne()` - it performs better since it doesn't select or hydrate a row:

```ts
const exists = (await productRepository.count().where({ sku: 'ABC123' })) > 0;
```

## Where operators

Calling `.where()` more than once replaces the previous filter - combine conditions in a single object
instead. (`.sort()` is the opposite: repeated calls [append](#multiple-sort-calls).)

### 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

Direction is `asc` or `desc` (case-insensitive) and defaults to ascending when omitted:

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

### Object syntax

Values can be `1`/`-1` or `'asc'`/`'desc'`:

```ts
await productRepository.find().where({}).sort({ name: 1 }); // ASC
await productRepository.find().where({}).sort({ name: 1, createdAt: -1 }); // ASC, DESC
await productRepository.find().where({}).sort({ name: 'asc', createdAt: 'desc' }); // Same as above
```

### Multiple sort calls

Repeated `.sort()` calls append sort columns instead of replacing them - these are equivalent:

```ts
await productRepository.find().sort('store').sort('createdAt desc');
await productRepository.find().sort('store, createdAt desc');
```

## Vector distance queries

BigAl supports nearest-neighbor queries on columns declared with `@column({ type: 'vector', dimensions: n })`,
backed by the [pgvector](https://github.com/pgvector/pgvector) extension. Four distance metrics are
available: `cosine`, `l2`, `l1`, and `innerProduct`. The `l1` metric requires pgvector >= 0.7.0.

| Metric         | PostgreSQL operator | Description               |
| -------------- | ------------------- | ------------------------- |
| `cosine`       | `<=>`               | Cosine distance (default) |
| `l2`           | `<->`               | Euclidean distance        |
| `l1`           | `<+>`               | Manhattan distance        |
| `innerProduct` | `<#>`               | Negative inner product    |

### Sorting by distance

Use the `nearestTo` sort to order results by vector similarity:

```ts
const similar = await documentRepository
  .find()
  .where({ title: { contains: 'biology' } })
  .sort({ embedding: { nearestTo: queryVector, metric: 'cosine' } })
  .limit(10);
// SQL: ... WHERE "title" ILIKE $1 ORDER BY "embedding" <=> $2 LIMIT 10
```

The `metric` option defaults to `'cosine'` if omitted. An unknown metric throws a `QueryError`.

### Filtering by distance

Combine `nearestTo` in the where clause with a distance threshold:

```ts
const nearby = await documentRepository
  .find()
  .where({
    embedding: {
      nearestTo: queryVector,
      metric: 'cosine',
      distance: { '<': 0.5 },
    },
  })
  .sort({ embedding: { nearestTo: queryVector, metric: 'cosine' } })
  .limit(10);
// SQL: ... WHERE "embedding" <=> $1 < $2 ORDER BY "embedding" <=> $3 LIMIT 10
```

At least one `distance` bound is required in where clauses; multiple bounds are combined with `AND`
(for example `distance: { '>': 0.1, '<': 0.5 }` finds a distance band). Vectors must be non-empty
arrays of finite numbers.

### Equality and writes

Vector values round-trip as `number[]`. Where clauses compare whole vectors, and create/update
serialize the array to pgvector's text format:

```ts
await documentRepository.create({ title: 'foo', embedding: [0.1, 0.2, 0.3] }); // Sends '[0.1,0.2,0.3]'
await documentRepository.findOne({ embedding: queryVector }); // WHERE "embedding"=$1
```

## Pagination

### skip and limit

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

### paginate

`paginate({ page, limit })` is shorthand for `.skip((page - 1) * limit).limit(limit)`. `page` starts at 1;
values below 1 are treated as page 1.

```ts
await productRepository.find().where({}).paginate({ page: 2, limit: 25 });
// SQL: ... LIMIT 25 OFFSET 25
```

### 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()`

## Row locking

`find()` and `findOne()` support explicit PostgreSQL row locks:

```ts
const product = await productRepository.findOne({ pool: transactionConnection }).where({ id: productId }).lock('update');

const queuedJobs = await jobRepository.find({
  pool: transactionConnection,
  where: { status: 'queued' },
  lock: { mode: 'update', wait: 'skipLocked' },
  limit: 10,
});
```

Modes are `'update'` (`FOR UPDATE`), `'noKeyUpdate'` (`FOR NO KEY UPDATE`), `'share'` (`FOR SHARE`), and `'keyShare'` (`FOR KEY SHARE`). Optional wait behavior is `'nowait'` or `'skipLocked'`:

```ts
await productRepository.findOne({ pool: transactionConnection }).where({ id: productId }).lock('noKeyUpdate', { wait: 'nowait' });
```

A locking read runs on the write pool, or on the `pool` override you pass, and PostgreSQL holds the lock only while a transaction is open on that connection.
BigAl locks only base-table rows. Joins may filter those rows, while `populate()` queries do not inherit the lock.

Locking cannot be combined with `distinctOn()` or `withCount()`.
See [Transactions](/guide/transactions#row-locking) for timeout parameters and safe locking protocols.

## Populate

`populate(propertyName, options?)` loads related entities onto the results. It is available on `find()` and
`findOne()` for any relationship defined with `model`, `collection`, or `through`
(see [Relationships](/guide/relationships)):

```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);
```

`populate()` does not use a SQL `JOIN`.
After the main query resolves, it runs a separate query per populated relation (batched by id and hydrated back onto the results), so `.join()` is not required to populate a relation.
Every matched primary row is returned whether or not the relation exists - an absent to-one is `undefined`, an empty to-many is `[]`.
The populate `where`/`limit` options constrain only the related rows, never the primary results.
Reach for [`.join()`](/guide/subqueries-and-joins#model-joins) only to constrain or sort the primary results by columns on the related table (for example, only products whose store is active).
Without such a constraint, `.populate()` on its own is all you need.

### Populate options

All options are optional and apply to the query for related rows, never to the primary results:

| Option    | Type                | Description                                                                                                            |
| --------- | ------------------- | ---------------------------------------------------------------------------------------------------------------------- |
| `select`  | `string[]`          | Columns to return on the related entities. The primary key is always included.                                         |
| `where`   | `WhereQuery`        | Filter related rows. Accepts the same operators as [`.where()`](#where-operators).                                     |
| `sort`    | `string \| object`  | Order related rows. Same syntax as [`.sort()`](#sorting).                                                              |
| `skip`    | `number`            | Skip related rows. Collections only.                                                                                   |
| `limit`   | `number`            | Maximum related rows to return. Collections only.                                                                      |
| `pool`    | `PoolLike`          | Connection pool for the populate query. Defaults to the main query's pool.                                             |
| `through` | `{ where?, sort? }` | Filter and order by junction table columns. Many-to-many relations only. See [below](#many-to-many-through-relations). |

### To-one relations

For `model` relations, `where` acts as a condition on the related row - when the row does not match, the
property is `undefined`. `skip` and `limit` are ignored.

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

// product.store is the Store when it is active, otherwise undefined
```

### Collections

For `collection` relations, every option applies to the related rows:

```ts
const store = await storeRepository
  .findOne()
  .where({ id: storeId })
  .populate('products', {
    select: ['name', 'sku'],
    where: { status: 'available' },
    sort: 'name asc',
    limit: 10,
  });

// store.products has at most 10 available products, sorted by name
```

Two caveats when populating collections on `find()` (multiple primary rows):

* BigAl fetches related rows for all primary rows in one query, so `limit` and `skip` apply to the combined
  set, not per primary row. For "top N per parent", populate from `findOne()` or use a
  [DISTINCT ON subquery join](/guide/subqueries-and-joins#distinct-on-in-subqueries).
* A populate `select` must include the relation's `via` property (the foreign key back to the parent).
  BigAl needs it to group rows by parent and throws if it is missing.

### Many-to-many (through) relations

For `through` relations, `through.where` filters junction rows and `through.sort` orders the populated items
by junction table columns. Item order always follows the junction query, so use `through.sort` (not `sort`)
to control ordering:

```ts
const product = await productRepository
  .findOne()
  .where({ id: productId })
  .populate('categories', {
    select: ['name'],
    where: { isActive: true }, // filters categories
    through: {
      where: { isPrimary: true }, // filters junction rows
      sort: 'ordering asc', // orders items by a junction column
    },
  });
```

### Populating multiple relations

Chain `.populate()` once per relation. The populate queries run in parallel:

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

### Populate with a narrowed select

`populate()` adds the relation's foreign key column to an earlier `.select()` automatically. Call `.select()`
before `.populate()` - a later `.select()` replaces the column list and can drop the foreign key the populate
needs:

```ts
const products = await productRepository
  .find()
  .select(['name'])
  .populate('store', { select: ['name'] });
```

### Type narrowing

`.populate()` changes the property's result type from the foreign key to the populated entity, narrowed by
the populate `select` when one is given. Use `QueryResultPopulated<T, K>` to name these types - see
[Relationships > QueryResultPopulated](/guide/relationships#queryresultpopulated).

## toJSON

Return plain objects without class prototypes. Populated relations are plain objects too:

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

---

---
url: /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.
`create()` and `update()` return affected records by default (using `RETURNING *`); `destroy()` does not.
All three accept `returnRecords` and `returnSelect` to shape what comes back.
Return only the columns you need, or skip the returned rows entirely, to reduce bytes over the wire and hydration cost.

## 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, ... }]
```

Passing an array builds a single multi-row `INSERT` statement, one round trip for the whole batch. Prefer this over calling
`create()` in a loop, which issues a separate `INSERT` (and round trip) per record:

```ts
// Correct - one INSERT statement for all rows
const products = await productRepository.create(items);

// Slower - a separate INSERT statement and round trip per item
const products = [];
for (const item of items) {
  products.push(await productRepository.create(item));
}
```

### 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. Unlike `create()` and `update()`, it does not return records by default.
It emits a plain `DELETE` with no `RETURNING` clause and resolves to `void`, which is the cheapest option.

```ts
// Delete a single record (resolves to void)
await productRepository.destroy({ id: 42 });

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

To get the deleted rows back, opt in with `returnRecords: true`:

```ts
const products = await productRepository.destroy({ id: [42, 43] }, { returnRecords: true });
// products = [{ id: 42, ... }, { id: 43, ... }]
```

With query projection (implies `returnRecords`):

```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.

## Pool overrides

Every write method accepts `pool`. This is useful when a helper receives an externally managed transaction connection or a BigAl `TransactionScope`:

```ts
await productRepository.create({ name: 'Widget', store: storeId }, { pool: connection });

const products = await productRepository.update({ id: productIds }, { location: 'A-12' }, { pool: connection });

await productRepository.destroy({ id: obsoleteProductIds }, { pool: connection });
```

Pool-only options preserve each method's default return behavior: single and bulk creates return records, updates return an array, and destroys return `void`.
Combine `pool` with `returnSelect`, `returnRecords`, and `onConflict` as usual.

The override only routes the statement; it does not begin, commit, roll back, or release a transaction. See [Transactions](/guide/transactions).

---

---
url: /guide/transactions.md
description: >-
  Run managed PostgreSQL transactions with typed repositories, explicit row
  locks, isolation levels, and transaction-local timeouts.
---

# Transactions

BigAl can acquire one connection, bind ordinary repositories to it, and manage `BEGIN`, `COMMIT`, `ROLLBACK`, and release for you. Queries inside the callback keep their existing syntax and types.

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

const repositories = {
  Product: productRepository,
  Store: storeRepository,
};

const product = await transaction({ pool, repositories }, async (transactionScope) => {
  const { Product, Store } = transactionScope.repositories;
  const store = await Store.create({ name: 'Warehouse' });

  return Product.create({ name: 'Widget', store: store.id });
});
```

The callback result is returned after the commit succeeds. Throwing from the callback, or encountering a database query error, rolls the transaction back.
For clients with `on` and `removeListener`, BigAl also handles fatal connection errors, including an expired idle transaction timeout, and discards the failed connection.
Queries attempted after that failure reject with the recorded database error, preserving its SQLSTATE when the driver provides one.
The timeout closes the database session; it does not cancel external work already running inside the callback.
Keep the application's pool-level error handler: drivers such as `postgres-pool` can also forward checked-out client errors to the pool.
Adapters exposing only `query()` and `release()` remain supported and must report their connection failures through rejected queries.

## Parameters and callback scope

`transaction(options, callback)` accepts:

| Option                       | Type                                                    | Required | Description                                                                    |
| ---------------------------- | ------------------------------------------------------- | -------- | ------------------------------------------------------------------------------ |
| `pool`                       | `TransactionPool`                                       | Yes      | Write pool with `connect()` support                                            |
| `repositories`               | `Record<string, Repository \| ReadonlyRepository>`      | Yes      | Standard repositories whose write pool is `pool`, from any `initialize()` call |
| `isolationLevel`             | `'readCommitted' \| 'repeatableRead' \| 'serializable'` | No       | PostgreSQL isolation level; the database default is retained when omitted      |
| `lockTimeoutMs`              | `number`                                                | No       | Transaction-local maximum wait for any lock acquisition                        |
| `statementTimeoutMs`         | `number`                                                | No       | Transaction-local maximum duration for each statement                          |
| `idleInTransactionTimeoutMs` | `number`                                                | No       | Transaction-local maximum idle time before PostgreSQL closes the session       |

Timeout values must be integers from `0` through `2_147_483_647`. An explicit `0` disables that PostgreSQL timeout for the transaction.
BigAl supplies no timeout defaults and emits no timeout-setting SQL for omitted options.

The callback receives:

| Property       | Type                                      | Description                                                    |
| -------------- | ----------------------------------------- | -------------------------------------------------------------- |
| `repositories` | Same keys and model capabilities as input | Fresh repositories routed to the checked-out connection        |
| `query()`      | `PoolLike['query']`                       | Guarded, parameterized SQL escape hatch on the same connection |

Repository keys are preserved, including custom names:

```ts
await transaction(
  {
    pool,
    repositories: {
      inventory: productRepository,
      locations: storeRepository,
    },
  },
  async ({ repositories }) => {
    const store = await repositories.locations.findOne().where({ id: storeId });
    if (!store) throw new Error('Store not found');

    return repositories.inventory.update({ id: productId }, { store: store.id });
  },
);
```

Readonly repositories stay readonly. Custom repository subclasses and wrappers are rejected with a `TypeError`; pass the underlying standard repository or use a per-operation pool override.

## Raw SQL and existing helpers

The transaction scope implements `PoolLike`, so helpers that already accept a pool can use it:

```ts
await transaction({ pool, repositories }, async (transactionScope) => {
  await transactionScope.query('SELECT pg_advisory_xact_lock($1::bigint)', [resourceKey]);
  await auditRepository.create(auditValues, { pool: transactionScope });
});
```

A repository used with `{ pool: transactionScope }` must use the same write pool as the transaction. A scoped repository accepts its own scope as an override and rejects any other pool.
Population inherits that managed connection. An explicit `populate(..., { pool })` override must refer to the same transaction scope.
This also applies when the parent query uses a global repository with `{ pool: transactionScope }`.

## Row locking

Use an explicit lock when a decision spans multiple statements and cannot be expressed with a database constraint or atomic conditional update.

```ts
await transaction(
  {
    pool,
    repositories,
    lockTimeoutMs: 2_000,
    statementTimeoutMs: 5_000,
  },
  async ({ repositories: { Product, Store } }) => {
    const store = await Store.findOne().where({ id: storeId }).lock('noKeyUpdate');
    if (!store) throw new Error('Store not found');

    const productCount = await Product.count({ where: { store: store.id } });
    if (productCount >= capacity) throw new Error('Store capacity reached');

    return Product.create({ name: 'Widget', store: store.id });
  },
);
```

Available lock modes:

| Mode            | PostgreSQL clause   | Use when                                                                |
| --------------- | ------------------- | ----------------------------------------------------------------------- |
| `'update'`      | `FOR UPDATE`        | Deletion or referenced-key changes need protection                      |
| `'noKeyUpdate'` | `FOR NO KEY UPDATE` | Coordinating ordinary updates without blocking foreign-key checks       |
| `'share'`       | `FOR SHARE`         | Reading rows that must not change until commit, alongside other readers |
| `'keyShare'`    | `FOR KEY SHARE`     | Rows must not be deleted or have their key changed; updates may proceed |

Share modes let several transactions lock the same row at once while blocking writers that conflict with them.
`'share'` blocks every update and delete of the row, while `'keyShare'` blocks only deletes and key changes, as a foreign-key check does.

The optional `wait` behavior is `'nowait'` or `'skipLocked'`:

```ts
const jobs = await jobRepository.find({
  pool: transactionConnection,
  where: { status: 'queued' },
  lock: { mode: 'update', wait: 'skipLocked' },
  limit: 10,
});

const product = await productRepository.findOne({ pool: transactionConnection }).where({ id: productId }).lock('update', { wait: 'nowait' });
```

Locking is opt-in. Ordinary reads, including reads inside managed transactions, remain ordinary `SELECT` statements.
If a model has a column named `lock`, shorthand `find({ lock: value })` and `findOne({ lock: value })` filter that column, even when its JSON value contains `mode`.
Request a row lock with the fluent `.lock()` method or an explicit options wrapper such as `{ where: {}, lock: { mode: 'update' } }`.
An undefined `lock` option is treated as omitted.
Population queries use the same transaction connection but do not inherit the primary query's lock clause.
Related models and junction tables must use the transaction's write pool, even when omitted from the public `repositories` map.
Repositories on other pools are excluded from the managed scope; attempting to populate them raises a missing-repository error before their SQL runs.

A locking read runs on the write pool, or on the `pool` override you pass, never on a read replica.
PostgreSQL releases a row lock when the transaction ends, so a lock taken outside a transaction block is released as soon as the statement completes.
Take locks through scoped repositories, through repositories initialized with a transaction connection, or with a `{ pool: connection }` override on a global repository.
Locks cannot be combined with `distinctOn()` or `withCount()`.

### Locking discipline

* Keep the transaction short. Do network calls, slow computation, and user interaction before entering it.
* Prefer constraints or a conditional `update()` for single-row state transitions.
* Acquire multiple resources in a consistent table and primary-key order.
* A query locks only rows it finds. Lock an existing parent row when coordinating creation of child rows.
* Every writer participating in a business invariant must use the same locking protocol.
* Handle deadlocks, lock timeouts, and serialization failures by retrying the whole transaction only when the complete operation is safe to repeat.

## Existing transaction owners

If application code already acquires a connection and owns the lifecycle, initialize local repositories with that connection:

```ts
const connection = await pool.connect();

try {
  await connection.query('BEGIN');

  const transactionRepositories = initialize({
    models: [Product, Store],
    pool: connection,
  });

  await transactionRepositories.Product.update({ id: productId }, { name: 'Renamed widget' });
  await connection.query('COMMIT');
} catch (error) {
  await connection.query('ROLLBACK');
  throw error;
} finally {
  await connection.release();
}
```

Include models needed by relationships and junctions, and do not configure a read replica for these local repositories.
Repositories initialized this way can use `.lock()` directly because every query already runs on the transaction connection.
`initialize()` does not begin, commit, roll back, release, or invalidate an externally owned connection.
It retains its existing broad repository-map return type, so use your application's established typed wrapper or assertion for model-specific properties.

Alternatively, route individual operations to that connection:

```ts
await productRepository.create({ name: 'Widget', store: storeId }, { pool: connection });
await productRepository.update({ id: productId }, { name: 'Renamed widget' }, { pool: connection, returnRecords: false });
await productRepository.destroy({ id: obsoleteProductIds }, { pool: connection });
```

Passing `pool` changes only where the operation executes. It does not start or finish a transaction.

## Lifetime and composition

Pass scoped repositories or the scope itself into helpers instead of capturing global repositories.
Queries started after the callback completes are rejected, including saved lazy builders and raw `query()` calls.

Return or await every query from the callback. A callback that completes while already-started database work is pending fails rather than committing around unfinished work.
Cleanup waits for those queries before rolling back and releasing the connection, even when the callback throws.
An application timeout such as `Promise.race()` does not cancel a database query; use `lockTimeoutMs` or `statementTimeoutMs` to bound its wait.

BigAl does not automatically retry, provide nested transactions or savepoints, or make external side effects atomic.
Perform external effects after `transaction()` resolves, or write an outbox record inside the transaction.

---

---
url: /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; `where` filters the target entities themselves
* `through.sort` orders populated items by junction table columns
* Item order always follows the junction query, so use `through.sort` (not `sort`) to control ordering of
  many-to-many results

See [Querying > Populate](/guide/querying#populate) for the full list of populate options.

## 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
6. **Load relations with `.populate()`** - it runs a separate query per relation and does not require `.join()`; reach for `.join()` only to constrain the primary results by a related table

---

---
url: /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"."id","products"."name",… FROM "products"
//   INNER JOIN "stores" AS "store" ON "products"."store_id"="store"."id"
//   WHERE "store"."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 });
```

When a query includes any join, BigAl automatically qualifies the base table's own columns (in `SELECT`, `WHERE`, `ORDER BY`, and `DISTINCT ON`) with the base table name.
This keeps columns that share a name across the base and joined tables (most commonly `id`) from being ambiguous, so `.join()` is safe to use without falling back to raw SQL.

### `.join()` vs `.populate()`

`.join()` and [`.populate()`](/guide/querying#populate) solve different problems, and you do not need `.join()` to populate a relation:

* `.join()` adds a SQL `JOIN` to the main query so you can filter or sort the base results by columns on the related table. It does not hydrate the related entity onto a nested property.
* `.populate()` loads the related records. It runs a separate query after the main query resolves (not a `JOIN`) and nests the results, so it works on its own.

Use `.join()` alongside `.populate()` only when you also need to constrain the base results by the related table. Without such a constraint, `.populate()` by itself is enough.

## 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"."id","stores"."name" 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: /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                 |

---

---
url: /reference/api.md
description: >-
  Complete API reference for BigAl - initialization, transactions, repositories,
  query builders, subqueries, 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>>`

## transaction()

Acquires one client and runs a callback with repositories bound to it.

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

const result = await transaction(
  {
    pool,
    repositories: { Product: productRepository, Store: storeRepository },
    isolationLevel: 'readCommitted',
    lockTimeoutMs: 2_000,
    statementTimeoutMs: 5_000,
    idleInTransactionTimeoutMs: 10_000,
  },
  async (transactionScope) => {
    await transactionScope.query('SELECT pg_advisory_xact_lock($1::bigint)', [resourceKey]);
    return transactionScope.repositories.Product.findOne().where({ id: productId });
  },
);
```

**Parameters:** `TransactionOptions<TRepositories>`

| Option                       | Type                                                    | Required | Description                                             |
| ---------------------------- | ------------------------------------------------------- | -------- | ------------------------------------------------------- |
| `pool`                       | `TransactionPool`                                       | Yes      | Pool that acquires a releasable PostgreSQL client       |
| `repositories`               | `Record<string, Repository \| ReadonlyRepository>`      | Yes      | Standard repositories that share the write pool         |
| `isolationLevel`             | `'readCommitted' \| 'repeatableRead' \| 'serializable'` | No       | Explicit isolation level; omitted uses database default |
| `lockTimeoutMs`              | `number`                                                | No       | Transaction-local lock wait timeout                     |
| `statementTimeoutMs`         | `number`                                                | No       | Transaction-local statement timeout                     |
| `idleInTransactionTimeoutMs` | `number`                                                | No       | Transaction-local idle transaction timeout              |

The callback receives `TransactionScope<TRepositories>`, containing the same repository keys and a `PoolLike`-compatible `query()` method.
The helper returns the awaited callback result after commit. It rolls back on callback or database query failure and releases the client.

See [Transactions](/guide/transactions) for locking, failure behavior, and external-owner syntax.

## 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? }`. Prefer this over `findOne()` for existence checks - it performs better since it doesn't select or hydrate a row.

### 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?, pool? }`.

An array inserts in a single statement. Prefer this over calling `create()` in a loop, which costs one round trip per record.

`returnSelect` narrows the returned columns and `returnRecords: false` skips them entirely, cutting transfer and hydration cost.

### update()

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

Update matching records. Options: `{ returnRecords?, returnSelect?, pool? }`.
As with `create()`, use `returnSelect` to return only the columns you need or `returnRecords: false` to skip the returned rows.

### destroy()

```ts
repository.destroy(where, options?): Promise<void>
repository.destroy(where, { returnRecords: true }): Promise<QueryResult<T>[]>
```

Delete matching records. Options: `{ returnRecords?, returnSelect?, pool? }`.
Unlike `create()`/`update()`, `destroy()` does not return records by default (plain `DELETE`, no `RETURNING`); pass `returnRecords: true` or `returnSelect` to get the deleted rows back.

## 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. Chained methods build up a single query object, which executes when
awaited. Each call to `find()`, `findOne()`, or `count()` starts a fresh query.

| Method                                 | Available on         | Description                      |
| -------------------------------------- | -------------------- | -------------------------------- |
| `.where(query)`                        | find, findOne, count | Filter records                   |
| `.select(columns)`                     | find, findOne        | Narrow returned columns          |
| `.sort(value)`                         | find, findOne        | Order results                    |
| `.limit(n)`                            | find                 | Limit rows returned              |
| `.skip(n)`                             | find                 | Skip rows                        |
| `.paginate({ page, limit })`           | find                 | Shorthand for skip + limit       |
| `.withCount()`                         | find                 | Return `{ results, totalCount }` |
| `.populate(propertyName, options?)`    | find, findOne        | Load related entities            |
| `.join(propertyName, alias?)`          | find, findOne        | INNER JOIN                       |
| `.leftJoin(propertyName, alias?, on?)` | find, findOne        | LEFT JOIN                        |
| `.distinctOn(columns)`                 | find                 | PostgreSQL DISTINCT ON           |
| `.lock(mode, options?)`                | find, findOne        | Lock matching base-table rows    |
| `.toJSON()`                            | find, findOne        | Return plain objects             |
| `.UNSAFE_withOriginalFieldType(name)`  | find, findOne        | Type-level escape hatch          |
| `.UNSAFE_withFieldValue(name, value)`  | findOne              | Set a field after the query      |

### where()

```ts
query.where(whereQuery);
```

Filter records. Calling `.where()` again replaces the previous filter, so combine conditions in one object.
See [Querying > Where operators](/guide/querying#where-operators) for the operator syntax.

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

### select()

```ts
query.select(columns);
```

Narrow the returned columns; the result type narrows to the picked keys. Equivalent to the `select` option on
`find()`/`findOne()`. The primary key column is always included in the generated SQL.

```ts
const products = await productRepository.find().select(['name', 'sku']);
// products: Pick<QueryResult<Product>, 'name' | 'sku'>[]
```

### sort()

```ts
query.sort(value);
```

Order results. Accepts a string (`'name'`, `'name asc'`, `'name asc, createdAt desc'`) or an object
(`{ name: 1, createdAt: -1 }`, with `1`/`'asc'` and `-1`/`'desc'`). Direction defaults to ascending.
Repeated `.sort()` calls append sort columns. Vector columns accept `{ nearestTo, metric }` - see
[Querying > Vector distance queries](/guide/querying#vector-distance-queries).

```ts
await productRepository.find().sort('store').sort('createdAt desc');
// Same as .sort('store, createdAt desc')
```

### limit() / skip()

```ts
query.limit(count);
query.skip(count);
```

`LIMIT` and `OFFSET` for the query.

### paginate()

```ts
query.paginate({ page, limit });
```

Shorthand for `.skip((page - 1) * limit).limit(limit)`. `page` starts at 1; values below 1 are treated as
page 1.

```ts
await productRepository.find().where({ store: storeId }).paginate({ page: 2, limit: 25 });
// SQL: ... LIMIT 25 OFFSET 25
```

### withCount()

```ts
query.withCount();
```

Resolves to `{ results, totalCount }` in a single query using `COUNT(*) OVER()`. `totalCount` is the number
of rows matching the where clause, ignoring `limit`/`skip`. Throws when combined with `.distinctOn()`.

```ts
const { results, totalCount } = await productRepository.find().where({ store: storeId }).paginate({ page: 1, limit: 10 }).withCount();
```

### populate()

```ts
query.populate(propertyName, options?)
```

Load related entities. Runs a separate query per relation after the main query resolves (batched by id - no
SQL `JOIN`) and changes the property's result type from foreign key to populated entity. Chain once per
relation; the populate queries run in parallel.

**Options:** `PopulateArgs`

| Option    | Type                | Description                                                                    |
| --------- | ------------------- | ------------------------------------------------------------------------------ |
| `select`  | `string[]`          | Columns to return on the related entities. The primary key is always included. |
| `where`   | `WhereQuery`        | Filter related rows.                                                           |
| `sort`    | `string \| object`  | Order related rows. Same syntax as `.sort()`.                                  |
| `skip`    | `number`            | Skip related rows. Collections only.                                           |
| `limit`   | `number`            | Maximum related rows to return. Collections only.                              |
| `pool`    | `PoolLike`          | Connection pool for the populate query. Defaults to the main query's pool.     |
| `through` | `{ where?, sort? }` | Filter and order by junction table columns. Many-to-many relations only.       |

Options apply to the related rows only, never to the primary results. See
[Querying > Populate](/guide/querying#populate) for per-relation behavior and caveats.

```ts
const product = await productRepository
  .findOne()
  .where({ id: 42 })
  .populate('store', { select: ['name'] })
  .populate('categories', { where: { isActive: true }, through: { sort: 'ordering asc' } });
```

### join() / leftJoin()

```ts
query.join(propertyName, alias?)
query.leftJoin(propertyName, alias?, on?)
query.join(subquery, alias, { on })       // find only
query.leftJoin(subquery, alias, { on })   // find only
```

Add an `INNER JOIN` or `LEFT JOIN` to the main query so `.where()` and `.sort()` can reference joined
columns (`.where({ alias: { column: value } })`, `.sort('alias.column desc')`). Joins do not hydrate related
entities - use `.populate()` for that. Subquery joins are available on `find()` only. See
[Subqueries and Joins](/guide/subqueries-and-joins#model-joins).

```ts
const products = await productRepository
  .find()
  .join('store')
  .where({ store: { name: 'Acme' } });
```

### distinctOn()

```ts
query.distinctOn(columns);
```

PostgreSQL `DISTINCT ON` - one row per unique combination of the given columns. The `ORDER BY` must start
with the same columns in the same order, and `.distinctOn()` cannot be combined with `.withCount()`. See
[Querying > DISTINCT ON](/guide/querying#distinct-on).

```ts
const latestPerStore = await productRepository.find().distinctOn(['store']).sort('store').sort('createdAt desc');
```

### lock()

```ts
query.lock('noKeyUpdate');
query.lock('update', { wait: 'nowait' });
```

Adds `FOR UPDATE`, `FOR NO KEY UPDATE`, `FOR SHARE`, or `FOR KEY SHARE` for the base table, from `'update'`, `'noKeyUpdate'`, `'share'`, or `'keyShare'`.
Optional `wait` is `'nowait'` or `'skipLocked'`. The same option can be supplied to `find()` or `findOne()`:

```ts
await productRepository.find({
  pool: transactionConnection,
  where: { id: productIds },
  lock: { mode: 'update', wait: 'skipLocked' },
});
```

A locking read runs on the write pool unless `pool` is supplied, and PostgreSQL holds the lock only while a transaction is open on that connection.
It cannot be combined with `distinctOn()` or `withCount()`, and it does not propagate to `populate()` queries.

### toJSON()

```ts
query.toJSON();
```

Return plain objects instead of entity class instances, including populated relations. Useful when results
must be serializable.

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

### UNSAFE\_withOriginalFieldType()

```ts
query.UNSAFE_withOriginalFieldType(propertyName);
```

Type-level escape hatch with no runtime effect: restores a relation property's original entity type (for
example `number | Store` instead of the narrowed `number`). Prefer `.populate()` or
`QueryResultPopulated<T, K>` when possible.

### UNSAFE\_withFieldValue()

```ts
findOneQuery.UNSAFE_withFieldValue(propertyName, value);
```

`findOne()` only. Sets the property to the given value after the query resolves and types the result
accordingly. The value is applied in memory - nothing is written to the database.

## 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.
Vector columns are declared with `{ type: 'vector', dimensions: n }` (`dimensions` is informational -
BigAl does not issue DDL).

### @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.

### VectorDistanceMetric

```ts
type VectorDistanceMetric = 'cosine' | 'innerProduct' | 'l1' | 'l2';
```

### VectorDistanceSort

```ts
interface VectorDistanceSort {
  nearestTo: number[];
  metric?: VectorDistanceMetric;
}
```

Used in `.sort()` for nearest-neighbor queries on vector columns. See
[Querying > Vector distance queries](/guide/querying#vector-distance-queries).

### VectorDistanceConstraint

```ts
interface VectorDistanceConstraint {
  nearestTo: number[];
  metric?: VectorDistanceMetric;
  distance: Partial<Record<'<' | '<=' | '>' | '>=', number>>;
}
```

Used in where clauses to filter vector columns by distance threshold. At least one `distance` bound is
required (pgvector distance operators return a number, so a bare distance expression is not a valid
where clause); multiple bounds are combined with `AND`. To order by distance without filtering, use
`sort()` with `nearestTo` instead.

### 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: /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`.
Managed transactions additionally require `connect()` and a releasable client, represented by `TransactionPool`. 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`.
Locking reads (`.lock()`) also use `pool`, because a replica cannot hold row locks.

Individual queries can override the pool:

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

Write operations accept the same override:

```ts
await productRepository.create({ name: 'Widget', store: storeId }, { pool: writeConnection });
await productRepository.update({ id: 42 }, { name: 'Renamed' }, { pool: writeConnection, returnRecords: false });
await productRepository.destroy({ id: 42 }, { pool: writeConnection });
```

Passing a pool changes only query routing. It does not begin or complete a transaction. See [Transactions](/guide/transactions) for managed and externally owned transaction patterns.

## Managed transaction pools

`transaction()` works with PostgreSQL pools whose `connect()` method returns a client with `query()` and `release()` methods.
This includes the pool APIs shown above. Query-only HTTP or batch executors can initialize repositories but cannot own an interactive managed transaction.

`TransactionConnection` also supports optional `on('error', listener)` and `removeListener('error', listener)` methods.
When both are available, BigAl listens for fatal client errors throughout the transaction and cleanup, discards a failed connection, and removes its listener after release.
Custom adapters with only `query()` and `release()` remain valid and must surface connection failures through query rejections.
Applications still need a pool-level error handler for events the driver sends to the pool, including checked-out client errors forwarded by `postgres-pool`.

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

await transaction(
  {
    pool,
    repositories: { Product: productRepository },
  },
  async ({ repositories }) => repositories.Product.update({ id: 42 }, { name: 'Renamed' }),
);
```

Managed transactions always route scoped reads to the checked-out write connection, bypassing `readonlyPool` so reads can observe writes made earlier in the same transaction.

## 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: /compare/bigal-vs-prisma.md
description: >-
  Compare BigAl and Prisma ORM 7 for PostgreSQL - schema definition, migrations,
  JSONB, pgvector, row locks, transactions, and runtime support.
---

# BigAl vs Prisma

Prisma is a multi-database ORM with its own schema language, a generated client, and a migration tool. BigAl is a
PostgreSQL-only ORM with decorator models and a fluent query builder, and it ships no migrations. Choose Prisma for
schema-driven development across databases. Choose BigAl when you want Postgres features such as row locks and
pgvector in the typed query API.

This page compares BigAl 16 with Prisma ORM 7, the current stable release. Prisma ORM 8 is a release candidate with
general availability expected in October 2026. It adds a TypeScript runtime, a new query API, and TypeScript schema
files, and some Prisma 7 features are not yet available in it.

## At a glance

| Criterion         | BigAl                                                                      | Prisma ORM 7                                                                          |
| ----------------- | -------------------------------------------------------------------------- | ------------------------------------------------------------------------------------- |
| Databases         | PostgreSQL only                                                            | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, CockroachDB, MongoDB                  |
| Query style       | Fluent builder: `find().where({...}).sort().limit()`                       | Object arguments: `findMany({ where, orderBy, take, include })`                       |
| Schema definition | Classes with `@table` and `@column` decorators                             | `schema.prisma`; `prisma generate` writes a typed client                              |
| Migrations        | None; BigAl issues no DDL, so pair it with a migration tool                | `prisma migrate dev` and `prisma migrate deploy`, generated from the schema           |
| JSONB             | Property paths (`->`, `->>`) and `@>` containment in `.where()`            | `Json` fields with `path` filters on PostgreSQL                                       |
| DISTINCT ON       | `.distinctOn([...])`                                                       | `distinct` option; native `DISTINCT ON` behind the `nativeDistinct` preview           |
| ON CONFLICT       | `create()` option: `onConflict` with `ignore` or `merge`                   | `upsert()` uses `ON CONFLICT` when criteria are met; `createMany({ skipDuplicates })` |
| pgvector          | `vector` columns; `nearestTo` sorting and distance filters                 | `Unsupported("vector")` columns; query with `$queryRaw` or TypedSQL                   |
| Row locks         | `.lock()`: update, no key update, share, key share; `nowait`, `skipLocked` | No API; `$queryRaw` with `FOR UPDATE` in an interactive transaction                   |
| Type safety       | From class properties; `.select()` and `.populate()` narrow results        | Generated from the schema; `select` and `include` narrow results                      |
| Runtime deps      | Zero; add `postgres-pool`, `pg`, or `@neondatabase/serverless`             | `@prisma/client`, a driver adapter such as `@prisma/adapter-pg`, `prisma` CLI         |
| Runtimes          | Node.js 22.11+, Bun, Deno 2; edge runtimes untested                        | Node.js 20.19+, Bun, Deno; Cloudflare Workers and Vercel Edge in preview              |
| Transactions      | `transaction()` with isolation level and lock, statement, idle timeouts    | `$transaction()` batch or interactive; `isolationLevel`, `maxWait`, `timeout`         |
| Raw SQL           | `pool.query()`, or `query()` on the transaction scope                      | `$queryRaw` tagged templates and TypedSQL `.sql` files                                |
| Read replicas     | Built in: `readonlyPool` serves reads                                      | `@prisma/extension-read-replicas` client extension                                    |

## When to choose BigAl

* You run only PostgreSQL and want [row locks](/guide/transactions#row-locking),
  [DISTINCT ON](/guide/querying#distinct-on), [JSONB paths](/guide/querying#jsonb-querying), and
  [vector search](/guide/querying#vector-distance-queries) in the typed API instead of raw SQL.
* Your schema already lives in SQL migrations, and you want the ORM to stay out of DDL.
* You want no code generation step and no runtime dependencies beyond your Postgres driver.
* You want read replica routing without an extension. See [Configuration](/reference/configuration#read-replicas).

## When to choose Prisma

* You want one schema file to drive migrations, client types, and Prisma Studio.
* You need MySQL, SQL Server, SQLite, or MongoDB, or you may switch databases later.
* You rely on nested writes, such as creating a store and its products in one call.
* Your team prefers a large ecosystem of guides, extensions, and hosted tooling.

## Migrating from Prisma

BigAl works with the tables Prisma already created, so the database does not change. Keep `prisma migrate` for schema
changes, or move to plain SQL migrations. Prisma names tables and columns after models and fields unless you use
`@@map` and `@map`, so set `name` on `@table` and `@column` to match.

### Models

::: code-group

```prisma [Prisma]
model Product {
  id         Int    @id @default(autoincrement())
  name       String
  sku        String @unique
  priceCents Int    @map("price_cents")
  storeId    Int    @map("store_id")
  store      Store  @relation(fields: [storeId], references: [id])

  @@map("products")
}
```

```ts [BigAl]
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({ type: 'string', required: true })
  public sku!: string;

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

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

:::

### Queries

::: code-group

```ts [Prisma]
const products = await prisma.product.findMany({
  where: { priceCents: { gte: 1000 }, name: { contains: 'widget', mode: 'insensitive' } },
  orderBy: { name: 'asc' },
  take: 10,
  include: { store: { select: { name: true } } },
});
```

```ts [BigAl]
const products = await productRepository
  .find()
  .where({ priceCents: { '>=': 1000 }, name: { contains: 'widget' } })
  .sort('name asc')
  .limit(10)
  .populate('store', { select: ['name'] });
```

:::

BigAl string operators such as `contains` are case-insensitive (`ILIKE`) by default.

### Row locks

::: code-group

```ts [Prisma]
await prisma.$transaction(async (tx) => {
  const [product] = await tx.$queryRaw<{ id: number; price_cents: number }[]>`
    SELECT id, price_cents FROM products WHERE sku = ${sku} FOR UPDATE`;
  // ...
});
```

```ts [BigAl]
await transaction({ pool, repositories: { Product: productRepository } }, async ({ repositories }) => {
  const product = await repositories.Product.findOne().where({ sku }).lock('update');
  // product is null when no row matches
});
```

:::

Last reviewed: September 2026

---

---
url: /compare/bigal-vs-drizzle.md
description: >-
  Compare BigAl and Drizzle ORM for PostgreSQL - query style, schema definition,
  migrations, JSONB, pgvector, row locks, and runtimes.
---

# BigAl vs Drizzle

Drizzle is a TypeScript ORM with SQL-shaped queries for PostgreSQL, MySQL, and SQLite. Schemas are TypeScript table
definitions, and the drizzle-kit CLI generates migrations. BigAl is a PostgreSQL-only ORM with decorator models, a
repository API, and no migration tooling. Choose Drizzle for SQL-shaped queries and built-in migrations. Choose BigAl
for repository-style queries with Postgres features built in.

This page compares BigAl 16 with Drizzle ORM 0.45, the current stable release. Drizzle 1.0 is in beta.

## At a glance

| Criterion         | BigAl                                                                      | Drizzle ORM 0.45                                                                     |
| ----------------- | -------------------------------------------------------------------------- | ------------------------------------------------------------------------------------ |
| Databases         | PostgreSQL only                                                            | PostgreSQL, MySQL, SQLite, and hosted variants such as Neon, Turso, and D1           |
| Query style       | Fluent builder: `find().where({...}).sort().limit()`                       | SQL-like `db.select().from(t).where(gte(t.price, 100))`; relational `db.query`       |
| Schema definition | Classes with `@table` and `@column` decorators                             | `pgTable()` definitions; types inferred with `$inferSelect`                          |
| Migrations        | None; BigAl issues no DDL, so pair it with a migration tool                | drizzle-kit `generate`, `migrate`, `push`, and `pull` (introspection)                |
| Relationships     | `model`, `collection`, `through`; loaded with `.populate()`                | Declared with `relations()`; relational queries load them with `with`                |
| JSONB             | Property paths (`->`, `->>`) and `@>` containment in `.where()`            | `jsonb().$type<T>()` columns; filter inside JSON with the `sql` template             |
| DISTINCT ON       | `.distinctOn([...])`                                                       | `db.selectDistinctOn([...])`                                                         |
| ON CONFLICT       | `create()` option: `onConflict` with `ignore` or `merge`                   | `.onConflictDoNothing()` and `.onConflictDoUpdate({ target, set })`                  |
| pgvector          | `vector` columns; `nearestTo` sorting and distance filters                 | `vector()` columns; `cosineDistance`, `l2Distance`, `innerProduct`; HNSW indexes     |
| Row locks         | `.lock()`: update, no key update, share, key share; `nowait`, `skipLocked` | `.for('update')`, also `no key update`, `share`, `key share`; `noWait`, `skipLocked` |
| Lifecycle hooks   | Static `beforeCreate` and `beforeUpdate` on the model                      | None; column-level `$defaultFn` and `$onUpdate`                                      |
| Type safety       | From class properties; `.select()` and `.populate()` narrow results        | Inferred from table definitions; selected fields shape each result                   |
| Runtime deps      | Zero; add `postgres-pool`, `pg`, or `@neondatabase/serverless`             | Zero; add a driver such as `pg`, `postgres`, or `@neondatabase/serverless`           |
| Runtimes          | Node.js 22.11+, Bun, Deno 2; edge runtimes untested                        | Node.js, Bun, Deno, and edge runtimes with HTTP or WebSocket drivers                 |
| Transactions      | `transaction()` with isolation level and lock, statement, idle timeouts    | `db.transaction()` with isolation level and access mode; nested savepoints           |
| Raw SQL           | `pool.query()`, or `query()` on the transaction scope                      | `sql` template inside any query, or `db.execute(sql...)`                             |

## When to choose BigAl

* You prefer a repository API with object filters over composing SQL operators such as `eq()` and `and()`.
* You want [JSONB property filters](/guide/querying#jsonb-querying) in the typed API instead of `sql` fragments.
* You want model-level `beforeCreate` and `beforeUpdate` hooks.
* Your schema already lives in SQL migrations, and you want the ORM to stay out of DDL.
* You want [read replica routing](/reference/configuration#read-replicas) built in.

## When to choose Drizzle

* You want queries that read like SQL, with CTEs (`$with()`) and set operations such as `union()` in the builder.
* You want migrations generated from your TypeScript schema, or introspection of an existing database.
* You need MySQL or SQLite, or a hosted database such as Turso or Cloudflare D1.
* You deploy to edge runtimes and want a documented path for each driver.

## Migrating from Drizzle

BigAl works with the tables Drizzle created, so the database does not change. Keep drizzle-kit for migrations, or move
to plain SQL files. Drizzle joins return flat rows you shape yourself; BigAl's `.populate()` attaches related records
to each result instead.

### Models

::: code-group

```ts [Drizzle]
import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  sku: text('sku').notNull().unique(),
  priceCents: integer('price_cents').notNull(),
  storeId: integer('store_id')
    .notNull()
    .references(() => stores.id),
});
```

```ts [BigAl]
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({ type: 'string', required: true })
  public sku!: string;

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

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

:::

### Queries and upserts

::: code-group

```ts [Drizzle]
const rows = await db
  .select({ id: products.id, name: products.name, storeName: stores.name })
  .from(products)
  .innerJoin(stores, eq(products.storeId, stores.id))
  .where(and(gte(products.priceCents, 1000), ilike(products.name, '%widget%')))
  .orderBy(asc(products.name))
  .limit(10);

await db
  .insert(products)
  .values({ sku: 'WDG-001', name: 'Widget', priceCents: 999, storeId: 1 })
  .onConflictDoUpdate({ target: products.sku, set: { priceCents: sql`excluded.price_cents` } });
```

```ts [BigAl]
const products = await productRepository
  .find()
  .where({ priceCents: { '>=': 1000 }, name: { contains: 'widget' } })
  .sort('name asc')
  .limit(10)
  .populate('store', { select: ['name'] });

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

:::

Last reviewed: September 2026

---

---
url: /compare/bigal-vs-typeorm.md
description: >-
  Compare BigAl and TypeORM 1.x for PostgreSQL - decorator models, migrations,
  JSONB, pgvector, row locks, type safety, and dependencies.
---

# BigAl vs TypeORM

TypeORM and BigAl both define models as decorated TypeScript classes. TypeORM supports 10 databases, the Active Record
and Data Mapper patterns, and generated migrations. BigAl supports only PostgreSQL and has no migration tooling. In
exchange, it puts Postgres features such as JSONB paths and pgvector distance queries in its typed query API.

This page compares BigAl 16 with TypeORM 1.1, the current stable release. TypeORM 1.0 shipped in May 2026.

## At a glance

| Criterion         | BigAl                                                                      | TypeORM 1.1                                                                       |
| ----------------- | -------------------------------------------------------------------------- | --------------------------------------------------------------------------------- |
| Databases         | PostgreSQL only                                                            | 10, including PostgreSQL, MySQL, SQL Server, Oracle, SQLite, and MongoDB          |
| Query style       | Fluent builder: `find().where({...}).sort().limit()`                       | `find({ where, order, take, relations })`, plus `createQueryBuilder()`            |
| Schema definition | Classes with `@table` and `@column`; no `reflect-metadata` needed          | `@Entity` classes or `EntitySchema`; needs `reflect-metadata`                     |
| Migrations        | None; BigAl issues no DDL, so pair it with a migration tool                | CLI `migration:generate`, `migration:run`, `migration:revert`; `synchronize`      |
| JSONB             | Property paths (`->`, `->>`) and `@>` containment in `.where()`            | `jsonb` columns and a `JsonContains` operator; paths via QueryBuilder SQL         |
| DISTINCT ON       | `.distinctOn([...])`                                                       | QueryBuilder `.distinctOn([...])`                                                 |
| ON CONFLICT       | `create()` option: `onConflict` with `ignore` or `merge`                   | `upsert(values, ['sku'])`; QueryBuilder `.orIgnore()` and `.orUpdate()`           |
| pgvector          | `vector` columns; `nearestTo` sorting and distance filters                 | `vector` and `halfvec` column types; distance queries through raw SQL             |
| Row locks         | `.lock()`: update, no key update, share, key share; `nowait`, `skipLocked` | `.setLock()` modes include `for_no_key_update`, `for_key_share`; `.setOnLocked()` |
| Relation typing   | `number \| Store`, narrowed to `Store` by `.populate()`                    | `store: Store` whether or not the relation was loaded                             |
| Lifecycle hooks   | Static `beforeCreate` and `beforeUpdate` on the model                      | `@BeforeInsert`, `@AfterLoad`, and other listeners, plus subscribers              |
| Runtime deps      | Zero; add `postgres-pool`, `pg`, or `@neondatabase/serverless`             | 10, including `reflect-metadata`, `dayjs`, `debug`, and `yargs`                   |
| Runtimes          | Node.js 22.11+, Bun, Deno 2; edge runtimes untested                        | Node.js 20+; Bun and Deno are not on its supported-platforms page                 |
| Transactions      | `transaction()` with isolation level and lock, statement, idle timeouts    | `dataSource.transaction()` with isolation level; `QueryRunner` for manual control |
| Raw SQL           | `pool.query()`, or `query()` on the transaction scope                      | `dataSource.query(sql, params)` and the `dataSource.sql` template                 |

## When to choose BigAl

* You want relation types that tell you whether a relation was loaded. BigAl types a foreign key as `number | Store`
  and narrows it after [`.populate()`](/guide/relationships).
* You want [JSONB property filters](/guide/querying#jsonb-querying) and
  [vector search](/guide/querying#vector-distance-queries) without dropping to QueryBuilder SQL.
* You want fewer moving parts: no `reflect-metadata`, no `emitDecoratorMetadata`, and zero runtime dependencies.
* You run on Bun or Deno as well as Node.js.

## When to choose TypeORM

* You need a database other than PostgreSQL, or several at once.
* You want migrations generated from entity changes.
* You use the Active Record pattern, or a framework integration such as NestJS's TypeORM module.
* You need entity listeners and subscribers beyond before-create and before-update hooks.
* You target React Native, NativeScript, or the browser with SQLite, which TypeORM documents.

## Migrating from TypeORM

The model code looks similar, because both use decorators on classes. Relations change the most. TypeORM's
`@ManyToOne` with `@JoinColumn` becomes a `model` column that holds the foreign key, and `relations: { store: true }`
becomes `.populate('store')`. BigAl works with the existing tables, and you can keep TypeORM's migrations or move to
plain SQL files.

### Models

::: code-group

```ts [TypeORM]
import { Column, Entity, JoinColumn, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';

@Entity({ name: 'products' })
export class Product {
  @PrimaryGeneratedColumn()
  public id!: number;

  @Column()
  public name!: string;

  @Column({ unique: true })
  public sku!: string;

  @Column({ name: 'price_cents' })
  public priceCents!: number;

  @ManyToOne(() => Store)
  @JoinColumn({ name: 'store_id' })
  public store!: Store;
}
```

```ts [BigAl]
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({ type: 'string', required: true })
  public sku!: string;

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

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

:::

### Queries

::: code-group

```ts [TypeORM]
const products = await dataSource.getRepository(Product).find({
  where: { priceCents: MoreThanOrEqual(1000), name: ILike('%widget%') },
  order: { name: 'ASC' },
  take: 10,
  relations: { store: true },
});
```

```ts [BigAl]
const products = await productRepository
  .find()
  .where({ priceCents: { '>=': 1000 }, name: { contains: 'widget' } })
  .sort('name asc')
  .limit(10)
  .populate('store', { select: ['name'] });
```

:::

Last reviewed: September 2026

---

---
url: /compare/bigal-vs-kysely.md
description: >-
  Compare BigAl and Kysely for PostgreSQL - ORM vs query builder, relationships,
  migrations, JSONB, pgvector, row locks, CTEs, and runtimes.
---

# BigAl vs Kysely

Kysely is a type-safe SQL query builder, not an ORM. You describe tables as TypeScript interfaces and write queries
that mirror SQL. BigAl is a PostgreSQL ORM with decorator models, relationships, and `.populate()`. Choose Kysely for
full SQL control across databases, including CTEs. Choose BigAl for repository-style CRUD with relationships on
Postgres and less SQL to write.

This page compares BigAl 16 with Kysely 0.29, the current stable release.

## At a glance

| Criterion         | BigAl                                                                      | Kysely 0.29                                                                        |
| ----------------- | -------------------------------------------------------------------------- | ---------------------------------------------------------------------------------- |
| Kind              | ORM with repositories and relationships                                    | SQL query builder; no entities or relationships                                    |
| Databases         | PostgreSQL only                                                            | PostgreSQL, MySQL, SQLite, SQL Server, PGlite; more via community dialects         |
| Query style       | Fluent builder: `find().where({...}).sort().limit()`                       | SQL-shaped: `selectFrom('products').where('price', '>=', 100)`                     |
| Schema definition | Classes with `@table` and `@column` decorators                             | A `Database` interface, hand-written or generated by kysely-codegen                |
| Migrations        | None; BigAl issues no DDL, so pair it with a migration tool                | `Migrator` runs hand-written `up` and `down` files; no diff generation             |
| Relationships     | `model`, `collection`, `through`; loaded with `.populate()`                | Joins, or `jsonArrayFrom` and `jsonObjectFrom` to nest rows as JSON                |
| JSONB             | Property paths (`->`, `->>`) and `@>` containment in `.where()`            | Typed paths: `eb.ref('col', '->>').key('theme')`                                   |
| DISTINCT ON       | `.distinctOn([...])`                                                       | `.distinctOn(...)`                                                                 |
| ON CONFLICT       | `create()` option: `onConflict` with `ignore` or `merge`                   | `.onConflict((oc) => oc.column('sku').doUpdateSet(...))` or `.doNothing()`         |
| pgvector          | `vector` columns; `nearestTo` sorting and distance filters                 | `sql` template, or helpers from the `pgvector/kysely` package                      |
| Row locks         | `.lock()`: update, no key update, share, key share; `nowait`, `skipLocked` | `.forUpdate()`, `.forShare()`, and key variants; `.skipLocked()`, `.noWait()`      |
| CTEs              | Not supported; use raw SQL                                                 | `.with()` and `.withRecursive()`                                                   |
| Type safety       | From class properties; `.select()` and `.populate()` narrow results        | Flows from the `Database` interface; selections and aliases shape results          |
| Runtime deps      | Zero; add `postgres-pool`, `pg`, or `@neondatabase/serverless`             | Zero; add a driver such as `pg`                                                    |
| Runtimes          | Node.js 22.11+, Bun, Deno 2; edge runtimes untested                        | Node.js 22+, Deno, Bun, and other JavaScript runtimes                              |
| Transactions      | `transaction()` with isolation level and lock, statement, idle timeouts    | `db.transaction().execute()` with isolation level; `startTransaction()` savepoints |
| Raw SQL           | `pool.query()`, or `query()` on the transaction scope                      | `sql` tagged template, composable inside builder queries                           |

## When to choose BigAl

* You want relationships defined once on the model and loaded with [`.populate()`](/guide/querying#populate), instead
  of writing joins or JSON aggregation per query.
* You want CRUD methods such as `create()`, `update()`, and `destroy()` that return typed records.
* You want [vector search](/guide/querying#vector-distance-queries) and
  [JSONB filters](/guide/querying#jsonb-querying) without extra helper packages.
* You want [read replica routing](/reference/configuration#read-replicas) and `beforeCreate` and `beforeUpdate` hooks
  built in.

## When to choose Kysely

* You want every query to read like the SQL it runs, including CTEs, window functions with `.over()`, and complex
  joins.
* You need MySQL, SQLite, or SQL Server, or you want one query style across them.
* You prefer database types generated from the live schema over hand-written model classes.
* You want built-in migration running and savepoints.

## Migrating from Kysely

BigAl and Kysely can share one `pg` pool, so you can move one table at a time. Keep Kysely, or `pool.query()`, for
CTEs and other queries BigAl does not model. See [BigAl vs Raw SQL](/advanced/bigal-vs-raw-sql) for that split.

### Models

::: code-group

```ts [Kysely]
import type { Generated } from 'kysely';

interface Database {
  products: {
    id: Generated<number>;
    name: string;
    sku: string;
    price_cents: number;
    store_id: number;
  };
  stores: { id: Generated<number>; name: string };
}
```

```ts [BigAl]
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({ type: 'string', required: true })
  public sku!: string;

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

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

:::

### Queries and upserts

::: code-group

```ts [Kysely]
const rows = await db
  .selectFrom('products')
  .innerJoin('stores', 'stores.id', 'products.store_id')
  .select(['products.id', 'products.name', 'stores.name as store_name'])
  .where('products.price_cents', '>=', 1000)
  .where('products.name', 'ilike', '%widget%')
  .orderBy('products.name')
  .limit(10)
  .execute();

await db
  .insertInto('products')
  .values({ sku: 'WDG-001', name: 'Widget', price_cents: 999, store_id: 1 })
  .onConflict((oc) => oc.column('sku').doUpdateSet((eb) => ({ price_cents: eb.ref('excluded.price_cents') })))
  .execute();
```

```ts [BigAl]
const products = await productRepository
  .find()
  .where({ priceCents: { '>=': 1000 }, name: { contains: 'widget' } })
  .sort('name asc')
  .limit(10)
  .populate('store', { select: ['name'] });

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

:::

Last reviewed: September 2026

---

---
url: /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
* Managed multi-repository transactions
* Explicit `FOR UPDATE`, `FOR NO KEY UPDATE`, `FOR SHARE`, and `FOR KEY SHARE` row locks

## 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
* Table-level (`LOCK TABLE`), advisory, or joined-table row locks
* 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']);
```

Inside a managed transaction, use the scope so raw SQL runs on the same checked-out connection:

```ts
await transaction({ pool, repositories }, async (transactionScope) => {
  await transactionScope.query('SELECT pg_advisory_xact_lock($1::bigint)', [resourceKey]);
  return transactionScope.repositories.Product.update({ id: productId }, { name: 'Renamed' });
});
```

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

---

---
url: /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.