Prisma ORM: Start to End With Project using JS

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?

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. (e.g., 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:

mkdir prisma-example
cd prisma-example

5. Generate the Prisma Client

npm init -y

This generates the type-safe Prisma Client in node_modules/@prisma/client.

6. Create a Migration

npm install prisma dotenv express --save-dev

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:

npm install @prisma/client --save-dev

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:

npx prisma init

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:

DATABASE_URL="postgresql://your_user:your_password@localhost:5432/your_database"

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:

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

9. Run the Script

npx prisma generate

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:

npx prisma migrate dev --name init

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 or include.

Examples of Prisma Syntax

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
    log: ["query"],
});

export default prisma;

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:

mkdir prisma-example
cd prisma-example

Counting the Number of Products in Each Category

npm init -y

Calculating the Average Price of All Products

npm install prisma dotenv express --save-dev

Finding the Minimum and Maximum Price of Products in a Specific Category

npm install @prisma/client --save-dev

Sum of Prices for Products Created in the Last Week

npx prisma init

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.
  

  DATABASE_URL="postgresql://your_user:your_password@localhost:5432/your_database"
  

• not: Negates a condition.
  

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

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.
  

  npx prisma generate
  

String Filters

• contains: Checks if a string contains a substring.
  

  npx prisma migrate dev --name init
  

• startsWith: Checks if a string starts with a prefix.
  

  import { PrismaClient } from "@prisma/client";
  
  const prisma = new PrismaClient({
      log: ["query"],
  });
  
  export default prisma;
  

• endsWith: Checks if a string ends with a suffix.
  

  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
  

• mode: insensitive: Performs case-insensitive searches.
  

  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;
  

List Filters

• in: Checks if a value is present in a list.
  

  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}`);
  });
  

• notIn: Checks if a value is not present in a list.
  

  node index.js
  

Logical Operators

• AND: Combines multiple conditions with a logical AND.
  

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

• OR: Combines multiple conditions with a logical OR.
  

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

• NOT: Negates a group of conditions.
  

  model Product {
      id        Int      @id @default(autoincrement())
      name      String
      price     Float
      category  String
      createdAt DateTime @default(now())
  }
  

Nested Filters (Filtering on Relations)

You can filter based on related models.

mkdir prisma-example
cd prisma-example

Filtering on Optional Relations

npm init -y

Combining Filters

You can combine these operators for complex filtering logic.

npm install prisma dotenv express --save-dev

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):
  

  npm install @prisma/client --save-dev
  

• Descending Order:
  

  npx prisma init
  

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.

DATABASE_URL="postgresql://your_user:your_password@localhost:5432/your_database"

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.

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

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.

npx prisma generate

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:

npx prisma migrate dev --name init

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.

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
    log: ["query"],
});

export default prisma;

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:

mkdir prisma-example
cd prisma-example

Hope this helps you to understand the Prisma ORM in a better way.
Feel free to give your feedback and suggestions.

Thanks for reading! ?

The above is the detailed content of Prisma ORM: Start to End With Project using JS. For more information, please follow other related articles on the PHP Chinese website!