logo
eng-flag

Prisma Cheatsheet

Table of Contents

  1. Installation
  2. Schema Definition
  3. Data Types
  4. Model Relations
  5. Model Relations Example
  6. Prisma Client
  7. CRUD Operations
  8. Filtering and Sorting
  9. Transactions
  10. Migrations
  11. Seeding

Installation

Install Prisma CLI:

npm install prisma --save-dev

Initialize Prisma in your project:

npx prisma init

Schema Definition

Define your data model in schema.prisma:

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

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

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

Data Types

  • String: Text values
  • Int: Integer numbers
  • Float: Floating-point numbers
  • Boolean: True/false values
  • DateTime: Date and time values
  • Json: JSON data
  • Bytes: Binary data

Model Relations

  • One-to-One: @relation with @unique field
  • One-to-Many: @relation on the "many" side
  • Many-to-Many: Implicit relation table or explicit through model

Example of Many-to-Many relation:

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]
}

Model Relations Example in Prisma

Let's explore a real-world scenario to demonstrate various types of relations in Prisma. We'll model a simple e-commerce system with users, orders, products, and categories.

Scenario Overview

We'll have the following entities:

  • Users who can place orders
  • Orders that contain multiple products
  • Products that belong to categories
  • Categories that can have subcategories

Schema Definition

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  orders    Order[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Order {
  id           Int           @id @default(autoincrement())
  user         User          @relation(fields: [userId], references: [id])
  userId       Int
  orderItems   OrderItem[]
  totalAmount  Decimal
  status       OrderStatus
  createdAt    DateTime      @default(now())
  updatedAt    DateTime      @updatedAt
}
//fields: [userId] indicates that the userId field in the Order model is used to establish this relation.
//references: [id] indicates that the userId field refers to the id field in the User model.

model OrderItem {
  id        Int      @id @default(autoincrement())
  order     Order    @relation(fields: [orderId], references: [id])
  orderId   Int
  product   Product  @relation(fields: [productId], references: [id])
  productId Int
  quantity  Int
  price     Decimal
}

model Product {
  id          Int         @id @default(autoincrement())
  name        String
  description String?
  price       Decimal
  stock       Int
  category    Category    @relation(fields: [categoryId], references: [id])
  categoryId  Int
  orderItems  OrderItem[]
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt
}

model Category {
  id            Int        @id @default(autoincrement())
  name          String
  products      Product[]
  parentCategory Category?  @relation("SubCategories", fields: [parentId], references: [id])
  parentId       Int?
  subcategories  Category[] @relation("SubCategories")
}
//Category? means this field is optional, as not all categories will necessarily have a parent.
//@relation("SubCategories") specifies the name of the relation. In this case, it is "SubCategories".
//fields: [parentId] indicates that the parentId field in this model is used to establish this relationship.
//references: [id] indicates that the parentId field refers to the id field in the same Category model.
//subcategories Category[] @relation("SubCategories"): This is the reverse side of the self-referential relationship, defining the subcategories of a category.
//Category[] indicates that this field holds an array of Category objects, representing all the subcategories under a given category.
//@relation("SubCategories") specifies the name of the relation, which must match the name used in the parentCategory field.

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

Relation Types Explained

  1. One-to-Many Relation:

    • User to Order: A user can have multiple orders, but each order belongs to only one user.
    • Order to OrderItem: An order can have multiple order items, but each order item belongs to only one order.
    • Category to Product: A category can have multiple products, but each product belongs to only one category.

  2. Many-to-Many Relation:

    • Product to Order: A product can be in multiple orders, and an order can have multiple products. This is implemented through the OrderItem model, which serves as a junction table.

  3. Self-Relation:

    • Category to Category: A category can have multiple subcategories, and each subcategory has one parent category. This is implemented using a self-relation.

  4. One-to-One Relation:

    • While not explicitly shown in this schema, a one-to-one relation could be implemented, for example, if we wanted to add a UserProfile model:
    model UserProfile {
  id     Int    @id @default(autoincrement())
  bio    String?
  user   User   @relation(fields: [userId], references: [id])
  userId Int    @unique
}

model User {
  id      Int          @id @default(autoincrement())
  email   String       @unique
  name    String
  profile UserProfile?
  // ... other fields
}

Using Relations in Queries

Here are some example queries using these relations:

  1. Get a user with their orders:
const userWithOrders = await prisma.user.findUnique({
  where: { id: 1 },
  include: { orders: true }
})
  1. Get an order with its items and products:
const orderWithItems = await prisma.order.findUnique({
  where: { id: 1 },
  include: {
    orderItems: {
      include: { product: true }
    }
  }
})
  1. Get a category with its subcategories and products:
const categoryWithSubcategories = await prisma.category.findUnique({
  where: { id: 1 },
  include: {
    subcategories: true,
    products: true
  }
})

Prisma Client

Generate Prisma Client:

npx prisma generate

Use Prisma Client in your code:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

CRUD Operations

Create

const newUser = await prisma.user.create({
  data: {
    email: 'john@example.com',
    name: 'John Doe',
    posts: {
      create: {
        title: 'My first post',
        content: 'This is the content of my first post',
      },
    },
  },
  include: {
    posts: true,
  },
})

Read

// Find a single user
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true },
})

// Find multiple users
const users = await prisma.user.findMany({
  where: { email: { contains: 'example.com' } },
  take: 10,
  skip: 5,
})

Update

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

Delete

const deletedUser = await prisma.user.delete({
  where: { id: 1 },
})

Filtering and Sorting

Filtering

const filteredPosts = await prisma.post.findMany({
  where: {
    OR: [
      { title: { contains: 'prisma' } },
      { content: { contains: 'database' } },
    ],
    AND: {
      published: true,
    },
  },
})

Sorting

const sortedUsers = await prisma.user.findMany({
  orderBy: {
    name: 'asc',
  },
})

Transactions

Use transactions to perform multiple operations atomically:

const [newUser, newPost] = await prisma.$transaction([
  prisma.user.create({
    data: { name: 'Alice', email: 'alice@example.com' },
  }),
  prisma.post.create({
    data: { title: 'My post', authorId: 1 },
  }),
])

Migrations

Create a migration:

npx prisma migrate dev --name init

Apply migrations to production:

npx prisma migrate deploy

Seeding

Create a seed file (prisma/seed.ts):

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const alice = await prisma.user.upsert({
    where: { email: 'alice@example.com' },
    update: {},
    create: {
      email: 'alice@example.com',
      name: 'Alice',
      posts: {
        create: {
          title: 'Check out Prisma with Next.js',
          content: 'https://www.prisma.io/nextjs',
          published: true,
        },
      },
    },
  })

  console.log({ alice })
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Run the seed script:

npx prisma db seed

Remember to add this script to your package.json:

{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

2024 © All rights reserved - buraxta.com