Relationships: explicit many to many

use them if you need add other props to relation as timestamp or quantity, or if you need more flexible queries

model Post {
	id Int	@id @default(autoincrement())
	title	String
	tags PostTag[]
}

model Tag {
	id Int	@id @default(autoincrement())
	posts PostTag[]
}

model PostTag {
	postId	Int
	post	Post	@relation(fields: [postId], references: [id])

	tagId	Int
	post	Tag	@relation(fields: [tagId], references: [id])

	@@id([postId, tagId])
}

prisma.post.findMany({
	include: {
		tags: true
	}
})

// { id: 1, title: 'Post1', tags: [{ id: 2 }] }

Welcome to Prisma ORM

Simple and powerful object relational mapper

Press Space for next page

Installation

Install prisma and configure commands

pnpm install prisam -D
pnpm install @prisma/client # if need prisma client
pnpm dlx prisma init --datasource-provider sqlite

add in package.json scripts

{
  "prisma:pull": "prisma db pull",
  "prisma:migrate": "prisma migrate dev",
  "prisma:generate": "prisma generate",
  "prisma:studio": "prisma studio"
}

Data Model

data model represent database tables as entities, here is prisma data model options

Interseption: every database table converted to entity, so data models created depend on database, database is the single source of truth, with npx prisma db pull, or pnpm prisma:pull
Migration: define data model manually then convert them to database tables through migration, npx prisma migrate dev --name init, or pnpm prisma:migrate -- --name init

Simpel Modeling Entities

every entity represent table

every model field consist of at least

field name
field type, ex: Int, Float, String, Blob, Json, Unsupported(""), other model like Article, or enum (in next slide)

types can appended by ? which mean optional, or [] for arrays

model User {
id Int @id @default(autoincrement()) // @id mean primary_key
email String @unique
name String?
articles Article[]
}

model Article {
id Int @id @default(autoincrement())
titleString
body String?
author User @relation(fields: [authorId], refrences: [id]) // used to connect article to user
authorId Int // register which user rigesterd to
}

Enums

enums represented as simple tables

model User {
	name String
	role	Role	@default(BAISC)
}

enum Role {
	BAISC
	EDITOR
	ADMIN
}

Field Attributes

apply constrains or functions on fields

Field Level Attributes

@id: make field primary key
@unique: make field unique
@map(''): change field name in database
@updatedAt: assign currrent timestamp when update row
@default() which use functions like autoincrement() for incremental numbers or uuid() for random uuid which is better for security or now() which enter current timestamp

Block Level Attributes

model User {
	name	String	@id @default(uuid())

	// change table name in database
	@@map("users")

  	// rows with same two fields values should be unique
  	@@unique([age, name])
	
  	// create index on field/s
  	@@index([email])
  
  	// remove above primary key (id), and use id from these two fields
  	@@id([name, age]) 
}

Client Init

npx prisma generate genertae client (run all generators in general), also client is generated by default when migrate db

import { PrismaClient } from '@prisma/client'

// we can specify which operations to log
const prisma = new PrismaClient({ log: ["query", info] })

async function main() {
	// prisma queries go here
}

main()
	.then(async () => {
		await prisma.$disconnect()
	})
	.catch(async (e) => {
		console.error(e)
		await prisam.$disconnect()
		process.exit(1)
	})

CRUD

prisma client CRUD cheatsheet

// Create user
const user = await prisma.user.create({
	data: {
		name: 'John Doe',
		email: 'john@gmail.com'
	}
})

// Create article and associate it with user
const article = await prisma.article.create({
	data: {
		title: 'John First Article',
		body: 'This is john first article',
		author: { 
			connect: { 
				id: 1 
			} 
		}
	}
})

// Create user and article and associate them
const user = await prisma.user.create({
	data: {
		name: 'Sara Smith',
		email: 'sara@gmail.com',
		articles: { 
			create: { 
				title: 'Saras first article', 
				body: 'This is saras first article' 
			} 
		}
	}
})

// create many
const user = await prisma.user.create({
	data: [{}, {}]
})

// Get all users only
const users = await prisma.user.findMany()

// Get all users with articles
const users = await prisma.user.findMany({
	include: {
		articles: true
	}
})

// Update data
const user = await prisma.user.update({
	where: { id: 1 },
	data: {
		name: 'John Doe Jr'
	}
})

// Delete data
const article = await prisma.article.delete({
	where: { id: 2 }
})

Prisma Tools ^[1]

enhance DX with local and online tools

Prisma Studio

npx prisma studio run server for prisma dashbaord UI

Prisma Schema Editors

for more ↩︎

Relationships

relations details with prisma client

Relationships: one to one

user have one profile and profile have one user

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

model Profile {
	name	String
	gender	String
	age	String
	
	// it should be @unique coz we want one Profile only reference one User
	userId	Int	@unique 
	user User	@relation(fields: [userId], refrences: [id])
}

Relationships: one to one

one-to-one relation with multi fields

model User {
	firstName	String
	lastName	String

	profile	Profile?
	
	@@id([firstName, lastName])
}

model Profile {
	name	String
	gender	String
	age	String
	
	userFirstName	String
	userLastName	String
	user User	@relation(fields: [userFirstName, userLastName], refrences: [firstName, lastName])

	@@unique([userFirstName, userLastName])
}

Relationships: one to one

client implementation

// 1. create user then profile
const user = await prisma.user.create({ data: {} })

const profile = await prisma.profile.create({
	data: {
		name: faker.name.firstName,
		userId: user.id
	}
})

// 2. create a user with a profile
const user = await prisma.user.create({
	data: {
		profiel: {
			create: {
				name: faker.name.firstName(),
			}
		}	
	},
	
	// we can also add include here, its not only for queries
})

// get user data only
prisma.user.findUnique({
	where: { id: user.id },
})
// { id: 1, email: 'test@gmail.com' }

// get user data included profile all props
prisma.user.findUnique({
	where: { id: user.id },
	include: { profile: true }
})
// { id: 1, email: 'test@gmail.com', profile: { name: 'Sabin', age: 10, gender: 'male' } }

// get only spesfic props in profile
prisma.user.findUnique({
	where: { id: user.id },
	include: {
		profile: {
			select: { name: true }
		}
	}
})

Relationships: one to many

user can have many posts, but post have one author

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

model Post {
	title	String
	
	// it can be @unique, this mean every post should have id of a user
	userId	Int
	user User	@relation(fields: [userId], refrences: [id])
}

// create post
const post = await prisma.post.create({
	data: {
		user: {
			connect: {
				id: user.id
			}
		}
	}
})

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

// { id: 1, email: 'test@gmail.com', posts: [{ name: 'Post1' }] }

Relationships: one to many

if there is one-to-maney relation to same table more than one time, we should give these relations names

model User {
	id	String	@id @default(uuid())
	name	String
	email	String
	isAdmin	Boolean
	preferences	Json
	writtenPosts	Post[]	@relation("WrittenPosts")
	favoritePosts	Post[]	@relation("FavoritePosts")
}

model Post {
	id	String	@id @default(@uuid())
	rating	Float
	createdAt	DataTime	@default(now())
	updatedAt	DateTime	@updatedAt
	
	author	User @relation("WrittenPosts", fields: [authorId], references: [id])
	authorId	String

	favoritedBy	User?	@relation("FavoritePosts", fields: [favoritedById], references: [id])
	favoritedById	String?
}

Relationships: implicit many to many

many diffrent posts associated with any diffrent tags, normally need intermidiary table, prisam in this case handle this intermiary table for us (create and name it under the hood)

model Post {
	id Int	@id @default(autoincrement())
	title	String
	tags Tag[]
}

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

prisma.post.findMany({
	include: {
		tags: true
	}
})

// { id: 1, title: 'Post1', tags: [{ id: 2 }] }

Relationships: explicit many to many

use them if you need add other props to relation as timestamp or quantity, or if you need more flexible queries

model Post {
	id Int	@id @default(autoincrement())
	title	String
	tags PostTag[]
}

model Tag {
	id Int	@id @default(autoincrement())
	posts PostTag[]
}

model PostTag {
	postId	Int
	post	Post	@relation(fields: [postId], references: [id])

	tagId	Int
	post	Tag	@relation(fields: [tagId], references: [id])

	@@id([postId, tagId])
}

prisma.post.findMany({
	include: {
		tags: true
	}
})

// { id: 1, title: 'Post1', tags: [{ id: 2 }] }

Relationships: onUpdate and onDelete relations

relations update and delete behavior

No Action (Default)

Behavior: The default behavior is to take no action on update or delete. This means that if a referenced record is updated or deleted, no automatic action is taken on the referencing records.
Example: If a user is updated or deleted, any posts referencing that user remain unchanged

Cascade

Behavior: When the referenced record is updated or deleted, the changes are cascaded to the referencing records. This often involves updating or deleting the referencing records automatically.
Example: If updating a user’s ID cascades to update the corresponding user ID in all associated posts.

Set Null

Behavior: When the referenced record is updated or deleted, the foreign key in the referencing records is set to null.
Example: If a user is deleted, set the user ID in all associated posts to null.

CRUD Details

CRUD operations on prisma client

Create

insert data with relations

// create 
const user = await prisma.user.create({
	data: {
		name: 'abbas',
		email: 'abbas@gmail.com'
	},
	userPrefrences: { // create other row with this
		create: {
			receiveEmails: true,
			categories: { // if not exist to  connect create it
				connectOrCreate: {
					where: { id: 3 },
					create: { name: "Big Data" }
				}
			}
		}
	},
	profile: { // connect created row with this
		connect: {}
	}
})

const users = await prisma.user.create({
	data: {},
	// returned result include only what in data, to include others
	include: {
		userPrefrences: true
	},
	// another option is select which enable us to exclude also main fields of row, we should use include or select not both
	select: {
		name: true,
		userPrefrences: true,
		another_relation: { 
			spesficFields: true
		}
	}
})

Read

query methods and filtring

const users = await prisma.user.findMany()

// fields not necessary be unique
const user = await prisma.user.findFirst()
const user = await prisma.user.findFirstOrThrow() // if not found throw error

// find one depend on unique attribute, should use { where }
const user = await prisma.user.findUnique({
	where: {
		email: 'kitkat@gmail.com',
		// we can use age_name in this case coz we did a unique constain on both of them
		age_name: {
			age: 27,
			name: 'kyle'
		}
	},
})
const user = await prisma.user.findUniqueOrThrow() // if not found throw error

// filter used with any query function 
const users = prisma.user.findMany({
	where: {
		name: 'kyle'
	},
	distinct: ["name", "age"], // return unique rows depend on these columns 
	orderBy: {
		age: "desc"
	},

	// offset pagination
	take: 2, // how many users we should take
	skip: 1, // how many users to skip

	// cursor pagination
	take: 2,
	cursor: { id: '234589' },
	// also we can use include or select here
})

Read: Query Filtring

advanced filters

// filter operation
{
	where: {
		name: 'abbas',
		name: { equals: 'abbas' },
		name: { not: 'abbas' },
		name: { in: ['abbas'. 'ali'] },
		name: { notIn: ['abbas'. 'ali'] },
		name: { contains: '@test.com' },
		name: { startWith: '@test.com' },
		name: { endWith: '@test.com' },
		age: { lt: 20 },
		age: { gt: 20 },
		age: { lte: 20 },
		age: { gte: 20 },
	}
}

// condation filtring
{
	where: {
		AND: [
			{ name: { startWith: '@test.com' } },
			{ name: { endWith: '@test.com' } }
		],
		OR: [
			{ name: { startWith: '@test.com' } },
			{ age: { gt: 20 } }
		],
		NOT: { name: { startWith: '@test.com' } },
	}
}

Read: Relation Filtring

filter depend on fields of relation

// One-to-Many
// Many-to-many
const users = await prisma.user.findMany({
	where: {
		// find users where userPrefs.reciveEmails equal true
		userPrefs: {
			reciveEmails: true
		}
		// or like this which mean, find useres with every userPrefs match 
		userPrefs: {
			// every, some, none
			every: {
				// AND, OR, NOT apply here
				title: 'call prefs'
			}
		}
	}
})

// Many-to-One
// One-to-One
const posts = await prisma.post.findMany({
	where: {
		// find posts which thier authors age is 27
		author: {
			// is, isNot
			is: { age: 27 }
		}
	}
})

Read: Aggregate and GroupBy

const aggregations = await prisma.post.aggregate({
	_sum: { 
		likeNum: true 
	},
	_avg: { 
		likeNum: true 
	},
	_count: { 
		id: true 
	},
	_max: { 
		likeNum: true 
	},
	_min: { 
		likeNum: true 
	},
})

const groupPosts = await prisma.post.groupBy({
	by: ["authorId"],
	_sum: { 
		likeNum: true 
	},
	_avg: { 
		likeNum: true 
	},
	_count: { 
		id: true 
	},
	_max: { 
		likeNum: true 
	},
	_min: { 
		likeNum: true 
	},
})

Update

there is update and updateMany, both accept ({ where: {}, data: {} }), also there is an intersting methods to update like upsert:

const user = await prisma.user.update({
	where: { name: 'rush' },
	data: { 
		// increment, decrement, multiply, divide
		age: { increment: 1 },
		// create or connect or disconnect relations when update
		userPrefs: {
			create: { emailUpdates: true }
		},
		userPrefs: {
			connect: { id: 114 }
		},
		userPrefs: {
			disconnect: true
		}
	},
	//  does'nt accept `select` and `include`
})

const user = await prisma.user.upsert({
	where: { name: 'rush' },
	update: {
		age: { increment: 1 },
		userPrefs: {
			create: { emailUpdates: true }
		},
		userPrefs: {
			connect: { id: 114 }
		},
		userPrefs: {
			disconnect: true
		}
	},
	create: {
		name: "rush",
		email: "rush@wusaby.com",
		age: 20,
	}
})

Delete

work as find, we have delete and deleteMany

// Delete a single user
const user = await prisma.user.delete({
  where: { name: 'rush' },
})

// Delete multiple users
const users = await prisma.user.deleteMany({
  where: { age: { lt: 18 } },
})

Client-Level Methods

provide extended functionalities

Transaction

execute all-or-nothing

// operation 1
const withDrawUpdate = prisma.post.update(/* ... */)

// operation 2
const depositeUpdate = prisma.post.update(/* ... */)

// execution: if one fail no one executed
const result = await prisma.$transaction([withDrawUpdate, depositeUpdate])

Relationships: explicit many to many

Relationships: onUpdate and onDelete relations

No Action (Default)

Cascade

Set Null

Welcome to Prisma ORM

Table of contents

Installation

Data Model

Simpel Modeling Entities

Enums

Field Attributes

Field Level Attributes

Block Level Attributes

Client Init

CRUD

Prisma Tools [1]

Prisma Studio

Prisma Schema Editors

Relationships

Relationships: one to one

Relationships: one to one

Relationships: one to one

Relationships: one to many

Relationships: one to many

Relationships: implicit many to many

Relationships: explicit many to many

Relationships: onUpdate and onDelete relations

No Action (Default)

Cascade

Set Null

CRUD Details

Create

Read

Read: Query Filtring

Read: Relation Filtring

Read: Aggregate and GroupBy

Update

Delete

Client-Level Methods

Transaction

Prisma Tools ^[1]