Manage a quota-based SaaS application with Upstash and Next.js
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.
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 aUser
. - A
User
can have multipleSpreadsheet
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.