Drizzle ORM - Count rows

Drizzle | Count rows

PostgreSQL

MySQL

SQLite

This guide assumes familiarity with:

Get started with PostgreSQL, MySQL and SQLite
Select statement
Filters and sql operator
Aggregations and Aggregation helpers
Joins

To count all rows in table you can use count() function or sql operator like below:

index.ts

schema.ts

import { count, sql } from 'drizzle-orm';
import { products } from './schema';

const db = drizzle(...);

await db.select({ count: count() }).from(products);

// Under the hood, the count() function casts its result to a number at runtime.
await db.select({ count: sql`count(*)`.mapWith(Number) }).from(products);

// result type
type Result = {
  count: number;
}[];

select count(*) from products;

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  discount: integer('discount'),
  price: integer('price').notNull(),
});

To count rows where the specified column contains non-NULL values you can use count() function with a column:

await db.select({ count: count(products.discount) }).from(products);

// result type
type Result = {
  count: number;
}[];

select count("discount") from products;

Drizzle has simple and flexible API, which lets you create your custom solutions. In PostgreSQL and MySQL count() function returns bigint, which is interpreted as string by their drivers, so it should be casted to integer:

import { AnyColumn, sql } from 'drizzle-orm';

const customCount = (column?: AnyColumn) => {
  if (column) {
    return sql<number>`cast(count(${column}) as integer)`; // In MySQL cast to unsigned integer
  } else {
    return sql<number>`cast(count(*) as integer)`; // In MySQL cast to unsigned integer
  }
};

await db.select({ count: customCount() }).from(products);
await db.select({ count: customCount(products.discount) }).from(products);

select cast(count(*) as integer) from products;
select cast(count("discount") as integer) from products;

In SQLite, count() result returns as integer.

import { sql } from 'drizzle-orm';

await db.select({ count: sql<number>`count(*)` }).from(products);
await db.select({ count: sql<number>`count(${products.discount})` }).from(products);

select count(*) from products;
select count("discount") from products;

💡

By specifying sql<number>, you are telling Drizzle that the expected type of the field is number.
If you specify it incorrectly (e.g. use sql<string> for a field that will be returned as a number), the runtime value won’t match the expected type. Drizzle cannot perform any type casts based on the provided type generic, because that information is not available at runtime.

If you need to apply runtime transformations to the returned value, you can use the .mapWith() method.

To count rows that match a condition you can use .where() method:

import { count, gt } from 'drizzle-orm';

await db
  .select({ count: count() })
  .from(products)
  .where(gt(products.price, 100));

select count(*) from products where price > 100

This is how you can use count() function with joins and aggregations:

index.ts

schema.ts

import { count, eq } from 'drizzle-orm';
import { countries, cities } from './schema';

// Count cities in each country
await db
  .select({
    country: countries.name,
    citiesCount: count(cities.id),
  })
  .from(countries)
  .leftJoin(cities, eq(countries.id, cities.countryId))
  .groupBy(countries.id)
  .orderBy(countries.name);

select countries.name, count("cities"."id") from countries
  left join cities on countries.id = cities.country_id
  group by countries.id
  order by countries.name;

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const countries = pgTable('countries', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const cities = pgTable('cities', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  countryId: integer('country_id').notNull().references(() => countries.id),
});

Benchmarks

Extension

Studio

Become a Gold Sponsor