Aggregating statistics with PostgreSQL and Prisma

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 model Article { id Int @id @default(autoincrement()) title String content String? upvotes Int @default(0) author User @relation(fields: [authorId], references: [id]) authorId Int } model User { id Int @id @default(autoincrement()) email String @unique name String password String articles Article[] }We can gather various information through aggregating data with the aggregate 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 import { Injectable } from '@nestjs/common'; import { PrismaService } from '../database/prisma.service'; @Injectable() export class ReportsService { constructor(private readonly prismaService: PrismaService) {} async getArticlesStatistics() { const result = await this.prismaService.article.aggregate({ _avg: { upvotes: true, }, }); return { averageUpvotesCount: result._avg.upvotes } } }Another operation worth mentioning is summing. For example, we can get a sum of all upvotes our articles received. reports.service.ts import { Injectable } from '@nestjs/common'; import { PrismaService } from '../database/prisma.service'; @Injectable() export class ReportsService { constructor(private readonly prismaService: PrismaService) {} async getArticlesStatistics() { const result = await this.prismaService.article.aggregate({ _avg: { upvotes: true, }, _sum: { upvotes: true, }, }); return { averageUpvotesCount: result._avg.upvotes, allUpvotesReceived: result._sum.upvotes, }; } }We can also use _max to get the biggest upvotes count any article ever received. reports.service.ts import { Injectable } from '@nestjs/common'; import { PrismaService } from '../database/prisma.service'; @Injectable() export class ReportsService { constructor(private readonly prismaService: PrismaService) {} async getArticlesStatistics() { const result = await this.prismaService.article.aggregate({ _avg: { upvotes: true, }, _sum: { upvotes: true, }, _max: { upvotes: true, }, }); return { averageUpvotesCount: result._avg.upvotes, allUpvotesReceived: result._sum.upvotes, biggestUpvotesCount: result._max.upvotes, }; } }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 max(), min(), and length() functions to get the biggest and smallest length of the content. The length()  function returns the length of a string. SELECT max(length(content)) AS longest_article_length, min(length(content)) AS shortest_article_length FROM "Article"Unfortunately, Prisma does not allow us to combine those functions using aggregate(). Because of that, we will have to run a raw SQL query. reports.service.ts import { Injectable, InternalServerErrorException } from '@nestjs/common'; import { PrismaService } from '../database/prisma.service'; import { isRecord } from '../utilities/is-record'; @Injectable() export class ReportsService { constructor(private readonly prismaService: PrismaService) {} private async getArticlesLength() { const rawResults = await this.prismaService.$queryRaw` SELECT max(length(content)) AS longest_article_length, min(length(content)) AS shortest_article_length FROM "Article" `; if (!Array.isArray(rawResults)) { throw new InternalServerErrorException(); } const result = rawResults[0]; if (!isRecord(result)) { throw new InternalServerErrorException(); } const longestArticleLength = result.longest_article_length; const shortestArticleLength = result.shortest_article_length; return { longestArticleLength, shortestArticleLength, }; } async getArticlesStatistics() { const result = await this.prismaService.article.aggregate({ _avg: { upvotes: true, }, _sum: { upvotes: true, }, _max: { upvotes: true, }, }); const { longestArticleLength, shortestArticleLength } = await this.getArticlesLength(); return { averageUpvotesCount: result._avg.upvotes, biggestUpvotesCount: result._max.upvotes, allUpvotesReceived: result._sum.upvotes, longestArticleLength, shortestArticleLength, }; } }Since the result of the $queryRaw has the type unknown, we need to narrow it down. Because of that, we use the isRecord function above. is-record.ts export function isRecord(value: unknown): value is Record<string, unknown> { return value !== null && typeof value === 'object' && !Array.isArray(value); } The isRecord 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 import { Injectable } from '@nestjs/common'; import { PrismaService } from '../database/prisma.service'; @Injectable() export class ReportsService { constructor(private readonly prismaService: PrismaService) {} async getAuthorsStatistics() { const results = await this.prismaService.article.groupBy({ by: 'authorId', _sum: { upvotes: true, }, }); return results.map(({ authorId, _sum }) => { return { authorId, allUpvotesReceived: _sum.upvotes, }; }); } // ... } Thanks to the above approach, we can get various information about all articles written by a particular author. Similarly to the aggregate function, we can calculate the average upvotes count and the maximum upvotes an author received on a single article. reports.service.ts import { Injectable } from '@nestjs/common'; import { PrismaService } from '../database/prisma.service'; @Injectable() export class ReportsService { constructor(private readonly prismaService: PrismaService) {} async getAuthorsStatistics() { const results = await this.prismaService.article.groupBy({ by: 'authorId', _sum: { upvotes: true, }, _avg: { upvotes: true, }, _max: { upvotes: true, }, }); return results.map(({ authorId, _sum, _max, _avg }) => { return { authorId, allUpvotesReceived: _sum.upvotes, averageUpvotesCount: _avg.upvotes, biggestUpvotesCount: _max.upvotes, }; }); } // ... } Sorting Both the groupBy and aggregate 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 import { Injectable } from '@nestjs/common'; import { PrismaService } from '../database/prisma.service'; @Injectable() export class ReportsService { constructor(private readonly prismaService: PrismaService) {} async getAuthorsStatistics() { const results = await this.prismaService.article.groupBy({ by: 'authorId', _sum: { upvotes: true, }, _avg: { upvotes: true, }, _max: { upvotes: true, }, orderBy: { _sum: { upvotes: 'desc' } } }); return results.map(({ authorId, _sum, _max, _avg }) => { return { authorId, allUpvotesReceived: _sum.upvotes, averageUpvotesCount: _avg.upvotes, biggestUpvotesCount: _max.upvotes, }; }); } // ... } 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 where property.const results = await this.prismaService.article.groupBy({ by: 'authorId', _sum: { upvotes: true, }, _avg: { upvotes: true, }, _max: { upvotes: true, }, where: { upvotes: { lt: 0, }, }, });With where, we’ve managed to filter out the rows used for aggregation. We can also use the having keyword to filter entire groups. For example, we can show only authors with an average of ten or more upvotes on their articles.const results = await this.prismaService.article.groupBy({ by: 'authorId', _sum: { upvotes: true, }, _avg: { upvotes: true, }, _max: { upvotes: true, }, having: { upvotes: { _avg: { gt: 10 } } } }); The groupBy function can use both where and having. 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. The post API with NestJS #134. Aggregating statistics with PostgreSQL and Prisma appeared first on Marcin Wanago Blog - JavaScript, both frontend and backend.