Working with relational databases is a major bottleneck in application development. Debugging SQL queries or complex ORM objects often consume hours of development time
Prisma makes it easy for developers to reason about their database queries by providing a clean and type-safe API for submitting database queries which returns plain old JavaScript objects.
--> Start migration with this commpand
##F Few Codes ======Aggregate (avg,count,max,min,sum)======
const result = await prisma.Employee.aggregate({ _avg: { salary: true }, _count: { salary: true }, _max: { salary: true }, _min: { salary: true }, _sum: { salary: true }, });
======Aggregate (groupBy,having)======
const result = await prisma.Employee.groupBy({ by: ["city"], _count: { salary: true }, })
const result = await prisma.Employee.groupBy({ by: ["city"], _sum: { salary: true }, having: { city: "Chicago"}, })
===========Pagination================
// Using cursor const result = await prisma.Employee.findMany({ cursor: { id: 2 }, take: 3, })
// Using Limit Offset const result = await prisma.Employee.findMany({ skip: 2, take: 3, })
===========Query Time Enable Logging================
// Time Calculation const startTime = Date.now(); const result = await prisma.Employee.findMany() const executionTime = (Date.now() - startTime) + " milliseconds";
// query | info | warn | error const prisma = new PrismaClient({log: ['query', 'info', 'warn', 'error']}); const result = await prisma.Employee.findMany()
==========Raw queries=====================
const prisma = new PrismaClient();
const result = await prisma.$queryRaw SELECT * FROM employee
;
========Transactions & Rollback================
try {
const prisma = new PrismaClient();
const createUser = prisma.User.create({
data:{email:"[email protected]", password:"123"}
})
const deleteComment=prisma.Comment.delete({
where:{id:5}
})
const result=await prisma.$transaction([createUser, deleteComment])
console.log(result)
}
catch (e) {
console.log(e)
}
======================Comparison Operators======================
Comparison Operators:
- equals: Matches values that are equal to the specified value.
- not: Negates the condition, matching values that are not equal to the specified value.
- in: Matches values that are in a specified array of values.
- notIn: Matches values that are not in a specified array of values.
- lt (Less Than): Matches values that are less than the specified value.
- lte (Less Than or Equal To): Matches values that are less than or equal to the specified value.
- gt (Greater Than): Matches values that are greater than the specified value.
- gte (Greater Than or Equal To): Matches values that are greater than or equal to the specified value.
- contains: Matches values that contain a specified substring.
- startsWith: Matches values that start with a specified substring.
- endsWith: Matches values that end with a specified substring.
const result = await prisma.Employee.findMany({ where:{salary:{equals:75000}} })
const result = await prisma.Employee.findMany({ where:{salary:{lt:75000}} })
const result = await prisma.Employee.findMany({ where:{salary:{lte:75000}} })
const result = await prisma.Employee.findMany({ where:{salary:{gt:75000}} })
const result = await prisma.Employee.findMany({ where:{salary:{gte:75000}} })
const result = await prisma.Employee.findMany({ where:{salary:{not:75000}} })
const result = await prisma.Employee.findMany({ where:{salary:{in:[75000,60000]}} })
const result = await prisma.Employee.findMany({ where:{salary:{notIn:[75000,60000]}} })
const result = await prisma.Employee.findMany({ where:{name:{contains:'John'}} })
const result = await prisma.Employee.findMany({ where:{name:{startsWith:'J'}} })
const result = await prisma.Employee.findMany({ where:{name:{endsWith:'n'}} })
======================Logical Operators======================
- AND: Combines multiple conditions with logical AND.
- OR: Combines multiple conditions with logical OR.
- NOT: Negates a condition.
const result = await prisma.Employee.findMany({ where:{ AND: [ { name: {contains:"Alice"} }, { salary: { gt: 50000 } } ] } })
const result = await prisma.Employee.findMany({ where:{ OR: [ { name: {contains:"Alice"} }, { salary: { gt: 50000 } } ] } })
const result = await prisma.Employee.findMany({ where:{ NOT:[ { name: {contains:"Alice"} }, { salary: { gt: 90000 } } ] } })
======================Date Operators=============================
-
lt (Less Than): Matches dates that are before the specified date.
-
lte (Less Than or Equal To): Matches dates that are on or before the specified date.
-
gt (Greater Than): Matches dates that are after the specified date.
-
gte (Greater Than or Equal To): Matches dates that are on or after the specified date.
const result=await prisma.Employee.findMany({ where:{ birthDay:{ lt:new Date("2023-10-11"), gt:new Date("2017-10-11") } } });