Querying

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

findOne

Returns a single record or null:

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.

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

Pool override

Use an explicit connection pool:

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

find

Returns an array of records:

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

count

Returns the number of matching records:

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:

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

Where operators

String matching

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

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

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

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

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

Array values (IN)

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

Negation (!)

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

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

AND with nested OR

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

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

Comparisons on JSON properties

Numeric and boolean values are automatically cast:

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

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:

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:

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

Sorting

String syntax

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

Object syntax

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

Vector distance queries

BigAl supports nearest-neighbor queries on columns declared with @column({ type: 'vector', dimensions: n }), backed by the 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:

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:

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:

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

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

paginate

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

withCount

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

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:

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

Requirements:

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

Populate

Load related entities:

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

toJSON

Return plain objects without class prototypes:

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