What is Prisma?
Prisma: Next-Generation ORM for Node.js and TypeScript
ORM: Object Relation Mapping is a way to communicate with databases using OOP languages, without the need to write complex queries.
- Prisma aims to make databases easy for developers.
- It provides type safety, an intuitive API, and boosts developer productivity.
- Focuses on the developer experience.
Key Components
- Prisma Schema: The single source of truth for your database schema. Defines models and their relations.
- Prisma Client: Auto-generated query builder tailored to your schema. Provides type-safe access to your database.
- Prisma Migrate: Tool for managing database schema migrations.
Why Prisma?
- Simplifying database interactions: Prisma ORM provides intuitive queries and schema migration.
- Enhancing code quality: Prisma ORM generates type-safe queries and integrates with popular IDEs.
- Supporting multiple databases: Prisma ORM makes it easier to adapt and grow applications. (e.g., MongoDB, MySQL, MariaDB, PostgreSQL, Microsoft SQL, etc.)
- Mitigating problems of traditional ORMs: Prisma ORM addresses issues like bloated model instances and unpredictable queries.
Project Setup
1. Project Setup
Create a new project directory
mkdir prisma-example
cd prisma-example
Initialize a Node.js project
npm init -y
Install Prisma CLI
npm install prisma dotenv express --save-dev
Install the Prisma Client
npm install @prisma/client --save-dev
This installs the Prisma Client for your database.
2. Initialize Prisma
npx prisma init
This creates a prisma
directory containing:
-
schema.prisma
: Your Prisma schema file. -
.env
: For your database connection string.
3. Configure the Database Connection
Choose a Database
The video likely uses PostgreSQL, but you can adapt this to MySQL, SQLite, or others. For this example, we'll use PostgreSQL.
Set the DATABASE_URL
In your .env
file, add the connection string. Example for PostgreSQL:
DATABASE_URL="postgresql://your_user:your_password@localhost:5432/your_database"
Replace your_user
, your_password
, and your_database
with your actual credentials. If you don't have a local PostgreSQL server, you'll need to install and configure one.
4. Define the Data Models (schema.prisma
)
Replace the contents of prisma/schema.prisma
with the following:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql" // Or "mysql", "sqlite", etc.
url = env("DATABASE_URL")
}
model Post {
id String @id @default(uuid())
title String
content String?
createdAt DateTime @default(now())
author User @relation(fields: [user_id], references: [id])
user_id Int
}
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
posts Post[]
}
5. Generate the Prisma Client
npx prisma generate
This generates the type-safe Prisma Client in node_modules/@prisma/client
.
6. Create a Migration
npx prisma migrate dev --name init
This creates a new migration file in the prisma/migrations
directory and applies it to your database, creating the tables. The --name init
gives the migration a descriptive name.
7. Creating a Separate File for Prisma Setup
Create a db.config.js
file in the root directory and add the following code:
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient({
log: ["query"],
});
export default prisma;
This will create a new instance of PrismaClient and export it so that it can be used in other files. The log: ['query']
option will log all queries to the console.
8. Creating a Basic Express API to Understand Prisma Working
1. Controller
Create a controllers.js
file in the root of the project. The following is the code for CRUD operations in the controllers.js
file:
import prisma from "./db.config.js";
// create user
export const createUser = async (req, res) => {
const { name, email } = req.body;
const existing_user = await prisma.user.findUnique({
where: {
email,
},
});
if (existing_user) {
return res.status(400).json({ message: "User already exists" });
}
const user = await prisma.user.create({
data: {
email,
name,
},
});
return res.status(201).json(user);
};
// create post
export const createPost = async (req, res) => {
const { title, content, user_id } = req.body;
const post = await prisma.post.create({
data: {
title,
content,
user_id,
},
});
return res.status(201).json(post);
};
// get all users
export const getUsers = async (req, res) => {
const users = await prisma.user.findMany({
include: {
posts: true,
},
});
return res.status(200).json(users);
};
// read specific user by id
export const getUserById = async (req, res) => {
const { id } = req.params;
const user = await prisma.user.findUnique({
where: {
id: parseInt(id),
},
include: {
posts: true,
},
});
if (!user) {
return res.status(404).json({ message: "User not found" });
}
return res.status(200).json(user);
};
// update user
export const updateUser = async (req, res) => {
const { id } = req.params;
const { name, email } = req.body;
const user = await prisma.user.update({
where: {
id: parseInt(id),
},
data: {
name,
email,
},
});
return res.status(200).json(user);
};
// delete user
export const deleteUser = async (req, res) => {
const { id } = req.params;
const user = await prisma.user.delete({
where: {
id: parseInt(id),
},
});
return res.status(200).json(user);
};
// create similar for post
2. Routes
Create a routes.js
file in the root of the project. The following is the code for CRUD operations in the routes.js
file:
import express from "express";
import {
createUser,
createPost,
getUsers,
getUserById,
updateUser,
deleteUser,
} from "./controllers.js";
const router = express.Router();
router.post("/users", createUser);
router.get("/users", getUsers);
router.get("/users/:id", getUserById);
router.put("/users/:id", updateUser);
router.delete("/users/:id", deleteUser);
// create similar for post
router.post("/posts", createPost);
// router.get('/posts', getPosts);
// router.get('/posts/:id', getPostById);
// router.put('/posts/:id', updatePost);
// router.delete('/posts/:id', deletePost);
export default router;
3. Server
Create a server.js
file in the root of the project. The following is the code for CRUD operations in the server.js
file:
import express from "express";
import dotenv from "dotenv";
import router from "./routes.js";
dotenv.config();
const app = express();
app.use(express.json());
app.use("/api", router);
const PORT = process.env.PORT || 5000;
app.listen(PORT, () => {
console.log(`Server running on port ${PORT}`);
});
9. Run the Script
node index.js
This will execute the script, creating a user and a post in your database, then updating the user and deleting the post.
You can use Postman to test the API with the following routes:
- POST:
http://localhost:5000/api/users
- GET:
http://localhost:5000/api/users
- GET:
http://localhost:5000/api/users/1
- PUT:
http://localhost:5000/api/users/1
- DELETE:
http://localhost:5000/api/users/1
Set the body in Postman for the POST request as JSON
with the following data:
{
"name": "John Doe",
"email": "sample@example.com"
}
Key Points to Consider for Prisma Syntax
-
findUnique
/findFirst
- to get a single record, does not return an array. -
findMany
- to get multiple records, returns an array. -
include
- to include the related data. -
select
- to select specific fields. - You can only use one of the attributes, either
select
orinclude
.
Examples of Prisma Syntax
// get all posts of a user with id
const user = await prisma.user.findUnique({
where: {
id: parseInt(id),
},
include: {
posts: true,
},
});
// select specific fields of user with post details
const user = await prisma.user.findUnique({
where: {
id: parseInt(id),
},
select: {
name: true,
posts: {
select: {
title: true,
content: true,
},
},
},
});
// get all users name with their posts count (Aggregation)
const req_data = await prisma.user.findMany({
select: {
id: true,
name: true,
_count: {
select: {
post: true,
},
},
},
});
More on Prisma Aggregations, Filtering, Ordering, and Relations
1. Aggregation Functions in Prisma
Prisma provides several aggregation functions that allow you to perform calculations on sets of data directly within your database queries. These functions are useful for summarizing data and gaining insights without having to fetch large amounts of data to your application.
Common Aggregation Functions
-
_count
: Counts the number of records that match the given criteria. -
_avg
: Calculates the average of a numeric field. -
_sum
: Calculates the sum of a numeric field. -
_min
: Finds the minimum value of a field. -
_max
: Finds the maximum value of a field.
Examples of Aggregation
Let's assume we have the following Product
model:
model Product {
id Int @id @default(autoincrement())
name String
price Float
category String
createdAt DateTime @default(now())
}
Counting the Number of Products in Each Category
const categoryCounts = await prisma.product.groupBy({
by: ["category"],
_count: {
id: true, // Count based on the 'id' field
},
});
console.log(categoryCounts);
// Example output:
// [
// { category: 'Electronics', _count: { id: 10 } },
// { category: 'Clothing', _count: { id: 5 } },
// ]
Calculating the Average Price of All Products
const averagePrice = await prisma.product.aggregate({
_avg: {
price: true,
},
});
console.log(averagePrice);
// Example output: { _avg: { price: 25.50 } }
Finding the Minimum and Maximum Price of Products in a Specific Category
const minMaxPrices = await prisma.product.aggregate({
where: {
category: "Electronics",
},
_min: {
price: true,
},
_max: {
price: true,
},
});
console.log(minMaxPrices);
// Example output: { _min: { price: 10.00 }, _max: { price: 50.00 } }
Sum of Prices for Products Created in the Last Week
const lastWeek = new Date();
lastWeek.setDate(lastWeek.getDate() - 7);
const sumOfPricesLastWeek = await prisma.product.aggregate({
where: {
createdAt: {
gte: lastWeek,
},
},
_sum: {
price: true,
},
});
console.log(sumOfPricesLastWeek);
2. Filtering in Prisma
Prisma offers a rich set of filtering options that allow you to precisely query your database. Here's a comprehensive overview with examples:
Basic Filtering
-
equals
(Default): Checks for exact equality.
const users = await prisma.user.findMany({ where: { email: "test@example.com", // Equivalent to: email: { equals: 'test@example.com' } }, });
-
not
: Negates a condition.
const users = await prisma.user.findMany({ where: { email: { not: "test@example.com" }, }, });
Comparison Operators
-
gt
(Greater Than),gte
(Greater Than or Equal To),lt
(Less Than),lte
(Less Than or Equal To): Used for numeric and date/time comparisons.
const posts = await prisma.post.findMany({ where: { id: { gt: 10 }, // Posts with ID greater than 10 }, }); const usersCreatedAfter = await prisma.user.findMany({ where: { createdAt: { gte: new Date("2024-01-01") }, }, });
String Filters
-
contains
: Checks if a string contains a substring.
const users = await prisma.user.findMany({ where: { name: { contains: "Test" }, // Users with names containing "Test" }, });
-
startsWith
: Checks if a string starts with a prefix.
const users = await prisma.user.findMany({ where: { name: { startsWith: "A" }, // Users with names starting with "A" }, });
-
endsWith
: Checks if a string ends with a suffix.
const users = await prisma.user.findMany({ where: { email: { endsWith: "@example.com" }, }, });
-
mode: insensitive
: Performs case-insensitive searches.
const users = await prisma.user.findMany({ where: { name: { contains: "test", mode: "insensitive" }, // Case-insensitive search }, });
List Filters
-
in
: Checks if a value is present in a list.
const users = await prisma.user.findMany({ where: { id: { in: [1, 2, 3] }, // Users with IDs 1, 2, or 3 }, });
-
notIn
: Checks if a value is not present in a list.
const users = await prisma.user.findMany({ where: { id: { notIn: [4, 5, 6] }, // Users with IDs not equal to 4, 5, or 6 }, });
Logical Operators
-
AND
: Combines multiple conditions with a logical AND.
const users = await prisma.user.findMany({ where: { AND: [ { name: { contains: "Test" } }, { email: { endsWith: "@example.com" } }, ], }, });
-
OR
: Combines multiple conditions with a logical OR.
const users = await prisma.user.findMany({ where: { OR: [{ name: { startsWith: "A" } }, { email: { contains: "gmail" } }], }, });
-
NOT
: Negates a group of conditions.
const users = await prisma.user.findMany({ where: { NOT: { email: { contains: "example" }, }, }, });
Nested Filters (Filtering on Relations)
You can filter based on related models.
const posts = await prisma.post.findMany({
where: {
author: {
email: { contains: "test" }, // Posts where the author's email contains "test"
},
},
});
const postsWithCategory = await prisma.post.findMany({
where: {
categories: {
some: {
// Use some for OR logic
name: "Simple Category",
},
},
},
include: {
categories: true,
author: true,
author: { include: { profile: true } },
},
});
Filtering on Optional Relations
const usersWithOrWithoutProfile = await prisma.user.findMany({
where: {
profile: {
is: null, // Find users without a profile
// isNot: null // Find users WITH a profile
},
},
Combining Filters
You can combine these operators for complex filtering logic.
const users = await prisma.user.findMany({
where: {
AND: [
{ name: { startsWith: "J" } },
{
OR: [
{ email: { contains: "gmail" } },
{ email: { contains: "yahoo" } },
],
},
],
},
});
These examples cover the most common filtering scenarios in Prisma. By combining these operators and nested filters, you can create very precise queries to retrieve the exact data you need. Remember to consult the official Prisma documentation for the most up-to-date and detailed information.
Order Records in Prisma
Prisma's orderBy
option allows you to sort the results of your queries. Here are some examples demonstrating its usage:
Basic Ordering
-
Ascending Order (Default):
```javascript const usersAsc = await prisma.user.findMany({ orderBy: { name: 'asc', // Sort users by name in ascending order (A-Z) }, }); ```
-
Descending Order:
```javascript const usersDesc = await prisma.user.findMany({ orderBy: { name: 'desc', // Sort users by name in descending order (Z-A) }, }); ```
Ordering by Multiple Fields
You can specify multiple fields to order by, with different directions for each. Prisma will first sort by the first field, then by the second field within groups of identical values in the first field, and so on.
const posts = await prisma.post.findMany({
orderBy: [
{
author: {
name: 'asc', // Sort by author's name first
},
},
{
title: 'desc', // Then sort by post title in descending order
},
],
});
In this example, posts will be primarily sorted by the author's name in ascending order. If multiple posts have the same author, they will then be sorted by their titles in descending order.
Ordering by Nested Fields (Relations)
As shown in the previous example, you can order by fields on related models.
const postsByAuthorEmail = await prisma.post.findMany({
orderBy: {
author: {
email: 'asc',
},
},
});
This will order posts based on the email address of their authors.
Ordering by Date/Time Fields
You can order by date and time fields as well.
const postsByCreatedAt = await prisma.post.findMany({
orderBy: {
createdAt: 'desc', // Sort posts by creation date in descending order (newest first)
},
});
Ordering by a Different Field than the Filtering Field
You can filter by one field and order by another. For example, you might want to find all users with a name containing "test" but order them by their email address:
const users = await prisma.user.findMany({
where:{
name:{
contains:"test"
}
},
orderBy:{
email:"asc"
}
})
Using nulls
Option
You can control how null
values are handled in the sorting. The nulls
option can be set to either first
or last
.
const usersWithNullsFirst = await prisma.user.findMany({
orderBy: {
name: {
sort: 'asc',
nulls: 'first', // Null values will appear first
},
},
});
const usersWithNullsLast = await prisma.user.findMany({
orderBy: {
name: {
sort: 'asc',
nulls: 'last', // Null values will appear last (default behavior)
},
},
});
By default, null
values are placed last when sorting in ascending order and first when sorting in descending order. The nulls
option allows you to override this default behavior.
Relation Functions in Prisma
Prisma excels at handling database relationships elegantly. Here are the key aspects:
Defining Relationships in the Schema: You define relationships between models directly in your
schema.prisma
file using the@relation
attribute.-
Types of Relationships: Prisma supports:
- One-to-One: One record in model A is related to exactly one record in model B.
- One-to-Many: One record in model A is related to multiple records in model B.
- Many-to-Many: Multiple records in model A are related to multiple records in model B (requires a join table).
Examples of Relations
Let's use the User
and Post
models from the previous examples:
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
profile Profile? @relation(fields: [profileId], references: [id]) // One-to-one (optional)
profileId Int? @unique
posts Post[] // One-to-many
}
model Profile {
id Int @id @default(autoincrement())
bio String?
user User @relation("UserToProfile", fields: [userId], references: [id]) // One-to-one
userId Int @unique
}
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id]) // One-to-many (Post to User)
authorId Int
categories Category[] @relation("PostToCategory") // Many-to-many
}
model Category {
id Int @id @default(autoincrement())
name String @unique
posts Post[] @relation("PostToCategory") // Many-to-many
}
Hope this helps you to understand the Prisma ORM in a better way.
Feel free to give your feedback and suggestions.
Thanks for reading! đ
Top comments (0)