February 8, 2023·6 min read

Manage a quota-based SaaS application with Upstash and Next.js

Thomas CazadeFounder of fastsheet (Guest Author)

In this post, we will show you how to use Upstash to create a quota system for a SaaS application, made with Next.js and Prisma. We will use Next.js API routes to create a simple API.

In many SaaS application, you may have encountered a quota system. It is a system that limits the number of actions a user can do in a given time.

In our example, a user can only send 1,000 API requests per month on the "Free" plan. If the user tries to send more than 1,000 API requests, the application will block the user from sending more requests.

Those API requests are used to retrieve the content of a spreadsheet, and turn it into a JSON. This is what fastsheet does, turn any Google Sheets spreadsheet into a JSON API.

Example of fastsheet's quota system

Defining the database schema

As said earlier, we will use Prisma as an ORM to interact with the database. Here is an example of a User model which implements a quota system, as well as its Spreadsheet model to store spreadsheets:

model User {
  id                Int           @id @default(autoincrement())
  planId            String        @default("FREE")
  email             String        @unique
  quota             Int           @default(0)
  spreadsheets      Spreadsheet[]
}

model Spreadsheet {
  id                Int           @id @default(autoincrement())
  userId            Int
  user              User          @relation(fields: [userId], references: [id])
  content           String
}

The planId field is the plan the user is subscribed to.
The quota field is the number of API requests the user has done in the current month.
The Spreadsheet model represents a spreadsheet, that is connected to a User.
A User can have multiple Spreadsheet linked to it.

To keep things simple, we will only handle the "FREE" plan, which allows 1,000 API requests per month.

Increasing the user's quota

We want to increase the user's quota every time the user makes an API request.

At first glance, this seems easy and straightforward to do. We can simply increase the user's quota by 1 every time the user makes an API request on our Next.js API route:

// pages/api/spreadsheets/[id]/index.ts
import type { NextApiRequest, NextApiResponse } from "next";
 
const MAX_FREE_TIER_QUOTA = 1000;
 
export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse,
) {
  // Get the ID of the spreadsheet from the URL.
  const { id } = req.params;
 
  // Get the spreadsheet and its associated user from the database.
  const spreadsheet = await prisma.spreadsheet.findUnique({
    where: { id: Number(id) },
    include: { user: true },
  });
 
  // Verify that the spreadsheet exists.
  if (!spreadsheet) {
    return res.status(404).json({ error: "Spreadsheet not found" });
  }
 
  // Verify that the user is not over the quota.
  if (spreadsheet.user.quota >= MAX_FREE_TIER_QUOTA) {
    return res.status(429).json({ error: "Quota exceeded" });
  }
 
  // Increase the user's quota by 1.
  await prisma.user.update({
    where: { id: spreadsheet.user.id },
    data: { quota: { increment: 1 } },
  });
 
  // Return the spreadsheet's content.
  return res.json({ content: spreadsheet.content });
}

// pages/api/spreadsheets/[id]/index.ts
import type { NextApiRequest, NextApiResponse } from "next";
 
const MAX_FREE_TIER_QUOTA = 1000;
 
export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse,
) {
  // Get the ID of the spreadsheet from the URL.
  const { id } = req.params;
 
  // Get the spreadsheet and its associated user from the database.
  const spreadsheet = await prisma.spreadsheet.findUnique({
    where: { id: Number(id) },
    include: { user: true },
  });
 
  // Verify that the spreadsheet exists.
  if (!spreadsheet) {
    return res.status(404).json({ error: "Spreadsheet not found" });
  }
 
  // Verify that the user is not over the quota.
  if (spreadsheet.user.quota >= MAX_FREE_TIER_QUOTA) {
    return res.status(429).json({ error: "Quota exceeded" });
  }
 
  // Increase the user's quota by 1.
  await prisma.user.update({
    where: { id: spreadsheet.user.id },
    data: { quota: { increment: 1 } },
  });
 
  // Return the spreadsheet's content.
  return res.json({ content: spreadsheet.content });
}

However there is a problem with this approach, it's not as fast as it should be.

Indeed, we are creating a database transaction to increase the user's quota on every API request.

We access the database to get the user's quota, create a transaction to increment the quota by 1, and then send the spreadsheet content.

This is not optimal for many reasons:

Your database may be slow, this will slow down your response time.
Your database may be far from your server, this will again slow down your response time.
This code is harder to make it Edge compatible, you need to create multiple database replicas around the world (not an easy task to do!).

Using Upstash to increase the user's quota

To solve this problem, we will use Upstash Redis® to increase the user's quota. Upstash Redis® is a fast and reliable Redis® database, hosted in the cloud made for serverless environments, ready for the Edge (run code close to your users).

We will use the INCR Redis® command to increase the user's quota by 1. This command is atomic, which means that it will only be executed once, and it will be executed fast.

Here is the same code as before, but using Upstash Redis® to manage the user's quota:

// pages/api/spreadsheets/[id]/index.ts
import type { NextApiRequest, NextApiResponse } from "next";
 
import { Redis } from "@upstash/redis";
 
const MAX_FREE_TIER_QUOTA = 1000;
 
// Generate an Upstash Redis instance using environment variables.
// Make sure those are defined in your .env file.
const redis = new Redis({
  url: process.env.UPSTASH_REDIS_REST_URL!,
  token: process.env.UPSTASH_REDIS_REST_TOKEN!,
});
 
export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse,
) {
  // Get the ID of the spreadsheet from the URL.
  const { id } = req.params;
 
  // Get the spreadsheet and its associated user from the database.
  const spreadsheet = await prisma.spreadsheet.findUnique({
    where: { id: Number(id) },
    include: { user: true },
  });
 
  // Verify that the spreadsheet exists.
  if (!spreadsheet) {
    return res.status(404).json({ error: "Spreadsheet not found" });
  }
 
  // Redis quota key, unique for each user by its ID.
  const quotaKey = `user:${spreadsheet.user.id}:quota`;
 
  // Retrieve the user's quota from Redis.
  const quota = await redis.incr(quotaKey);
 
  // If the key did not exist before, the returned value will be 1
  // and we set the expiration to 1 day.
  if (quota === 1) {
    await redis.expire(quotaKey, 60 * 60 * 24);
  }
 
  // Verify that the user is not over the quota.
  if (quota > MAX_FREE_TIER_QUOTA) {
    return res.status(429).json({ error: "Quota exceeded" });
  }
 
  // Return the spreadsheet's content.
  return res.json({ content: spreadsheet.content });
}

// pages/api/spreadsheets/[id]/index.ts
import type { NextApiRequest, NextApiResponse } from "next";
 
import { Redis } from "@upstash/redis";
 
const MAX_FREE_TIER_QUOTA = 1000;
 
// Generate an Upstash Redis instance using environment variables.
// Make sure those are defined in your .env file.
const redis = new Redis({
  url: process.env.UPSTASH_REDIS_REST_URL!,
  token: process.env.UPSTASH_REDIS_REST_TOKEN!,
});
 
export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse,
) {
  // Get the ID of the spreadsheet from the URL.
  const { id } = req.params;
 
  // Get the spreadsheet and its associated user from the database.
  const spreadsheet = await prisma.spreadsheet.findUnique({
    where: { id: Number(id) },
    include: { user: true },
  });
 
  // Verify that the spreadsheet exists.
  if (!spreadsheet) {
    return res.status(404).json({ error: "Spreadsheet not found" });
  }
 
  // Redis quota key, unique for each user by its ID.
  const quotaKey = `user:${spreadsheet.user.id}:quota`;
 
  // Retrieve the user's quota from Redis.
  const quota = await redis.incr(quotaKey);
 
  // If the key did not exist before, the returned value will be 1
  // and we set the expiration to 1 day.
  if (quota === 1) {
    await redis.expire(quotaKey, 60 * 60 * 24);
  }
 
  // Verify that the user is not over the quota.
  if (quota > MAX_FREE_TIER_QUOTA) {
    return res.status(429).json({ error: "Quota exceeded" });
  }
 
  // Return the spreadsheet's content.
  return res.json({ content: spreadsheet.content });
}

Using Upstash Redis® makes your code easily scalable, as you can leverage the power of Upstash Redis® Edge to run your code close to your users.

Conclusion

Upstash Redis® makes it very easy to implement a caching system for a serverless environment.

We used it to implement a quota system but the original idea can be applied to much more use-cases, such as caching more database queries, or caching the results of an API request.

The final goal of caching is to make your API faster and more likely to consume less resources (e.g. Planetscale database read/writes queries).

In a future article, we will see how to use Upstash QStash to retrieve the user's quota from Redis® and store it in the database, to make sure that the user's quota is always up-to-date and not lost.

See it live

If you want to see this kind of optimisation live, you can check out fastsheet, an API services that turns your Google Sheets into an API in a few clicks. It's free to use with a generous free tier.

redis quota edge billing saas prisma