Prisma

Prisma Architecture

┌──────────────┐
│ schema.prisma│
└──────┬───────┘
       │
       ▼
┌──────────────┐
│ Migration    │
│ Engine       │
└──────┬───────┘
       │
       ▼
┌──────────────┐
│ Database     │
└──────┬───────┘
       │
       ▼
┌──────────────┐
│ Prisma Client│
└──────────────┘

Prisma is:

1. Schema Language
2. Migration Engine
3. Query Engine
4. Type Generator

Prisma Setup

Initialize:

npx prisma init

Creates:

prisma/
 └─ schema.prisma
 
.env

schema.prisma

This is Prisma’s DSL (Domain Specific Language).

Example:

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String?
}

Think:

SQL + TypeScript
combined into one file

Datasource

Prisma must know where your database lives.

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

This tells Prisma:

Database Type
      +
Connection String

Example:

DATABASE_URL="postgresql://..."

Generator

Generator tells Prisma what code to generate.

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

After generation:

prisma.user.findMany()

becomes available.

Models

Model = Database Table

model User {
  id Int @id
}

becomes:

CREATE TABLE users (
  id INTEGER PRIMARY KEY
)

Under The Hood

When Prisma sees:

model User {
  id Int @id
}

It stores an internal representation similar to:

{
  "name": "User",
  "fields": [
    {
      "name": "id",
      "type": "Int",
      "primary": true
    }
  ]
}

Prisma then uses this metadata to:

1. Generate SQL
2. Generate TS types
3. Generate Query APIs

One schema → many outputs.

Model Fields

model User {
  id    Int
  name  String
  age   Int
}

Field = Column

id INTEGER
name TEXT
age INTEGER

Optional Fields

name String?

Question mark means:

NULL

Equivalent:

name TEXT NULL

Default Values

createdAt DateTime @default(now())

SQL:

DEFAULT CURRENT_TIMESTAMP

Migration Basics

After changing schema:

npx prisma migrate dev

What happens?

Step 1

Prisma reads schema.

model User {
  id Int @id
}

Step 2

Compares with previous schema.

Old Schema
vs
New Schema

This is called a diff.

Step 3

Generates SQL.

CREATE TABLE User (
  id INTEGER PRIMARY KEY
)

Step 4

Stores migration.

prisma/migrations/

Example:

202601010101_init/

containing SQL files.

Prisma migrations are basically:

Schema Diff
      →
SQL
      →
Version History

Prisma Client

Generate:

npx prisma generate

Creates:

const prisma = new PrismaClient()

Think of Prisma Client as:

Typed Query Builder

Instead of:

SELECT *
FROM users
WHERE age > 18

You write:

prisma.user.findMany({
  where: {
    age: {
      gt: 18
    }
  }
})

Under The Hood Of Client

You write:

prisma.user.findMany()

Prisma converts this internally to a query representation.

Something conceptually like:

{
  "model": "User",
  "action": "findMany"
}

Then Query Engine translates to:

SELECT * FROM users

Database returns rows.

Rows become JS objects.

Relationships

This is where Prisma becomes powerful.

One User

Many Posts

User
 ├─ Post
 ├─ Post
 └─ Post

SQL:

posts.user_id

Prisma:

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

Mental model:

posts Post[]

means

One user has many posts

user User

means

Each post belongs to one user

Prisma uses these declarations to generate:

FOREIGN KEY(userId)
REFERENCES User(id)

Attributes

Attributes begin with @

Example:

id Int @id

Meaning:

This field is primary key

Common ones:

@id

Primary key.

@unique

Unique index.

@default()

Default value.

@relation()

Relationship.

Enums

Instead of:

status = "ACTIVE"

or

status = "INACTIVE"

You define:

enum UserStatus {
  ACTIVE
  INACTIVE
}

Use:

status UserStatus

Generated TypeScript:

type UserStatus =
  | "ACTIVE"
  | "INACTIVE"

Create

await prisma.user.create({
  data: {
    email: "a@test.com"
  }
})

Generated SQL:

INSERT INTO users(email)
VALUES('a@test.com')

Conceptually.

Read

Single record:

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

SQL:

SELECT *
FROM users
WHERE id = 1
LIMIT 1

Many records:

await prisma.user.findMany()

Filtering

await prisma.user.findMany({
  where: {
    age: {
      gt: 18
    }
  }
})

SQL:

WHERE age > 18

Operators:

gt
gte
lt
lte
contains
startsWith
endsWith

These map directly to SQL operators.

Relationship Filtering

Example:

Users who have published posts.

await prisma.user.findMany({
  where: {
    posts: {
      some: {
        published: true
      }
    }
  }
})

Conceptually:

EXISTS(
 SELECT *
 FROM posts
 WHERE posts.user_id = users.id
)

Include

Load related data.

await prisma.user.findMany({
  include: {
    posts: true
  }
})

Returns:

[
  {
    id: 1,
    posts: [...]
  }
]

Prisma internally generates additional joins or optimized queries depending on the situation.

Update

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

SQL:

UPDATE users
SET name = 'Raja'
WHERE id = 1

Connect Existing Relationships

Suppose:

User exists.

Post exists.

Connect:

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

Internally:

UPDATE posts
SET user_id = 10
WHERE id = 1

Delete

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

SQL:

DELETE
FROM users
WHERE id = 1