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
   1node -v # To verify Node.js
   2mysql -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
1npx create-next-app@latest --ts

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

Copy
1npm 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
1npm install prisma --save-dev

Next, initialize Prisma:

Copy
1npx 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
1npm install @prisma/client

---

Configuring MySQL Database Connection

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

Copy
1DATABASE_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 schema.prisma, define your data structure and relationships. Here’s an example schema for a User and Post model:

Copy
1// prisma/schema.prisma
2
3generator client {
4  provider = "prisma-client-js"
5}
6
7datasource db {
8  provider = "mysql"
9  url      = env("DATABASE_URL")
10}
11
12model User {
13  id    Int     @id @default(autoincrement())
14  name  String
15  email String  @unique
16  posts Post[]
17}
18
19model Post {
20  id      Int     @id @default(autoincrement())
21  title   String
22  content String
23  author  User    @relation(fields: [authorId], references: [id])
24  authorId Int
25}

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

---

Running Database Migrations

Run migrations with:

Copy
1npx prisma migrate dev --name init

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

---

Generating Prisma Client

To generate Prisma Client, execute:

Copy
1npx prisma generate

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

---

Connecting Prisma to Next.js

Create a prisma.ts file inside the lib directory to instantiate and export the Prisma Client instance:

Copy
1// lib/prisma.ts
2import { PrismaClient } from '@prisma/client';
3
4const prisma = new PrismaClient();
5
6export default prisma;

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

---

Creating API Routes with Prisma

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

Create User Route

To add a new user with Prisma’s create() method:

Copy
1// pages/api/users/index.ts
2
3import prisma from '../../../lib/prisma';
4import { NextApiRequest, NextApiResponse } from 'next';
5
6export default async function handler(req: NextApiRequest, res: NextApiResponse) {
7  if (req.method === 'POST') {
8    const { name, email } = req.body;
9    const user = await prisma.user.create({
10      data: { name, email },
11    });
12    res.status(201).json(user);
13  } else {
14    res.status(405).json({ message: 'Method not allowed' });
15  }
16}

Get Users Route

To retrieve user data with findMany():

Copy
1// pages/api/users/index.ts
2
3export default async function handler(req: NextApiRequest, res: NextApiResponse) {
4  if (req.method === 'GET') {
5    const users = await prisma.user.findMany();
6    res.status(200).json(users);
7  } else {
8    res.status(405).json({ message: 'Method not allowed' });
9  }
10}

Update User Route

To update a user with update():

Copy
1// pages/api/users/[id].ts
2
3export default async function handler(req: NextApiRequest, res: NextApiResponse) {
4  const { id } = req.query;
5  const { name, email } = req.body;
6
7  if (req.method === 'PUT') {
8    const user = await prisma.user.update({
9      where: { id: Number(id) },
10      data: { name, email },
11    });
12    res.status(200).json(user);
13  } else {
14    res.status(405).json({ message: 'Method not allowed' });
15  }
16}

Delete User Route

To delete a user with delete():

Copy
1// pages/api/users/[id].ts
2
3export default async function handler(req: NextApiRequest, res: NextApiResponse) {
4  const { id } = req.query;
5
6  if (req.method === 'DELETE') {
7    const user = await prisma.user.delete({
8      where: { id: Number(id) },
9    });
10    res.status(204).end();
11  } else {
12    res.status(405).json({ message: 'Method not allowed' });
13  }
14}

---

Building Next.js Components

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

UserList Component

Fetch and display users with getStaticProps:

Copy
1// components/UserList.tsx
2import prisma from '../lib/prisma';
3
4export async function getStaticProps() {
5  const users = await prisma.user.findMany();
6  return { props: { users } };
7}
8
9export default function UserList({ users }) {
10  return (
11    <ul>
12      {users.map(user => (
13        <li key={user.id}>{user.name} - {user.email}</li>
14      ))}
15    </ul>
16  );
17}

AddUserForm Component

Create a form for adding new users:

Copy
1// components/AddUserForm.tsx
2
3export default function AddUserForm() {
4  const handleSubmit = async (event) => {
5    event.preventDefault();
6    const name = event.target.name.value;
7    const email = event.target.email.value;
8    
9    await fetch('/api/users', {
10      method: 'POST',
11      headers: { 'Content-Type': 'application/json' },
12      body: JSON.stringify({ name, email }),
13    });
14  };
15
16  return (
17    <form onSubmit={handleSubmit}>
18      <input type="text" name="name" placeholder="Name" required />
19      <input type="email" name="email" placeholder="Email" required />
20      <button type="submit">Add User</button>
21    </form>
22  );
23}

---

Testing Your Application

Before deploying, test the app by running:

Copy
1npm run dev

For database management, use Prisma Studio:

Copy
1npx 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:

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

---

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.