### Install BigAl and PostgreSQL drivers

Source: https://bigalorm.github.io/bigal/getting-started.html

Commands to install the BigAl package and compatible PostgreSQL drivers. Users can choose between postgres-pool, node-postgres, or Neon serverless drivers.

```sh
npm install bigal
npm install postgres-pool
npm install pg
npm install @neondatabase/serverless
```

--------------------------------

### Initialize BigAl repositories

Source: https://bigalorm.github.io/bigal/getting-started.html

Shows how to initialize the ORM by passing models and a database connection pool, resulting in a repository map for data operations.

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

--------------------------------

### Add BigAl AI skill

Source: https://bigalorm.github.io/bigal/getting-started.html

Command to integrate BigAl-specific guidance into AI-powered development tools.

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

--------------------------------

### Execute database queries

Source: https://bigalorm.github.io/bigal/getting-started.html

Examples of using the fluent query builder to find, filter, sort, and count records from the database. Queries are PromiseLike and support async/await syntax.

```ts
const products = await productRepository
  .find()
  .where({ priceCents: { '>=': 1000 } })
  .sort('name asc')
  .limit(10);

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

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

--------------------------------

### Install BigAl and dependencies

Source: https://bigalorm.github.io/bigal/llms-full.txt

Commands to install the BigAl package and the required PostgreSQL driver.

```bash
npm install bigal
# Option 1: postgres-pool (recommended)
npm install postgres-pool
# Option 2: node-postgres
npm install pg
# Option 3: Neon serverless
npm install @neondatabase/serverless
```

--------------------------------

### Initialize BigAl with Connection Pools

Source: https://bigalorm.github.io/bigal/reference/configuration.html

Demonstrates how to initialize BigAl using different PostgreSQL drivers including postgres-pool, node-postgres, and Neon serverless. Each example requires a connection string and a models object to establish the repository layer.

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

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

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

```typescript
import pg from 'pg';

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

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

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

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

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

--------------------------------

### Perform Basic Queries with BigAl

Source: https://bigalorm.github.io/bigal/advanced/bigal-vs-raw-sql.html

Examples of mapping standard SQL SELECT statements to BigAl repository methods for filtering, sorting, and pagination.

```sql
SELECT * FROM products WHERE id = 1;
SELECT name FROM products WHERE id = 1;
SELECT * FROM products WHERE name ILIKE '%widget%';
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE status IN ('a','b');
SELECT * FROM products WHERE status <> 'x';
SELECT * FROM products WHERE deleted_at IS NOT NULL;
SELECT * FROM products ORDER BY name LIMIT 10;
SELECT COUNT(*) FROM products WHERE active = true;
```

```typescript
productRepo.findOne().where({ id: 1 });
productRepo.findOne({ select: ['name'] }).where({ id: 1 });
productRepo.find().where({ name: { contains: 'widget' } });
productRepo.find().where({ price: { '>=': 100 } });
productRepo.find().where({ status: ['a', 'b'] });
productRepo.find().where({ status: { '!': 'x' } });
productRepo.find().where({ deletedAt: { '!': null } });
productRepo.find().where({}).sort('name asc').limit(10);
productRepo.count().where({ active: true });
```

--------------------------------

### Define a database model

Source: https://bigalorm.github.io/bigal/getting-started.html

Demonstrates how to define a database model in TypeScript by extending the Entity class and using decorators to map properties to table columns.

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

--------------------------------

### Setup Multiple Databases

Source: https://bigalorm.github.io/bigal/reference/configuration.html

Configures BigAl to handle entities across different databases by defining named connections. Entities specify their connection name, and the initialize function maps these names to specific pool configurations.

```typescript
@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,
    },
  },
});
```

--------------------------------

### Perform Aggregate Functions in Subqueries

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Demonstrates how to use aggregate functions like count, sum, and avg within a subquery select statement. Includes examples of aliasing and distinct counting.

```typescript
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']);
```

--------------------------------

### SQL to BigAl Translation

Source: https://bigalorm.github.io/bigal/llms-full.txt

Reference guide for mapping standard SQL operations to BigAl fluent API methods.

```APIDOC
## SQL to BigAl Translation

### Description
Common patterns for translating SQL queries into BigAl repository method calls.

### Basic Queries
- **Select All**: `productRepo.findOne().where({ id: 1 })`
- **Filter (ILIKE)**: `productRepo.find().where({ name: { contains: 'widget' } })`
- **Filter (IN)**: `productRepo.find().where({ status: ['a', 'b'] })`
- **Sorting/Limit**: `productRepo.find().where({}).sort('name asc').limit(10)`

### CRUD Operations
- **Create**: `productRepo.create({ name: 'Widget' })`
- **Update**: `productRepo.update({ id: 1 }, { name: 'X' })`
- **Delete**: `productRepo.destroy({ id: 1 })`

### Advanced Patterns
- **Subqueries**: `.where({ store: { in: subquery(storeRepo).select(['id']).where({ active: true }) } })`
- **Joins**: `.join('store').where({ store: { name: 'Acme' } })`
- **Upsert**: `{ onConflict: { action: 'merge', targets: ['sku'], merge: ['name'] } }`
```

--------------------------------

### Count Matching Records with BigAl

Source: https://bigalorm.github.io/bigal/llms-full.txt

Example of using the `count()` method on a repository to get the number of records that satisfy the given conditions, including using string matching operators.

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

--------------------------------

### Sorting and Pagination

Source: https://bigalorm.github.io/bigal/guide/querying.html

Demonstrates how to order results and implement pagination using skip, limit, and the withCount helper.

```typescript
await productRepository.find().sort('name asc, createdAt desc');
await productRepository.find().sort({ name: 1, createdAt: -1 });
await productRepository.find().skip(20).limit(10);
await productRepository.find().paginate(2, 25);
const { results, totalCount } = await productRepository.find().withCount();
```

--------------------------------

### Create and Configure Subqueries in BigAl

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Demonstrates how to initialize a subquery using the subquery() function and apply various builders like select, where, and sort to define the query structure.

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

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

--------------------------------

### Find Single Record with BigAl

Source: https://bigalorm.github.io/bigal/llms-full.txt

Example of using the `findOne()` method on a repository to retrieve a single record based on a specific condition, such as an ID.

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

--------------------------------

### Basic Repository Query Methods

Source: https://bigalorm.github.io/bigal/guide/querying.html

Demonstrates core repository methods including findOne, find, and count. These methods support fluent builder patterns for filtering and projection.

```typescript
const product = await productRepository.findOne().where({ id: 42 });
const product = await productRepository.findOne({ select: ['name', 'sku'] }).where({ id: 42 });
const product = await productRepository.findOne({ pool: poolOverride }).where({ id: 42 });
const products = await productRepository.find().where({ store: storeId });
const count = await productRepository.count().where({ name: { like: 'Widget%' } });
```

--------------------------------

### Use Expose Callback

Source: https://bigalorm.github.io/bigal/reference/configuration.html

Utilizes the expose callback during initialization to execute custom logic for each repository after it has been created.

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

--------------------------------

### BigAl String Matching Operators

Source: https://bigalorm.github.io/bigal/llms-full.txt

Provides examples of using string matching operators like `contains`, `startsWith`, and `endsWith` within the `where` clause for case-insensitive (`ILIKE`) pattern matching.

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

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

--------------------------------

### Execute Raw SQL Queries

Source: https://bigalorm.github.io/bigal/advanced/bigal-vs-raw-sql.html

Demonstrates how to bypass the ORM to execute raw SQL queries directly through the database pool.

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

--------------------------------

### Execute Advanced Queries and Joins with BigAl

Source: https://bigalorm.github.io/bigal/advanced/bigal-vs-raw-sql.html

Shows how to handle subqueries, joins, distinct operations, and conflict resolution using BigAl's fluent API.

```sql
WHERE store_id IN (SELECT id FROM stores WHERE active);
INNER JOIN stores s ON p.store_id = s.id WHERE s.name = 'Acme';
SELECT DISTINCT ON (store_id) * ... ORDER BY store_id, created_at DESC;
ON CONFLICT (sku) DO NOTHING;
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name;
```

```typescript
.where({ store: { in: subquery(storeRepo).select(['id']).where({ active: true }) } });
.join('store').where({ store: { name: 'Acme' } });
.distinctOn(['store']).sort('store').sort('createdAt desc');
{ onConflict: { action: 'ignore', targets: ['sku'] } };
{ onConflict: { action: 'merge', targets: ['sku'], merge: ['name'] } };
```

--------------------------------

### Configure Read Replicas and Query Overrides

Source: https://bigalorm.github.io/bigal/reference/configuration.html

Configures separate read and write pools to optimize database load. Shows how to initialize with a readonlyPool and how to override the pool selection on a per-query basis.

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

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

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

--------------------------------

### QueryResult Type Narrowing in BigAl ORM (TypeScript)

Source: https://bigalorm.github.io/bigal/guide/relationships.html

Demonstrates how BigAl's QueryResult type automatically narrows relationship fields when querying entities. For example, a property defined as `number | Store` becomes just `number` (the foreign key) in the QueryResult.

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

--------------------------------

### initialize()

Source: https://bigalorm.github.io/bigal/llms-full.txt

Initializes the Bigal ORM by creating repositories for the provided entity models using the specified database connection pools.

```APIDOC
## [FUNCTION] initialize()

### Description
Creates repositories for all provided models and establishes database connections.

### Parameters
#### Request Body
- **models** (EntityStatic<Entity>[]) - Required - Model classes decorated with @table()
- **pool** (PoolLike) - Required - Primary connection pool
- **readonlyPool** (PoolLike) - Optional - Pool for read operations
- **connections** (Record<string, IConnection>) - Optional - Named connections for multi-database setups
- **expose** ((repo, metadata) => void) - Optional - Callback invoked for each created repository

### Request Example
{
  "models": [Product, Store],
  "pool": "pool_instance"
}

### Response
#### Success Response (200)
- **repositories** (Record<string, IRepository>) - A map of initialized repositories
```

--------------------------------

### Expose Callback

Source: https://bigalorm.github.io/bigal/llms-full.txt

Utilize the `expose` callback to perform actions for each repository after it's initialized.

```APIDOC
## Expose Callback

### Description
The `expose` callback is invoked for each repository immediately after it has been created. This allows for custom logic, such as logging or setting up additional properties on the repository.

### Method
`initialize({ models, pool, expose, ... })`

### Endpoint
N/A (Initialization function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```ts
const repos = initialize({
  models,
  pool,
  expose(repository, tableMetadata) {
    console.log(`Initialized ${tableMetadata.name}`);
    // You can perform other actions with 'repository' and 'tableMetadata' here
  },
});
```

### Response
#### Success Response (200)
- **repos** (Object) - An object containing initialized repositories.

#### Response Example
(No direct response example, as this is an initialization step.)
```

--------------------------------

### Perform CRUD Operations with BigAl

Source: https://bigalorm.github.io/bigal/advanced/bigal-vs-raw-sql.html

Demonstrates how to perform INSERT, UPDATE, and DELETE operations using BigAl repository methods.

```sql
INSERT INTO products (name) VALUES ('Widget') RETURNING *;
UPDATE products SET name = 'X' WHERE id = 1 RETURNING *;
DELETE FROM products WHERE id = 1 RETURNING *;
```

```typescript
productRepo.create({ name: 'Widget' });
productRepo.update({ id: 1 }, { name: 'X' });
productRepo.destroy({ id: 1 });
```

--------------------------------

### Initialize BigAl Repositories

Source: https://bigalorm.github.io/bigal/reference/api.html

The `initialize` function creates repositories for all provided model classes. It requires model definitions, a primary connection pool, and optionally accepts a read-only pool, named connections, and an expose callback.

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

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

--------------------------------

### Raw SQL Integration

Source: https://bigalorm.github.io/bigal/llms-full.txt

Demonstrates how to use raw SQL queries with the same connection pool used by BigAl for flexibility.

```APIDOC
## Raw SQL Integration

### Description
BigAl allows you to seamlessly integrate raw SQL queries using the same connection pool. This is useful for complex queries or when BigAl's abstractions are not sufficient.

### Method
`pool.query()`

### Endpoint
N/A (Direct method call on the pool)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

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

### Response
#### Success Response (200)
- **rows** (Array) - An array of result rows from the executed query.

#### Response Example
```json
{
  "rows": [
    { "id": 1, "name": "Example Product", "tsv": "'example':1" } 
  ]
}
```
```

--------------------------------

### Initialize BigAl with Neon Serverless

Source: https://bigalorm.github.io/bigal/llms-full.txt

Initializes BigAl using the '@neondatabase/serverless' driver for serverless PostgreSQL connections. A Pool instance is created using the DATABASE_URL environment variable and passed to BigAl's initialize function.

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

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

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

--------------------------------

### Run database queries

Source: https://bigalorm.github.io/bigal/llms-full.txt

Demonstrates fluent query building for finding, filtering, sorting, and counting records.

```typescript
// 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 } });
```

--------------------------------

### Initialize and Query Readonly Repositories

Source: https://bigalorm.github.io/bigal/guide/views.html

Illustrates how to register view models in the BigAl initialization process and perform read-only queries using the repository pattern.

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

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

--------------------------------

### Pagination with Skip and Limit

Source: https://bigalorm.github.io/bigal/llms-full.txt

Shows how to implement pagination using `skip` and `limit` methods. This is useful for retrieving data in chunks, controlling the offset and the number of records per page.

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

--------------------------------

### Inherit and Configure View Models

Source: https://bigalorm.github.io/bigal/guide/views.html

Shows how to extend existing models for readonly views and how to specify a custom database schema for a view model.

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

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

--------------------------------

### Logical Query Composition

Source: https://bigalorm.github.io/bigal/guide/querying.html

Shows how to combine conditions using OR and AND operators for complex filtering requirements.

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

--------------------------------

### Create records with BigAl repository

Source: https://bigalorm.github.io/bigal/guide/crud-operations.html

Demonstrates how to insert single or multiple records into a repository. Includes options for disabling returned records and using query projections to limit returned fields.

```typescript
const product = await productRepository.create({
  name: 'Widget',
  priceCents: 999,
});

const products = await productRepository.create([
  { name: 'Widget', priceCents: 999 },
  { name: 'Gadget', priceCents: 1499 },
]);

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

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

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

--------------------------------

### Connection Pool Configuration

Source: https://bigalorm.github.io/bigal/llms-full.txt

Configuration options for initializing BigAl with different PostgreSQL connection pool drivers.

```APIDOC
## Connection Pool Configuration

### Description
BigAl requires a PostgreSQL connection pool that implements `PoolLike`. It supports multiple drivers including `postgres-pool`, `node-postgres (pg)`, and Neon serverless.

### Method
`initialize({ models, pool, readonlyPool, connections, expose })`

### Endpoint
N/A (Initialization function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
**Using `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 });
```

**Using `node-postgres (pg)`:**
```ts
import pg from 'pg';

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

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

**Using Neon serverless:**
```ts
import { Pool } from '@neondatabase/serverless';

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

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

### Response
#### Success Response (200)
- **repos** (Object) - An object containing initialized repositories.

#### Response Example
(No direct response example, as this is an initialization step. The `repos` object would contain repository instances.)
```

--------------------------------

### Pagination with Paginate Method

Source: https://bigalorm.github.io/bigal/llms-full.txt

Demonstrates using the `paginate` method for simpler pagination. This method takes the page number and page size as arguments to fetch a specific subset of results.

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

--------------------------------

### Initialize BigAl with postgres-pool

Source: https://bigalorm.github.io/bigal/llms-full.txt

Initializes BigAl using the recommended 'postgres-pool' driver. This involves creating a 'postgres-pool' Pool instance and then passing it to BigAl's initialize function along with your models.

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

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

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

--------------------------------

### Initialize BigAl with node-postgres (pg)

Source: https://bigalorm.github.io/bigal/llms-full.txt

Initializes BigAl using the 'node-postgres' (pg) driver. This requires creating a pg.Pool instance and then passing it to BigAl's initialize function along with your models.

```typescript
import pg from 'pg';

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

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

--------------------------------

### Enable SQL Debugging

Source: https://bigalorm.github.io/bigal/reference/configuration.html

Enables SQL query logging by setting the DEBUG_BIGAL environment variable before running the application.

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

--------------------------------

### Aggregate Functions

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Available aggregate functions for subquery selects including count, sum, avg, max, and min.

```APIDOC
## Aggregate Functions

### Description
Perform calculations on sets of values within a subquery. If `.as()` is not called, the function name is used as the alias.

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

### Request Example
```typescript
const stats = subquery(productRepository)
  .select(['store', (sb) => sb.count().as('totalProducts'), (sb) => sb.sum('price').as('totalValue')])
  .groupBy(['store']);
```
```

--------------------------------

### Execute Model and Subquery Joins

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Covers joining related entities defined in models or joining against subquery results, including support for inner/left joins and custom aliases.

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

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

--------------------------------

### JSONB Column Querying

Source: https://bigalorm.github.io/bigal/guide/querying.html

Explains how to query nested properties within JSONB columns using PostgreSQL-style operators, including type casting and containment checks.

```typescript
await repo.find().where({ bar: { theme: 'dark' } });
await repo.find().where({ bar: { retryCount: { '>=': 3 } } });
await repo.find().where({ bar: { failure: { stage: 'transcription' } } });
await repo.find().where({ bar: { contains: { type: 'recovery' }, retryCount: { '<': 3 } } });
```

--------------------------------

### Query readonly repositories

Source: https://bigalorm.github.io/bigal/llms-full.txt

Demonstrates that ReadonlyRepository instances support standard query methods like find, where, and sort, while omitting write operations.

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

--------------------------------

### Define and initialize readonly view models

Source: https://bigalorm.github.io/bigal/llms-full.txt

Illustrates how to map a PostgreSQL view to a BigAl entity using the @table decorator with readonly: true, and how to initialize the corresponding ReadonlyRepository.

```TypeScript
@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;
}
```

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

--------------------------------

### Translate SQL Queries to BigAl Repository Methods

Source: https://bigalorm.github.io/bigal/llms-full.txt

A collection of common SQL patterns translated into BigAl's fluent repository API, covering basic selection, filtering, and CRUD operations.

```typescript
// Basic Queries
productRepo.findOne().where({ id: 1 });
productRepo.findOne({ select: ['name'] }).where({ id: 1 });
productRepo.find().where({ name: { contains: 'widget' } });
productRepo.find().where({ price: { '>=': 100 } });
productRepo.find().where({ status: ['a', 'b'] });
productRepo.find().where({ status: { '!': 'x' } });
productRepo.find().where({ deletedAt: { '!': null } });
productRepo.find().where({}).sort('name asc').limit(10);
productRepo.count().where({ active: true });

// CRUD
productRepo.create({ name: 'Widget' });
productRepo.update({ id: 1 }, { name: 'X' });
productRepo.destroy({ id: 1 });

// Advanced
.where({ store: { in: subquery(storeRepo).select(['id']).where({ active: true }) } });
.join('store').where({ store: { name: 'Acme' } });
.distinctOn(['store']).sort('store').sort('createdAt desc');
{ onConflict: { action: 'ignore', targets: ['sku'] } };
{ onConflict: { action: 'merge', targets: ['sku'], merge: ['name'] } };
```

--------------------------------

### Create Record with Query Projection (returnSelect)

Source: https://bigalorm.github.io/bigal/llms-full.txt

Creates a record and returns only specified columns using the 'returnSelect' option. The primary key is always included in the returned object. An empty array for 'returnSelect' returns only the primary key.

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

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

--------------------------------

### Query Projection in BigAl

Source: https://bigalorm.github.io/bigal/llms-full.txt

Demonstrates how to select specific columns when fetching a single record using `findOne()` by providing a `select` array in the query options.

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

--------------------------------

### Sort Results by Joined Columns

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Explains how to use dot notation to sort query results based on columns from joined models or subquery aliases.

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

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

--------------------------------

### Perform Scalar Subquery Comparisons

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Illustrates comparing column values against the result of a single-value subquery, such as aggregate functions like avg, max, or min.

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

const expensiveProducts = await productRepository.find().where({
  price: { '>': avgPrice },
});
```

--------------------------------

### Read Replicas Configuration

Source: https://bigalorm.github.io/bigal/llms-full.txt

Configure BigAl to use separate read and write connection pools for optimized performance.

```APIDOC
## Read Replicas Configuration

### Description
Separate read and write pools by passing a `readonlyPool` option during initialization. Read operations (`find`, `findOne`, `count`) will use the `readonlyPool`, while write operations (`create`, `update`, `destroy`) will use the primary `pool`.

### Method
`initialize({ models, pool, readonlyPool, ... })`

### Endpoint
N/A (Initialization function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

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

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

### Response
#### Success Response (200)
- **repos** (Object) - An object containing initialized repositories.

#### Response Example
(No direct response example, as this is an initialization step.)

### Overriding Pool for Individual Queries
Individual queries can override the default pool:

```ts
const product = await productRepository
  .findOne({
    pool: writePool, // Explicitly use writePool for this findOne operation
  })
  .where({ id: 42 });
```
```

--------------------------------

### Sorting Query Results

Source: https://bigalorm.github.io/bigal/llms-full.txt

Illustrates two syntaxes for sorting query results: string syntax and object syntax. Both methods allow specifying multiple fields for sorting in ascending or descending order.

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

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

--------------------------------

### Create Operations

Source: https://bigalorm.github.io/bigal/guide/crud-operations.html

Handles the creation of single or multiple records, with options to skip returning records or specify a query projection.

```APIDOC
## Create Operations

### Description
Handles the creation of single or multiple records, with options to skip returning records or specify a query projection.

### Method
POST

### Endpoint
`/api/records` (Conceptual Endpoint)

### Parameters
#### Request Body
- **data** (object or array of objects) - Required - The data for the record(s) to be created.
- **options** (object) - Optional - Configuration options for the create operation.
  - **returnRecords** (boolean) - Optional - Whether to return the created records. Defaults to true.
  - **returnSelect** (array of strings) - Optional - Specifies which columns to return. If empty, only the primary key is returned. If omitted, all columns are returned.

### Request Example (Single Record)
```typescript
const product = await productRepository.create({
  name: 'Widget',
  priceCents: 999,
});
```

### Request Example (Multiple Records)
```typescript
const products = await productRepository.create([
  { name: 'Widget', priceCents: 999 },
  { name: 'Gadget', priceCents: 1499 },
]);
```

### Request Example (Skip Returning Records)
```typescript
await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnRecords: false });
```

### Request Example (Query Projection)
```typescript
const product = await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnSelect: ['name'] });
```

### Request Example (Query Projection - Primary Key Only)
```typescript
const product = await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnSelect: [] });
```

### Response
#### Success Response (200)
- **records** (array of objects) - The created record(s) or specified fields.

#### Response Example (Single Record)
```json
{
  "id": 42,
  "name": "Widget",
  "priceCents": 999,
  "createdAt": "2023-10-27T10:00:00Z"
}
```

#### Response Example (Multiple Records)
```json
[
  { "id": 42, "name": "Widget", "priceCents": 999, "createdAt": "2023-10-27T10:00:00Z" },
  { "id": 43, "name": "Gadget", "priceCents": 1499, "createdAt": "2023-10-27T10:00:00Z" }
]
```

#### Response Example (Query Projection)
```json
{
  "id": 42,
  "name": "Widget"
}
```

#### Response Example (Primary Key Only)
```json
{
  "id": 42
}
```
```

--------------------------------

### Debugging with DEBUG_BIGAL

Source: https://bigalorm.github.io/bigal/llms-full.txt

Enable detailed logging of generated SQL queries by setting the `DEBUG_BIGAL` environment variable.

```APIDOC
## Debugging with DEBUG_BIGAL

### Description
To help diagnose issues and understand how BigAl translates your code into SQL, you can enable SQL logging by setting the `DEBUG_BIGAL` environment variable to `true`.

### Method
Environment Variable

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
**Shell Command:**
```sh
DEBUG_BIGAL=true node app.js
```

### Response
#### Success Response (200)
N/A (This is a debugging configuration, not an API endpoint.)

#### Response Example
(When enabled, SQL queries will be logged to the console during application runtime.)
```

--------------------------------

### Filter Subqueries with GROUP BY and HAVING

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Shows how to group data and apply filtering conditions on aggregated values using the HAVING clause. Supports various comparison operators and multiple conditions.

```typescript
const popularCategories = subquery(productRepository)
  .select(['category', (sb) => sb.count().as('productCount')])
  .groupBy(['category'])
  .having({ productCount: { '>': 10 } });

// Multiple conditions
.having({ productCount: { '>=': 5, '<=': 100 } });
```

--------------------------------

### Define and Query PostgreSQL Models with BigAl TypeScript ORM

Source: https://bigalorm.github.io/bigal

This snippet demonstrates how to define a PostgreSQL table using decorator-based models in TypeScript with the BigAl ORM. It also shows how to initialize the ORM with a connection pool and perform fluent queries, including filtering, sorting, limiting, joins, subqueries, and upserts with ON CONFLICT.

```typescript
import { column, primaryColumn, table, Entity, initialize } from 'bigal';
import { Pool } from 'postgres-pool';

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

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

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

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

const repos = initialize({
  models: [Product, Store],
  pool: new Pool('postgres://localhost/mydb'),
});
const productRepo = repos.Product as Repository<Product>;

// Fluent queries — just await the chain
const products = await productRepo
  .find()
  .where({ priceCents: { '>': 1000 }, name: { contains: 'widget' } })
  .sort('name asc')
  .limit(10);

// Joins and subqueries
const expensiveProducts = await productRepo
  .find()
  .join('store')
  .where({
    store: { name: 'Acme' },
    price: { '>': subquery(productRepo).avg('price') },
  });

// Upserts with ON CONFLICT
await productRepo.create({ name: 'Widget', sku: 'WDG-001', priceCents: 999 }, { onConflict: { action: 'merge', targets: ['sku'], merge: ['priceCents'] } });

```

--------------------------------

### Implement type-safe subqueries

Source: https://bigalorm.github.io/bigal/llms-full.txt

Demonstrates the creation of subqueries for use in WHERE IN, NOT IN, EXISTS, and NOT EXISTS clauses.

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

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

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

// WHERE EXISTS
const stores = await storeRepository.find().where({
  exists: subquery(productRepository).where({ name: { like: 'Widget%' } }),
});
```

--------------------------------

### Filter with WHERE IN and EXISTS Clauses

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Shows how to use subqueries to filter main query results using IN/NOT IN and EXISTS/NOT EXISTS operators.

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

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

--------------------------------

### Load related entities with Populate

Source: https://bigalorm.github.io/bigal/guide/querying.html

Fetches related entities for a given record. Allows selection of specific fields to optimize performance and returns the full entity object.

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

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

--------------------------------

### Core Types and Interfaces

Source: https://bigalorm.github.io/bigal/llms-full.txt

Definitions for foundational types and repository interfaces used within the BigAl ORM ecosystem.

```APIDOC
## Core Types and Interfaces

### Description
Defines the base structures for entities, connection pools, and repository patterns.

### Interfaces
- **IConnection**: Defines the connection pool configuration.
  ```ts
  interface IConnection {
    pool: PoolLike;
    readonlyPool?: PoolLike;
  }
  ```
- **IRepository<T>**: Interface for full CRUD operations.
- **IReadonlyRepository<T>**: Interface for read-only data access.

### Key Types
- **Entity**: Base class for all models.
- **NotEntity<T>**: Wrapper for JSON objects to prevent entity type treatment.
- **QueryResult<T>**: Narrows relationship fields to foreign key types.
- **PoolLike**: Interface for compatible drivers (postgres-pool, pg, @neondatabase/serverless).
```

--------------------------------

### Aggregate Functions

Source: https://bigalorm.github.io/bigal/llms-full.txt

Utilize aggregate functions like count(), sum(), avg(), max(), and min() within subquery selects. Aliases can be specified using .as().

```APIDOC
## Aggregate Functions

### Description
Available aggregate functions in subquery selects.

### Method
N/A (Illustrative code)

### Endpoint
N/A (Illustrative code)

### Parameters
N/A

### Request Example
```ts
// Available functions:
// count()
// count(column)
// count(column).distinct()
// sum(column)
// avg(column)
// max(column)
// min(column)

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

### Response
N/A

### Success Response (200)
N/A

### Response Example
N/A
```

--------------------------------

### Using QueryResult in Type Definitions (TypeScript)

Source: https://bigalorm.github.io/bigal/guide/relationships.html

Shows the correct way to create derived types from queried entities using `Pick<QueryResult<T>, ...>` instead of `Pick<T, ...>` to leverage the automatic type narrowing provided by QueryResult.

```typescript
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'>;
```

--------------------------------

### Paginated Results with Total Count

Source: https://bigalorm.github.io/bigal/llms-full.txt

Explains how to retrieve paginated results along with the total count of records using `withCount()`. This is achieved efficiently in a single query using `COUNT(*) OVER()`, useful for displaying total pages or item counts.

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

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

--------------------------------

### Query Filtering Operators

Source: https://bigalorm.github.io/bigal/guide/querying.html

Covers various where clause operators including string matching, numerical comparisons, array inclusion, and logical negation.

```typescript
await productRepository.find().where({ name: { contains: 'widget' } });
await productRepository.find().where({ name: { startsWith: 'Pro' } });
await productRepository.find().where({ price: { '>=': 100 } });
await productRepository.find().where({ createdAt: { '>=': startDate, '<': endDate } });
await personRepository.find().where({ age: [22, 23, 24] });
await productRepository.find().where({ status: { '!': 'discontinued' } });
```

--------------------------------

### Define Many-to-Many Relationship in BigAl

Source: https://bigalorm.github.io/bigal/llms-full.txt

Illustrates setting up a many-to-many relationship using the `@column` decorator with `collection` and `through` options, specifying the join table and the inverse property.

```typescript
import type { Category } from './Category';
import type { ProductCategory } from './ProductCategory';

// ...

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

--------------------------------

### GROUP BY and HAVING

Source: https://bigalorm.github.io/bigal/guide/subqueries-and-joins.html

Grouping results and applying conditional filters on aggregate values using the HAVING clause.

```APIDOC
## GROUP BY and HAVING

### Description
Groups query results by specified columns and filters groups using aggregate conditions.

### Supported Operators
- `{ alias: 5 }` -> AGG(*)=5
- `{ alias: { '>': 5 } }` -> AGG(*)>5
- `{ alias: { '<': 5 } }` -> AGG(*)<5
- `{ alias: { '!=': 5 } }` -> AGG(*)<>5

### Request Example
```typescript
.having({ productCount: { '>': 10 } })
```
```

--------------------------------

### Create Single Record

Source: https://bigalorm.github.io/bigal/llms-full.txt

Creates a single new record in the database. By default, it returns the affected record(s) using `RETURNING *`.

```APIDOC
## Create Single Record

### Description
Creates a single new record in the database. By default, the created record is returned.

### Method
`create(data, options?)`

### Endpoint
N/A (Repository method)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **data** (Object) - An object containing the fields and values for the new record.
- **options** (Object) - Optional configuration for the create operation.
  - **returnRecords** (Boolean) - If `false`, records are not returned. Defaults to `true`.
  - **returnSelect** (Array<String>) - Specifies which columns to return. The primary key is always included. If empty array, only the primary key is returned.

### Request Example
**Basic Create:**
```ts
const product = await productRepository.create({
  name: 'Widget',
  priceCents: 999,
});
```

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

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

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

### Response
#### Success Response (200)
- **createdRecord** (Object) - The newly created record, including its primary key and returned fields based on `returnRecords` and `returnSelect` options. If `returnRecords` is `false`, no record is returned.

#### Response Example
```json
{
  "id": 42,
  "name": "Widget",
  "priceCents": 999,
  "createdAt": "2023-10-27T10:00:00.000Z"
}
```
```

--------------------------------

### BigAl AND with Nested OR Conditions

Source: https://bigalorm.github.io/bigal/llms-full.txt

Demonstrates how to combine `AND` and nested `OR` conditions in the `where` clause for complex query logic.

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