Next.js Prisma MySQL Integration to Boost Your Next.js App

Looking to integrate Next.js, Prisma, and MySQL for your web application?

In this blog, you will learn how to set up your environment, configure MySQL, and create API routes with Prisma for efficient database operations using Next.js, Prisma, and MySQL.

We will explore how to leverage these tools to enhance your application’s performance and scalability, including the benefits of using Next.js, Prisma, and MySQL together.

---

Key Takeaways

• To integrate Prisma with a Next.js app, first set up the environment with Node.js, Next.js, and MySQL before initializing the project and installing necessary components.
• The Prisma schema defines the application’s data structure and relational models, allowing for efficient database management and seamless interaction through generated API routes.
• Testing the application and ensuring optimal MySQL connection configurations are crucial for deployment, especially using platforms like Vercel. Troubleshooting common issues like SQL Views and connection management is essential for smooth operation.

---

Setting Up the Environment

To develop our Next.js application, we must prepare the environment by installing Node.js and Next.js for server-side JavaScript execution and user interface development, as well as MySQL for database administration.

1. Install Node.js from its official site .
2. Install MySQL and verify the installation with:

   Copy
   node -v # To verify Node.js
   mysql -V # To verify MySQL

With these tools installed, we’re ready to initialize our project with Next.js.

---

Initializing a Next.js Project

To create a new Next.js project, run:

Copy
npx create-next-app@latest --ts

Once in your project’s directory, start the development server with:

Copy
npm run dev

This command will host your Next.js app locally, allowing you to verify that everything is functioning correctly.

---

Adding Prisma to Your Project

Install the Prisma CLI with:

Copy
npm install prisma --save-dev

Next, initialize Prisma:

Copy
npx prisma init

This will create a prisma folder and schema.prisma file, where you define the database schema. Then install Prisma Client to interact with the database:

Copy
npm install @prisma/client

---

Configuring MySQL Database Connection

To connect Prisma to a MySQL database, configure the connection in .env:

Copy
DATABASE_URL="mysql://USERNAME:PASSWORD@HOST:PORT/DATABASE"

Make sure MySQL server is active, and for deployments on services like Vercel, ensure port 3306 is open for public access.

---

Defining Prisma Schema

In , define your data structure and relationships. Here’s an example schema for a and model:

After defining the schema, synchronize it with your database using migrations.

---

Running Database Migrations

Run migrations with:

This command creates the tables and relationships in your database according to your schema. The migrations folder inside the directory will contain SQL statements that track changes.

---

Generating Prisma Client

To generate Prisma Client, execute:

This command makes the Prisma Client available to query your database in a type-safe manner.

---

Connecting Prisma to Next.js

Create a file inside the directory to instantiate and export the Prisma Client instance:

Now, Prisma Client is accessible throughout your Next.js app by importing it from .

---

Creating API Routes with Prisma

Next.js uses serverless functions to handle API routes. To create these routes, add an directory in and define route files for CRUD operations.

Create User Route

To add a new user with Prisma’s method:

Get Users Route

To retrieve user data with :

Update User Route

To update a user with :

Delete User Route

To delete a user with :

---

Building Next.js Components

Create a directory to organize your React components. Use CSS Modules for styling.

UserList Component

Fetch and display users with :

AddUserForm Component

Create a form for adding new users:

---

Testing Your Application

Before deploying, test the app by running:

For database management, use Prisma Studio:

---

Deploying to Vercel

Deploying a Next.js project on Vercel is straightforward. Ensure MySQL connection strings include SSL parameters, especially for Azure databases:

---

Common Issues and Troubleshooting

When using Prisma with MySQL, SQL Views aren’t supported, but detailed guides are available to work around this. Prisma Accelerate can also manage connection pooling during high-traffic periods.

---

The Bottom Line!

Integrating Prisma with Next.js and MySQL improves efficiency and scalability. From environment setup to deployment, these tools help create dynamic, data-driven applications.

DATABASE_URL="mysql://USERNAME:PASSWORD@HOST/DATABASE?sslaccept=strict"

npx prisma migrate dev --name init

npx prisma generate

npm run dev

npx prisma studio

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

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

model User {
  id    Int     @id @default(autoincrement())
  name  String
  email String  @unique
  posts Post[]
}

model Post {
  id      Int     @id @default(autoincrement())
  title   String
  content String
  author  User    @relation(fields: [authorId], references: [id])
  authorId Int
}

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export default prisma;

// pages/api/users/index.ts

import prisma from '../../../lib/prisma';
import { NextApiRequest, NextApiResponse } from 'next';

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  if (req.method === 'POST') {
    const { name, email } = req.body;
    const user = await prisma.user.create({
      data: { name, email },
    });
    res.status(201).json(user);
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

// pages/api/users/index.ts

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  if (req.method === 'GET') {
    const users = await prisma.user.findMany();
    res.status(200).json(users);
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

// pages/api/users/[id].ts

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  const { id } = req.query;
  const { name, email } = req.body;

  if (req.method === 'PUT') {
    const user = await prisma.user.update({
      where: { id: Number(id) },
      data: { name, email },
    });
    res.status(200).json(user);
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

// pages/api/users/[id].ts

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  const { id } = req.query;

  if (req.method === 'DELETE') {
    const user = await prisma.user.delete({
      where: { id: Number(id) },
    });
    res.status(204).end();
  } else {
    res.status(405).json({ message: 'Method not allowed' });
  }
}

// components/UserList.tsx
import prisma from '../lib/prisma';

export async function getStaticProps() {
  const users = await prisma.user.findMany();
  return { props: { users } };
}

export default function UserList({ users }) {
  return (
    <ul>
      {users.map(user => (
        <li key={user.id}>{user.name} - {user.email}</li>
      ))}
    </ul>
  );
}

// components/AddUserForm.tsx

export default function AddUserForm() {
  const handleSubmit = async (event) => {
    event.preventDefault();
    const name = event.target.name.value;
    const email = event.target.email.value;
    
    await fetch('/api/users', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ name, email }),
    });
  };

  return (
    <form onSubmit={handleSubmit}>
      <input type="text" name="name" placeholder="Name" required />
      <input type="email" name="email" placeholder="Email" required />
      <button type="submit">Add User</button>
    </form>
  );
}