DEV Community

Cover image for Quickstart: Querying databases with Prisma.
Matthew Bill
Matthew Bill

Posted on

3 1

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,
    },
);
Enter fullscreen mode Exit fullscreen mode

Filtering

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

Paging

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

Ordering

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

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;
    }
  }
Enter fullscreen mode Exit fullscreen mode

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

typescript

11 Tips That Make You a Better Typescript Programmer

1 Think in {Set}

Type is an everyday concept to programmers, but it’s surprisingly difficult to define it succinctly. I find it helpful to use Set as a conceptual model instead.

#2 Understand declared type and narrowed type

One extremely powerful typescript feature is automatic type narrowing based on control flow. This means a variable has two types associated with it at any specific point of code location: a declaration type and a narrowed type.

#3 Use discriminated union instead of optional fields

...

Read the whole post now!

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay