Prisma ORM

Created	@November 7, 2022 3:48 PM
Type	Library
Language	Javascript
Last Edit	@June 28, 2025 5:51 PM

Setup

Installation

npm install prisma --save-dev

Invoke

npx prisma

Prisma Project Setup

npx prisma init

This command does two things:

Creates a new directory called prisma that contains a file called schema.prisma, which contains the Prisma schema with your database connection variable and schema models

Creates the .env file in the root directory of the project, which is used for defining environment variables (such as your database connection)

DB Config

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

Setup Env File

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA"

DB Schema

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

DB Schema With UUID

model user {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name      String
  email     String   @unique
  password  String
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now()) @updatedAt
  role      String   @default("reader")
}

DataTypes

DB Exists

If database with tables already exist, then those tables can be used to generate schema:

npx prisma db pull

Migrate

npx prisma migrate dev --name init

This command does two things:

It creates a new SQL migration file for this migration

It runs the SQL migration file against the database

generate is called under the hood by default, after running prisma migrate dev

Migrate to SQL Without Applying

Use the --create-only flag to generate a new migration without applying it:

npx prisma migrate dev --create-only

Prisma Client

npm install @prisma/client

CRUD

https://www.prisma.io/docs/concepts/components/prisma-client/crud

Sample Schema For Examples

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

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

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

model User {
  id           Int              @id @default(autoincrement())
  name         String?
  email        String           @unique
  profileViews Int              @default(0)
  role         Role             @default(USER)
  coinflips    Boolean[]
  posts        Post[]
  profile      ExtendedProfile?
}

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  published  Boolean    @default(true)
  author     User       @relation(fields: [authorId], references: [id])
  authorId   Int
  comments   Json?
  views      Int        @default(0)
  likes      Int        @default(0)
  categories Category[]
}

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

enum Role {
  USER
  ADMIN
}

Create

Create Single

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

Create Multiple

const createMany = await prisma.user.createMany({
  data: [
    { name: 'Bob', email: 'bob@prisma.io' },
    { name: 'Bobo', email: 'bob@prisma.io' }, // Duplicate unique key!
    { name: 'Yewande', email: 'yewande@prisma.io' },
    { name: 'Angelique', email: 'angelique@prisma.io' },
  ],
  skipDuplicates: true, // Skip 'Bobo'
})

Create Multiple With Many-to-Many Relations

courses_id.forEach(async (course_id) => {
      await prisma.coursesInPackages.create({
        data: {
          assigned_at: new Date(),
          courses: {
            connect: {
              id: course_id,
            },
          },
          packages: {
            connect: {
              id: created_package.id,
            },
          },
        },
      });
    });

Read

Read Single

// By unique identifier
const user = await prisma.user.findUnique({
  where: {
    email: 'elsa@prisma.io',
  },
})

// By ID
const user = await prisma.user.findUnique({
  where: {
    id: 99,
  },
})

If you are using the MongoDB connector and your underlying ID type is ObjectId, you can use the string representation of that ObjectId

// By ID
const user = await prisma.user.findUnique({
  where: {
    id: '60d5922d00581b8f0062e3a8',
  },
})

Read Multiple

const users = await prisma.user.findMany()

Read First Record

The following findFirstquery returns the most recently created user

const findUser = await prisma.user.findFirst({
    orderBy: {
      id: "desc"
    }
  })
}

Select Specific Fields

Use select to return a limited subset of fields instead of all fields. The following example returns the emailand name fields only:

// Returns an object or null
const getUser: object | null = await prisma.user.findUnique({
  where: {
    id: 22,
  },
  select: {
    email: true,
    name: true,
  },
})

https://www.prisma.io/docs/concepts/components/prisma-client/select-fields

Compound ID or Compound Unique Identifier

Advanced Filters

Update

Update Single

const updateUser = await prisma.user.update({
  where: {
    email: 'viola@prisma.io',
  },
  data: {
    name: 'Viola the Magnificent',
  },
})

Update Multiple

const updateUsers = await prisma.user.updateMany({
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
  data: {
    role: 'ADMIN',
  },
})

Update Or Create

The following query uses upsert to update a User record with a specific email address, or create that User record if it does not exist:

const upsertUser = await prisma.user.upsert({
  where: {
    email: 'viola@prisma.io',
  },
  update: {
    name: 'Viola the Magnificent',
  },
  create: {
    email: 'viola@prisma.io',
    name: 'Viola the Magnificent',
  },
})

Update Number Field

const updatePosts = await prisma.post.updateMany({
  data: {
    views: {
      increment: 1,
    },
    likes: {
      increment: 1,
    },
  },
})

Operators	Description
`increment`	Adds `n` to the current value.
`decrement`	Subtacts `n` from the current value.
`multiply`	Multiplies the current value by `n`.
`divide`	Divides the current value by `n`.
`set`	Sets the current field value. Identical to `{ myField : n }`.

JSON Fields

Delete

Delete Single

const deleteUser = await prisma.user.delete({
  where: {
    email: 'bert@prisma.io',
  },
})

Delete Multiple

const deleteUsers = await prisma.user.deleteMany({
  where: {
    email: {
      contains: 'prisma.io',
    },
  },
})

Delete All

const deleteUsers = await prisma.user.deleteMany({})

⚠️

This query will fail if the user has any related records (such as posts). In this case, you need to delete the related records first