This content originally appeared on DEV Community and was authored by Harsh Mishra
Complete Guide to Prisma.js with MySQL: A Comprehensive Tutorial
Prisma.js is a modern database toolkit that simplifies working with databases in Node.js and TypeScript applications. It offers a type-safe and intuitive way to interact with databases, making development faster and less error-prone. This comprehensive guide will cover everything you need to know about Prisma.js with MySQL, from installation and setup to advanced database queries, migrations, and best practices.
Table of Contents
- Introduction to Prisma.js
- Why Use Prisma with MySQL?
- Setting Up Prisma with MySQL
- Understanding Prisma Schema
- Running Database Migrations
- CRUD Operations with Prisma Client
- Advanced Queries in Prisma
- Using Prisma with Express.js
- Working with Relations
- Seeding the Database
- Using Transactions
- Performance Optimization
- Error Handling in Prisma
- Deployment Best Practices
- Conclusion
1. Introduction to Prisma.js
Prisma is an open-source ORM (Object-Relational Mapper) that simplifies database access in Node.js and TypeScript applications. It provides:
- Prisma Client: Auto-generated and type-safe database queries.
- Prisma Migrate: A powerful migration tool for database schema changes.
- Prisma Studio: A GUI for viewing and managing database records.
Prisma supports MySQL, PostgreSQL, SQLite, SQL Server, MongoDB, and CockroachDB.
2. Why Use Prisma with MySQL?
Prisma is an excellent choice for working with MySQL because it offers:
✅ Type Safety: Auto-generated TypeScript types for database models.
✅ Readable Queries: Uses a simple, declarative syntax.
✅ Auto Migrations: Handles schema changes effortlessly.
✅ Performance Optimization: Optimized queries and caching.
✅ Database Agnostic: Easily switch between databases.
3. Setting Up Prisma with MySQL
Step 1: Install Node.js and MySQL
Make sure you have Node.js installed. Install MySQL and create a database:
CREATE DATABASE mydatabase;
Step 2: Initialize a Node.js Project
mkdir my-prisma-app
cd my-prisma-app
npm init -y
Step 3: Install Prisma and MySQL Client
npm install prisma --save-dev
npm install @prisma/client mysql2
Step 4: Initialize Prisma
npx prisma init
This creates a prisma folder with a schema.prisma file.
Step 5: Configure Prisma for MySQL
Edit prisma/schema.prisma:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
Step 6: Set Up Environment Variables
Edit .env:
DATABASE_URL="mysql://root:password@localhost:3306/mydatabase"
4. Understanding Prisma Schema
The schema.prisma file defines the database structure.
Example schema for Users and Posts:
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: [userId], references: [id])
userId Int
}
-
@id: Defines a primary key. -
@default(autoincrement()): Auto-increment ID. -
@unique: Ensures unique values. -
@relation: Defines foreign key relationships.
5. Running Database Migrations
Generate migration files:
npx prisma migrate dev --name init
Apply migrations:
npx prisma migrate deploy
Check database models:
npx prisma db push
6. CRUD Operations with Prisma Client
1. Import Prisma Client
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
2. Create a User
const newUser = await prisma.user.create({
data: {
name: "John Doe",
email: "john@example.com"
}
});
console.log(newUser);
3. Read Users
const users = await prisma.user.findMany();
console.log(users);
4. Update a User
const updatedUser = await prisma.user.update({
where: { id: 1 },
data: { name: "Jane Doe" }
});
console.log(updatedUser);
5. Delete a User
await prisma.user.delete({
where: { id: 1 }
});
7. Advanced Queries in Prisma
Filtering and Sorting
const users = await prisma.user.findMany({
where: { name: { contains: "John" } },
orderBy: { name: "asc" }
});
Pagination
const users = await prisma.user.findMany({
skip: 10,
take: 5
});
Raw SQL Queries
const result = await prisma.$queryRaw`SELECT * FROM User`;
console.log(result);
8. Using Prisma with Express.js
Install Express
npm install express
Create an Express API
const express = require('express');
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
const app = express();
app.use(express.json());
app.get('/users', async (req, res) => {
const users = await prisma.user.findMany();
res.json(users);
});
app.listen(3000, () => console.log('Server running on port 3000'));
9. Working with Relations
Prisma supports one-to-one, one-to-many, and many-to-many relations.
- One-to-Many (User → Posts)
- Many-to-Many (Users ↔ Groups)
Example many-to-many relationship:
model User {
id Int @id @default(autoincrement())
name String
groups Group[] @relation("UserGroups")
}
model Group {
id Int @id @default(autoincrement())
name String
users User[] @relation("UserGroups")
}
10. Seeding the Database
Create prisma/seed.js:
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
async function main() {
await prisma.user.createMany({
data: [{ name: "Alice" }, { name: "Bob" }]
});
}
main().catch(console.error).finally(() => prisma.$disconnect());
Run the seed script:
node prisma/seed.js
Conclusion
Prisma.js with MySQL offers a type-safe, intuitive, and efficient way to manage databases. From migrations and CRUD operations to advanced queries and API integration, Prisma simplifies database management while improving performance and developer experience. 🚀
This content originally appeared on DEV Community and was authored by Harsh Mishra
Harsh Mishra | Sciencx (2025-02-16T21:18:06+00:00) Prisma.js with MySQL. Retrieved from https://www.scien.cx/2025/02/16/prisma-js-with-mysql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.