[FEATURE]: Add findManyAndCount to query API

[andresmanz]

Describe what you want

Finding and counting results is one of the most frequent things we need in any of our projects. There's an example in the migrate from prisma guide:

const [response, count] = await Promise.all([
  db.query.products.findMany({
    where: whereOptions,
    columns: {
      id: true,
      name: true,
      unitPrice: true,
      unitsInStock: true,
    },
    offset: 0,
    limit: 10,
  }),
  db
    .select({ count: sql<number>`cast(count(${products.id}) as integer)` })
    .from(products)
    .where(whereOptions),
]);

However, this gets tedious very quickly and I can't think of a way to easily put this stuff into a helper function. I think findManyAndCount would be a rather simple addition to the query API that could save a lot of duplicate code.

Maybe there already is some better way? But I've been searching for two hours and people just seem happily duplicate it all over the place or use aggregates instead of two queries (which isn't optimal and still tedious).

[crrobinson14]

We'd love to see something similar but findByPK. I'm posting this here as a comment rather than a separate ticket because the bigger picture is really just "there are a few helpers folks are used to in other systems that would reduce a lot of boilerplate code". For instance, to find by PK, that simple operation is very verbose in Drizzle because you need at least 3 imports just to do it and in most code styles the code itself wraps to 3 lines:

      import {myDb} ...;
      import {MyTable} ...;
      import eq ...;

      const myRecord = await myDb.query.MyTable.findFirst({
        where: eq(MyTable.id, id),
      });

We tried doing this with a helper class, but pgTable and drizzle itself appear to operate more as factory functions, so there's no base class to extend.

I know Drizzle's goal is to be as streamlined as possible, but the strong-typing is a huge win for catching developer mistakes, and it's a pretty short list:

findByPK() - findFirst, but takes the PK as its param. findManyAndCount() - as described by OP above. exists() - More of a nice to have. In many apps, constraints can't do everything - sometimes you need to quickly check if a related record exists and it's OK if it does or does not, but can trigger different business logic. Commonly used as gates/guards in API route entrypoints.

[guiyumin]

I second this feature request.

I mean, we need a query api to return the total count of rows of a table.

[volkandkaya]

Any update on this? Supabase js includes it, seems like nearly every API needs it.