QueryBuilder

QueryBuilder<T> is returned by pb.from(ModelClass). Methods are chainable — most return this and are resolved by a terminal execution method.

Read Methods (Terminal)

.find()

Executes a SELECT and returns an array of records.

const users = await pb.from(User).find();
// Returns: User[]

.findOne()

Returns the first matching record, or null.

const user = await pb.from(User).where('email', 'jane@example.com').findOne();
// Returns: User | null

.count()

Returns the number of matching records.

const total = await pb.from(User).where('status', 'active').count();
// Returns: number

---

Filter Methods (Chainable)

.where(field, value)

.where(field, operator, value)

Add a WHERE condition. Multiple .where() calls are combined with AND.

Operator	SQL
(default)	=
'$eq'	=
'$ne'	!=
'$gt'	>
'$gte'	>=
'$lt'	<
'$lte'	<=
'$like'	LIKE '%x%'
'$ilike'	ILIKE '%x%'
'$in'	IN (...)
'$nin'	NOT IN (...)
'$between'	BETWEEN x AND y

pb.from(User)
  .where('status', 'active')
  .where('age', '$gte', 18)
  .where('role', { $in: ['admin', 'member'] })
  .find();

// SQL operators also work
pb.from(User).where('age', '>=', 18).find();

.whereRaw(rawConditions)

Pass a raw query object directly (advanced usage).

pb.from(User).whereRaw({ $or: [{ status: 'active' }, { role: 'admin' }] }).find();

.whereHas(relationName, callback?)

Filter records that have at least one related record (EXISTS subquery).

// Users that have at least one order
pb.from(User).whereHas('orders').find();

// Users with at least one pending order
pb.from(User)
  .whereHas('orders', (q) => q.where('status', 'pending'))
  .find();

.whereDoesntHave(relationName, callback?)

Filter records that have no related records.

// Users with no orders
pb.from(User).whereDoesntHave('orders').find();

.whereFts(fields, query, options?)

PostgreSQL full-text search across one or more columns.

pb.from(Article)
  .whereFts(['title', 'body'], 'typescript tutorial')
  .find();

// With language config
pb.from(Article)
  .whereFts(['title', 'body'], 'machine learning', { language: 'english' })
  .find();

---

Column Selection (Chainable)

.select(...fields)

Select specific columns or aggregate expressions.

pb.from(User).select('id', 'name', 'email').find();

// With aggregations
pb.from(Order).select(
  'status',
  { Function: 'COUNT', Field: '*', As: 'total' },
  { Function: 'AVG', Field: 'amount', As: 'avg_amount' }
).groupBy('status').find();

.distinct()

Add SELECT DISTINCT.

pb.from(User).select('city').distinct().find();

.distinctOn(...columns)

PostgreSQL SELECT DISTINCT ON (...).

pb.from(Order).distinctOn('user_id').orderBy('created_at', 'desc').find();

---

Sorting & Pagination (Chainable)

.orderBy(field, direction?, nulls?)

pb.from(Post).orderBy('created_at', 'desc').find();
pb.from(Post).orderBy('created_at', 'desc', 'last').find(); // NULLS LAST

.limit(n)

pb.from(User).limit(50).find();

Default limit: 100 when not specified.

.offset(n)

pb.from(User).limit(20).offset(40).find(); // page 3

---

Relations (Chainable)

.with(relationName, options?)

Eager-load a relation. Returns nested data attached to each parent record.

pb.from(Room).with('members').find();

pb.from(Room).with('members', {
  select: ['id', 'email'],
  limit: 50,
}).find();

// Nested eager loading
pb.from(Post).with('author').with('comments', { with: { author: {} } }).find();

.join(clause)

Structured SQL JOIN (type-safe — no raw SQL).

pb.from(User)
  .join({
    table: 'orders',
    type: 'LEFT',
    on: [{ left: { table: 'users', column: 'id' }, op: '=', right: { table: 'orders', column: 'user_id' } }],
  })
  .select('users.name', 'orders.total')
  .find();

type options: 'INNER', 'LEFT', 'RIGHT', 'FULL', 'CROSS'.

---

Grouping & Aggregation (Chainable)

.groupBy(...columns)

pb.from(Order).select('status', { Function: 'COUNT', Field: '*', As: 'n' })
  .groupBy('status').find();

.having(conditions)

Post-aggregation filter.

pb.from(Order)
  .select('user_id', { Function: 'COUNT', Field: '*', As: 'total' })
  .groupBy('user_id')
  .having({ total: { $gt: 5 } })
  .find();

---

Write Methods (Terminal)

.insert(payload)

// Single record
const result = await pb.from(User).insert({ name: 'Jane', email: 'jane@example.com' });

// Multiple records (bulk)
const result = await pb.from(User).insert([
  { name: 'Jane', email: 'jane@example.com' },
  { name: 'John', email: 'john@example.com' },
]);

Returns MutationResponse<T>.

.update(payload)

await pb.from(User).where('id', userId).update({ status: 'banned' });

// Advanced: increment a column
await pb.from(Post).where('id', postId).update({ $inc: { view_count: 1 } });

.delete()

await pb.from(User).where('id', userId).delete();

Caution

Always include a .where() clause — without it, delete() will delete all rows in the table.

.upsert(payload, onConflict)

Insert or update on conflict.

await pb.from(UserSettings).upsert(
  { user_id: userId, theme: 'dark' },
  { columns: ['user_id'], do: 'update' }
);

.insertWith(payload, relations)

Create a parent record with nested child records in a single operation.

await pb.from(Order).insertWith(
  { user_id: userId, total: 150 },
  [{ order_items: [{ product_id: 'p-1', qty: 2 }, { product_id: 'p-2', qty: 1 }] }]
);

.bulkUpdate(instructions)

Multiple targeted updates in one request.

await pb.from(Product).bulkUpdate([
  { where: { id: 'p-1' }, payload: { stock: 10 } },
  { where: { id: 'p-2' }, payload: { stock: 0, status: 'sold_out' } },
]);

.bulkDelete(instructions)

Multiple targeted deletes in one request.

await pb.from(Notification).bulkDelete([
  { where: { id: 'n-1' } },
  { where: { id: 'n-2' } },
]);

---

Safe Variants (Go-style Error Handling)

Returns a result object instead of throwing. Ideal for form processing.

.safeInsert(payload)

const result = await pb.from(User).safeInsert({ email: 'invalid' });

if (!result.ok) {
  result.errors.forEach(e => console.log(e.field, e.message));
} else {
  console.log(result.data);
}

.safeUpdate(payload)

const result = await pb.from(User).where('id', id).safeUpdate({ age: -1 });
if (!result.ok) { /* handle validation errors */ }

SafeResult<T> shape:

{
  ok: boolean;
  data: MutationResponse<T> | null;
  errors: Array<{ field: string; message: string }>;
}

---

Transaction Builders (Synchronous)

These prepare (but do not execute) an operation for use in pb.transaction([...]).

Method	Description
.buildInsert(payload)	Prepare an INSERT
.buildUpdate(payload)	Prepare an UPDATE
.buildDelete()	Prepare a DELETE
.buildFind()	Prepare a SELECT (inside transaction snapshot)
.buildFindOne()	Prepare a SELECT ONE
.buildUpsert(payload, onConflict)	Prepare an UPSERT
.buildBulkUpdate(instructions)	Prepare a bulk update
.buildBulkDelete(instructions)	Prepare a bulk delete
.toPayload()	Extract the raw QueryRequest payload

const results = await pb.transaction([
  pb.from(Account).where('id', fromId).buildUpdate({ $dec: { balance: 100 } }),
  pb.from(Account).where('id', toId).buildUpdate({ $inc: { balance: 100 } }),
]);

---

Real-time (Fluent)

.on(...events).listen(callback)

Subscribe to real-time changes for this model’s table. Returns an unsubscribe function.

const unsub = pb.from(Message).on('INSERT').listen((msg) => {
  console.log('New message:', msg.data);
});
// Stop listening:
unsub();

.listenAll(callback)

Listen to all events (INSERT, UPDATE, DELETE) on this table.

const unsub = pb.from(User).listenAll((msg) => {
  console.log(msg.action, msg.data);
});

---

returning(...columns) (Chainable)

Specify which columns to return after a mutation.

const { data } = await pb.from(User).returning('id', 'created_at').insert({ name: 'Jane' });

---

.getFormMeta()

Returns form metadata inferred from the model’s validation rules — useful for building dynamic forms.

const meta = pb.from(User).getFormMeta();
// { fields: { email: { required: true, format: 'email' }, name: { min: 3 } } }

---

.relation(name)

Access a RelationBuilder for relation management (M2M).

// Also accessible via pb.model(Room, id).members()
pb.from(Room).relation('members'); // → RelationBuilder

---

Return Types

MutationResponse<T>

{
  data: T | T[];
  rows_affected: number;
  is_inserted?: boolean;  // For upsert only
}

ChangeMessage

{
  table: string;
  action: 'INSERT' | 'UPDATE' | 'DELETE';
  data: Record<string, any>;
  old_data?: Record<string, any>;
}