Here's a complete cheat sheet for Prisma, covering essential concepts, commands, and operations that you’ll often use when working with Prisma in full-stack applications.
Prisma Cheat Sheet
1. Installation and Setup
Install Prisma CLI
npm install prisma --save-dev
Initialize Prisma
Sets up prisma directory and a .env file.
npx prisma init
2. Prisma Schema
The Prisma schema is where you define your data models, database connection, and Prisma Client generation.
Basic Structure
datasource db {
provider = "postgresql" // Database provider (e.g., postgresql, mysql, sqlite)
url = env("DATABASE_URL") // Connection URL from .env file
}
generator client {
provider = "prisma-client-js" // Client generator
}
model User {
id Int @id @default(autoincrement()) // Auto-increment primary key
name String
email String @unique // Unique email field
}
Data Types
-
Int,String,Boolean,Float,DateTime -
@id: Primary Key -
@unique: Unique field constraint -
@default: Default values (e.g.,autoincrement(),now())
3. Migrations
Prisma migrations help in managing schema changes in your database.
Create Migration
Generates a migration based on changes in the Prisma schema.
npx prisma migrate dev --name <migration_name>
Apply Pending Migrations
Apply migrations to the database.
npx prisma migrate deploy
Reset the Database
Reset the database by applying migrations from scratch.
npx prisma migrate reset
4. Prisma Client
Prisma Client is an auto-generated and type-safe query builder.
Generate Prisma Client
After modifying the schema, generate Prisma Client.
npx prisma generate
Import and Initialize Prisma Client
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
5. CRUD Operations
Create
const newUser = await prisma.user.create({
data: {
name: "Alice",
email: "alice@example.com",
},
});
Read
const users = await prisma.user.findMany();
const user = await prisma.user.findUnique({
where: { email: "alice@example.com" },
});
Update
const updatedUser = await prisma.user.update({
where: { id: 1 },
data: { name: "Alice Updated" },
});
Delete
const deletedUser = await prisma.user.delete({
where: { id: 1 },
});
Upsert (Create or Update)
const user = await prisma.user.upsert({
where: { email: "alice@example.com" },
update: { name: "Updated Alice" },
create: { name: "Alice", email: "alice@example.com" },
});
6. Query Filters
Basic Filtering
const users = await prisma.user.findMany({
where: {
name: "Alice", // Exact match
},
});
Advanced Filtering
-
contains: Check if a string contains a value. -
startsWith: Check if a string starts with a value. -
endsWith: Check if a string ends with a value.
const users = await prisma.user.findMany({
where: {
email: { contains: "@example.com" },
},
});
Filtering with Conditions (AND, OR, NOT)
const users = await prisma.user.findMany({
where: {
AND: [
{ name: "Alice" },
{ email: { contains: "@example.com" } },
],
},
});
7. Relations
One-to-Many
Define a Post model related to User.
model User {
id Int @id @default(autoincrement())
name String
posts Post[] // A user has many posts
}
model Post {
id Int @id @default(autoincrement())
title String
userId Int
user User @relation(fields: [userId], references: [id]) // Post belongs to a user
}
Querying Relations
-
Include Related Data:
include -
Filter by Related Data:
where
Fetch a user and their posts:
const userWithPosts = await prisma.user.findUnique({
where: { id: 1 },
include: { posts: true }, // Include posts in the result
});
8. Transactions
Atomic Transactions
Execute multiple queries in a single transaction.
const [user, post] = await prisma.$transaction([
prisma.user.create({ data: { name: "Alice" } }),
prisma.post.create({ data: { title: "Hello World", userId: 1 } }),
]);
9. Pagination
Skip and Take
const users = await prisma.user.findMany({
skip: 10, // Skip the first 10 users
take: 5, // Fetch the next 5 users
});
10. Aggregations
Prisma allows you to perform aggregations such as counting, averaging, summing, etc.
Count
const userCount = await prisma.user.count();
Average
const avgAge = await prisma.user.aggregate({
_avg: {
age: true,
},
});
Group By
const postsByUser = await prisma.post.groupBy({
by: ['userId'],
_count: {
_all: true, // Count all posts per user
},
});
11. Soft Deletes (Optional)
You can implement soft deletes by adding a deletedAt field and filtering records accordingly.
model User {
id Int @id @default(autoincrement())
name String
deletedAt DateTime? // Optional timestamp for soft deletes
}
Querying Non-Deleted Records
const activeUsers = await prisma.user.findMany({
where: {
deletedAt: null, // Only fetch users that are not soft-deleted
},
});
12. Environment Variables (.env)
Prisma uses a .env file to manage environment variables, such as your database connection URL.
Example .env:
DATABASE_URL="postgresql://user:password@localhost:5432/mydb"
13. Seeding the Database
Seeding is helpful for adding initial or sample data to the database.
Seed Script
- Define the seed script in
package.json:
"prisma": {
"seed": "node prisma/seed.js"
}
- Implement the seed file:
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
async function main() {
await prisma.user.create({
data: {
name: "Alice",
email: "alice@example.com"
},
});
}
main();
- Run the seed:
npx prisma db seed
Key Commands Recap
| Command | Description |
|---|---|
npx prisma init |
Initialize Prisma |
npx prisma migrate dev |
Create and apply a migration |
npx prisma migrate reset |
Reset the database |
npx prisma generate |
Generate Prisma Client |
npx prisma db seed |
Run the seed script |
npx prisma studio |
Launch Prisma Studio (GUI for database) |
This cheat sheet covers the essential Prisma features you’ll need for most projects. Keep in mind that Prisma is highly versatile and works with relational databases, making it an excellent ORM for modern web development.
Top comments (0)