We’ve got our database filled with data — now we need to fetch that data.

Prisma gives us a ton of flexibility and power in how we do that.

We can easily make complex queries, all while keeping everything typesafe — you just have to know a couple tricks to get it to work correctly.

This is the fourth article in a series dedicated to showing you how to use Prisma in your Nuxt 3 app:

1. Setting up Prisma (with Supabase)
2. Creating the Prisma Schema
3. Seeding the Database with Dummy Data
4. Getting Data from our Database with Prisma 👈 we’re here
5. Modifying Data using Prisma

In this article we’ll cover:

• How to use the Prisma client to fetch data from our database
• Adding in a route to get lesson data
• Adding a second route to get the course outline
• Cleaning up our code while keeping it all typesafe, using generated types

The first thing I want to show you is the Prisma client, and the different options it gives us on how to write our queries.

Working with the Prisma Client

The Prisma client has a really powerful and flexible interface, so let’s take a look and see what kinds of things we can do with it.

A very basic, but incomplete, server route that fetches the very first lesson in our database looks like this:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export default defineEventHandler(async (event) => {
  const { chapterSlug, lessonSlug } = event.context.params;

  return prisma.lesson.findFirst();
});

If we wanted to grab all the lessons, we could use the findMany query:

prisma.lesson.findMany();

Just like with a SQL statement, we can also add a where clause to filter our lessons:

prisma.lesson.findMany({
  where: {
    chapter: {
      slug: chapterSlug,
    },
  },
});

We can also add a select clause to only return the fields we need:

prisma.lesson.findMany({
  where: {
    chapter: {
      slug: chapterSlug,
    },
  },
  select: {
    slug: true,
  },
});

Remember that each Lesson is linked to a specific Chapter. If we want that Chapter to be included in the returned object we can do that too:

prisma.lesson.findMany({
  where: {
    chapter: {
      slug: chapterSlug,
    },
  },
  include: {
    chapter: true,
  },
}

Now, we’ll get the Lesson object with the related Chapter object nested inside of it. But internally, the include works like a default select, so we can’t specify both at the same time.

However, we can nest queries and make them as complex as we want to:

prisma.lesson.findMany({
  where: {
    chapter: {
      slug: chapterSlug,
    },
  },
  include: {
    chapter: {
      select: {
        slug: true,
        title: true,
      },
    },
  },
}

Completing our Lesson Route

In the Mastering Nuxt 3 course we’re building a course platform, so one of the most important endpoints we need to create returns a specific lesson.

The route looks like this: /api/course/chapter/[chapterSlug]/lesson/[lessonSlug]

Based on the chapterSlug and the lessonSlug we can find the correct lesson and return it:

// server/api/course/chapter/[chapterSlug]/lesson/[lessonSlug].get.ts

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export default defineEventHandler(async (event) => {
  const { chapterSlug, lessonSlug } = event.context.params;

  const lesson = await prisma.lesson.findFirst({
    where: {
      slug: lessonSlug,
      chapter: {
        slug: chapterSlug,
      },
    },
  });

  if (!lesson) {
    throw createError({
      statusCode: 404,
      statusMessage: 'Lesson not found',
    });
  }

  return lesson;
});

The key part is the findFirst method:

const lesson = await prisma.lesson.findFirst({
  where: {
    slug: lessonSlug,
    chapter: {
      slug: chapterSlug,
    },
  },
});

We create a where clause that matches the lesson’s slug to the lessonSlug, but also matches the related Chapter object. They each have their own slug property, but by nesting the where clauses like this we can match against both of them in the same query.

Now, technically, we could find multiple matches in our database, even though this probably wouldn’t happen.

We can fix this by using the @@unique attribute from Prisma, but that’s beyond the scope of this series.

Adding a Course Outline Route

In order to show the outline of the course in the nav, we need to fetch a list of all the chapters in the course, along with all the lessons that are in each chapter.

We’ll create a new endpoint at server/api/course/meta:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export default defineEventHandler((event) =>
  prisma.course.findFirst({
    include: {
      chapters: {
        include: {
          lessons: true,
        },
      },
    },
  })
);

Here, we’re just grabbing all the data to start with.

We’re grabbing all the data for each lesson, which is not what we want. We only need a few things, like the title and slug in order to construct the right links.

We also don’t want to accidentally include the actual lesson content, since we’ll have that all locked down so only paying customers can access it! (TK link to series on auth)

Since we can’t combine include and select on the same query, we can only use the select:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export default defineEventHandler((event) =>
  prisma.course.findFirst({
    select: {
      title: true,
      chapters: {
        select: {
          title: true,
          slug: true,
          number: true,
          lessons: {
            select: {
              title: true,
              slug: true,
              number: true,
            },
          },
        },
      },
    },
  })
);

Yeah, this is pretty gross, but it works — we’ll clean it up in a second.

Let’s go through what’s happening here step-by-step:

1. We specify we want to find the first course — our schema is set up to handle multiple courses, even though we only have one right now.
2. We select the title on the course
3. On the chapters in the course, we also want to select the title, slug, number, and it’s lessons.
4. For each lesson, we’ll select the title, slug, and number

A lot is going on here in this one query!

But that’s one benefit of using Prisma. It’s much easier to write a complex query like this than to try and figure out the SQL to make it all happen — at least, it is for me.

Prisma is busy converting all of this into a SQL command to send to our Postgres database. And it does this all in a single query, so it’s pretty efficient too.

We haven’t even touched on the sorting that Prisma can do, which is also very helpful. There’s no need to write that logic yourself, since your database has it implemented and is likely faster anyway.

Improving Type Safety and Readability

Okay, now to clean up this mess.

But we need to do this in a way that keeps all of our type safety. Otherwise, we’re losing out on a huge benefit of Prisma.

We’ll use something called generated types from Prisma:

import { PrismaClient, Prisma } from '@prisma/client';

const prisma = new PrismaClient();

const lessonSelect = Prisma.validator<Prisma.LessonArgs>()({
  select: {
    title: true,
    slug: true,
    number: true,
  },
});
export type LessonOutline = Prisma.LessonGetPayload<
  typeof lessonSelect
>;

Here, we’re creating a new lessonSelect object that we can use in our queries. Then, we create a LessonOutline type that matches the type of that object.

It looks a little crazy, but we have to do it this way because of how Prisma works internally. A bit unfortunate, I know, but the boilerplate isn’t too bad.

Conclusion

In this article, we saw how we can use the Prisma client to fetch data in a bunch of different ways.

We saw how to build complex queries, how to simplify them, and how to keep them typesafe so we can work with Typescript in our applications.

But fetching static data is kind of boring.

In the next article in this series, we’ll cover how we can modify data and add new data to our database.

Next Article: Modifying Data using Prisma