Quickstart: Querying databases with Prisma.
Tags: prisma, webdev, typescript, repository
Published: 2024-06-16 09:18:51

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](https://www.prisma.io/docs/orm/prisma-client/queries/crud), 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](https://ui.shadcn.com/), [TanStack Table](https://tanstack.com/table/latest) and [Query](https://tanstack.com/query/latest). 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`.

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

## Filtering

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

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

## Ordering
``` ts
      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.

``` ts
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;
    }
  }
```