Prisma ORM: Full Guide with Relations, Queries, Filters, and Schema Design

Prisma = modern ORM for Node.js/TypeScript.
Works with PostgreSQL, MySQL, SQLite, MongoDB, CockroachDB.

In the context of databases, ORM stands for Object-Relational Mapping. It's a programming technique that creates a "bridge" between object-oriented programming (OOP) languages and relational databases.

1. generator and datasource

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

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

• generator tells Prisma how to generate the client

• datasource tells Prisma which DB to use and how to connect

2. Schema Management: Migrate vs Push

3. Model Attributes

model User {
  id        Int      @id @default(autoincrement())
  uuid      String   @default(uuid()) @unique
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  role      Role     @default(USER)
}

4. Global Constraints & Indexing

model Product {
  sku       String
  warehouse String

  @@unique([sku, warehouse])
  @@index([warehouse])
}

5. Enums

enum Role {
  ADMIN
  USER
  GUEST
}

Used to limit a field to specific values.

6. Relationships

One-to-One

model User {
  id      Int     @id @default(autoincrement())
  profile Profile?
}

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

One-to-Many

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id     Int  @id @default(autoincrement())
  userId Int
  user   User @relation(fields: [userId], references: [id])
}

Many-to-Many (implicit)

model Student {
  id      Int     @id @default(autoincrement())
  classes Class[]
}

model Class {
  id       Int       @id @default(autoincrement())
  students Student[]
}

7. Create with Nested select and include

await prisma.user.create({
  data: {
    name: 'Alice',
    profile: {
      create: {
        bio: 'Developer',
      }
    }
  },
  include: {
    profile: true
  }
})

Use select to pick fields, include to include related models.

8. Read Queries

findUnique, findFirst, findMany

prisma.user.findUnique({ where: { id: 1 } })
prisma.user.findFirst({ where: { isAdmin: true } })
prisma.user.findMany({
  where: {
    name: {
      startsWith: "A",
      contains: "b",
      notIn: ["Abhi", "Dev"],
    },
    age: {
      gt: 18,
    },
  },
  orderBy: {
    createdAt: 'desc'
  },
  skip: 2,
  take: 5
})

Filtering

where: {
  name: { equals: "Alice", not: "Bob" },
  id: { in: [1, 2], notIn: [3] },
  email: { endsWith: "@gmail.com", contains: "test" },
}

9. Logical Operators

where: {
  AND: [
    { name: { startsWith: "A" } },
    { isAdmin: true }
  ],
  OR: [
    { email: { endsWith: "@gmail.com" } },
    { email: { endsWith: "@yahoo.com" } }
  ],
  NOT: {
    name: "Blocked"
  }
}

10. Filtering by Relations

prisma.user.findMany({
  where: {
    preferences: {
      emailUpdates: true
    }
  }
})

With quantifiers:

where: {
  posts: {
    some: { published: true },
    none: { archived: true },
    every: { authorId: 1 }
  }
}

Nested relations:

where: {
  author: {
    is: { role: 'ADMIN' }
  }
}

11. Update Operations

update & updateMany

await prisma.user.update({
  where: { id: 1 },
  data: { name: "New Name" }
})

await prisma.user.updateMany({
  where: { isAdmin: false },
  data: { isAdmin: true }
})

Arithmetic

await prisma.product.update({
  where: { id: 1 },
  data: {
    stock: { decrement: 1 },
    price: { multiply: 1.1 }
  }
})

12. Connect & Disconnect Relationships

await prisma.post.update({
  where: { id: 1 },
  data: {
    author: {
      connect: { id: 2 }
    }
  }
})

await prisma.post.update({
  where: { id: 1 },
  data: {
    author: {
      disconnect: true
    }
  }
})

13. Other Useful Features

@map

model User {
  fullName String @map("full_name")
}

Maps Prisma field to actual DB column.

@ignore

model User {
  tempField String @ignore
}

Tells Prisma to ignore this field (not generated in client).

Summary Table

Thanks to Web Dev Simplified- Prisma