Harsh Mishra

Posted on Feb 16

Prisma.js with MySQL

#mysql #sql #prisma #programming

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.

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. 🚀

DEV Community