using-bigal

name	using-bigal
description	Type-safe PostgreSQL ORM guidance for BigAl. Use when importing BigAl, defining Entity models with decorators, writing WhereQuery filters, using Repository patterns, or deciding between BigAl and raw SQL. Covers model definition, fluent query building, joins, subqueries, pagination, JSONB querying, and common gotchas.

Using BigAl

BigAl is a PostgreSQL-optimized, type-safe TypeScript ORM. It uses decorator-based models, a fluent builder pattern for queries, and the Repository pattern for CRUD operations.

Quick Start

import { column, primaryColumn, table, Entity, initialize, Repository } 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, name: 'price_cents' })
  public priceCents!: number;
}

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

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

When to Use BigAl vs Raw SQL

Use BigAl for:

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

Drop to raw SQL for:

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

BigAl wraps your existing connection pool - postgres-pool, pg, or @neondatabase/serverless. The pool is always accessible for raw queries, so you can eject to SQL at any point:

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

Use BigAl for the 90% of queries that fit its fluent API, and raw SQL for the rest.

SQL-to-BigAl Translation Table

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	productRepo.destroy({ id: 1 })
DELETE FROM products WHERE id = 1 RETURNING *	productRepo.destroy({ id: 1 }, { returnRecords: true })

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 ON products.store_id = stores.id WHERE stores.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'] } }

Model Definition

Decorators

Every model extends Entity and uses decorators:

import { column, primaryColumn, createDateColumn, updateDateColumn, versionColumn, table, Entity } from 'bigal';

@table({ name: 'products', schema: 'public' })
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;

  @column({ type: 'json' })
  public metadata?: Record<string, unknown>;

  @createDateColumn()
  public createdAt!: Date;

  @updateDateColumn()
  public updatedAt!: Date;

  @versionColumn()
  public version!: number;
}

Column types

'string', 'integer', 'float', 'boolean', 'date', 'datetime', 'json', 'string[]', 'integer[]', 'float[]', 'boolean[]', 'vector'

Vector columns (pgvector) are declared with @column({ type: 'vector', dimensions: n }) on a number[] property. dimensions is informational - BigAl does not issue DDL.

Relationships

Many-to-one - current entity holds the foreign key:

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

One-to-many - inverse side (must be optional):

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

Many-to-many - requires a join table Entity:

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

Reference model names by string ('Store', not Store) to avoid circular imports. Model names are case-insensitive.

Readonly models (views)

@table({ name: 'product_summaries', readonly: true })
class ProductSummary extends Entity {
  /* ... */
}

initialize() returns a ReadonlyRepository which omits create, update, and destroy.

Query Patterns

Fluent builder

Every query method returns a new immutable instance. Queries are PromiseLike - just await the chain.

// Find multiple
const products = await productRepo.find().where({ store: storeId }).sort('name asc').limit(10);

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

// Count
const count = await productRepo.count().where({ sku: { '!': null } });

Where operators

// Comparison: <, <=, >, >=
.where({ price: { '>=': 100 } })

// Negation
.where({ status: { '!': 'discontinued' } })    // <> value
.where({ status: { '!': ['a', 'b'] } })        // NOT IN
.where({ deletedAt: { '!': null } })            // IS NOT NULL

// String matching (case-insensitive via ILIKE)
.where({ name: { contains: 'widget' } })        // %widget%
.where({ name: { startsWith: 'Pro' } })         // Pro%
.where({ name: { endsWith: 'ket' } })           // %ket
.where({ name: { like: 'W_dget%' } })           // raw ILIKE

// Array values (IN)
.where({ age: [22, 23, 24] })

// OR conditions
.where({ or: [{ firstName: 'Walter' }, { lastName: 'White' }] })

// Range (multiple operators on same field = AND)
.where({ createdAt: { '>=': startDate, '<': endDate } })

JSONB querying

// Property equality
.where({ metadata: { theme: 'dark' } })

// Numeric comparison (auto-casts)
.where({ metadata: { retryCount: { '>=': 3 } } })

// Nested paths
.where({ metadata: { failure: { stage: 'transcription' } } })

// Containment
.where({ metadata: { contains: { type: 'recovery' } } })

Pagination

// Manual
.skip(20).limit(10)

// Shorthand
.paginate(2, 25)   // page 2, 25 per page

// With total count (single query using COUNT(*) OVER())
const { results, totalCount } = await productRepo
  .find().where({}).sort('name').limit(10).skip(20).withCount();

Populate (eager loading)

const product = await productRepo
  .findOne()
  .where({ id: 42 })
  .populate('store', { select: ['name'] });
// product.store is the full Store entity

populate() does not use a SQL JOIN. After the main query resolves, it runs a separate query per relation (batched by id, in parallel) and nests the results, so .join() is not needed to populate. Every primary row is returned whether or not the relation exists (an absent to-one is undefined, an empty to-many is []). populate's where/limit constrain only the related rows, not the primary results. Add .join() only when you need to filter or sort the primary results by a related table's columns.

Joins

Use .join()/.leftJoin() to constrain or sort the primary results by columns on a related table (or to join subquery aggregates), not to load related records. To load related records, use .populate() (above), which does not require a join.

// Model join (INNER)
await productRepo
  .find()
  .join('store')
  .where({ store: { name: 'Acme' } });

// Left join
await productRepo
  .find()
  .leftJoin('store')
  .where({ store: { name: 'Acme' } });

// Subquery join with aggregates
const productCounts = subquery(productRepo)
  .select(['store', (sb) => sb.count().as('productCount')])
  .groupBy(['store']);

await storeRepo
  .find()
  .join(productCounts, 'stats', { on: { id: 'store' } })
  .sort('stats.productCount desc');

DISTINCT ON

// Most recently created product per store
await productRepo.find().distinctOn(['store']).sort('store').sort('createdAt desc');

ORDER BY must start with the DISTINCT ON columns. Cannot combine with withCount().

Create

// Single record - returns the created record
const product = await productRepo.create({ name: 'Widget', priceCents: 999 });

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

// Upsert: ON CONFLICT DO NOTHING
await productRepo.create({ name: 'Widget', sku: 'WDG-001' }, { onConflict: { action: 'ignore', targets: ['sku'] } });

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

// Skip returning records
await productRepo.create({ name: 'Widget', priceCents: 999 }, { returnRecords: false });

// Return only specific columns (primary key always included)
await productRepo.create({ name: 'Widget', priceCents: 999 }, { returnSelect: ['name'] });

Update

By default update() runs RETURNING * and hydrates the full updated rows. Shape what comes back to only what you need, or skip it entirely, to reduce bytes over the wire and hydration cost:

// Returns array of updated records (RETURNING *)
const products = await productRepo.update({ id: 42 }, { name: 'Super Widget' });

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

// Return only specific columns (primary key always included) - smaller RETURNING clause, less to hydrate
const products = await productRepo.update({ id: 42 }, { name: 'Super Widget' }, { returnSelect: ['name'] });

// Skip returning records entirely (no RETURNING clause) - fastest when you do not need the rows back
await productRepo.update({ id: 42 }, { name: 'Super Widget' }, { returnRecords: false });

Destroy

Unlike create()/update(), destroy() does not return records by default. It emits a plain DELETE with no RETURNING clause, which is the cheapest option. Opt in to the deleted rows only when you need them, and prefer returnSelect to bring back just the columns you use:

// Default: deletes and resolves to void (no RETURNING clause)
await productRepo.destroy({ id: 42 });

// Opt in to the deleted rows (RETURNING *)
const products = await productRepo.destroy({ id: 42 }, { returnRecords: true });

// Return only specific columns (primary key always included) - returnSelect implies returnRecords
const products = await productRepo.destroy({ id: 42 }, { returnSelect: ['name'] });

Subqueries

import { subquery } from 'bigal';

// WHERE IN
const activeStores = subquery(storeRepo).select(['id']).where({ isActive: true });
await productRepo.find().where({ store: { in: activeStores } });

// WHERE EXISTS
const hasProducts = subquery(productRepo).where({ name: { like: 'Widget%' } });
await storeRepo.find().where({ exists: hasProducts });

// Scalar comparison
const avgPrice = subquery(productRepo).avg('price');
await productRepo.find().where({ price: { '>': avgPrice } });

Vector distance queries

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

// Sort by similarity - metric: 'cosine' (default, <=>), 'l2' (<->), 'l1' (<+>), 'innerProduct' (<#>)
const similar = await documentRepo
  .find()
  .sort({ embedding: { nearestTo: queryVector, metric: 'cosine' } })
  .limit(10);

// Filter by distance threshold
const nearby = await documentRepo
  .find()
  .where({ embedding: { nearestTo: queryVector, metric: 'cosine', distance: { '<': 0.5 } } })
  .sort({ embedding: { nearestTo: queryVector, metric: 'cosine' } })
  .limit(10);

// Writes serialize number[] to pgvector text format; reads parse it back to number[]
await documentRepo.create({ title: 'foo', embedding: [0.1, 0.2, 0.3] });

Gotchas

Collections must be optional

Collection properties (one-to-many, many-to-many) must use ?, not !. They are only present after .populate():

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

// Wrong - causes QueryResult type errors
@column({ collection: () => 'Product', via: 'store' })
public products!: Product[];

NotEntity for JSON objects with id fields

If a JSON column contains objects with an id property, wrap the type with NotEntity<T> to prevent BigAl's type system from treating them as entities:

import type { NotEntity } from 'bigal';

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

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

Query state is immutable

Each fluent method returns a new instance. Do not ignore the return value:

// Correct
const query = productRepo.find().where({ store: storeId });
const sorted = query.sort('name asc');
const results = await sorted.limit(10);

// Wrong - .sort() result is discarded
const query = productRepo.find().where({ store: storeId });
query.sort('name asc'); // This does nothing to `query`
const results = await query;

QueryResult narrows relationship types

QueryResult<T> automatically narrows number | Store to number. Use QueryResult<T> (not T) for derived types:

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

Select only the columns you need

find()/findOne() select every column by default. Pass select to return only the columns you actually use. This shrinks the SELECT list, reduces bytes transferred, and lowers hydration cost. For wide rows, large JSON blobs, or vector/embedding columns it can be a large win, since the database only reads and ships those columns when you ask for them. populate() and joined models accept the same select option:

// Selects every column
const product = await productRepo.findOne().where({ id: 42 });

// Selects only the primary key (always included) + name + priceCents
const product = await productRepo.findOne({ select: ['name', 'priceCents'] }).where({ id: 42 });

// Applies to lists, populated relations, and joins too
const products = await productRepo
  .find({ select: ['name'] })
  .where({ store: storeId })
  .populate('store', { select: ['name'] });

Prefer count() over findOne() for existence checks

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:

// Correct
const exists = (await productRepo.count().where({ sku: 'ABC123' })) > 0;

// Wasteful - findOne() selects and hydrates a full row just to check for existence
const exists = (await productRepo.findOne().where({ sku: 'ABC123' })) != null;

Prefer an array to create() over looping

Passing an array to create() builds a single multi-row INSERT statement - one round trip for the whole batch. Calling create() in a loop issues a separate INSERT (and round trip) per record:

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

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

Debugging SQL

Set DEBUG_BIGAL=true to log all generated SQL and parameter values:

DEBUG_BIGAL=true node app.js

Success Criteria

After applying this skill, verify:

• Models extend Entity and use @table(), @primaryColumn(), @column() decorators
• Queries use the fluent builder pattern (not raw SQL strings)
• CRUD uses Repository methods: create(), update(), destroy()
• Query chains are awaited (not fire-and-forget)
• Query state is treated as immutable (return values are used)
• Collection properties are optional (?)
• JSON column types with id fields use NotEntity<T>
• Model names in decorators are strings ('Store') to avoid circular imports
• QueryResult<T> is used for derived types involving relationships
• Queries return only needed data: select on find()/findOne()/populate(), and returnSelect / returnRecords: false on create()/update()/destroy() when the full row is not needed

Further Reading

• Getting Started - install, first model, first query
• Models - decorators, column options, relationships
• Querying - operators, pagination, JSONB, DISTINCT ON
• CRUD Operations - create, update, destroy, upserts
• Relationships - many-to-one, one-to-many, many-to-many, QueryResult
• Subqueries and Joins - subquery builder, aggregates, GROUP BY
• Views - readonly models and ReadonlyRepository
• API Reference - all exports and method signatures
• Configuration - pools, read replicas, multi-database
• BigAl vs Raw SQL - decision framework
• Known Issues - workarounds and debugging