A Beginner’s Guide to Prisma ORM

Welcome to this comprehensive guide on Prisma ORM! If you’re new to backend development or struggling with traditional ways of interacting with databases, you’ve come to the right place. Prisma offers a modern, type-safe, and intuitive approach to database management, making it an excellent choice for both beginners and experienced developers.

This document is structured as a textbook, guiding you from the absolute basics of Prisma to more advanced topics, complete with practical examples, exercises, and guided projects. By the end, you’ll have a solid understanding of Prisma and the skills to apply it effectively in your own applications.

1. Introduction to Prisma

What is Prisma?

Prisma is a next-generation open-source Object-Relational Mapper (ORM) designed for Node.js and TypeScript applications. In simpler terms, it’s a tool that helps your application “talk” to your database without you having to write complex SQL queries directly.

Imagine your database as a vast library of information, and your application as a reader who wants to find specific books (data), add new ones, or update existing ones. Traditionally, you’d need to learn a special “library language” (SQL) to communicate with the librarian. Prisma acts as a universal translator. You tell Prisma what you want in your application’s language (JavaScript/TypeScript), and Prisma handles the complex translation to SQL for the database.

Prisma consists of three main tools:

• Prisma Client: An auto-generated, type-safe query builder for your database. It allows you to interact with your database using familiar JavaScript/TypeScript syntax.
• Prisma Migrate: A migration system that helps you evolve your database schema over time in a controlled and predictable manner.
• Prisma Studio: A visual database browser that allows you to view and edit your data directly in your web browser.

Why Learn Prisma?

Learning Prisma offers numerous benefits, making it a popular choice in modern web development:

• Type-Safety: Prisma automatically generates TypeScript types based on your database schema. This means you get autocompletion in your code editor, and many errors are caught during development (compile-time) rather than at runtime. This leads to more robust and bug-free applications.
• Developer Experience (DX): Prisma is designed with developers in mind. Its intuitive API, clear error messages, and seamless integration with tools like VS Code (with syntax highlighting and autocompletion) significantly enhance productivity.
• Simplicity and Readability: Instead of writing raw SQL or dealing with complex query builders, Prisma’s declarative schema and generated client provide a clean and expressive syntax for database operations.
• Automatic Migrations: Evolving your database schema can be a headache. Prisma Migrate simplifies this process, generating SQL migrations based on changes in your schema.prisma file, ensuring your database stays in sync with your application’s data model.
• Database Agnostic (to an extent): Prisma supports a wide range of popular relational databases (PostgreSQL, MySQL, SQLite, SQL Server) and also has experimental support for NoSQL databases like MongoDB. This flexibility allows you to choose the right database for your project without learning a new ORM.
• Scalability and Performance: Features like Prisma Accelerate (a managed connection pooler and global caching layer) help applications handle increased traffic and reduce database load, especially in serverless and edge environments.
• Industry Relevance: Prisma is widely adopted by individual developers, startups, and even large enterprises. Its growing community and ecosystem mean you’ll find ample resources, examples, and support.

Use Cases:

Prisma is an excellent choice for:

• Building REST APIs and GraphQL servers.
• Developing full-stack applications with frameworks like Next.js, Express, or NestJS.
• Creating backend services that require efficient and reliable database interactions.
• Any project where you value type-safety, a great developer experience, and simplified database management.

A Brief History

Prisma started as a GraphQL API layer (Prisma 1) before evolving into the powerful ORM it is today. Its focus shifted to providing a robust data access layer for any Node.js/TypeScript application. This evolution led to the Prisma ORM (often referred to as Prisma 2 and later versions), which prioritizes type-safety, a declarative schema, and intuitive database operations. Recent developments include enhancements to Prisma Postgres, the Query Compiler for a Rust-free ORM experience, and increased AI integration through the Model Context Protocol (MCP).

Setting Up Your Development Environment

To get started with Prisma, you’ll need Node.js and a package manager (npm or Yarn). We’ll also set up a basic project and connect it to a SQLite database, which is perfect for local development as it doesn’t require a separate database server.

Prerequisites:

1. Node.js: Ensure you have the latest Long-Term Support (LTS) version of Node.js installed. You can download it from nodejs.org.
2. Package Manager: npm (Node Package Manager) is included with Node.js. Yarn is another popular alternative. We’ll use npm in this guide.
3. Code Editor: A good code editor like Visual Studio Code (VS Code) is highly recommended for its excellent TypeScript support and Prisma extensions.

Step-by-Step Setup:

1. Create a New Project Directory: Open your terminal or command prompt and create a new folder for your project. Then navigate into it:

   mkdir my-prisma-app
   cd my-prisma-app
   

2. Initialize a Node.js Project: Initialize a new Node.js project. This will create a package.json file.

   npm init -y
   

   The -y flag answers “yes” to all prompts, creating a default package.json.

3. Install Prisma CLI and Prisma Client: Install Prisma as a development dependency and the Prisma Client as a regular dependency.

   npm install prisma @prisma/client
   

   • prisma: This is the Prisma Command Line Interface (CLI) tool, used for running Prisma commands like init, migrate, and generate.
   • @prisma/client: This is the auto-generated Prisma Client that you’ll use in your application code to interact with your database.

4. Initialize Prisma in Your Project: This command sets up Prisma in your project, creating the essential prisma/schema.prisma file and a .env file. We’ll specify SQLite as our database provider.

   npx prisma init --datasource-provider sqlite
   

   This command performs the following actions:

   • Creates a prisma folder in your project root.
   • Inside the prisma folder, it creates schema.prisma. This is where you define your database schema (your data models).
   • Creates a .env file in your project root. This file is used to store environment variables, such as your database connection URL.
   • The schema.prisma file will be pre-configured to use SQLite, and your .env file will contain DATABASE_URL="file:./dev.db", pointing to a local SQLite database file named dev.db.

   Your project structure should now look something like this:

   my-prisma-app/
   ├── node_modules/
   ├── prisma/
   │   └── schema.prisma
   ├── .env
   ├── package.json
   └── package-lock.json
   

5. Explore the schema.prisma file: Open prisma/schema.prisma. You’ll see two main blocks: datasource and generator.

   // prisma/schema.prisma
   datasource db {
     provider = "sqlite"
     url      = env("DATABASE_URL")
   }
   
   generator client {
     provider = "prisma-client-js"
   }
   

   • datasource db: Defines your database connection.
     • provider = "sqlite": Specifies that you’re using SQLite.
     • url = env("DATABASE_URL"): Tells Prisma to get the database connection string from the DATABASE_URL environment variable defined in your .env file.
   • generator client: Configures how the Prisma Client is generated.
     • provider = "prisma-client-js": Specifies that you want to generate a JavaScript/TypeScript client.

You’ve successfully set up your development environment for Prisma! In the next section, we’ll dive into defining your first data models and interacting with your database.

2. Core Concepts and Fundamentals

In this section, we’ll break down the fundamental building blocks of Prisma. You’ll learn how to define your data models, create a database based on your schema, and perform basic database operations (Create, Read, Update, Delete) using the Prisma Client.

2.1 Understanding the Prisma Schema

The Prisma Schema (defined in prisma/schema.prisma) is the heart of your Prisma project. It’s a human-readable, declarative way to define your application’s data models and their relationships. Prisma uses this schema to generate the Prisma Client and to manage your database migrations.

Let’s define our first model, a Post for a simple blog application.

Detailed Explanation:

Each model in your schema.prisma file directly maps to a table in your database. Inside a model, you define fields, which correspond to columns in your database table.

// prisma/schema.prisma

// ... (existing datasource and generator blocks) ...

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Let’s break down each field and its annotations (starting with @):

• id Int @id @default(autoincrement()):
  • id: The name of the field.
  • Int: The data type (integer).
  • @id: Marks this field as the primary key for the Post model. Every model should have a unique identifier.
  • @default(autoincrement()): Sets a default value for this field. autoincrement() means the database will automatically assign a unique, incrementing integer whenever a new Post record is created.
• title String:
  • title: Field name.
  • String: Data type (text). This field is required by default.
• content String?:
  • content: Field name.
  • String?: The ? makes this field optional. If you omit ?, the field is required.
• published Boolean @default(false):
  • published: Field name.
  • Boolean: Data type (true/false).
  • @default(false): Sets a default value of false for new Post records.
• createdAt DateTime @default(now()):
  • createdAt: Field name.
  • DateTime: Data type for dates and times.
  • @default(now()): Automatically sets the current timestamp when a new record is created.
• updatedAt DateTime @updatedAt:
  • updatedAt: Field name.
  • DateTime: Data type.
  • @updatedAt: This special annotation automatically updates the timestamp to the current time whenever the record is updated.

Code Examples:

1. Initial schema.prisma with Post model:

   // prisma/schema.prisma
   datasource db {
     provider = "sqlite"
     url      = env("DATABASE_URL")
   }
   
   generator client {
     provider = "prisma-client-js"
   }
   
   model Post {
     id        Int      @id @default(autoincrement())
     title     String
     content   String?
     published Boolean  @default(false)
     createdAt DateTime @default(now())
     updatedAt DateTime @updatedAt
   }
   

Exercises/Mini-Challenges:

1. Add a User model:

   • Create a new model User in your schema.prisma file.
   • It should have an id (integer, primary key, auto-incrementing).
   • It should have a name (string, required).
   • It should have an email (string, required, and unique - meaning no two users can have the same email). Use the @unique annotation for this.
   • Save your schema.prisma file.

2. Verify your changes: After saving, Prisma’s VS Code extension (if installed) should highlight any syntax errors. If you don’t have the extension, you can run npx prisma format to check for formatting and basic syntax errors.

2.2 Creating the Database and Running Migrations

After defining your schema, you need to apply these changes to your actual database. This is where Prisma Migrate comes in.

Detailed Explanation:

Prisma Migrate handles the process of creating and evolving your database schema. When you make changes to your schema.prisma file, prisma migrate dev detects these changes, generates a SQL migration file (a set of instructions to modify your database), and then applies that migration to your database. It also updates the Prisma Client, ensuring your application’s code reflects the latest schema.

Key commands:

• npx prisma migrate dev --name <migration-name>: This is the primary command you’ll use in development.

  • It compares your schema.prisma to the actual database state.
  • It generates a new migration file (e.g., 20250813123456_my_migration_name/migration.sql) in prisma/migrations.
  • It applies the SQL migration to your database.
  • It regenerates the Prisma Client.
  • The --name flag is important for giving your migration a descriptive name (e.g., init_database, add_users_and_posts).

• npx prisma generate: This command only regenerates the Prisma Client based on your current schema.prisma. You usually run this after modifying schema.prisma if you’re not also running a migration, or if prisma migrate dev fails to generate the client for some reason.

Code Examples:

1. Run your first migration (after adding Post and User models):

   npx prisma migrate dev --name initial_schema_with_users_and_posts
   

   You might be asked if you want to reset your database if it detects unapplied migrations or a schema drift. For a new project, you can proceed.

   After successful execution, you’ll see messages indicating that a migration has been generated and applied, and the Prisma Client has been updated. A migrations folder will appear inside prisma/.

Exercises/Mini-Challenges:

1. Inspect the generated migration file:

   • Open the newly created file in prisma/migrations/<timestamp>_<name>/migration.sql.
   • Can you see the SQL commands that create the User and Post tables?
   • Notice how Prisma handles primary keys, unique constraints, and default values in SQL.

2. Add another field and run a new migration:

   • In your User model, add an optional age field of type Int?.
   • Save schema.prisma.
   • Run npx prisma migrate dev --name add_age_to_user.
   • Observe the new migration file. Does it contain ALTER TABLE SQL?

2.3 Performing CRUD Operations with Prisma Client

Now that your database schema is defined and applied, you can start interacting with your data using the Prisma Client. CRUD stands for Create, Read, Update, and Delete – the four basic operations for persistent data.

Detailed Explanation:

The Prisma Client is a lightweight, auto-generated library that provides an intuitive and type-safe API for performing database operations. When you run npx prisma generate (or npx prisma migrate dev), Prisma inspects your schema.prisma file and generates a node_modules/@prisma/client folder containing the client code tailored to your models.

To use it in your Node.js or TypeScript application:

1. Instantiate Prisma Client: You create an instance of PrismaClient. It’s best practice to create a single instance and reuse it throughout your application to manage database connections efficiently.

   import { PrismaClient } from '@prisma/client';
   const prisma = new PrismaClient();
   

   Self-correction for Next.js/Hot-reloading: In environments with hot-reloading (like Next.js development server), directly instantiating new PrismaClient() can lead to multiple client instances being created, exhausting database connections. A common pattern is to use a global variable to ensure a single instance.

   // lib/prisma.ts (or wherever you initialize your PrismaClient)
   import { PrismaClient } from '@prisma/client';
   
   const globalForPrisma = global as unknown as {
     prisma: PrismaClient | undefined;
   };
   
   export const prisma =
     globalForPrisma.prisma ??
     new PrismaClient({
       log: ['query', 'info', 'warn', 'error'], // Optional: Log database queries
     });
   
   if (process.env.NODE_ENV !== 'production') {
     globalForPrisma.prisma = prisma;
   }
   

2. Perform Operations: The prisma instance exposes methods for each of your models (e.g., prisma.user, prisma.post). Each model then has methods for CRUD operations:

   • create(): Insert a new record.
   • findMany(): Retrieve multiple records.
   • findUnique(): Retrieve a single record by a unique identifier.
   • findFirst(): Retrieve the first record that matches a criteria.
   • update(): Modify an existing record.
   • delete(): Remove a record.

Code Examples:

Create a new file, for example, script.ts (if you’re using TypeScript) or script.js (if JavaScript), at the root of your project to test these operations.

If using TypeScript: First, install TypeScript and ts-node for easier execution:

npm install typescript ts-node @types/node --save-dev
npx tsc --init # Initializes tsconfig.json

Now, create script.ts:

// script.ts
import { PrismaClient } from '@prisma/client';

// Recommended single instance pattern for development (see above explanation)
const globalForPrisma = global as unknown as {
  prisma: PrismaClient | undefined;
};

const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: ['query', 'info', 'warn', 'error'],
  });

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}


async function main() {
  // 1. CREATE: Create a new user and a post
  console.log('--- Creating a new User and Post ---');
  const newUser = await prisma.user.create({
    data: {
      name: 'Alice Smith',
      email: 'alice@example.com',
      age: 30,
    },
  });
  console.log('Created User:', newUser);

  const newPost = await prisma.post.create({
    data: {
      title: 'My First Prisma Post',
      content: 'Learning Prisma is fun!',
      published: true,
    },
  });
  console.log('Created Post:', newPost);

  // 2. READ: Find all users
  console.log('\n--- Finding all Users ---');
  const allUsers = await prisma.user.findMany();
  console.log('All Users:', allUsers);

  // 2. READ: Find a unique user by email
  console.log('\n--- Finding a unique User by email ---');
  const alice = await prisma.user.findUnique({
    where: {
      email: 'alice@example.com',
    },
  });
  console.log('Found Alice:', alice);

  // 2. READ: Find posts that are published and contain "Prisma" in title or content
  console.log('\n--- Finding published posts containing "Prisma" ---');
  const prismaPosts = await prisma.post.findMany({
    where: {
      published: true,
      OR: [
        { title: { contains: 'Prisma' } },
        { content: { contains: 'Prisma' } },
      ],
    },
  });
  console.log('Prisma related posts:', prismaPosts);

  // 3. UPDATE: Update a user's age
  console.log('\n--- Updating a User ---');
  const updatedUser = await prisma.user.update({
    where: {
      email: 'alice@example.com',
    },
    data: {
      age: 31,
    },
  });
  console.log('Updated Alice:', updatedUser);

  // 4. DELETE: Delete a post
  console.log('\n--- Deleting a Post ---');
  const deletedPost = await prisma.post.delete({
    where: {
      id: newPost.id, // Use the ID of the post we created earlier
    },
  });
  console.log('Deleted Post:', deletedPost);

}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect(); // Disconnect Prisma Client when done
    console.log('\n--- Prisma Client disconnected ---');
  });

To run this script:

npx ts-node script.ts

If using JavaScript: Create script.js:

// script.js
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
  // 1. CREATE: Create a new user and a post
  console.log('--- Creating a new User and Post ---');
  const newUser = await prisma.user.create({
    data: {
      name: 'Bob Johnson',
      email: 'bob@example.com',
      age: 25,
    },
  });
  console.log('Created User:', newUser);

  const newPost = await prisma.post.create({
    data: {
      title: 'Another Prisma Article',
      content: 'This is my second post about Prisma.',
      published: false,
    },
  });
  console.log('Created Post:', newPost);

  // 2. READ: Find all posts
  console.log('\n--- Finding all Posts ---');
  const allPosts = await prisma.post.findMany();
  console.log('All Posts:', allPosts);

  // 2. READ: Find a unique user by ID
  console.log('\n--- Finding a unique User by ID ---');
  const bob = await prisma.user.findUnique({
    where: {
      id: newUser.id, // Use the ID of the user we created earlier
    },
  });
  console.log('Found Bob:', bob);

  // 3. UPDATE: Update a post to be published
  console.log('\n--- Updating a Post ---');
  const updatedPost = await prisma.post.update({
    where: {
      id: newPost.id,
    },
    data: {
      published: true,
    },
  });
  console.log('Updated Post:', updatedPost);

  // 4. DELETE: Delete a user (and associated posts if relationship configured for cascade delete, otherwise might error)
  console.log('\n--- Deleting a User ---');
  // For simplicity, let's delete the user created at the start.
  // Note: Without proper relationship handling (covered in intermediate topics),
  // deleting a user might fail if there are related posts that still reference them.
  // For now, if you get an error, skip this delete or manually delete related posts first.
  const deletedUser = await prisma.user.delete({
    where: {
      id: newUser.id,
    },
  });
  console.log('Deleted User:', deletedUser);
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
    console.log('\n--- Prisma Client disconnected ---');
  });

To run this script:

node script.js

Exercises/Mini-Challenges:

1. Retrieve specific fields: Modify a findMany query to only return the title and published status of posts. (Hint: Use select).
2. Filter by multiple conditions: Find all users named “Alice” who are older than 25.
3. Use upsert: Research Prisma’s upsert method. Write a script that tries to update a user by email, but creates the user if that email doesn’t exist.

3. Intermediate Topics

Building on the fundamentals, this section will delve into more advanced aspects of Prisma, including relationships between models, filtering and pagination, and using Prisma Studio for visual data management.

3.1 Working with Relationships

Real-world applications rarely have isolated tables. Data models are usually interconnected through relationships. Prisma makes it straightforward to define and query these relationships.

Detailed Explanation:

Prisma supports common relational database patterns:

• One-to-Many (1-N): One record in a model relates to multiple records in another model (e.g., one User can have many Posts).
• One-to-One (1-1): One record in a model relates to exactly one record in another model (e.g., one User might have one Profile).
• Many-to-Many (M-N): Multiple records in one model relate to multiple records in another model (e.g., a Post can have many Tags, and a Tag can be associated with many Posts).

Prisma uses the @relation attribute to define these connections in your schema.prisma. For one-to-many relationships, you typically define a foreign key field on the “many” side.

Let’s establish a one-to-many relationship between User and Post (a User can write many Posts).

Code Examples:

1. Update schema.prisma for a One-to-Many Relationship:

   First, open prisma/schema.prisma. We’ll add a posts field to the User model and userId and user fields to the Post model.

   // prisma/schema.prisma
   
   // ... existing datasource and generator ...
   
   model User {
     id        Int      @id @default(autoincrement())
     email     String   @unique
     name      String
     age       Int?
     posts     Post[]   // A User can have many Posts
   }
   
   model Post {
     id        Int      @id @default(autoincrement())
     title     String
     content   String?
     published Boolean  @default(false)
     createdAt DateTime @default(now())
     updatedAt DateTime @updatedAt
     authorId  Int      // Foreign key to User
     author    User     @relation(fields: [authorId], references: [id]) // Relation field
   }
   

   • posts Post[] on User: This defines a list of Post records associated with a User. The [] indicates a list (many).
   • authorId Int: This is the foreign key in the Post model that holds the id of the associated User.
   • author User @relation(fields: [authorId], references: [id]): This defines the actual relation.
     • fields: [authorId]: Specifies the foreign key field in the current model (Post).
     • references: [id]: Specifies the primary key field in the related model (User) that authorId refers to.

2. Run a new migration: After updating your schema, run the migration command:

   npx prisma migrate dev --name add_user_post_relationship
   

3. Perform operations with relationships: Let’s update script.ts (or script.js) to demonstrate creating data with relationships and querying related data.

   // script.ts (continued or new file)
   import { PrismaClient } from '@prisma/client';
   
   // ... (single PrismaClient instance pattern) ...
   
   async function main() {
     // Create a user and some posts for that user
     console.log('--- Creating a User with Posts ---');
     const newUserWithPosts = await prisma.user.create({
       data: {
         name: 'Charlie Brown',
         email: 'charlie@example.com',
         age: 40,
         posts: {
           create: [
             { title: 'Learning Prisma Relationships', content: 'This is exciting!' },
             { title: 'Advanced Data Modeling', content: 'Still learning...', published: false },
           ],
         },
       },
       include: {
         posts: true, // Include the created posts in the returned object
       },
     });
     console.log('Created User with Posts:', newUserWithPosts);
   
     // Find all posts and include their authors
     console.log('\n--- Finding all Posts including their Authors ---');
     const postsWithAuthors = await prisma.post.findMany({
       include: {
         author: true, // Include the related User object
       },
     });
     console.log('Posts with Authors:', postsWithAuthors);
   
     // Find a user and include only their published posts
     console.log('\n--- Finding Charlie and his published posts ---');
     const charlieAndPublishedPosts = await prisma.user.findUnique({
       where: { email: 'charlie@example.com' },
       include: {
         posts: {
           where: {
             published: true,
           },
           select: {
             title: true, // Only select the title of the post
           },
         },
       },
     });
     console.log('Charlie and his published posts:', charlieAndPublishedPosts);
   }
   
   main()
     .catch((e) => {
       console.error(e);
       process.exit(1);
     })
     .finally(async () => {
       await prisma.$disconnect();
       console.log('\n--- Prisma Client disconnected ---');
     });
   

   Run the script: npx ts-node script.ts (or node script.js).

Exercises/Mini-Challenges:

1. Implement a One-to-One Relationship:

   • Create a new model Profile in schema.prisma.
   • A Profile should have an id and a bio (String?, optional).
   • Establish a one-to-one relationship between User and Profile, where each User can have one Profile and each Profile belongs to one User. (Hint: Use @unique on the relation field in one of the models).
   • Run a migration.
   • Write a script to create a user and their profile in a single create operation.

2. Implement a Many-to-Many Relationship (Bonus):

   • Create a model Tag with id and name (unique string).
   • Establish a many-to-many relationship between Post and Tag. (Hint: Prisma automatically creates a “join table” for you. You just need to define lists on both sides of the relation).
   • Run a migration.
   • Write a script to create a post and connect it to existing tags, or create new tags simultaneously.

3.2 Filtering, Ordering, and Pagination

Retrieving all records from a large table is rarely efficient or necessary. Prisma provides powerful ways to filter, order, and paginate your results.

Detailed Explanation:

• Filtering (where): The where clause allows you to specify conditions to match records. Prisma provides a rich set of filter conditions (e.g., equals, contains, startsWith, gt (greater than), lt (less than), in, NOT, AND, OR).
• Ordering (orderBy): The orderBy clause allows you to sort results by one or more fields in ascending (asc) or descending (desc) order.
• Pagination (skip, take): Pagination is crucial for handling large datasets by returning results in chunks.
  • take: Specifies the maximum number of records to return.
  • skip: Specifies the number of records to skip from the beginning of the result set. This is often used with take to implement offset-based pagination. For example, to get the second page of 10 items, you would skip: 10, take: 10.

Code Examples:

Let’s continue using script.ts (or script.js).

// script.ts (continued)
import { PrismaClient } from '@prisma/client';

// ... (single PrismaClient instance pattern) ...

async function main() {
  // Ensure some data exists for demonstration
  await prisma.user.createMany({
    data: [
      { name: 'David', email: 'david@example.com', age: 22 },
      { name: 'Eve', email: 'eve@example.com', age: 35 },
      { name: 'Frank', email: 'frank@example.com', age: 28 },
    ],
    skipDuplicates: true, // Avoid errors if users already exist
  });

  await prisma.post.createMany({
    data: [
      { title: 'The Art of Code', content: 'A deep dive into clean code.', published: true, authorId: 1 }, // Assuming ID 1 exists
      { title: 'Database Essentials', content: 'Understanding SQL and NoSQL.', published: false, authorId: 1 },
      { title: 'Frontend Frameworks', content: 'React, Vue, Angular.', published: true, authorId: 2 }, // Assuming ID 2 exists
      { title: 'Backend APIs with Node.js', content: 'Building robust backends.', published: true, authorId: 3 }, // Assuming ID 3 exists
    ],
    skipDuplicates: true,
  });


  // 1. Filtering: Find users older than 25
  console.log('\n--- Users older than 25 ---');
  const oldUsers = await prisma.user.findMany({
    where: {
      age: { gt: 25 },
    },
    select: { name: true, age: true }, // Select only name and age
  });
  console.log(oldUsers);

  // 2. Ordering: Find posts, ordered by title alphabetically
  console.log('\n--- Posts ordered by title (ASC) ---');
  const orderedPosts = await prisma.post.findMany({
    orderBy: {
      title: 'asc', // 'desc' for descending
    },
    select: { title: true, author: { select: { name: true } } }, // Include author name
  });
  console.log(orderedPosts);

  // 3. Pagination: Get the first 2 posts
  console.log('\n--- First 2 Posts ---');
  const firstTwoPosts = await prisma.post.findMany({
    take: 2,
    select: { title: true },
    orderBy: { createdAt: 'asc' }, // Ensure consistent ordering for pagination
  });
  console.log(firstTwoPosts);

  // 4. Pagination: Get the next 2 posts (skip the first 2)
  console.log('\n--- Next 2 Posts (skipping first 2) ---');
  const nextTwoPosts = await prisma.post.findMany({
    skip: 2,
    take: 2,
    select: { title: true },
    orderBy: { createdAt: 'asc' },
  });
  console.log(nextTwoPosts);

  // Combining filters, ordering, and includes
  console.log('\n--- Published posts by Charlie, ordered by creation date (DESC) ---');
  const charliePublishedPosts = await prisma.post.findMany({
    where: {
      published: true,
      author: {
        email: 'charlie@example.com',
      },
    },
    orderBy: {
      createdAt: 'desc',
    },
    select: {
      title: true,
      createdAt: true,
      author: { select: { name: true } },
    },
  });
  console.log(charliePublishedPosts);
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
    console.log('\n--- Prisma Client disconnected ---');
  });

Run the script: npx ts-node script.ts (or node script.js).

Exercises/Mini-Challenges:

1. Filter with contains and OR: Find all posts that contain either “Node.js” or “React” in their title or content, regardless of their published status.
2. Order by multiple fields: Get all users, ordered first by age (ascending) and then by name (descending) for users with the same age.
3. Implement cursor-based pagination (Advanced): Research how to implement cursor-based pagination (using cursor and take) with Prisma, which is more efficient for infinite scrolling than skip/take on large datasets. Try to write a function that fetches a “page” of posts after a given postId.

3.3 Using Prisma Studio

Prisma Studio is a powerful graphical user interface (GUI) for your database that comes bundled with Prisma. It allows you to visually inspect, edit, and manage your data without writing any code.

Detailed Explanation:

Prisma Studio provides a friendly browser-based interface to:

• View data in your tables (models).
• Filter, sort, and paginate records.
• Create new records.
• Edit existing records.
• Delete records.
• Navigate relationships between models.

It’s an invaluable tool for debugging, prototyping, and quickly understanding your database’s current state.

Code Examples:

1. Start Prisma Studio: Open your terminal in your project root and simply run:

   npx prisma studio
   

   This command will start a local server and open Prisma Studio in your default web browser (usually at http://localhost:5555).

   You’ll see your User and Post models listed on the left sidebar. Click on them to view the data. Try adding, editing, or deleting records directly in the UI. Observe how relationships are displayed (e.g., clicking on a Post will show its author, and clicking the author’s ID will navigate to the User record).

Exercises/Mini-Challenges:

1. Create data in Studio: Use Prisma Studio to manually create a new User and a new Post for that user. Pay attention to how the foreign key (authorId) is handled.
2. Experiment with filters: In Prisma Studio, try filtering your Post records to only show those where published is true.
3. Explore relations: Navigate from a User record to see all posts authored by that user. Then, from a Post record, click on the author to jump to their User profile.

4. Advanced Topics and Best Practices

This section explores more complex aspects of Prisma, focusing on features that are crucial for building robust, performant, and maintainable applications. We’ll also cover best practices to help you avoid common pitfalls.

4.1 Transactions

Transactions are a fundamental concept in database management, ensuring data integrity when performing multiple, dependent operations.

Detailed Explanation:

A database transaction is a sequence of operations performed as a single logical unit of work. This means either all operations within the transaction succeed (commit), or if any operation fails, all operations are rolled back (atomicity). This prevents your database from being left in an inconsistent state.

Prisma offers two types of transactions:

1. Interactive Transactions (prisma.$transaction(async (prisma) => { ... })): This is the recommended way to perform multiple dependent operations. You pass an async function to $transaction, and Prisma provides a transactional prisma client instance to that function. All operations within the async function using this transactional client will be part of the same transaction. If an error occurs, Prisma automatically rolls back all changes.
2. Batch Transactions (prisma.$transaction([...])): This allows you to execute an array of independent write operations (e.g., create, update, delete) as a single transaction. All operations succeed or fail together. It’s simpler but less flexible than interactive transactions as operations cannot depend on the results of previous operations within the batch.

Code Examples:

Let’s use an example of transferring money between two bank accounts (a classic use case for transactions).

// script.ts (continued)
import { PrismaClient } from '@prisma/client';

// ... (single PrismaClient instance pattern) ...

async function main() {
  // Setup: Create two users with initial balances
  await prisma.user.createMany({
    data: [
      { name: 'Bank Account A', email: 'accountA@example.com', age: 0 },
      { name: 'Bank Account B', email: 'accountB@example.com', age: 0 },
    ],
    skipDuplicates: true,
  });

  // Make sure accounts have some initial amount
  await prisma.user.update({
    where: { email: 'accountA@example.com' },
    data: { age: 100 }, // Using 'age' as a proxy for 'balance' for simplicity
  });
  await prisma.user.update({
    where: { email: 'accountB@example.com' },
    data: { age: 50 },
  });

  console.log('\n--- Initial Balances ---');
  const initialBalances = await prisma.user.findMany({
    where: { email: { in: ['accountA@example.com', 'accountB@example.com'] } },
    select: { name: true, age: true },
  });
  console.log(initialBalances);

  // Interactive Transaction Example: Transfer money
  const transferAmount = 30;

  try {
    console.log(`\n--- Attempting to transfer ${transferAmount} from Account A to Account B ---`);
    const transactionResult = await prisma.$transaction(async (tx) => {
      // 1. Deduct from Account A
      const sender = await tx.user.update({
        where: { email: 'accountA@example.com' },
        data: { age: { decrement: transferAmount } },
      });

      // Simulate an error here to see rollback in action
      // if (sender.age < 0) {
      //   throw new Error('Insufficient funds in Account A');
      // }

      // 2. Add to Account B
      const receiver = await tx.user.update({
        where: { email: 'accountB@example.com' },
        data: { age: { increment: transferAmount } },
      });

      return { sender, receiver };
    });
    console.log('Transfer successful!', transactionResult);
  } catch (error: any) {
    console.error('Transfer failed and rolled back:', error.message);
  }

  console.log('\n--- Final Balances ---');
  const finalBalances = await prisma.user.findMany({
    where: { email: { in: ['accountA@example.com', 'accountB@example.com'] } },
    select: { name: true, age: true },
  });
  console.log(finalBalances);

  // Batch Transaction Example (independent operations)
  console.log('\n--- Batch deleting some posts and creating new ones ---');
  try {
    const batchResult = await prisma.$transaction([
      prisma.post.deleteMany({
        where: { published: false }, // Delete all unpublished posts
      }),
      prisma.post.create({
        data: {
          title: 'New Article for Batch',
          content: 'This was created in a batch transaction.',
          published: true,
          authorId: 1, // Assuming user with ID 1 exists
        },
      }),
    ]);
    console.log('Batch operations successful:', batchResult);
  } catch (error: any) {
    console.error('Batch operations failed:', error.message);
  }
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
    console.log('\n--- Prisma Client disconnected ---');
  });

Run the script: npx ts-node script.ts (or node script.js). Try uncommenting the throw new Error line in the interactive transaction to see the rollback in action.

Exercises/Mini-Challenges:

1. Implement an order processing transaction:

   • Imagine you have Order and Product models. An Order contains items which are linked to Products.
   • Write an interactive transaction that, when a new Order is created:
     • Decrements the stock count of each Product in the order.
     • If any product goes out of stock (stock < 0), the transaction should throw an error and roll back.
     • (Optional) If you have a money field on the user, decrement it.

2. Batch update with conditions:

   • Write a batch transaction that first updates all posts with “Prisma” in their title to be published: true, and then deletes all posts that were created more than a week ago. (You might need to adjust createdAt values for testing).

4.2 Raw Database Queries ($queryRaw and $executeRaw)

While Prisma ORM provides a fantastic abstraction, there are times when you need the full power and flexibility of raw SQL.

Detailed Explanation:

Prisma allows you to execute raw SQL queries when the ORM’s API isn’t sufficient or when you need to leverage specific database features not exposed by Prisma Client.

• prisma.$queryRaw(sql, ...values) or prisma.$queryRawUnsafe(sql, ...values): Executes a raw SQL query that returns data. The $queryRaw method supports tagged template literals for safer parameter binding, preventing SQL injection. $queryRawUnsafe allows direct string concatenation but is riskier.
• prisma.$executeRaw(sql, ...values) or prisma.$executeRawUnsafe(sql, ...values): Executes a raw SQL query that modifies data (e.g., INSERT, UPDATE, DELETE) and returns the number of affected rows. Similar safety considerations apply to $executeRaw vs. $executeRawUnsafe.

Best Practice: Always use the tagged template literal versions ($queryRaw, $executeRaw) to prevent SQL injection vulnerabilities. Only use Unsafe methods if you are absolutely certain about the query’s source and parameters (e.g., for very static queries).

Code Examples:

// script.ts (continued)
import { PrismaClient, Prisma } from '@prisma/client';

// ... (single PrismaClient instance pattern) ...

async function main() {
  // Ensure some data exists
  await prisma.user.upsert({
    where: { email: 'rawquery@example.com' },
    update: {},
    create: { name: 'Raw User', email: 'rawquery@example.com', age: 45 },
  });

  await prisma.post.upsert({
    where: { title: 'Raw Query Post' },
    update: {},
    create: { title: 'Raw Query Post', content: 'Content via raw query.', published: true, authorId: 1 },
  });


  // $queryRaw: Select all users with a specific age using raw SQL
  console.log('\n--- Users with age 45 via raw query ---');
  const usersWithAge45 = await prisma.$queryRaw(
    Prisma.sql`SELECT * FROM User WHERE age = ${45}`
  );
  console.log(usersWithAge45);

  // $executeRaw: Update a post directly using raw SQL
  console.log('\n--- Updating a post via raw execution ---');
  const postIdToUpdate = (await prisma.post.findFirst({ where: { title: 'Raw Query Post' } }))?.id;

  if (postIdToUpdate) {
    const updatedRows = await prisma.$executeRaw(
      Prisma.sql`UPDATE Post SET content = ${'Updated content via raw query.'} WHERE id = ${postIdToUpdate}`
    );
    console.log(`Updated ${updatedRows} row(s).`);

    const updatedPost = await prisma.post.findUnique({ where: { id: postIdToUpdate } });
    console.log('Updated Post:', updatedPost);
  } else {
    console.log('Post not found for raw update.');
  }
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
    console.log('\n--- Prisma Client disconnected ---');
  });

Run the script: npx ts-node script.ts (or node script.js).

Exercises/Mini-Challenges:

1. Raw insert: Use prisma.$executeRaw to insert a new user directly into the User table without using prisma.user.create(). Make sure to handle all required fields.
2. Complex join with $queryRaw: If you’re familiar with SQL joins, try writing a $queryRaw query that joins the User and Post tables to retrieve all users along with the titles of their published posts.

4.3 Best Practices and Common Pitfalls

Adhering to best practices can significantly improve the performance, maintainability, and reliability of your Prisma-backed applications.

Best Practices:

1. Singleton PrismaClient Instance: As discussed, always create a single, shared instance of PrismaClient and reuse it throughout your application. This optimizes connection pooling and resource usage.
2. Graceful Shutdown: Ensure you disconnect the Prisma Client when your application shuts down to prevent lingering database connections. This is crucial for long-running servers.

   // Example for a simple Node.js script:
   // process.on('beforeExit', async () => {
   //   await prisma.$disconnect();
   //   console.log('Prisma Client disconnected on exit.');
   // });
   // In frameworks like Express/Fastify, handle this in server cleanup logic.
   
   // (Already implemented in main() -> finally block for our scripts)
   

3. Environment Variables for Credentials: Never hardcode sensitive information like database URLs directly in your code. Use .env files and environment variables, especially for different deployment environments (development, staging, production).
4. Descriptive Migration Names: Use meaningful names for your migrations (e.g., add_auth_models, update_post_status) to easily understand their purpose later.
5. Use select and include judiciously: Only fetch the data you actually need. Over-fetching can lead to performance bottlenecks. Use select to specify individual fields and include to fetch related models.
6. Error Handling: Always wrap database operations in try...catch blocks to gracefully handle potential errors (e.g., network issues, constraint violations).
7. Transactions for Dependent Writes: As covered in 4.1, use prisma.$transaction for multiple write operations that must succeed or fail together to maintain data consistency.
8. Schema Design:
   • Meaningful Model and Field Names: Use clear, descriptive names for your models and fields.
   • Proper Types: Choose the correct Prisma types that map appropriately to your database types.
   • Relations: Define relationships explicitly. Understand and apply onDelete actions (e.g., CASCADE, SET NULL, RESTRICT) to define how related data behaves when a record is deleted. (This is an advanced aspect of relations, see Prisma docs).
9. Logging: Configure Prisma Client logging to get insights into database queries and potential issues. log: ['query', 'info', 'warn', 'error'] is a good starting point for development. In production, you might want more structured logging or only log warn and error levels.

Common Pitfalls:

1. Multiple Prisma Client Instances (in development): As mentioned, Next.js hot-reloading can lead to this. Use the global variable pattern (globalForPrisma).
2. Forgetting npx prisma generate or npx prisma migrate dev: After making changes to schema.prisma, you must run one of these commands to update your database and/or the Prisma Client. Your code won’t reflect schema changes until you do.
3. SQL Injection with $queryRawUnsafe / $executeRawUnsafe: Using the Unsafe variants with concatenated user input is a major security vulnerability. Always use tagged template literals (Prisma.sql) with $queryRaw and $executeRaw.
4. Long-running Transactions: Keeping a transaction open for too long can lock database resources, reduce concurrency, and lead to performance issues or deadlocks. Keep transactions as short and focused as possible.
5. Not Handling Disconnects in Serverless Functions: In serverless environments (like AWS Lambda, Google Cloud Functions), each function invocation might be a new process. If you create a new PrismaClient instance per invocation and don’t await prisma.$disconnect() at the end, connections might linger and exhaust your database’s connection limit. Prisma Accelerate helps mitigate this.
6. Ignoring Database Constraints: Prisma helps, but you still need to understand your underlying database’s constraints (e.g., foreign key constraints, unique constraints). Prisma will throw errors if your operations violate these.

5. Guided Projects

In this section, you’ll apply everything you’ve learned by building two guided projects. These projects will simulate real-world scenarios and help solidify your understanding of Prisma.

Project 1: Simple Blog API with User Authentication

Objective: Build a backend API for a blog platform where users can register, log in, create posts, and view posts by other users. We’ll use a minimalist approach focusing on Prisma.

Problem Statement: We need an API that allows:

• Users to sign up with email and password.
• Users to log in and receive a session token.
• Authenticated users to create, update, and delete their own posts.
• Anyone to view all posts or a specific post.

Technologies Used:

• Node.js
• Prisma ORM
• Express.js (for API endpoints)
• Bcrypt.js (for password hashing)
• JSON Web Tokens (JWT) (for authentication)

Setup:

1. New Project Folder:

   mkdir prisma-blog-api
   cd prisma-blog-api
   npm init -y
   

2. Install Dependencies:

   npm install express @prisma/client bcryptjs jsonwebtoken dotenv
   npm install --save-dev prisma typescript ts-node @types/node @types/express @types/bcryptjs @types/jsonwebtoken
   npx tsc --init
   

3. Initialize Prisma (SQLite):

   npx prisma init --datasource-provider sqlite
   

4. Update schema.prisma: Add User and Post models. Notice the password field.

   // prisma/schema.prisma
   datasource db {
     provider = "sqlite"
     url      = env("DATABASE_URL")
   }
   
   generator client {
     provider = "prisma-client-js"
   }
   
   model User {
     id        Int      @id @default(autoincrement())
     email     String   @unique
     password  String   // Store hashed password
     name      String?
     posts     Post[]
     createdAt DateTime @default(now())
     updatedAt DateTime @updatedAt
   }
   
   model Post {
     id        Int      @id @default(autoincrement())
     title     String
     content   String?
     published Boolean  @default(false)
     createdAt DateTime @default(now())
     updatedAt DateTime @updatedAt
     author    User     @relation(fields: [authorId], references: [id])
     authorId  Int
   }
   

5. Run Migration:

   npx prisma migrate dev --name init_blog_schema
   

6. Create src folder and prismaClient.ts:

   mkdir src
   touch src/prismaClient.ts
   

   src/prismaClient.ts (our singleton Prisma client):

   // src/prismaClient.ts
   import { PrismaClient } from '@prisma/client';
   
   const globalForPrisma = global as unknown as {
     prisma: PrismaClient | undefined;
   };
   
   export const prisma =
     globalForPrisma.prisma ??
     new PrismaClient({
       log: ['warn', 'error'], // Only log warnings and errors in production-like settings
     });
   
   if (process.env.NODE_ENV !== 'production') {
     globalForPrisma.prisma = prisma;
   }
   

7. Create src/server.ts: This will be our main Express app file.

   // src/server.ts
   import express from 'express';
   import bcrypt from 'bcryptjs';
   import jwt from 'jsonwebtoken';
   import dotenv from 'dotenv';
   import { prisma } from './prismaClient';
   
   dotenv.config(); // Load environment variables from .env
   
   const app = express();
   app.use(express.json()); // Enable JSON body parsing
   
   const PORT = process.env.PORT || 3000;
   const JWT_SECRET = process.env.JWT_SECRET || 'supersecretjwtkey'; // USE A STRONG SECRET IN PRODUCTION!
   
   // Middleware to authenticate JWT
   const authenticateToken = (req: any, res: any, next: any) => {
     const authHeader = req.headers['authorization'];
     const token = authHeader && authHeader.split(' ')[1];
   
     if (token == null) return res.sendStatus(401);
   
     jwt.verify(token, JWT_SECRET, (err: any, user: any) => {
       if (err) return res.sendStatus(403);
       req.user = user; // Attach user payload to request
       next();
     });
   };
   
   // --- User Routes ---
   
   // 1. Register User
   app.post('/register', async (req, res) => {
     try {
       const { email, password, name } = req.body;
       if (!email || !password) {
         return res.status(400).json({ error: 'Email and password are required' });
       }
   
       const hashedPassword = await bcrypt.hash(password, 10);
       const user = await prisma.user.create({
         data: {
           email,
           password: hashedPassword,
           name,
         },
       });
   
       res.status(201).json({ message: 'User registered successfully', userId: user.id });
     } catch (error: any) {
       if (error.code === 'P2002') { // Unique constraint violation
         return res.status(409).json({ error: 'Email already registered' });
       }
       console.error('Registration error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 2. Login User
   app.post('/login', async (req, res) => {
     try {
       const { email, password } = req.body;
       const user = await prisma.user.findUnique({
         where: { email },
       });
   
       if (!user) {
         return res.status(400).json({ error: 'Invalid credentials' });
       }
   
       const isPasswordValid = await bcrypt.compare(password, user.password);
       if (!isPasswordValid) {
         return res.status(400).json({ error: 'Invalid credentials' });
       }
   
       // Generate JWT
       const token = jwt.sign({ userId: user.id, email: user.email }, JWT_SECRET, { expiresIn: '1h' });
       res.json({ message: 'Logged in successfully', token });
     } catch (error) {
       console.error('Login error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // --- Post Routes ---
   
   // 3. Create a new post (Auth required)
   app.post('/posts', authenticateToken, async (req: any, res) => {
     try {
       const { title, content, published } = req.body;
       const post = await prisma.post.create({
         data: {
           title,
           content,
           published: published ?? false,
           authorId: req.user.userId, // Get authorId from authenticated user
         },
       });
       res.status(201).json(post);
     } catch (error) {
       console.error('Create post error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 4. Get all posts (Public)
   app.get('/posts', async (req, res) => {
     try {
       const posts = await prisma.post.findMany({
         where: { published: true }, // Only show published posts
         include: {
           author: {
             select: { id: true, name: true, email: true }, // Only select certain author fields
           },
         },
         orderBy: { createdAt: 'desc' },
       });
       res.json(posts);
     } catch (error) {
       console.error('Get all posts error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 5. Get a single post by ID (Public)
   app.get('/posts/:id', async (req, res) => {
     try {
       const postId = parseInt(req.params.id);
       const post = await prisma.post.findUnique({
         where: { id: postId },
         include: {
           author: {
             select: { id: true, name: true, email: true },
           },
         },
       });
       if (!post || !post.published) { // Only return if published
         return res.status(404).json({ error: 'Post not found or not published' });
       }
       res.json(post);
     } catch (error) {
       console.error('Get single post error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 6. Update a post (Auth required, must be author)
   app.put('/posts/:id', authenticateToken, async (req: any, res) => {
     try {
       const postId = parseInt(req.params.id);
       const { title, content, published } = req.body;
   
       const existingPost = await prisma.post.findUnique({
         where: { id: postId },
       });
   
       if (!existingPost) {
         return res.status(404).json({ error: 'Post not found' });
       }
   
       // Check if the authenticated user is the author of the post
       if (existingPost.authorId !== req.user.userId) {
         return res.status(403).json({ error: 'Unauthorized: You can only update your own posts' });
       }
   
       const updatedPost = await prisma.post.update({
         where: { id: postId },
         data: {
           title,
           content,
           published: published ?? existingPost.published,
           updatedAt: new Date(), // Manually update this or rely on @updatedAt
         },
       });
       res.json(updatedPost);
     } catch (error) {
       console.error('Update post error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 7. Delete a post (Auth required, must be author)
   app.delete('/posts/:id', authenticateToken, async (req: any, res) => {
     try {
       const postId = parseInt(req.params.id);
   
       const existingPost = await prisma.post.findUnique({
         where: { id: postId },
       });
   
       if (!existingPost) {
         return res.status(404).json({ error: 'Post not found' });
       }
   
       // Check if the authenticated user is the author of the post
       if (existingPost.authorId !== req.user.userId) {
         return res.status(403).json({ error: 'Unauthorized: You can only delete your own posts' });
       }
   
       await prisma.post.delete({
         where: { id: postId },
       });
       res.status(204).send(); // No content
     } catch (error) {
       console.error('Delete post error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // Start the server
   app.listen(PORT, () => {
     console.log(`Server running on http://localhost:${PORT}`);
   });
   
   // Graceful shutdown
   process.on('SIGTERM', async () => {
     console.log('SIGTERM received. Shutting down gracefully.');
     await prisma.$disconnect();
     process.exit(0);
   });
   
   process.on('SIGINT', async () => {
     console.log('SIGINT received. Shutting down gracefully.');
     await prisma.$disconnect();
     process.exit(0);
   });
   

8. Add start script to package.json:

   // package.json
   {
     "name": "prisma-blog-api",
     "version": "1.0.0",
     "description": "",
     "main": "index.js",
     "scripts": {
       "start": "ts-node src/server.ts",
       "dev": "nodemon src/server.ts", // Optional: for automatic restarts during development
       "test": "echo \"Error: no test specified\" && exit 1"
     },
     "keywords": [],
     "author": "",
     "license": "ISC",
     "dependencies": {
       "@prisma/client": "^5.x.x", // Replace with your installed version
       "bcryptjs": "^2.4.3",
       "dotenv": "^16.4.5",
       "express": "^4.19.2",
       "jsonwebtoken": "^9.0.2",
       "prisma": "^5.x.x" // Replace with your installed version
     },
     "devDependencies": {
       "@types/bcryptjs": "^2.4.6",
       "@types/express": "^4.17.21",
       "@types/jsonwebtoken": "^9.0.6",
       "@types/node": "^20.14.9",
       "nodemon": "^3.1.4", // Optional
       "ts-node": "^10.9.2",
       "typescript": "^5.5.3"
     }
   }
   

   If you install nodemon, you can use npm run dev for development. Otherwise, npm start.

9. Create a .env file:

   # .env
   DATABASE_URL="file:./dev.db"
   JWT_SECRET="YOUR_VERY_STRONG_AND_RANDOM_JWT_SECRET_HERE"
   PORT=3000
   

   IMPORTANT: Change YOUR_VERY_STRONG_AND_RANDOM_JWT_SECRET_HERE to a long, random string for production.

Step-by-Step Implementation and Testing:

1. Start the server:

   npm start
   

   You should see Server running on http://localhost:3000.

2. Test with a tool like Postman, Insomnia, or curl:

   • Register a User: POST http://localhost:3000/register Body (JSON):

     {
       "email": "test@example.com",
       "password": "password123",
       "name": "Test User"
     }
     

     Expected: 201 Created with user ID.

   • Login User: POST http://localhost:3000/login Body (JSON):

     {
       "email": "test@example.com",
       "password": "password123"
     }
     

     Expected: 200 OK with a token. Copy this token; you’ll need it for authenticated requests.

   • Create a Post: POST http://localhost:3000/posts Headers: Authorization: Bearer <YOUR_JWT_TOKEN_HERE> Body (JSON):

     {
       "title": "My First Blog Post",
       "content": "This is the content of my first post.",
       "published": true
     }
     

     Expected: 201 Created with post details.

   • Get All Posts: GET http://localhost:3000/posts Expected: 200 OK with an array of published posts.

   • Get Single Post: GET http://localhost:3000/posts/<POST_ID> (replace <POST_ID> with the ID from creation) Expected: 200 OK with the post details.

   • Update Post: PUT http://localhost:3000/posts/<POST_ID> Headers: Authorization: Bearer <YOUR_JWT_TOKEN_HERE> Body (JSON):

     {
       "title": "My Updated Blog Post",
       "content": "The content has been updated!",
       "published": false
     }
     

     Expected: 200 OK with updated post details. (Note: It will now be published: false so it won’t appear in “Get All Posts”).

   • Delete Post: DELETE http://localhost:3000/posts/<POST_ID> Headers: Authorization: Bearer <YOUR_JWT_TOKEN_HERE> Expected: 204 No Content.

Encourage Independent Problem-Solving:

• Error Handling: Implement more specific error handling for different scenarios (e.g., trying to create a post with an invalid authorId).
• Post Authorship Validation: When updating/deleting posts, ensure that only the original author can perform these actions (this is already implemented, but review and understand the logic!).
• Pagination for Posts: Add skip and take parameters to the /posts GET endpoint to allow for paginated results.
• Search Posts: Add a query parameter (e.g., ?search=keyword) to the /posts GET endpoint to filter posts by title or content using Prisma’s contains filter.

Project 2: Simple E-commerce Product Catalog

Objective: Create a backend service for an e-commerce product catalog, including categories and products, and advanced querying capabilities.

Problem Statement: We need an API that manages products and categories.

• Products belong to categories.
• Users can browse products, filter by category, and search by name.
• Admin users can add, update, and delete categories and products.

Technologies Used:

• Node.js
• Prisma ORM
• Express.js (for API endpoints)

Setup:

1. New Project Folder (similar to Project 1, but new files):

   mkdir prisma-ecommerce-api
   cd prisma-ecommerce-api
   npm init -y
   

2. Install Dependencies:

   npm install express @prisma/client dotenv
   npm install --save-dev prisma typescript ts-node @types/node @types/express
   npx tsc --init
   

3. Initialize Prisma (SQLite):

   npx prisma init --datasource-provider sqlite
   

4. Update schema.prisma: Add Category and Product models with a one-to-many relationship.

   // prisma/schema.prisma
   datasource db {
     provider = "sqlite"
     url      = env("DATABASE_URL")
   }
   
   generator client {
     provider = "prisma-client-js"
   }
   
   model Category {
     id        Int      @id @default(autoincrement())
     name      String   @unique
     products  Product[]
     createdAt DateTime @default(now())
     updatedAt DateTime @updatedAt
   }
   
   model Product {
     id          Int      @id @default(autoincrement())
     name        String
     description String?
     price       Float
     imageUrl    String?
     stock       Int      @default(0)
     createdAt   DateTime @default(now())
     updatedAt   DateTime @updatedAt
     category    Category @relation(fields: [categoryId], references: [id])
     categoryId  Int
   }
   

5. Run Migration:

   npx prisma migrate dev --name init_ecommerce_schema
   

6. Create src/prismaClient.ts: (Same content as Project 1)
7. Create src/server.ts: (Main Express app file for e-commerce)

   // src/server.ts
   import express from 'express';
   import dotenv from 'dotenv';
   import { prisma } from './prismaClient';
   
   dotenv.config();
   
   const app = express();
   app.use(express.json());
   
   const PORT = process.env.PORT || 3001; // Use a different port than project 1
   
   // --- Category Routes (Admin-like, simplified for this project) ---
   
   // 1. Create Category
   app.post('/categories', async (req, res) => {
     try {
       const { name } = req.body;
       const category = await prisma.category.create({
         data: { name },
       });
       res.status(201).json(category);
     } catch (error: any) {
       if (error.code === 'P2002') {
         return res.status(409).json({ error: 'Category with this name already exists' });
       }
       console.error('Create category error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 2. Get All Categories
   app.get('/categories', async (req, res) => {
     try {
       const categories = await prisma.category.findMany({
         include: { _count: { select: { products: true } } }, // Get product count per category
         orderBy: { name: 'asc' },
       });
       res.json(categories);
     } catch (error) {
       console.error('Get categories error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // --- Product Routes ---
   
   // 3. Create Product
   app.post('/products', async (req, res) => {
     try {
       const { name, description, price, imageUrl, stock, categoryId } = req.body;
       const product = await prisma.product.create({
         data: {
           name,
           description,
           price: parseFloat(price),
           imageUrl,
           stock: parseInt(stock),
           categoryId: parseInt(categoryId),
         },
       });
       res.status(201).json(product);
     } catch (error: any) {
       if (error.code === 'P2003') { // Foreign key constraint failed
         return res.status(400).json({ error: 'Category not found' });
       }
       console.error('Create product error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 4. Get All Products (with filtering, searching, pagination)
   app.get('/products', async (req, res) => {
     try {
       const { category, search, skip, take, orderBy } = req.query;
   
       const where: any = {};
       if (search) {
         where.OR = [
           { name: { contains: search as string, mode: 'insensitive' } },
           { description: { contains: search as string, mode: 'insensitive' } },
         ];
       }
       if (category) {
         where.category = {
           name: category as string,
         };
       }
   
       const orderByOption: any = {};
       if (orderBy === 'price_asc') {
         orderByOption.price = 'asc';
       } else if (orderBy === 'price_desc') {
         orderByOption.price = 'desc';
       } else {
         orderByOption.createdAt = 'desc'; // Default order
       }
   
       const products = await prisma.product.findMany({
         where,
         include: { category: { select: { name: true } } },
         skip: skip ? parseInt(skip as string) : undefined,
         take: take ? parseInt(take as string) : undefined,
         orderBy: orderByOption,
       });
   
       const totalProducts = await prisma.product.count({ where });
   
       res.json({ products, total: totalProducts });
     } catch (error) {
       console.error('Get products error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 5. Get Single Product by ID
   app.get('/products/:id', async (req, res) => {
     try {
       const productId = parseInt(req.params.id);
       const product = await prisma.product.findUnique({
         where: { id: productId },
         include: { category: { select: { name: true } } },
       });
       if (!product) {
         return res.status(404).json({ error: 'Product not found' });
       }
       res.json(product);
     } catch (error) {
       console.error('Get single product error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 6. Update Product
   app.put('/products/:id', async (req, res) => {
     try {
       const productId = parseInt(req.params.id);
       const { name, description, price, imageUrl, stock, categoryId } = req.body;
   
       const updatedProduct = await prisma.product.update({
         where: { id: productId },
         data: {
           name,
           description,
           price: price ? parseFloat(price) : undefined,
           imageUrl,
           stock: stock ? parseInt(stock) : undefined,
           categoryId: categoryId ? parseInt(categoryId) : undefined,
           updatedAt: new Date(),
         },
       });
       res.json(updatedProduct);
     } catch (error: any) {
       if (error.code === 'P2025') { // Record not found
         return res.status(404).json({ error: 'Product not found' });
       }
       if (error.code === 'P2003') { // Foreign key constraint failed
         return res.status(400).json({ error: 'Category not found' });
       }
       console.error('Update product error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   // 7. Delete Product
   app.delete('/products/:id', async (req, res) => {
     try {
       const productId = parseInt(req.params.id);
       await prisma.product.delete({
         where: { id: productId },
       });
       res.status(204).send();
     } catch (error: any) {
       if (error.code === 'P2025') {
         return res.status(404).json({ error: 'Product not found' });
       }
       console.error('Delete product error:', error);
       res.status(500).json({ error: 'Something went wrong' });
     }
   });
   
   
   // Start the server
   app.listen(PORT, () => {
     console.log(`E-commerce API running on http://localhost:${PORT}`);
   });
   
   // Graceful shutdown
   process.on('SIGTERM', async () => {
     console.log('SIGTERM received. Shutting down gracefully.');
     await prisma.$disconnect();
     process.exit(0);
   });
   
   process.on('SIGINT', async () => {
     console.log('SIGINT received. Shutting down gracefully.');
     await prisma.$disconnect();
     process.exit(0);
   });
   

8. Add start script to package.json:

   // package.json
   {
     "name": "prisma-ecommerce-api",
     "version": "1.0.0",
     "description": "",
     "main": "index.js",
     "scripts": {
       "start": "ts-node src/server.ts",
       "dev": "nodemon src/server.ts",
       "test": "echo \"Error: no test specified\" && exit 1"
     },
     "keywords": [],
     "author": "",
     "license": "ISC",
     "dependencies": {
       "@prisma/client": "^5.x.x",
       "dotenv": "^16.4.5",
       "express": "^4.19.2",
       "prisma": "^5.x.x"
     },
     "devDependencies": {
       "@types/express": "^4.17.21",
       "@types/node": "^20.14.9",
       "nodemon": "^3.1.4",
       "ts-node": "^10.9.2",
       "typescript": "^5.5.3"
     }
   }
   

9. Create a .env file:

   # .env
   DATABASE_URL="file:./ecommerce.db"
   PORT=3001