API with NestJS #134. Aggregating statistics with PostgreSQL and Prisma

NestJS SQL

This entry is part 134 of 173 in the API with NestJS

We can learn much about how users use our app by looking at our database. With this information, we can improve the experience of the users of our application. Luckily, PostgreSQL and Prisma make it easy to collect different kinds of data statistics. In this article, we learn how to use them to group and aggregate data.

Aggregating data

Let’s say we have the following models in our application.

schema.prisma

We can gather various information through aggregating data with the function. Its job is to compute a single result from multiple rows.

One of the most straightforward operations we can do is to count an average upvotes value across all our articles.

reports.service.ts

Another operation worth mentioning is summing. For example, we can get a sum of all upvotes our articles received.

reports.service.ts

We can also use to get the biggest upvotes count any article ever received.

reports.service.ts

If we want to get the length of the longest and shortest articles using Prisma, we need to get a little creative.

In PostgreSQL, we can use the combination of the , , and functions to get the biggest and smallest length of the content.

The   function returns the length of a string.

Unfortunately, Prisma does not allow us to combine those functions using . Because of that, we will have to run a raw SQL query.

reports.service.ts

Since the result of the has the type , we need to narrow it down. Because of that, we use the function above.

is-record.ts

The function is a type guard. If you want to know more, check out Structural type system and polymorphism in TypeScript. Type guards with predicates

Grouping

Besides aggregating our table as a whole, we can group the data by one or more fields.

For example, let’s sum all upvotes received by each author.

reports.service.ts

Thanks to the above approach, we can get various information about all articles written by a particular author. Similarly to the function, we can calculate the average upvotes count and the maximum upvotes an author received on a single article.

reports.service.ts

Sorting

Both the and functions allow us to sort the results. For example, let’s make sure to start with the authors who have the biggest sum of all their upvotes.

reports.service.ts

Filtering

We can also filter the rows taken into account when calculating the results. For example, let’s only consider articles with a negative upvotes count. To do that, we need the property.

With , we’ve managed to filter out the rows used for aggregation. We can also use the keyword to filter entire groups. For example, we can show only authors with an average of ten or more upvotes on their articles.

The function can use both and .

Summary

In this article, we’ve gone through the idea of aggregating our data to collect various statistics. To do that, we learned how to aggregate a table as a whole or group them by a particular field. Since Prisma does not support all cases we might encounter, we also used some raw queries to get the necessary information. By combining these methods, we can tailor our data analysis to fit exactly what we need for our projects.

Series Navigation<< API with NestJS #133. Introducing database normalization with PostgreSQL and PrismaAPI with NestJS #135. Referential actions and foreign keys in PostgreSQL with Prisma >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments