Creating and querying a MongoDB View with Prisma in Next.js 13
So I’m building a little site called The Mold Guide and I’m leveling up my knowledge of working with databases in Next.js 13. One challenge is that I want to query one model in the database and then use some of the data from those records to look up a related piece of data in another model.
The issue: I want to display reviews of a doctor on the doctor’s listing page. But in addition to listing out all of their reviews, I want to to pull in the displayname of the user that wrote the review. The user’s displayName is located in the User model, meaning that in order for my application to get all the relevant data, it has to query the Review model, and then go through the User model to find the relevant Users.
With server-side-rendered components, this was relatively simple but it gets a little tricky with client components because you have to make one call to the database and then once that data is available, make another call to the database to enrich the data.
There are probably a bunch of ways to solve this, but I’m using Prisma views because it just makes writing the queries so much simpler.
I’m using Next.js 13, Prisma, and MongoDB. Other databases should work similarly.
First, we need to add the views feature to the schema.prisma:
// /prisma/schema.prisma
generator client {
provider = "prisma-client-js"
previewFeatures = ["views"]
}Then we need to create an Aggregation in MongoDB.
This part took me a while because every piece of documentation was giving me some variation of what the Prisma docs say:
Ok, sure, but where do you run that command? And what the hell does it mean? I guess you could write a Node script to connect to Mongo and then send that command but…
Creating a View in MongoDB Compass
There’s an easier way, using MongoDB Compass (download it and connect to your database before moving on).
MongoDB Compass has this great little aggregation builder that makes it really easy to build and preview a view.
Once you have MongoDB installed and have connected to your database, go to the left sidebar and open up the Model that you want to use as the starting point for the view.
In my case, I want to add some simple User info to the Review records, so I’m starting with the Review model.
Then you want to select the Aggregations tab at the top of MongoDB Compass. You should see something like this:
Remember that db.createView() code from earlier? We can start breaking that down and putting it into the different stages of the pipeline builder.
The first stage is a $lookup, so you can enter that into the stage builder and you’ll get some boilerplate code:
Amazing. Now I’m going to fill out the variables to bring in some data from the User collection:
from: 'User' means that we’re going to join some data from the User collection with the Review collection. The localField: 'authorId' should correspond to the foreignField: '_id' in the User collection.
In other words, because I’ve created a relationship in prisma.schema between the User and Review collections, I can now match Review.authorId with User._id in this $lookup.
Here’s what that looks like in prisma.schema:
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
name String?
email String @unique
password String
displayName String?
reviews Review[] // Establishes a relationship with the Review model
}
model Review {
id String @id @default(auto()) @map("_id") @db.ObjectId
title String
body String
rating Int
author User @relation(fields: [authorId], references: [id])
authorId String @db.ObjectId
Doctor Doctor? @relation(fields: [doctorId], references: [id])
doctorId String
createdAt DateTime @default(now())
}The as: 'UserData' line in the query just gives us a name for the data we’ll be reading from the User collection.
Ok, now to the next step, using $project:
The first seven lines are just telling MongoDB that we want the view to display the normal data from the Review collection. You can add or delete lines, depending on what you want your View to include.
The final line is where the magic happens, as we pull in some data from the User collection with this code:
authorDisplayName: '$UserData.displayName',That’s telling MongoDB to find the displayName from the associated User record and include it in this view, alongside the other Review data.
And then the final step is to $unwind it:
Basically, this converts the authorDisplayName from an array to a string.
OK, so we have our aggregation all ready to go. Now, we just need to save it as a View. Open the Save dropdown menu and click Create view:
You’ll see a pop-up that asks you to name your View and once you save that, you’ll now have a View that can be queried just like a normal database Model!
Querying the View
Before being able to query the View in Next.js you’ll have to update the prisma.schema to include the view. In my case, I added this:
view EnrichedReviews { // The name you gave your view when you saved it
// The various fields in your view:
id String @id @default(auto()) @map("_id") @db.ObjectId
title String
body String
rating Int
authorId String @db.ObjectId
authorDisplayName String
doctorId String
createdAt DateTime
}Don’t forget to run npx prisma generate to update the Prisma schema.
Server-side Queries
In a server-side component, you can query the view like you would query any normal Model:
const enrichedReview = await prisma.enrichedReviews.findMany({
where: {
displayName: 'Alice',
},
})Client-side Queries
To query the view from a client component, using the API, you can build a route for querying the View.
In my /api/ folder, I created a new set of folders: /api/reviews-with-displayname/by-doctor-id/[id]/route.js
// /api/reviews-with/displayname/by-doctor-id/[id]route.js
import { prisma } from "../../../../lib/prisma";
import { NextResponse } from "next/server";
export async function GET(request, { params }) {
const doctorId = params.id; // I'm using the doctorId in the API URL call
const reviewsOfThisDoctor = await prisma.enrichedReviews.findMany({
where: {
doctorId: {
equals: doctorId,
},
},
});
if (!reviewsOfThisDoctor) {
return new NextResponse("No review with ID found", { status: 404 });
}
return NextResponse.json(reviewsOfThisDoctor);
}
And here’s what the call looks like in my client component:
"use client";
// React
import { cache, use } from "react";
// Pulls from a Prisma view that joins the reviews table with the users table
const getDoctorReviews = cache((doctorId) =>
fetch("/api/reviews-with-displayname/by-doctor-id/" + doctorId).then((res) =>
res.json()
)
);
const DoctorReviews = ({ doctorId }) => {
// Use the cached query
let reviews = use(getDoctorReviews(doctorId));
return (
<div className={styles.doctorReviewsContainer}>
<h3>Patient Reviews</h3>
{reviews &&
reviews.map((rev) => {
return (
<div key={rev.id} className={styles.doctorReview}>
<h4 style>{rev.title}</h4>
<p style>{rev.body}</p>
<p>
posted by <b>{rev.authorDisplayName}</b> on {rev.createdAt}
</p>
</div>
);
})}
</div>
);
};
export default DoctorReviews;Hope that helps!