Introduction
Performance is critical in CMS(Content Management System) applications where users expect rapid content delivery and smooth navigation. Leveraging Prisma Accelerate with Next.js provides a powerful solution for high-performance database interaction.
This article will walk you through how to set up Prisma Accelerate, create a simple CMS project, and benchmark its performance improvements. By the end, you’ll understand how it can reduce latency and optimize database load for a responsive, scalable CMS.
Table of Contents
- Introduction
- What is Prisma Accelerate?
- Setting Up a CMS Project with Next.js
- Setting Up PostgreSQL and Prisma Accelerate
- Building CMS Functionality
- Benchmarking Prisma Accelerate
- Conclusion
What is Prisma Accelerate?
Prisma Accelerate is an add-on to the Prisma ORM designed to enhance database query performance through global caching and scalable connection pooling. It’s built to reduce database load and improve response times, particularly in data-heavy applications like CMS.
Key Features
- Global Caching: Caches frequent database queries, reducing the need to access the database repeatedly.
- Connection Pooling: Manages connections efficiently, allowing the application to handle a high volume of simultaneous queries without overwhelming the database.
By integrating Prisma Accelerate, Next.js applications can deliver faster, more reliable experiences, especially under heavy traffic.
Setting Up a CMS Project with Next.js
Initialize the Next.js Project
- Start by creating a new Next.js application.
npx create-next-app high-perform-cms
Navigate to the project folder and open it in your preferred IDE, the folder structure should look like this:
Setting Up PostgreSQL and Prisma Accelerate
Set Up a PostgreSQL Database
- Choose a Provider: You can set up PostgreSQL locally or through a managed provider like Supabase, Railway, Heroku, or Neon.
- Get the Database URL: Once the database is set up, you’ll receive a connection string (URL) for PostgreSQL.
Configure Prisma Data Proxy for Prisma Accelerate
Prisma Accelerate requires the Prisma Data Proxy to enable caching and connection pooling.
Go to Prisma’s Data Platform and create an account.
Once you create a data proxy account either with GitHub or Google account, we will choose the Accelerate Option, remember we already have a database.
- Select
Accelerate, and provide your database URL and region. Once you provide this, clickEnable Accelerate.
- You’ll receive a connection URL for the Data Proxy, as shown in the image below.
- Update the
.envFile with the Data Proxy URL: In the root of your project, add or update theDATABASE_URLin the.envfile with the Data Proxy URL. This will allow Prisma to use caching and connection pooling.
DATABASE_URL="prisma://<your-data-proxy-connection-url>"
DIRECT_DATABASE_URL="<your-postgres-database-connection-url>"
API_KEY="<Prisma-api-key>"
Once you have stored the configurations, click I have securely stored my configuration.
Install Prisma Dependencies
- Install Prisma, Prisma Client and set up the Prisma schema.
npm install prisma @prisma/client@latest @prisma/extension-accelerate
Initialize Prisma to create a prisma/schema.prisma file:
npx prisma init
The schema should look like this:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
Note:
If you’re using a managed database provider that supports connection pooling (e.g., Supabase, Neon, or PlanetScale), you may need to configure two URLs:
Pooled Connection URL (for runtime): This URL is optimized for production use, where connection pooling is handled by the database provider and this is the
DATABASE_URL.Direct Connection URL (for schema migrations and CLI commands): This direct URL bypasses connection pooling, allowing Prisma CLI commands (like migrations) to work without compatibility issues and this is the
DIRECT_DATABASE_URL.
So, update schema.prisma to Use directUrl for CLI Commands for migrations or schema updates.
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
directUrl = env("DIRECT_DATABASE_URL")
}
- Define CMS Data Models In the schema.prisma file, define the models for User, Post, and Category. These represent users, content, and categories in the CMS.
model User {
id Int @id @default(autoincrement())
name String
email String @unique
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String
category Category @relation(fields: [categoryId], references: [id])
categoryId Int
author User @relation(fields: [authorId], references: [id])
authorId Int
createdAt DateTime @default(now())
}
model Category {
id Int @id @default(autoincrement())
name String
posts Post[]
}
- Run Migrations to Apply Models to the Database Run migrations so Prisma applies these models to your PostgreSQL database.
npx prisma migrate dev --name init
Once it successfully runs, we see the update on the migrations created in the prisma/migrations folder.
The following migration(s) have been created and applied to new schema changes:
migrations/
└─ 20241106145324_init/
└─ migration.sql
Your database is now in sync with your schema.
✔ Generated Prisma Client (v5.22.0) to ./node_modules/@prisma/client in 56ms
The above should be similar to what you see in your terminal.
Building CMS Functionality with Prisma and Next.js
With our Next.js and Prisma Accelerate setup in place, it’s time to create the core functionality of our CMS. We’ll focus on initializing the Prisma Client, setting up API routes and pages for managing posts, categories, and authors, and using Prisma Accelerate to optimize data fetching for high performance.
Initialize the Prisma Client
To interact with the database in our Next.js project, we need to initialize the Prisma client. We’ll create a reusable Prisma client instance that can be imported anywhere in the app.
In the project root, create a folder named src/lib, and add a file called prisma.ts. Inside this file, initialize and export the Prisma client:
// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client/edge';
import { withAccelerate } from '@prisma/extension-accelerate';
const prisma = new PrismaClient().$extends(withAccelerate());
export default prisma;
This setup creates a single instance of the Prisma client, which will be used for all database operations, ensuring efficient and consistent database access across the application.
Project Structure Overview
Now that we have Prisma set up, here’s an overview of the project structure so far:
prisma-accelerate-cms/
├── app/ # Next.js pages and API endpoints
│ ├── api/ # API routes for CRUD operations
│ ├── posts/ # Static and dynamic pages for posts
│ └── layout.tsx # Layout file
├── src/
│ ├── lib/
│ │ └── prisma.ts # Prisma client configuration
├── prisma/ # Prisma configuration and migrations
│ ├── schema.prisma # Prisma schema with model definitions
│ └── migrations/ # Migration files
├── .env # Environment variables
└── package.json # Project dependencies
Building API Routes
With the Prisma client set up, we’re ready to build the API routes for creating and fetching data in our CMS. In the next section, we’ll define API routes for User, Post, and Category models, leveraging Prisma’s data operations to interact with our PostgreSQL database.
- Create User API Route
Create an api/users/route.js folder in the app folder. Set up a POST endpoint to add new and fetch new users in app/api/users/route.js.
import prisma from '../../../src/lib/prisma';
// POST method to create a user
export async function POST(req) {
const data = await req.json();
const user = await prisma.user.create({
data: {
name: data.name,
email: data.email,
},
});
return new Response(JSON.stringify(user), {
headers: { 'Content-Type': 'application/json' },
status: 201,
});
}
// GET method to retrieve all users
export async function GET() {
const users = await prisma.user.findMany();
return new Response(JSON.stringify(users), {
headers: { 'Content-Type': 'application/json' },
});
}
This single file handles both the creation and retrieval of users. The POST method adds new users, while the GET method retrieves all users.
- Create Category API Route Define a route to create categories in app/api/categories/route.js.
import prisma from '../../../src/lib/prisma';
// POST method to create a category
export async function POST(req) {
const data = await req.json();
const category = await prisma.category.create({
data: {
name: data.name,
},
});
return new Response(JSON.stringify(category), {
headers: { 'Content-Type': 'application/json' },
status: 201,
});
}
// GET method to retrieve all categories
export async function GET() {
const categories = await prisma.category.findMany();
return new Response(JSON.stringify(categories), {
headers: { 'Content-Type': 'application/json' },
});
}
- Create Posts API Route For the Posts endpoint, we’ll use Prisma Accelerate’s caching to improve data retrieval efficiency. We will define a POST and GET Method to create and fetch All Posts with Prisma Accelerate.
import prisma from '../../../src/lib/prisma';
// POST method to create a post
export async function POST(req) {
const data = await req.json();
const post = await prisma.post.create({
data: {
title: data.title,
content: data.content,
authorId: data.authorId,
categoryId: data.categoryId,
},
});
return new Response(JSON.stringify(post), {
headers: { 'Content-Type': 'application/json' },
status: 201,
});
}
// GET method to retrieve all posts with Prisma Accelerate caching
export async function GET(req) {
const posts = await prisma.post.findMany({
include: { author: true, category: true },
cacheStrategy: { ttl: 3600 } // Cache results for 1 hour
});
return new Response(JSON.stringify(posts), {
headers: { 'Content-Type': 'application/json' },
});
}
The cacheStrategy option allows you to control the caching behavior of specific queries. By setting a ttl (time-to-live) value, you define how long a cached response should remain valid before being refreshed from the origin database as it minimizes repeated database calls, thus reducing latency and server load.
This is particularly beneficial for high-traffic CMS applications, where quick access to content without frequent database hits improves both performance and user experience.
Building a Posts UI in Next.js
With Prisma Accelerate and our API routes set up, let's create a simple UI in Next.js to display the posts from our CMS. This will give us a practical view of how cached data improves the user experience in the front end, allowing us to measure the impact of Prisma Accelerate on page load times.
Add a new PostsPage component in your Next.js project to fetch and display posts from the database. This component will use Next.js's Client Component mode since it leverages React’s useEffect and useState hooks to fetch data when the page loads.
// app/posts/page.js
"use client";
import { useEffect, useState } from 'react';
export default function PostsPage() {
const [posts, setPosts] = useState([]);
useEffect(() => {
async function fetchPosts() {
try {
const response = await fetch('/api/posts');
if (response.ok) {
const data = await response.json();
setPosts(data);
} else {
console.error("Failed to fetch posts:", response.status);
}
} catch (error) {
console.error("Error fetching posts:", error);
}
}
fetchPosts();
}, []);
return (
<div>
<h1>Posts</h1>
<ul>
{posts.map((post) => (
<li key={post.id}>
<h2>{post.title}</h2>
<p>{post.content}</p>
</li>
))}
</ul>
</div>
);
}
It fetches post data from the http://localhost:3000/api/postsendpoint using fetch.
Benchmarking Prisma Accelerate
With the UI in place, we’re ready to benchmark Prisma Accelerate. We’ll conduct tests on three types of fetches: an initial fetch, a cached fetch, and a repeated fetch. To see measurable differences, we’ve seeded our database with multiple posts, simulating a realistic content environment.
By increasing the data volume, we simulated a more realistic CMS environment, which allowed Prisma Accelerate’s caching to significantly impact response times. Here's how to seed the database and why it matters.
Step-by-Step: Seeding the Database
- Create Seed Script: In the prisma folder, create a file named seed.js to define the sample data.
- Run the Seed Script: Execute the script to populate your database. Here’s a sample seed.js script to add multiple posts, categories, and users:
// prisma/seed.js
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
async function main() {
// Creating sample users
const user = await prisma.user.create({
data: { name: "Dinma Jane", email: "dinma@gmail.com" },
});
// Creating sample categories
const category = await prisma.category.create({
data: { name: "Technology" },
});
// Creating multiple posts
for (let i = 0; i < 1000; i++) {
await prisma.post.create({
data: {
title: `Sample Post ${i + 1}`,
content: `This is the content of post ${i + 1}`,
authorId: user.id,
categoryId: category.id,
},
});
}
}
main()
.catch((e) => console.error(e))
.finally(async () => {
await prisma.$disconnect();
});
To execute the seed file and populate the database run:
node prisma/seed.js
This script will create:
One user (Dinma Jane) serves as the author of all posts.
One category (Technology).
1,000 posts, each linked to the same user and category. To make it more realistic, we generated 7000 records.
All the codes can be found in this repository
Updated Benchmark Results
|
Based on the screenshot provided above(Query latency distribution), here’s a breakdown of the performance metrics for Prisma Accelerate with caching enabled:
Initial Fetch (No Cache): This is the first query hitting the database directly without a cache.
The average latency for origin queries (served from the database, without cache) is around 263.84 ms.
Cached Fetch: After the initial query, the data is stored in the cache. Subsequent requests should be faster due to caching.
The average latency for cached queries is around 30.56 ms.
Repeated Fetch: This would be similar to the cached fetch if you run it multiple times, as the data is already cached. The cache hit rate is around 81.82%, indicating most queries are served from the cache.
With the seeded data, here’s an updated comparison of response times:
| Test Scenario | Direct Database Access (No Cache) | Prisma Accelerate (Caching Enabled) |
|---|---|---|
| Initial Fetch (No Cache) | ~263.84 ms | ~263.84 ms |
| Cached Fetch | N/A | ~30.56 ms |
| Repeated Fetch | N/A | ~30.56 ms |
Conclusion
We explored how to set up a high-performance CMS using Next.js and Prisma Accelerate. By leveraging Prisma’s cacheStrategy and caching mechanisms, we were able to dramatically improve query response times, with cached queries returning data nearly 10 times faster than those directly accessing the database. This speed boost is particularly advantageous in CMS applications, where users expect quick access to content and smooth, responsive navigation.
Key Takeaways
Efficient Database Access: Prisma Accelerate’s caching and connection pooling reduce database load, allowing frequently accessed data to be served directly from the cache.
Improved User Experience: Using Prisma Accelerate with Next.js enables us to build a CMS that’s not only fast but also scalable, making it ideal for dynamic applications with heavy read requirements.
Easy Caching Configuration: The cacheStrategy option provides fine-grained control over caching, allowing developers to configure cache duration based on content freshness needs.
By integrating Prisma Accelerate into your Next.js applications, you can build a CMS that meets high-performance demands while maintaining scalability. Try adding caching configurations in your projects, and see how Prisma Accelerate transforms your application's performance.
Top comments (0)