Analyzing API Call Trends with Prisma and Next.js: Grouping by Week, Month, or Year

Hey, this post is exactly what I needed when I was trying to figure out how to use Prisma to group data by day, month, or year. I had to go through various documentation to understand how to implement this. So let's get started. We'll explore how to query by grouping data based on the DateTime type. We'll use an example to analyze an API call trend using Prisma, Next.js, and MongoDB. Our focus will be on querying data to track API call metrics such as success rates and call frequency over time.

Simplified API Call Data Model

Grouping API calls by time periods such as week, month, or year is crucial for implementing dashboard. To illustrate this, consider the following minimal Prisma schema:

model ApiCall {
  id        String    @id @default(auto()) @map("_id") @db.ObjectId
  timestamp DateTime  @default(now())
  status    ApiCallStatus // Enum for success or failure.
}

enum ApiCallStatus {
  SUCCESS
  FAILURE
}

This model captures the timestamp of each API call and its status, sufficient for trend analysis.

Querying API Call Trends

Here’s an implementation of an API endpoint in Next.js that provides insights into API call trends by grouping the data by time periods such as week, month, or year. This endpoint helps monitor API usage patterns and identify potential system issues efficiently:

import { NextRequest, NextResponse } from 'next/server';
import { startOfYear, endOfYear, startOfMonth, endOfMonth } from 'date-fns';

export async function GET(req: NextRequest) {
    const range = req.nextUrl.searchParams.get("range"); // 'year' or 'month'
    const groupBy = req.nextUrl.searchParams.get("groupby"); // 'yearly', 'monthly', 'daily'

    if (!range || (range !== 'year' && range !== 'month')) {
        return NextResponse.json({ error: "Range must be 'year' or 'month'" }, { status: 400 });
    }

    if (!groupBy || (groupBy !== 'yearly' && groupBy !== 'monthly' && groupBy !== 'daily')) {
        return NextResponse.json({ error: "Group by must be 'yearly', 'monthly', or 'daily'" }, { status: 400 });
    }

    try {
        let start: Date, end: Date;
        if (range === 'year') {
            start = startOfYear(new Date());
            end = endOfYear(new Date());
        } else { // range === 'month'
            start = startOfMonth(new Date());
            end = endOfMonth(new Date());
        }

        let groupByFormat: string;
        switch (groupBy) {
            case 'yearly':
                groupByFormat = "%Y";
                break;
            case 'monthly':
                groupByFormat = "%Y-%m";
                break;
            case 'daily':
                groupByFormat = "%Y-%m-%d";
                break;
        }

        const apiCallTrends = await db.apiCall.aggregateRaw({
            pipeline: [
                {
                    $match: {
                        timestamp: { $gte: { $date: start }, $lte: { $date: end } }
                    }
                },
                {
                    $group: {
                        _id: { $dateToString: { format: groupByFormat, date: '$timestamp' } },
                        SUCCESS: { $sum: { $cond: [{ $eq: ['$status', 'SUCCESS'] }, 1, 0] } },
                        FAILURE: { $sum: { $cond: [{ $eq: ['$status', 'FAILURE'] }, 1, 0] } },
                        TOTAL: { $sum: 1 }
                    }
                },
                {
                    $sort: {
                        _id: 1
                    }
                }
            ]
        });

        return NextResponse.json({ apiCallTrends });
    } catch (error) {
        console.error(error);
        return NextResponse.json({ error: "An error occurred while fetching data." }, { status: 500 });
    }
}

Possible Response

GET /api/your-endpoint?range=year&groupby=monthly

{
  "apiCallTrends": [
    {
      "_id": "2025-01", // Grouped by month (January 2025)
      "SUCCESS": 120,
      "FAILURE": 15,
      "TOTAL": 135
    },
    {
      "_id": "2025-02", // Grouped by month (February 2025)
      "SUCCESS": 110,
      "FAILURE": 10,
      "TOTAL": 120
    },
    {
      "_id": "2025-03", // Grouped by month (March 2025)
      "SUCCESS": 130,
      "FAILURE": 20,
      "TOTAL": 150
    }
    // ... more grouped results for each month in the range
  ]
}

Key Highlights

1. Dynamic Grouping by Date: The aggregation pipeline groups API calls by year, month or day, depending on the user’s selection.
2. Trend Analysis: Success and failure counts, along with total calls, are calculated for each time period.
3. Error Handling: User-friendly error responses ensure a smooth API experience.
4. Efficiency: MongoDB's aggregation pipeline minimizes server load and optimizes performance.

Conclusion

With this setup ,you can query timestamp groupBy various time range from MongoDB using Prisma ORM.

Thank you for reading! If you found this post helpful, please consider subscribing and liking the post. Feel free to connect with me on GitHub and LinkedIn. Your support is greatly appreciated!