Counting

UQL has five ways to ask “how many”, each at a different cost:

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)	one statement on SQL in most cases, two on MongoDB
How many rows a relation holds	$count on a read	part of the read’s own statement
A rough size of a huge table	estimatedCount(Entity)	no scan at all

count

await pool.count(User, { $where: { active: true } });

With $skip or $limit, count counts that page only, which caps 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 paged count runs as SELECT COUNT(*) FROM (SELECT ... LIMIT 1000), so no row leaves the database. count takes no $sort, since order never changes how many rows match.

exists

exists is a count capped at one row, so the engine stops at the first match. Prefer it to count(...) > 0:

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

findManyAndCount

The page plus how many rows matched in total, which is 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 the total rides in a column of the page itself (COUNT(*) OVER ()), so rows and total come from one statement and always agree. A second statement counts instead for an empty page, which has no row to carry the total, for a $distinct read, for a $lock on the Postgres family, and always on MongoDB. With $distinct, the total counts the distinct rows.

Counting relations

$count returns how many rows a to-many relation holds, without loading them. The tallies arrive under _count, and each can take a filter of its own:

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

Counting and populating the same relation are independent, so you can load the newest five posts and still know how many there are:

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

Each tally is a correlated count inside the read’s own statement, and it works with $distinct and a raw $select. For a tally you always want, or for sum, min, max and avg, declare a relation aggregate field instead, which $where and $sort can also use.

Ordering by a relation’s size

$sort can rank rows by a tally, for a top-N:

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

To filter by a relation’s size instead, use $size in $where: { posts: { $size: { $gte: 5 } } }.

estimatedCount

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

It reads the row count the engine already keeps, so it answers in constant time on any table: PostgreSQL’s pg_class.reltuples, CockroachDB’s table statistics, MySQL and MariaDB’s information_schema.TABLES, SQL Server’s sys.partitions, MongoDB’s estimatedDocumentCount. Use it only where an approximation is fine:

• It is as stale as the engine’s last statistics refresh (ANALYZE on PostgreSQL).
• It takes no filter, so soft-deleted rows and every query filter are inside the number.
• The SQLite family (SQLite, libSQL, Turso, D1) keeps no such statistic, so it throws there.
• The browser client does not have it.