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

November 20, 2023

This entry is part 134 of 175 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

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.

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 >>