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 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?

1. Simplifying database interactions: Prisma ORM provides intuitive queries and schema migration
2. Enhancing code quality: Prisma ORM generates type-safe queries and integrates with popular IDEs
3. Supporting multiple databases: Prisma ORM makes it easier to adapt and grow applications. (Ex. MongoDB, MySQL, MariaDb, PostgreSQL, MicroSoft SQL, etc.)
4. 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 separate file for prisma setup so that it can be used every where in project

• Create a db.config.js file in 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 Basic Express API to understand prisma Working:
1. Controller:
_ Create a controllers.js file in the root of the project
_ Following is a code for CRUD operations in 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
_ Following is a code for CRUD operations in 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
_ Following is a code for CRUD operations in 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.

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 post request as JOSN with the following data:

{
  "name": "John Doe",
  "email": "sample@example.com"
}

Key Points to consider for Prisma Syntax:

• findUnique/ findFirst - to get single record does not return array
• findMany - to get multiple records returns array
• include - to include the related data
• select - to select the specific fields
• we can only use one of the attributes, either select or include

** 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,
      },
    },
  },
});

10. More on Prisma Aggregations,Filtering 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.

Here's a breakdown of the 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())
}

Here are some examples of using aggregation functions:

1. 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 } },
   // ]
   

2. 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 } }
   

3. 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 } }
   

4. 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.

3. 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!