ORM and Middleware

Object Relational Mapping (ORM)

Object-Relational Mapping, more commonly known as ORM, is a programming technique that allows developers to interact with their database like they would with SQL. However, instead of writing SQL queries or stored procedures, with ORM, you directly use your programming language of choice.

The ORM software will convert these lines of code into SQL and send it to the database to perform the requested operation. This allows developers to think and program in their language of choice, thus abstracting away the SQL language.

Understanding ORM

ORM translates the database's tabular data into a format more fitting with the language you're working with. For instance, in JavaScript, a row in a database table can be represented as an object, where each attribute of the object corresponds to a column of the table.

Object models are used to represent the data in the database. These models are then used to perform CRUD operations on the database. For instance, to represent a user in the database, you would create a user object with the following attributes:

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  age       Int
  role      Role     @default(USER)
}

Then, to create a new user in the database, you would write something like this:

const createdUser = await prisma.user.create({
  data: userObject,
});

The ORM software will convert this code into SQL and send it to the database to create a new user. The ORM software will also convert the result of the query into a JavaScript object and return it to the caller.

Advantages of ORM

ORM comes with several advantages:

• Abstraction of SQL: With ORM, you don't need to write SQL code. The ORM system takes care of this. This can lead to faster development and less room for SQL syntax errors.
• Database Agnostic: ORMs allow you to switch between different types of SQL databases (MySQL, SQLite, PostgreSQL, etc.) with minimal code changes.
• Maintainability: Since the database operations are written in the same language as the rest of your code, it's easier to maintain and understand.
• Use of Language Features: ORM lets you utilize the full power of your programming language to manipulate data, including functions, data types, loops, and more.
• Advanced features: Many ORMs come with additional features like caching, transactions, and more.

Prisma

Prisma is an open-source ORM for Node.js and TypeScript. It supports various databases including PostgreSQL, MySQL, SQLite, and SQL Server. One of the key benefits of Prisma is its focus on type safety and the ability to auto-generate a client based on your database schema, thus reducing a lot of boilerplate and potential for human error.

Read more about Prisma here: https://www.prisma.io/docs/concepts/overview/what-is-prisma (opens in a new tab)

Here are a few steps to help you get started with Prisma.

npm install prisma -D

npx prisma init

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
  relationMode = "prisma"
}

DATABASE_URL="mysql://user:password@localhost:3306/mydb?schema=public"

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}
 
model User {
  id    Int      @id @default(autoincrement())
  email String   @unique
  name  String?
  posts Post[]
}

npx prisma generate

npx prisma db push

npm install dotenv
npx dotenv -e .env.local prisma db push

Prisma and PlanetScale

The following YouTube playlist shows how to use Prisma with PlanetScale MySQL.

https://www.youtube.com/playlist?list=PLlRapu2ErjJ9DGsGHRwhRlm1FJVSN3FK7 (opens in a new tab)

Middleware

Middleware is a powerful feature that allows you to run server-side code during the lifecycle of a request. Middleware functions can perform tasks like manipulating the request or response objects, handling cookies, checking authentication status, and more. Most modern web frameworks have some sort of middleware system.

This week we'll learn how to create and use middleware in Next.js, and then we'll learn how middleware can be used to implement authentication in our applications.

Next.js Middleware

To implement middleware in Next.js, we need to create a file called middleware.js in the root of the project.

Here is an example of a middleware function that redirects the user to the login page if they are not authenticated:

import type { NextRequest } from "next/server";
import { NextResponse } from "next/server";
import { getSession } from "next-auth/client";
 
export function middleware(request: NextRequest) {
  const session = await getSession({ request });
 
  if (!session) {
    return NextResponse.redirect(new URL("/login", request.nextUrl));
  }
}

Please read through this short page on middleware in Next.js (opens in a new tab) or watch the video below.

Auth.js

Auth.js (formerly NextAuth.js) is a library that makes it easy to add authentication to your Next.js application. It supports a wide variety of authentication providers, including email/password, OAuth, and more. It also supports a wide variety of databases, including MongoDB, Postgres, and Prisma ORM.

New documentation can be found here: https://authjs.dev/getting-started/introduction (opens in a new tab)

Old documentation can be found here: https://next-auth.js.org/getting-started/introduction (opens in a new tab)

Next-Auth.js Tutorials

Here are some tutorials that will help you get started with Next-Auth.js:

Starter Templates

As we move into more complex applications, you may find it helpful to use a starter template. For example, here are some starter templates that use Next-Auth.js and PlanetScale: https://vercel.com/templates?framework=next.js&auth=nextauth.js&database=planetscale (opens in a new tab)

🌐 Real-World Example

Let return to the Iotawise project and see how PlanetScale MySQL and Prisma were used to build the backend. We will continue to focus on the activities.

schema.prisma

The schema.prisma file defines the database schema. It contains a list of models, each with a list of fields. The fields are mapped to columns in the database table.

model Activity {
  id          String   @id @default(cuid())
  userId      String   @map(name: "user_id")
  name        String
  description String?
  colorCode   String   @map(name: "color_code")
  createdAt   DateTime @default(now()) @map(name: "created_at")
  updatedAt   DateTime @updatedAt @map(name: "updated_at")
 
  user User @relation(fields: [userId], references: [id])
 
  activityLogs ActivityLog[]
 
  @@map(name: "activities")
}

In the example above, we have an Activity model with fields for id, userId, name, description, colorCode, createdAt, and updatedAt. The id field is marked as the primary key with the @id attribute. The userId field is mapped (as a foreign key) to the user_id column in the database table.

Because of the @relation attribute, Prisma will automatically create a user field on the Activity model. This field will contain the User object associated with the activity. This is known as a one-to-many relationship. An activity can only have one user, but a user can have many activities. Given an activity, you can get the user by accessing the user field.

CRUD Operations

Prisma provides a set of methods for performing CRUD operations on the database. These methods are automatically generated based on the database schema.

Create

In /api/activities/route.ts, we have the following code in the POST function to create a new activity.

const activity = await db.activity.create({
  data: {
    name: body.name,
    description: body.description,
    colorCode: body.colorCode,
    userId: session.user.id,
  },
  select: {
    id: true,
  },
});

The activity.create method takes an object with the data to insert into the database. It returns an object with the newly created activity. The select attribute tells Prisma which fields to return. In this case, we only want the id field.

Read

In /api/activities/route.ts, we have the following code in the GET function to get a list of activities.

// Get all of current user's activities
const activities = await db.activity.findMany({
  select: {
    id: true,
    name: true,
    description: true,
    colorCode: true,
    createdAt: true,
  },
  where: {
    userId: session.user.id,
  },
});

In this code, we're using the activity.findMany method to get a list of activities. The select attribute tells Prisma which fields to return. In this case, we want all of the fields except for updatedAt. The where attribute tells Prisma to only return activities where the userId matches the current user's ID.

Update

In /api/activities/[activityId]/route.ts, we have the following code in the PATCH function to update an activity.

// Update the activity
await db.activity.update({
  where: {
    id: params.activityId,
  },
  data: {
    name: body.name,
    description: body.description,
    colorCode: body.colorCode,
    updatedAt: new Date(),
  },
});

In this code, we're using the activity.update method to update an activity. The where attribute tells Prisma which activity to update. The data attribute tells Prisma which fields to update.

Delete

In /api/activities/[activityId]/route.ts, we have the following code in the DELETE function to delete an activity.

// Delete the activity
await db.activity.delete({
  where: {
    id: params.activityId as string,
  },
});

In this code, we're using the activity.delete method to delete an activity. The where attribute tells Prisma which activity to delete. In this case, we're using the activityId from the URL, and as string to tell TypeScript that it's a string.