Prisma.js with MySQL

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

CREATE DATABASE mydatabase;

mkdir my-prisma-app
cd my-prisma-app
npm init -y

npm install prisma --save-dev
npm install @prisma/client mysql2

npx prisma init

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

DATABASE_URL="mysql://root:password@localhost:3306/mydatabase"

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
}

npx prisma migrate dev --name init

npx prisma migrate deploy

npx prisma db push

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

const newUser = await prisma.user.create({
  data: {
    name: "John Doe",
    email: "john@example.com"
  }
});
console.log(newUser);

const users = await prisma.user.findMany();
console.log(users);

const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: { name: "Jane Doe" }
});
console.log(updatedUser);

await prisma.user.delete({
  where: { id: 1 }
});

const users = await prisma.user.findMany({
  where: { name: { contains: "John" } },
  orderBy: { name: "asc" }
});

const users = await prisma.user.findMany({
  skip: 10,
  take: 5
});

const result = await prisma.$queryRaw`SELECT * FROM User`;
console.log(result);

npm install express

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'));

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")
}

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());

node prisma/seed.js

Related Posts

How to perform a mathematical optimization with tight d...

Feb 13, 2025  0

I built a scheduling platform for Bluesky

Mar 1, 2025  0

How to Run LaTeX Projects Locally (for Free) On Windows

Feb 28, 2025  0