Prisma ORM Setup Guide for MySQL
Prisma is a modern Object-Relational Mapper (ORM) for Node.js and TypeScript,
streamlining database interactions.
It offers type safety, automated migrations, and an intuitive query API, enhancing
developer productivity and reducing errors.
Prisma supports various databases, including PostgreSQL, MySQL, SQLite, and
MongoDB.
It is used as an alternative to writing plain SQL or using other database access tools
like SQL query builders or other ORMs.
Installation & Setup
1. Install Prisma Dependencies
yarn add prisma @prisma/client
or
npm install prisma --save-dev
npm install @prisma/client
Initialize Prisma Structure
Create the following file structure:
prisma/
└── schema.prisma
Configure Prisma Schema
Add this configuration to 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
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
Set Environment Variables
Create .env file in your project root:
DATABASE_URL = mysql://root:dan12345678@localhost:3306/backendsample
Put the database url
Generate Prisma Client
yarn prisma generate
Create Migration
yarn prisma migrate dev --name init --create-only
Apply Database Migration
yarn prisma migrate deploy
The folders structure
project-root/
├── .env
├── .gitignore
├── package.json
├── prisma/
│ ├── schema.prisma
│ └── migrations/
│ └── ... (auto-generated)
│ ├── config/
│ │ └── prisma.js
│ ├── controllers/
│ │ └── userController.js
│ └── routes/
│ └── userRoutes.js
└── server.js
Verify the following things
1. Check Database Connection:
o Verify MySQL is running
o Confirm the backendsample database exists
2. Verify Prisma Client:
o Check node_modules/@prisma/client exists
o Confirm prisma/client is importable in your code
3. Inspect Database Tables:
o After migration, verify the User table exists with correct schema
Now let’s write the following sample code to look at the functionality of restful
Api and store data to the database
Server.js
const express = require('express')
const { PrismaClient } = require('@prisma/client')
const userRoutes = require('./routes/userRoutes')
// Initialize Express and Prisma
const app = express()
const prisma = new PrismaClient()
const PORT = process.env.PORT || 3000
// Middleware
app.use(express.json())
// Routes
app.use('/api/users', userRoutes)
// Error handling middleware
app.use((err, req, res, next) => {
console.error(err.stack)
res.status(500).json({ error: 'Something broke!' })
})
// Start server
app.listen(PORT, () => {
console.log(`Server running on port ${PORT}`)
})
Routes/userRoute.js
const express = require('express')
const router = express.Router()
const {
getUsers,
createUser,
getUserById,
updateUser,
patchUser,
deleteUser
} = require('../controllers/userController')
// GET all users
router.get('/', getUsers)
// GET single user
router.get('/:id', getUserById)
// POST new user
router.post('/', createUser)
// PUT update user (full)
router.put('/:id', updateUser)
// PATCH update user (partial)
router.patch('/:id', patchUser)
// DELETE user
router.delete('/:id', deleteUser)
module.exports = router
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
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
Controllers/usercontroller.js
const prisma = require('../config/prisma')
const getUsers = async (req, res) => {
try {
const { name, email } = req.query
const users = await prisma.user.findMany({
where: {
name: name ? { contains: name, mode: 'insensitive' } : undefined,
email: email ? { contains: email, mode: 'insensitive' } : undefined
}
})
res.json(users)
} catch (error) {
console.error(error)
res.status(500).json({ error: 'Database error' })
}
}
// Create new user
const createUser = async (req, res) => {
try {
const { name, email } = req.body
if (!name || !email) {
return res.status(400).json({
error: 'Both name and email are required'
})
}
const newUser = await prisma.user.create({
data: { name, email }
})
res.status(201).json(newUser)
} catch (error) {
if (error.code === 'P2002') {
return res.status(400).json({ error: 'Email already exists' })
}
console.error(error)
res.status(500).json({ error: 'Database error' })
}
}
// Get single user by ID
const getUserById = async (req, res) => {
try {
const id = parseInt(req.params.id)
const user = await prisma.user.findUnique({
where: { id }
})
if (!user) {
return res.status(404).json({ error: 'User not found' })
}
res.json(user)
} catch (error) {
console.error(error)
res.status(500).json({ error: 'Database error' })
}
}
// Full update user
const updateUser = async (req, res) => {
try {
const id = parseInt(req.params.id)
const { name, email } = req.body
if (!name || !email) {
return res.status(400).json({
error: 'Both name and email are required for full update'
})
}
const updatedUser = await prisma.user.update({
where: { id },
data: { name, email }
})
res.json(updatedUser)
} catch (error) {
if (error.code === 'P2025') {
return res.status(404).json({ error: 'User not found' })
}
if (error.code === 'P2002') {
return res.status(400).json({ error: 'Email already exists' })
}
console.error(error)
res.status(500).json({ error: 'Database error' })
}
}
// Partial update user
const patchUser = async (req, res) => {
try {
const id = parseInt(req.params.id)
const { name, email } = req.body
if (!name && !email) {
return res.status(400).json({
error: 'At least one field (name or email) is required for update'
})
}
const existingUser = await prisma.user.findUnique({
where: { id }
})
if (!existingUser) {
return res.status(404).json({ error: 'User not found' })
}
const updatedUser = await prisma.user.update({
where: { id },
data: {
name: name ?? existingUser.name,
email: email ?? existingUser.email
}
})
res.json(updatedUser)
} catch (error) {
if (error.code === 'P2002') {
return res.status(400).json({ error: 'Email already exists' })
}
console.error(error)
res.status(500).json({ error: 'Database error' })
}
}
// Delete user
const deleteUser = async (req, res) => {
try {
const id = parseInt(req.params.id)
const deletedUser = await prisma.user.delete({
where: { id }
})
res.json({ message: 'User deleted successfully', deletedUser })
} catch (error) {
if (error.code === 'P2025') {
return res.status(404).json({ error: 'User not found' })
}
console.error(error)
res.status(500).json({ error: 'Database error' })
}
}
module.exports = {
getUsers,
createUser,
getUserById,
updateUser,
patchUser,
deleteUser
}
Config/prisma.js
const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()
module.exports = prisma
.env
DATABASE_URL = mysql://root:dan12345678@localhost:3306/backendsample
