Integrating Prisma ORM with Express.js for a Modern Database Setup

Want to supercharge your Express.js app with a modern, flexible database solution? In this guide, we’ll walk through integrating Prisma ORM into your Node.js project, making data handling seamless and efficient.

Setting Up Prisma in an Express.js App

We begin with a basic Node.js Express app that has routes for tasks and projects. To integrate Prisma, follow these steps:

1. Install and Initialize Prisma

First, install Prisma and initialize it with MySQL as the database provider:

npm install prisma --save-dev
npx prisma init

This creates a prisma directory and a .env file where we configure the database connection.

2. Configure the Database Connection

In .env, set the DATABASE_URL to match your MySQL database. If using Docker, ensure your database container is running.

3. Define the Prisma Schema

Prisma generates a schema.prisma file. Modify it to define our models:

model Task {
  id          String  @id @default(cuid())
  user_id     String  @db.VarChar(36)
  project_id  String? @db.VarChar(36)
  name        String
  description String?
  due_date    DateTime?
  completed_on DateTime?
  created_at  DateTime @default(now())
  updated_at  DateTime @updatedAt
  project     Project? @relation(fields: [project_id], references: [id])
}

model Project {
  id         String  @id @default(cuid())
  user_id    String  @db.VarChar(36)
  name       String
  description String?
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt
  tasks      Task[]
}

4. Generate Prisma Client

After defining the schema, run:

npx prisma generate

This command generates the Prisma client, enabling interaction with the database.

🚀Boost your career with 👉 IBM Full Stack Software Developer Certificate

Applying Migrations

To apply changes to the database, create a migration:

npm run migration:create

Prisma generates an SQL migration file, which we can review before applying:

npm run migrate

Implementing Prisma in Controllers

Now, let’s use Prisma to handle database operations in our controllers.

1. Set Up Prisma Client

Create a new file prismaClient.ts:

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
export default prisma;

2. Fetching Projects

Modify projectController.ts to retrieve projects:

import prisma from "../prismaClient";
export const listProjects = async (req, res, next) => {
  try {
    const projects = await prisma.project.findMany();
    res.json(projects);
  } catch (error) {
    next(error);
  }
};

3. Fetching Tasks

Similarly, update taskController.ts:

export const listTasks = async (req, res, next) => {
  try {
    const tasks = await prisma.task.findMany();
    res.json(tasks);
  } catch (error) {
    next(error);
  }
};

Testing and Debugging

Run npm run dev and test the API endpoints. If there’s no data, manually insert sample records into MySQL.

Conclusion

You now have a fully integrated Prisma ORM setup in your Express.js app, streamlining database interactions with a modern and efficient approach. In the next tutorial, we’ll cover automating test data seeding and implementing API authentication.