This content originally appeared on DEV Community and was authored by Matthew Bill
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;
}
}
This content originally appeared on DEV Community and was authored by Matthew Bill
Matthew Bill | Sciencx (2024-06-16T09:18:51+00:00) Quickstart: Querying databases with Prisma.. Retrieved from https://www.scien.cx/2024/06/16/quickstart-querying-databases-with-prisma/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.