Is it okay to use transactions to group queries

[ChoaibMouhrach]

Question ?

I utilize an endpoint that triggers two queries—one to retrieve records and another to obtain the count—for client-side pagination. While I currently execute these queries using Promise.all(), my understanding from the documentation suggests that it's possible to send both queries in a single transaction and receive their results simultaneously.

What I'm hoping to achieve with this?

One request rather than two

Example?

const indexSchema = z.object({
  query: z.string(),
  page: z.number().int().gt(0).default(1),
});

const index = protectedProcedure
  .input(indexSchema)
  .query(async ({ ctx, input }) => {
    const { page, query } = input;
    const limit = 8;
    const offset = (page - 1) * limit;

    const { id: userId } = ctx.user;

    const where = and(
      eq(categories.userId, userId),
      query ? ilike(categories.name, `%${query}%`) : undefined
    );

    const [dbCategories, count] = await db.transaction(async (tx): Promise<[Category[], number]> => {
      const [dbCategories, count] = await Promise.all([
        tx.query.categories.findMany({
          where,
          offset,
          limit,
        }),
        tx
          .select({
            count: sql<number>`COUNT(*)`,
          })
          .from(categories)
          .where(where)
          .then((res) => res[0].count),
      ]);

      return [dbCategories, count];
    });

    return {
      data: dbCategories,
      lastPage: Math.ceil(count / limit),
    };
  });

  return [data, count]
})

Is this the optimal approach, and what potential drawbacks does it entail?

[Angelelz]

You don't need to run this in 2 queries or a transaction. You just need a window function:

db.query.categories.findMany({
    where,
    offset,
    limit,
    extras: {
      count: sql`count(*) over()`.as("count")
    }
  }),

[ChoaibMouhrach]

Nice touch

[ChoaibMouhrach]

Would it be considered a good practice to use transactions in order to combine multiple queries into a single request?