Counting

Five ways to ask “how many”, each with a different cost. Pick by what you actually need.

You need	Use	Costs
How many match	count(Entity, query?)	one statement
Whether any match	exists(Entity, query?)	one statement, stops at the first row
A page and its total	findManyAndCount(Entity, query)	usually one statement on SQL, two on MongoDB
How many rows a relation holds	$count on a read	one statement per relation, whatever the page size
A rough size of a huge table	estimatedCount(Entity)	no scan at all

count

await pool.count(User); // every row
await pool.count(User, { $where: { active: true } }); // matching rows

$skip and $limit count that page rather than every match, which is how you cap the work on a table too big to scan:

await pool.count(User, { $limit: 1000 }); // "1,000+ matches" without counting the rest
await pool.count(User, { $skip: 20 }); // how many remain after the first 20

A plain count is SELECT COUNT(*). A paged one settles the matching ids instead and counts those, because OFFSET would push a single COUNT(*) row out of the result set entirely.

count takes no $sort: ordering picks which rows a page holds, never how many.

exists

if (await pool.exists(User, { $where: { email } })) {
  throw new Error('email already registered');
}

A count capped at one row, so the engine stops at the first match instead of scanning the rest. Prefer it over count(...) > 0, which counts every match to learn something the first row settles.

findManyAndCount

The page plus how many matched beyond it - what a paginated list needs:

const [users, total] = await pool.findManyAndCount(User, {
  $where: { active: true },
  $sort: { createdAt: -1 },
  $skip: 40,
  $limit: 20,
});
// users.length === 20, total === 1247

On SQL this is normally one statement: the page carries its own unpaged total in a column of its own, so the rows and the total come from the same snapshot and cannot disagree. A second statement is needed only for an empty page (a $skip past the end has no row to carry the total on), a $distinct read, a $lock on PostgreSQL or CockroachDB, and MongoDB, which always uses two.

$distinct counts the rows you get back, not the rows before deduplication:

// three users, two distinct names
const [names, total] = await pool.findManyAndCount(User, {
  $select: { name: true },
  $distinct: true,
});
// names.length === 2, total === 2

Counting relations

$count answers how many rows a relation holds, without loading any of them. Results arrive under _count:

const users = await pool.findMany(User, {
  $select: { name: true },
  $count: { posts: true },
});
// [{ name: 'Ada', _count: { posts: 42 } }]

Name as many relations as you like; each becomes a key of _count. Narrow what counts with a filter of its own:

const users = await pool.findMany(User, {
  $count: { posts: { $where: { published: true } } },
});
// [{ ...user, _count: { posts: 12 } }]  // published ones only

Counting and populating the same relation are independent - ask for both and get the page of rows and the total:

const [user] = await pool.findMany(User, {
  $select: { name: true },
  $populate: { posts: { $sort: { createdAt: -1 }, $limit: 5 } },
  $count: { posts: true },
});
user.posts.length; // 5, the newest
user._count.posts; // 128, how many there are

Each named relation costs one grouped statement over the whole page, not one per row, so a page of three costs what a page of three hundred does.

Only to-many relations can be counted - a to-one resolves to at most one row, so there is nothing to tally - and neither $count nor the ordering below can be combined with $distinct or a raw $select, which take away the row id the tallies are grouped by. UQL refuses rather than answering zero.

Ordering by a relation’s size

$sort ranks parents by a tally, which is how you get a top-N:

const busiest = await pool.findMany(User, {
  $sort: { posts: { $count: -1 } },
  $limit: 10,
});

Each parent’s tally is computed as a correlated count, so a top-10 never loads the posts it ranked by.

estimatedCount

await pool.estimatedCount(User); // 2_400_000, instantly

The row count the engine already keeps: Postgres’ pg_class.reltuples, CockroachDB’s table statistics, MySQL and MariaDB’s information_schema, MongoDB’s estimatedDocumentCount. Nothing is scanned, so it answers in constant time on a table of any size.

The trade-offs are real, so use it only where an approximation is genuinely fine:

• Approximate, and as stale as the last ANALYZE.
• Whole-table. It takes no filter, so soft-deleted rows and every entity filter are inside the number.
• SQLite keeps no such statistic and throws.
• Server-side only - a browser client has no business asking a server to read engine statistics.

Filtering by a relation’s size

To filter parents by how many rows a relation holds, that belongs in $where as $size:

await pool.findMany(User, { $where: { posts: { $size: { $gte: 5 } } } });

$size is a comparison, so it lives with the other $where operators; $count is a value you project or rank by. Same question, different clause.