$count

[AlexBlokh]

In recent release we've introduced $count API and I wanted to shine some light on why we decided to implement one.

We pursue SQL-likeness with Drizzle, yet sometimes pure SQL is just bad, this is how you'd do count with our query builder:

const res = await db.select({ count: sql`count(*)` }).from(users);
const count = res[0].count;

not that bad you might say, yet count type would be unknown, since we didn't explicitly tell our sql tag the type of a column, which would be

const res = await db.select({ count: sql<number>`count(*)` }).from(users);
const count = res[0].count;

That still wouldn't work, because PostgreSQL will return count as bigint and MySQL will return count as decimal which will be returned as string, so if we want something robust - we should do:

const res = await db.select({ count: sql<number>`cast(count(*)) as int` }).from(users);
const count = res[0].count;

// or alternatively, yet we don't recommend
const res = await db.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(users);
const count = res[0].count;

These all the above are reasons why we sometimes introduce utilities like $count, we will be prefixing util function like this one with $ to separate them from query builder APIs

const count = await db.$count(users); // this returns number
const count = await db.$count(users, eq(users.name, "Dan"); // with filtering

we've designed it to be used as a subquery too:

const users = await db.select({
  ...users, // all users columns
  postsCount: db.$count(posts, eq(posts.authorId, users.id)),
  // ^ subquery, will be inferred as number
}).from(users);

this way you can easily filter out only users with at least one post

const users = await db.select({
  ...users,
  postsCount: db.$count(posts, eq(posts.authorId, users.id)),
}).from(users)
  .where((t) => gte(t.postsCount, 1));

and you can use it with our relational queries too:

const users = await db.query.users.findMany({
  extras: {
    postsCount: db.$count(posts, eq(posts.authorId, users.id)),
  },
});

[ryespresso]

@AlexBlokh This doesn't appear to work with planetscale driver even though the types say they do. Getting db.$count is not a function

[ryespresso]

@Sukairo-02 just do a simple $count on a table with planet scale driver.Typescript code will pass but you'll get db.$count is not a function when running the query

[AlexBlokh]

we did, it works just fine, that's why we ask for a reproducible example project

[hnh-zeal]

If you are using database replica, db.$count cannot be used. It will return db.$count is not a function.

I tried with db.$primary.$count. It went well for me.

[AlexBlokh]

cc: @Sukairo-02

[ryespresso]

@AlexBlokh @Sukairo-02 - this is the exact bug. as @hnh-zeal stated

#4039

[capaj]

I love the addition. It's indeed a bit too verbose without this.

@AlexBlokh have you considered adding this into the query builder API?

[AlexBlokh]

thank you!

yes, but we don't wanna touch RQB v1, going to land in RQB v2
btw, I expect RQB v2 land soon, we're finally up to speed with this track

[capaj]

Soon like q1 2025?

[rgrullondev]

I'm getting a typescript type error.

Property 'fieldAlias' is missing in type 'MySqlCountBuilder<MySqlSession<any, any, any, any>>' but required in type 'Aliased<unknown>'

[rgrullondev]

I solved it, you need to set the .as('') to the name of the extra field.
So if the field is topicCount, you need to add .as('topicCount')

The example should be update to reflect that.
postsCount: db.$count(posts, eq(posts.authorId, users.id)).as('postsCount')

[Curetix]

Did you get this to work? I'm getting the error PostgresError: column channels.channel_id does not exist for my code:

await db.query.channels.findMany({
  extras: {
    videoCount: db.$count(videos, eq(videos.channelId, channels.id)).as("video_count"),
  },
});

Drizzle generates the query select "id", "user_id", "display_name", "name", "created_at", "updated_at", (select count(*) from "videos" where "channels"."channel_id" = "channels"."id") as "video_count" from "channels".

Obviously the "channels"."channel_id" = "channels"."id" should be "videos"."channel_id" = "channels"."id", but I'm not sure why it's wrong in the first place.

[NebraskaCoder]

Same issue. After using .as("name") I get the "does not exist" error that @Curetix details.

[mfazail]

✅Got it working through raw sql, lets take example of @Curetix

await db.query.channels.findMany({
  extras: {
    videoCount: db.$count(videos, eq(videos.channelId, channels.id)).as("video_count"),
  },
});

Drizzle generates the query select "id", "user_id", "display_name", "name", "created_at", "updated_at", (select count(*) from "videos" where "channels"."channel_id" = "channels"."id") as "video_count" from "channels".

Obviously the "channels"."channel_id" = "channels"."id" should be "videos"."channel_id" = "channels"."id", but I'm not sure why it's wrong in the first place.

Altered

await db.query.channels.findMany({
   extras: {
     videoCount: sql`(select count(*) from "videos" where "videos"."channel_id" = "channels"."id")`.as("video_count"),
   },
 });

💚 Hope this helps

[MickL]

Seriously, the docs show $count here and here but they generate typing errors as well as a wrong query.

Issue to fix the query: #4164 (possibly upvote)

This hasnt been fixed since winter 2024. On the Drizzle website it says "ORM for you to settle" and "We ship decently fast", but critical documentation issues and bugs are not fixed at all.

Either fix the docs please or make it open source so we could fix it.

[ivan-kleshnin]

Will the support of this be added to a QueryBuilder? E.g. qb argument of a view / mat. view factory:

export let usersCountView = github.materializedView("users_count")
  .as(qb =>
    qb.$count().from(usersTable) // Property $count does not exist on type QueryBuilder
  )

[MickL]

Will this ever get documented? There is a guide about counting but there you use count() which you didnt use in any of your above examples so I am a bit confused.

[hyunbinseo]

Isn't this the documentation? https://orm.drizzle.team/docs/query-utils

[MickL]

There is a specific guide about counting, but no word about $count: https://orm.drizzle.team/docs/guides/count-rows

[hyunbinseo]

If the following works:

const users = await db.query.users.findMany({
  extras: {
    postsCount: db.$count(posts, eq(posts.authorId, users.id)),
  },
});

could this be added to the RQB v1 extras documentation? I was using core queries just for count(*)

As of now aggregations are not supported in extras, please use core queries for that.

[devzaak]

@antonius-dev bug opened with repro steps as #4169

[e2goon]

I am experiencing the same issue as you.
As shown in the code image I attached, I am using a temporary workaround.
I plan to track this issue and update the code once the problem is resolved.

[AlexBlokh]

this work properly in RQB v2

[lupus78]

this work properly in RQB v2

What is RQB v2 ?

[sicarius97]

@lupus78 Im assuming it stands for relational query builder or something, but anyway I had the same issue and upgrading to the newest version solved it for me

[phenomen]

$count with relational queries still generates invalid SQL.
Replacing it with sql and count() works but you have to write raw SQL.

[MickL]

The example code provided here and in the docs generates an error for me too:DrizzleQueryError: Failed query: select "id", select count(*) as count from "posts" where "users"."user_id" = "users"."id"; as "undefined" from "users" "users"

It works with .select() but not with .query

[MickL]

@AlexBlokh The examples you provided in this discussion, as well as in the docs here and here are wrong code and not working. Unfortunately the docs are not open source so I cant provide a PR, but I feel like none of Drizzle Team is looking into Github issues/PRs anyway?

1. With select

import { users } from '.models';

const users = await db.select({
  ...users,
  postsCount: db.$count(posts, eq(posts.authorId, users.id)),
}).from(users);

-> TS2448: Block-scoped variable users used before its declaration.
Solution: Rename const users to const results

2. With Query

import { users } from '.models';

const users = await db.query.users.findMany({
  extras: {
    postsCount: db.$count(posts, eq(posts.authorId, users.id)),
  },
});

-> TS2448: Block-scoped variable users used before its declaration.
Solution: Rename const users to const results

-> TS2741: Property fieldAlias is missing in type PgCountBuilder<PgSession<any, any, any>> but required in type Aliased
-> DrizzleQueryError: Failed query: select "id", select count(*) as count from "posts" where "users"."user_id" = "users"."id"; as "undefined" from "users" "users"
Solution: ???
Issue + Workaround: #4164