prisma（Prisma ORM）

Prisma ORM

Schema-first, type-safe database toolkit. Auto-generated client from schema.prisma.

Quick Start

Install

npm install prisma --save-dev
npm install @prisma/client
npx prisma init

Config

// prisma.config.ts
import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "./prisma/schema.prisma",
  migrations: { path: "prisma/migrations" },
  datasource: { url: env("DATABASE_URL") },
});

Schema

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
}

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

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

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int
}

Generate & Query

npx prisma migrate dev --name init
# or for prototyping: npx prisma db push

import { PrismaClient } from "./generated/prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL! });
const prisma = new PrismaClient({ adapter });

// create
const user = await prisma.user.create({
  data: { email: "alice@prisma.io", name: "Alice" },
});

// read
const users = await prisma.user.findMany({
  where: { email: { endsWith: "@prisma.io" } },
});

// update
await prisma.user.update({
  where: { email: "alice@prisma.io" },
  data: { name: "Alice Updated" },
});

// delete
await prisma.user.delete({ where: { email: "alice@prisma.io" } });

See references/connections.md for driver adapters (PostgreSQL, MySQL, SQL Server, edge runtimes) and singleton patterns.

Schema

Models map to database tables. Fields map to columns.

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?            // optional (nullable)
  tags      String[]           // list (PostgreSQL/CockroachDB)
  role      Role     @default(USER)
}

enum Role {
  USER
  ADMIN
}

Scalar Types

Key Attributes

@id                          // primary key
@default(autoincrement())    // auto-increment
@default(now())              // current timestamp
@default(uuid())             // UUID v4
@default(cuid())             // CUID
@default(dbgenerated("...")) // native DB function
@unique                      // unique constraint
@updatedAt                   // auto-update timestamp
@map("column_name")          // custom column name
@db.VarChar(200)             // native type mapping
@relation(fields: [...], references: [...])

@@id([fieldA, fieldB])       // composite primary key
@@unique([fieldA, fieldB])   // composite unique
@@index([fieldA, fieldB])    // composite index
@@map("table_name")          // custom table name

Full schema reference: references/schema.md

Relations

One-to-One

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

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

One-to-Many

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

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

Many-to-Many (Implicit)

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

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

Prisma manages the join table automatically. For extra fields on the relation, use explicit m-n with a join model.

Referential Actions

model Post {
  author   User @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId Int
}

Actions: Cascade, Restrict, NoAction, SetNull, SetDefault.

Full relations reference: references/relations.md

CRUD Operations

Read

// findUnique — by unique field
const user = await prisma.user.findUnique({ where: { email: "a@b.io" } });

// findFirst — first match
const user = await prisma.user.findFirst({
  where: { posts: { some: { likes: { gt: 100 } } } },
});

// findMany — all matching
const users = await prisma.user.findMany({
  where: { email: { endsWith: "@prisma.io" } },
  orderBy: { name: "asc" },
  skip: 10,
  take: 20,
});

Write

// create
const user = await prisma.user.create({
  data: { email: "elsa@prisma.io", name: "Elsa" },
});

// createMany
await prisma.user.createMany({
  data: [{ email: "a@b.io" }, { email: "b@b.io" }],
  skipDuplicates: true,
});

// update
await prisma.user.update({
  where: { email: "viola@prisma.io" },
  data: { name: "Viola the Magnificent" },
});

// upsert
await prisma.user.upsert({
  where: { email: "viola@prisma.io" },
  update: { name: "Viola" },
  create: { email: "viola@prisma.io", name: "Viola" },
});

// delete
await prisma.user.delete({ where: { email: "bert@prisma.io" } });

Select / Include / Omit

// select — return only specified fields
const user = await prisma.user.findFirst({
  select: { email: true, name: true },
});

// include — return all fields + relations
const user = await prisma.user.findFirst({
  include: { posts: true },
});

// omit — exclude specific fields
const user = await prisma.user.findFirst({ omit: { password: true } });

Filtering

where: {
  email: { contains: "prisma", mode: "insensitive" },
  age: { gte: 18 },
  id: { in: [1, 2, 3] },
  OR: [{ name: { startsWith: "A" } }, { role: "ADMIN" }],
  posts: { some: { published: true } },  // relation filter
}

Nested Writes

// create with nested child
await prisma.user.create({
  data: {
    email: "alice@prisma.io",
    posts: { create: [{ title: "Hello" }, { title: "World" }] },
  },
});

// connect to existing record
await prisma.post.create({
  data: {
    title: "New Post",
    author: { connect: { id: 1 } },
  },
});

Full queries reference: references/queries.md

Aggregation

const result = await prisma.user.aggregate({
  _avg: { age: true },
  _count: { _all: true },
  where: { role: "ADMIN" },
});

const groups = await prisma.user.groupBy({
  by: ["country"],
  _count: { country: true },
  having: { profileViews: { _avg: { gt: 100 } } },
});

Transactions

Sequential (array)

const [posts, count] = await prisma.$transaction([
  prisma.post.findMany({ where: { title: { contains: "prisma" } } }),
  prisma.post.count(),
]);

Interactive

const result = await prisma.$transaction(async (tx) => {
  const sender = await tx.account.update({
    data: { balance: { decrement: 100 } },
    where: { email: "alice@prisma.io" },
  });
  if (sender.balance < 0) throw new Error("Insufficient funds");
  return tx.account.update({
    data: { balance: { increment: 100 } },
    where: { email: "bob@prisma.io" },
  });
});

Raw SQL

// queryRaw — returns records (tagged template for SQL injection safety)
const users = await prisma.$queryRaw`SELECT * FROM "User" WHERE email = ${email}`;

// executeRaw — returns affected row count
const count = await prisma.$executeRaw`UPDATE "User" SET active = true WHERE "emailValidated" = true`;

TypedSQL: write .sql files in prisma/sql/, generate with prisma generate --sql, get fully type-safe query functions.

Full raw SQL reference: references/raw-sql.md

Prisma Migrate

Full migrations reference: references/migrations.md

Client Extensions

Extend Prisma Client with custom model methods, query hooks, computed fields, and client-level methods via $extends:

const prisma = new PrismaClient({ adapter }).$extends({
  model: {
    user: {
      async signUp(email: string) {
        return prisma.user.create({ data: { email } });
      },
    },
  },
  result: {
    user: {
      fullName: {
        needs: { firstName: true, lastName: true },
        compute(user) {
          return `${user.firstName} ${user.lastName}`;
        },
      },
    },
  },
});

Four component types: model, client, query, result.

Full extensions reference: references/client-extensions.md

Type Safety

import { Prisma } from "./generated/prisma/client";

// Derive return type for a query shape
type UserWithPosts = Prisma.UserGetPayload<{ include: { posts: true } }>;

// Input types
const data: Prisma.UserCreateInput = { email: "alice@prisma.io" };

// Type-safe reusable fragments
const withPosts = { include: { posts: true } } satisfies Prisma.UserDefaultArgs;
type UserWithPosts2 = Prisma.UserGetPayload<typeof withPosts>;

Full type safety reference: references/type-safety.md

Error Handling

import { Prisma } from "./generated/prisma/client";

try {
  await prisma.user.create({ data: { email: "existing@mail.com" } });
} catch (e) {
  if (e instanceof Prisma.PrismaClientKnownRequestError) {
    if (e.code === "P2002") console.log("Unique constraint violated");
    if (e.code === "P2025") console.log("Record not found");
  }
}

Reference Index