Quickstart: Querying databases with Prisma.

Prisma is my ORM of choice, which works nicely with TypeScript and Postgres (as well as many other data stores). Something no doubt you have to do a lot in your apps is get a collection of objects from your data stores.

You can read all about how to do queries with prisma over here, but what does a real world example look like where we want to query our data store for some results.

I have put together some sample code (its not perfect) with some of the key functionality you might need, like sorting, filtering, and paging.

This is set up in particular to work with shadcn/ui, TanStack Table and Query. For example, the Pagination result returns the total so that we can know how many pages to show on the pagination control and pageIndex/pageSize is used as this is what is used in the PaginationState interface for Tanstack Table.

Query

The query we have will eventually look like the following. prisma is a singleton implementation of the PrismaClient object which is created through prisma generate.

const items = await prisma.organization.findMany(
    {
    skip,
    take,
      where: whereClause,
      orderBy: orderByClause,
    },
);

Filtering

      // Filtering
      let whereClause: any = {};
      if (options.name) {
        whereClause.name = {
          contains: options.name,
        };
      }

Paging

      let skip = undefined;
      let take = undefined;
      if (options.pageIndex && options.pageSize) {
        skip = options.pageIndex * options.pageSize;
        take = options.pageSize;
      }

Ordering

      let orderByClause: any = {
        [options.sort ?? "name"]: options.sortDirection ?? "desc",
      };

Final Result

When all put together it looks like this (including the types). Note - I have not included the surrounding class, which has an instance of logger and other functionality.

export type PaginationResult<T> = {
  items: T[];
  total: number;
  pageIndex?: number;
  pageSize?: number;
}

type CollectionQuery = {
  sort?: string;
  sortDirection?: string;
  pageIndex?: number;
  pageSize?: number;
};

type OrganizationCollectionQuery = {
  name?: string;
} & CollectionQuery;

async getCollection(options: OrganizationCollectionQuery): Promise<PaginationResult<Organization>> {
    try {
      // Filtering
      let whereClause: any = {};
      if (options.name) {
        whereClause.name = {
          contains: options.name,
        };
      }

      // Ordering
      let orderByClause: any = {
        [options.sort ?? "name"]: options.sortDirection ?? "desc",
      };

      // Paging
      let skip = undefined;
      let take = undefined;
      if (options.pageIndex && options.pageSize) {
        skip = options.pageIndex * options.pageSize;
        take = options.pageSize;
      }

      const items = await prisma.organization.findMany(
        {
          skip,
          take,
          where: whereClause,
          orderBy: orderByClause,
        },
      );
      const total = await prisma.organization.count();
      return {
        items,
        total,
        pageIndex: options.pageIndex,
        pageSize: options.pageSize,
      };
    } catch (error: any) {
      this.logger.error(error);
      throw error;
    }
  }

Blog

Quickstart: Querying databases with Prisma.

Matthew Bill

Query

Filtering

Paging

Ordering

Final Result

Join Our Newsletter. No Spam, Only the good stuff.

Related