drizzle-orm: what type should I set when passing query builder as param to another function

Question

I'm creating a generic function to generate paginated results for different entities. In order to do that, I'm using part of my query builder as a subquery to fetch the number of the records and set the pagination accordingly.

Here is my main function:

const findMany = async (options?: {
  sort?: AuthorSorting;
  filters?: AuthorFilter[];
  pagination?: QueryStringPagination;
}): Promise<{ data: Author[]; pagination: Pagination }> => {
  const query = db.select().from(authors);

  if (options?.sort) {
    const fieldName = options.sort.field;
    const sortingField = authors[fieldName] ?? authors["name"];

    query.orderBy(
      options.sort.direction === "desc" ? desc(sortingField) : asc(sortingField)
    );
  }

  if (options?.filters) {
    const conditions: SQL<Author>[] = [];
    const filters = options?.filters;

    filters.forEach((filter: Record<string, any>) => {
      const field = authors[filter.field as keyof Author];
      const op = sql.raw(Operators[filter.operator]);
      const value = filter.value;

      if (["like", "ilike"].includes(filter.operator)) {
        conditions.push(sql`${field} ${op} ${"%" + value + "%"}`);
      } else {
        conditions.push(sql`${field} ${op} ${value}`);
      }
    });

    if (conditions.length > 0) {
      const combinedConditions = conditions.reduce(
        (prev, curr) => sql`${prev} AND ${curr}`
      );
      query.where(combinedConditions);
    }
  }

  return paginateQuery<Author>({
    query,
    QueryStringPagination: options.pagination,
  });
};

And here is my generic pagination function:

import { sql } from "drizzle-orm";

import { db } from "../db";
import { Pagination, QueryStringPagination } from "../types";

export async function paginateQuery<T>({
  query,
  QueryStringPagination,
}: {
  query: any; // <---- What type should I set?
  QueryStringPagination: QueryStringPagination;
}): Promise<{ data: T[]; pagination: Pagination }> {
  const subQuery = query.as("sub");
  const totalRecordsQuery = db
    .select({ total: sql<number>`count(*)` })
    .from(subQuery);

  const totalRecordsResult = await totalRecordsQuery.execute();
  const totalRecords = Number(totalRecordsResult[0].total);
  const totalPages = Math.ceil(totalRecords / QueryStringPagination.limit);

  query
    .limit(QueryStringPagination.limit)
    .offset((QueryStringPagination.page - 1) * QueryStringPagination.limit);

  const results = (await query.execute()) as T[];

  return {
    data: results,
    pagination: {
      totalRecords: totalRecords,
      totalPages: totalPages,
      currentPage: QueryStringPagination.page,
      limit: QueryStringPagination.limit,
    },
  };
}

And this is my Author entity just in case:

export const authors = pgTable(
  "authors",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    name: varchar("name", { length: 256 }).notNull(),
    bio: text("bio").notNull(),
    birthdate: date("birthdate", { mode: "date" }).notNull(),
    createdAt: timestamp("createdAt").defaultNow().notNull(),
    updatedAt: timestamp("updatedAt").defaultNow().notNull(),
  },
  (authors) => {
    return {
      nameIndex: uniqueIndex("name_idx").on(authors.name),
    };
  }
);

I've tried different types from drizzle-orm but I couldn't find one that fits it correctly.

So, what type should I set when passing my query builder as param to another function?

Answer 1

I was able to find the right type for it looking at how to handle pagination in the drizzle documentation.

Here is the final code with the right type:

import { sql } from "drizzle-orm";
import { PgSelect } from "drizzle-orm/pg-core";

import { db } from "../db";
import { Pagination, QueryStringPagination } from "../types";

export async function paginateQuery<T extends PgSelect>({
  query,
  QueryStringPagination,
}: {
  query: T; // <---- correct type
  QueryStringPagination: QueryStringPagination;
}): Promise<{ data: T[]; pagination: Pagination }> {
  const subQuery = query.as("sub");
  const totalRecordsQuery = db
    .select({ total: sql<number>`count(*)` })
    .from(subQuery);

  const totalRecordsResult = await totalRecordsQuery.execute();
  const totalRecords = Number(totalRecordsResult[0].total);
  const totalPages = Math.ceil(totalRecords / QueryStringPagination.limit);

  query
    .limit(QueryStringPagination.limit)
    .offset((QueryStringPagination.page - 1) * QueryStringPagination.limit);

  const results = (await query.execute()) as T[];

  return {
    data: results,
    pagination: {
      totalRecords: totalRecords,
      totalPages: totalPages,
      currentPage: QueryStringPagination.page,
      limit: QueryStringPagination.limit,
    },
  };
}