MS Prisma Guide - PROCEED-Labs/proceed GitHub Wiki

Prisma Guide

Table of Contents

  1. Introduction
  2. Installation
  3. Essential Prisma Commands
  4. Prisma Schema
  5. Data Modeling
  6. Prisma Migrations
  7. Prisma Client

Introduction

Prisma is a next-generation ORM (Object-Relational Mapping) that simplifies database workflows for application developers. It consists of three main tools:

  • Prisma Client: Auto-generated and type-safe query builder for Node.js & TypeScript
  • Prisma Migrate: Declarative data modeling & migration system
  • Prisma Studio: GUI to view and edit data in your database

Installation

Install Prisma and Prism Client: Prisma Client is an auto-generated database client that's tailored to your database schema

yarn add prisma --dev
yarn add @prisma/client

Essential Prisma Commands

  1. Initialize Prisma in your project:

    npx prisma init

    This creates a prisma directory with a schema.prisma file and a .env file.

  2. Generate Prisma Client after schema changes:

    npx prisma generate

    This updates the Prisma Client API based on your schema.

  3. Create and apply a migration:

    npx prisma migrate dev --name init

    This creates a new migration based on your schema changes and applies it to your database.

  4. Open Prisma Studio (GUI to view and edit data):

    npx prisma studio

Prisma Schema

The Prisma schema file (schema.prisma) is the main configuration file for your Prisma setup. It contains three main parts:

  1. Data source: Specifies your database connection
  2. Generator: Indicates that you want to generate Prisma Client
  3. Data model: Defines your application models

Example:

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

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

Data Modeling

In your schema.prisma file, you can define various types of relations between models.

Foreign Key Relations

Foreign key relations in Prisma are defined using the @relation attribute.

model User {
  id            String        @id @default(uuid())
  firstName     String?
  lastName      String?
  email         String?       @unique
  systemAdmin   SystemAdmin?  @relation("systemAdmin")
}

model SystemAdmin {
  id            String @id @default(uuid())
  role          String @default("admin")
  user          User @relation(name: "systemAdmin", fields: [userId], references: [id])
  userId        String @unique
}

Explanation:

  • The @relation attribute establishes the foreign key relation between User and SystemAdmin.
  • fields: [userId] specifies that userId in SystemAdmin holds the foreign key.
  • references: [id] indicates that userId references the id field in User.
  • The @unique constraint on userId ensures a one-to-one relationship.
  • The name: "systemAdmin" parameter gives a name to this specific relation, which is useful when you have multiple relations between the same models.

One-to-One Relations

A one-to-one relation means that one record in a model is associated with exactly one record in another model.

model User {
  id            String        @id @default(uuid())
  systemAdmin   SystemAdmin?  @relation("systemAdmin")
}

model SystemAdmin {
  id            String @id @default(uuid())
  user          User @relation(name: "systemAdmin", fields: [userId], references: [id])
  userId        String @unique
}

Explanation:

  • Each User can have at most one SystemAdmin profile.
  • Each SystemAdmin belongs to exactly one User.
  • The @unique constraint on userId in the SystemAdmin model ensures the one-to-one relationship.
  • The ? after SystemAdmin in the User model makes it optional, allowing users without admin privileges.

One-to-Many Relations

In a one-to-many relation, one record in a model can be associated with multiple records in another model.

model User {
  id         String    @id @default(uuid())
  processes  Process[]
}

model Process {
  id        String   @id @default(uuid())
  name      String
  owner     User?    @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  ownerId   String
}

Explanation:

  • The User model has a processes field of type Process[], indicating that a user can have multiple processes.
  • The Process model has an owner field and an ownerId field for the foreign key.
  • The @relation in Process establishes the many-to-one relationship with User.
  • The onDelete: Cascade option means that if a User is deleted, all associated Process records will also be deleted.

Many-to-Many Relations

A many-to-many relation allows multiple records in one model to be associated with multiple records in another model.

model User {
  id       String       @id @default(uuid())
  memberIn Membership[]
}

model Space {
  id      String       @id @default(uuid())
  name    String?
  members Membership[]
}

model Membership {
  id            String   @id @default(uuid())
  userId        String
  environmentId String
  user          User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  space         Space    @relation(fields: [environmentId], references: [id], onDelete: Cascade)
}

Explanation:

  • This is an example of a many-to-many relation implemented with an explicit join model (Membership).
  • A User can be a member of multiple Spaces, and a Space can have multiple User members.
  • The Membership model serves as a join table, connecting User and Space.
  • Both User and Space have a field referencing an array of Membership.
  • The Membership model has foreign keys to both User and Space.
  • The onDelete: Cascade option ensures that when a User or Space is deleted, the corresponding Membership records are also deleted.

Self-Relations

Self-relations are used when a model needs to reference itself, typically to represent hierarchical data structures.

model Folder {
  id             String    @id @default(uuid())
  name           String
  description    String
  parentFolder   Folder?   @relation("parent-child", fields: [parentId], references: [id], onDelete: Cascade)
  parentId       String?
  childrenFolder Folder[]  @relation("parent-child")
  space          Space     @relation(fields: [environmentId], references: [id], onDelete: Cascade)
  environmentId  String
  processes      Process[] 
}

Explanation:

  • This Folder model has a self-relation to represent a folder hierarchy.
  • The parentFolder field references another Folder, representing the parent folder.
  • parentId is the foreign key that stores the ID of the parent folder.
  • The childrenFolder field is an array of Folder, representing subfolders.
  • Both parentFolder and childrenFolder use the same relation name "folderChildren", creating a two-way relationship.
  • parentId is optional (String?), allowing for top-level folders that don't have a parent.
  • The onDelete: Cascade option means that when a parent folder is deleted, all its subfolders are also deleted.

Migrations

Prisma Migrate helps you manage database schemas: Once you have created or made any changes to the schema, apply it to the db

  1. Create a migration:

    npx prisma migrate dev --name init

  2. Apply migrations to your database:

    npx prisma migrate deploy

Prisma Client

Prisma Client is an auto-generated, type-safe query builder.

  1. Generate Prisma Client:

    npx prisma generate

  2. Use Prisma Client in your code:

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
// use `prisma` in your application to read and write data in your DB

CRUD Operations

Here are some basic CRUD operations using Prisma Client:

// Create
const newUser = await prisma.user.create({
  data: {
    email: '[email protected]',
    name: 'Alice',
  },
})

// Read
const user = await prisma.user.findUnique({
  where: {
    email: '[email protected]',
  },
  select: {
    name:true
  }
})

// Update
const updatedUser = await prisma.user.update({
  where: {
    email: '[email protected]',
  },
  data: {
    name: 'Alicia',
  },
})

// Delete
const deletedUser = await prisma.user.delete({
  where: {
    email: '[email protected]',
  },
})

For more detailed information, always refer to the official Prisma documentation.