Design Converter
Education
Last updated on Dec 3, 2024
Last updated on Nov 5, 2024
Software Development Executive - II
I know who I am.
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.
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.
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.
To create a new Next.js project, run:
1npx create-next-app@latest --ts
Once in your project’s directory, start the development server with:
1npm run dev
This command will host your Next.js app locally, allowing you to verify that everything is functioning correctly.
Install the Prisma CLI with:
1npm install prisma --save-dev
Next, initialize Prisma:
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:
1npm install @prisma/client
To connect Prisma to a MySQL database, configure the connection in .env
:
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.
In schema.prisma
, define your data structure and relationships. Here’s an example schema for a User
and Post
model:
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.
Run migrations with:
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.
To generate Prisma Client, execute:
1npx prisma generate
This command makes the Prisma Client available to query your database in a type-safe manner.
Create a prisma.ts
file inside the lib
directory to instantiate and export the Prisma Client instance:
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
.
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.
To add a new user with Prisma’s create()
method:
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}
To retrieve user data with findMany()
:
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}
To update a user with update()
:
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}
To delete a user with delete()
:
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}
Create a components
directory to organize your React components. Use CSS Modules for styling.
Fetch and display users with getStaticProps
:
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}
Create a form for adding new users:
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}
Before deploying, test the app by running:
1npm run dev
For database management, use Prisma Studio:
1npx prisma studio
Deploying a Next.js project on Vercel is straightforward. Ensure MySQL connection strings include SSL parameters, especially for Azure databases:
1DATABASE_URL="mysql://USERNAME:PASSWORD@HOST/DATABASE?sslaccept=strict"
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.
Integrating Prisma with Next.js and MySQL improves efficiency and scalability. From environment setup to deployment, these tools help create dynamic, data-driven applications.
Tired of manually designing screens, coding on weekends, and technical debt? Let DhiWise handle it for you!
You can build an e-commerce store, healthcare app, portfolio, blogging website, social media or admin panel right away. Use our library of 40+ pre-built free templates to create your first application using DhiWise.