So far in this series we’ve covered a lot on how to use Prisma in our Nuxt 3 apps.

But we’ve left out a major piece — actually being able to modify the data in the database.

A pretty crucial part of the puzzle I think!

This is the final article in our 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
5. Modifying Data using Prisma 👈 we’re here

In this article we’ll cover:

• Adding a new LessonProgress model so we can track student progress through the course
• Understanding how to create unique fields in Prisma
• Creating and updating data with Prisma
• Creating a more complex server route that performs several steps

We’ll save the student’s progress to the database. But to do that, we first need to create a new model in our Prisma schema.

Add LessonProgress to our Schema

In order to store our progress, we’ll need to first create the LessonProgress model in our schema:

model LessonProgress {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  completed Boolean  @default(false)
  Lesson    Lesson   @relation(fields: [lessonId], references: [id])
  lessonId  Int
  userEmail String
}

This has the standard properties we create with most objects: id, createdAt, and updatedAt.

We also have a completed boolean, a userEmail so we know which user this is for, and then the relation to the Lesson object so we can also know which lesson we’ve completed (or not completed).

We also need to update our Lesson object to add in the relation. We do that by adding in the LessonProgress line:

model Lesson {
  id             Int              @id @default(autoincrement())
  title          String
  slug           String
  number         Int
  downloadUrl    String
  videoId        Int
  text           String
  sourceUrl      String?
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt
  Chapter        Chapter          @relation(fields: [chapterId], references: [id])
  chapterId      Int
  LessonProgress LessonProgress[]
}

But we’re not quite done with the schema yet.

Each user should only have one object in the database for every lesson. Either they’ve completed that lesson or not, so it doesn’t make sense to allow multiple rows in the database with the same userEmail and lessonId.

To prevent this, we can tell Prisma (and the underlying Postgres database) that these two fields taken together should always be unique. We do this with the @@unique attribute:

@@unique([lessonId, userEmail])

We’ll just add this in at the end of the LessonProgress model:

model LessonProgress {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  completed Boolean  @default(false)
  Lesson    Lesson   @relation(fields: [lessonId], references: [id])
  lessonId  Int
  userEmail String

  @@unique([lessonId, userEmail])
}

This will come in handy when we write our endpoint.

But first, remember to run prisma migrate dev to regenerate the Prisma client and sync these new changes with our Supabase database.

Updating our Database with Lesson Progress

Now we get to create the endpoint that we’ll use to update our progress as we go. We’ll create it at the route ~/server/api/course/chapter/[chapterSlug]/lesson/[lessonSlug]/progress.ts. Here’s the full endpoint, which we’ll go over section by section:

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

const prisma = new PrismaClient();

// Endpoint that updates the progress of a lesson
export default defineEventHandler(async (event) => {
  // Only allow PUT, PATCH, or POST requests
  assertMethod(event, ['PUT', 'PATCH', 'POST']);

    // Throw a 401 if there is no user logged in.
  protectRoute(event);

  // Get the route params
  const { chapterSlug, lessonSlug } = event.context.params;

  // Get the lesson from the DB
  const lesson = await prisma.lesson.findFirst({
    where: {
      slug: lessonSlug,
      Chapter: {
        slug: chapterSlug,
      },
    },
  });

  // If the lesson doesn't exist, throw a 404
  if (!lesson) {
    throw createError({
      statusCode: 404,
      statusMessage: 'Lesson not found',
    });
  }

  // Get the completed value from the request body and update progress in DB
  // Select based on the chapter and lesson slugs
  const { completed, userEmail } = await readBody(event);
  // Get user email from the supabase user if there is one.
  const {
    user: { email: userEmail },
  } = event.context;
  return prisma.lessonProgress.upsert({
    where: {
      lessonId_userEmail: {
        lessonId: lesson.id,
        userEmail,
      },
    },
    update: {
      completed,
    },
    create: {
      completed,
      userEmail,
      Lesson: {
        connect: {
          id: lesson.id,
        },
      },
    },
  });
});

1. Only allow the right HTTP methods

With Nuxt we can simply add a prefix to the server route filename to indicate we only want to accept certain HTTP methods, like POST or GET: ~/server/api/route.get.ts

But with this method, we actually want to support multiple. This is because the logic for handling a POST and PUT or PATCH are the same in this case. We use POST for creating new objects in the database, and then PUT or PATCH for updating them.

Using assertMethod we can guarantee that we’re only going to run with these methods:

assertMethod(event, ['PUT', 'PATCH', 'POST']);

2. Protect the server route using authentication

The protectRoute method is a custom method that we create in Mastering Nuxt 3 in order to keep routes safe behind authentication.

Now to the main part of this endpoint. We’re doing two main things here.

3. Fetch the correct Lesson from the database

We need to find the Lesson object based on the chapterSlug and lessonSlug. This is so we can grab the id of the Lesson object:

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

4. Grab values from the request

In between the database calls we get the completed and userEmail values from the JSON payload in the request using the readBody method:

const { completed, userEmail } = await readBody(event);

It’s also possible to grab the userEmail using the Supabase authentication method, but that’s outside of the scope for this article. You’ll have to check out Mastering Nuxt 3 if you want those full details.

5. Insert or update the LessonProgress object

This is really where the interesting part is.

Now that we have the lesson id, we can upsert the LessonProgress based on the lesson id and the userEmail:

// Get user email from the supabase user if there is one.
const {
  user: { email: userEmail },
} = event.context;
return prisma.lessonProgress.upsert({
  where: {
    lessonId_userEmail: {
      lessonId: lesson.id,
      userEmail,
    },
  },
  update: {
    completed,
  },
  create: {
    completed,
    userEmail,
    Lesson: {
      connect: {
        id: lesson.id,
      },
    },
  },
});

The upsert command is a combination of insert and update. If the object doesn’t already exist, we create it. Otherwise, we update with the new information. Remember, we’ve defined in our schema that any combination of userEmail and lessonId is unique, so we’re allowed to do that here.

You’ll notice that we have a special key in the where clause:

where: {
  lessonId_userEmail: {
    lessonId: lesson.id,
    userEmail,
  },
},

In order to indicate that we’re accessing a unique value, we have to combine the keys using an underscore _ to get lessonId_userEmail as the key. The order of these is based on the order they are in your schema @@unique([lessonId, userEmail]).

We have two other sections in the query.

The update section contains all the fields we want to update, which is only ever the completed value. The create section contains all the fields that are necessary for us to properly create the object and link it up to the correct Lesson object:

create: {
  completed,
  userEmail,
  Lesson: {
    connect: {
      id: lesson.id,
    },
  },
},

Other than some error handling, that’s it!

To recap, here’s what we’re doing in this endpoint:

1. Only allow the right HTTP methods
2. Protect the server route using authentication
3. Fetch the correct Lesson from the database
4. Grab values from the request
5. Insert or update the LessonProgress object

Next, let’s see how we can use this endpoint in the frontend of our application.

Using our new server route

Here is some sample code showing how you might use this endpoint:

try {
  await $fetch(
    `/api/course/chapter/${chapter}/lesson/${lesson}/progress`,
    {
      method: 'POST',
      // Automatically stringified by ofetch
      body: {
        completed: !currentProgress,
        userEmail: user.value.email,
      },
    }
  );
} catch (error) {
  console.error(error);

  // Any other logic needed to recover from a failed update
}

The main thing to pay attention to here is the $fetch request.

We use a POST method, passing in the completed and userEmail values that we need in the endpoint. The built-in $fetch method will automatically stringify our payload for us, so we can just pass a JS object — really nice!

Of course, we’ve wrapped this all in a try...catch block, so that if something goes wrong we can recover from the error.

In Mastering Nuxt 3 we implement an optimistic UI here. As soon as the user clicks the button we update our UI, without even waiting for the database update to go through. If it fails, we can undo our UI update, but this makes for a much more responsive UI.

Wrapping Up

In this article we saw how we can use Prisma to modify the data in our database.

We used the upsert method here, but there are many other methods you can use to interact with your database, depending on your specific situation.

And this article wraps up this series on using Prisma with Nuxt 3!

In Mastering Nuxt 3 we cover Prisma and Supabase in even more depth, showing more ways to use it, including how to add in authentication.

Here’s a list of the articles in this series you want to go back and re-read (or if you haven’t read them yet):

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
5. Modifying Data using Prisma