generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

# 'dev' is for development -- it creates the migration, applies it,
# and regenerates the Prisma Client in one step.
# The --name flag is a human-readable label for the migration.
npx prisma migrate dev --name init

# In production, use 'deploy' instead -- it only applies pending
# migrations without generating new ones or resetting data
npx prisma migrate deploy

npm install @prisma/client

const { PrismaClient } = require('@prisma/client');

// Create a single instance -- every method on prisma (prisma.user, prisma.post, etc.)
// is auto-generated from your schema.prisma file
const prisma = new PrismaClient();

async function main() {
  // ... database queries
}

const newUser = await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@prisma.io',
    posts: {
      create: { title: 'Hello World' },
    },
  },
});

// Find all users
const users = await prisma.user.findMany({
  include: {
    posts: true, // Include relations
  },
});

// Find unique
const user = await prisma.user.findUnique({
  where: {
    email: 'alice@prisma.io',
  },
});

const updatedUser = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Wonderland' },
});

const deletedUser = await prisma.user.delete({
  where: { id: 1 },
});

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String?
  role      Role      @default(USER)
  posts     Post[]
  profile   Profile?
  orders    Order[]
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt

  @@index([email])
  @@map("users") // Custom table name
}

model Profile {
  id       Int     @id @default(autoincrement())
  bio      String?
  avatar   String?
  userId   Int     @unique
  user     User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model Post {
  id          Int        @id @default(autoincrement())
  title       String
  content     String?
  published   Boolean    @default(false)
  authorId    Int
  author      User       @relation(fields: [authorId], references: [id])
  categories  Category[]
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  @@index([authorId])
  @@fulltext([title, content])
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

model Order {
  id        Int         @id @default(autoincrement())
  status    OrderStatus @default(PENDING)
  total     Decimal     @db.Decimal(10, 2)
  userId    Int
  user      User        @relation(fields: [userId], references: [id])
  items     OrderItem[]
  createdAt DateTime    @default(now())
}

model OrderItem {
  id        Int   @id @default(autoincrement())
  quantity  Int
  price     Decimal @db.Decimal(10, 2)
  productId Int
  orderId   Int
  order     Order @relation(fields: [orderId], references: [id], onDelete: Cascade)
}

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

// Complex filtering
const users = await prisma.user.findMany({
  where: {
    AND: [
      { email: { contains: '@gmail.com' } },
      { role: 'USER' },
      {
        OR: [
          { name: { startsWith: 'A' } },
          { posts: { some: { published: true } } }
        ]
      }
    ]
  },
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5
    },
    profile: true
  },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 10
});

// Aggregations
const stats = await prisma.post.aggregate({
  _count: { id: true },
  _avg: { views: true },
  _sum: { views: true },
  where: { published: true }
});

// Group by
const postsByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { id: true },
  _sum: { views: true },
  having: {
    id: { _count: { gt: 5 } }
  },
  orderBy: {
    _count: { id: 'desc' }
  }
});

// Raw SQL when needed
const users = await prisma.$queryRaw`
  SELECT * FROM users
  WHERE created_at > ${new Date('2024-01-01')}
  ORDER BY created_at DESC
`;

// Interactive transaction -- the callback receives a transaction client (tx)
// that you use instead of prisma. If ANY operation fails or you throw an error,
// ALL changes within this block are rolled back automatically.
const result = await prisma.$transaction(async (tx) => {
  // Decrease stock -- decrement is an atomic operation
  const product = await tx.product.update({
    where: { id: productId },
    data: { stock: { decrement: quantity } }
  });
  
  // Business logic check: if stock went negative, throw to trigger rollback.
  // The decrement already happened in the DB, but throwing here rolls it back.
  if (product.stock < 0) {
    throw new Error('Insufficient stock');
  }
  
  // Create order -- this only persists if we reach the end without errors
  const order = await tx.order.create({
    data: {
      userId,
      total: product.price * quantity,
      items: {
        create: {
          productId,
          quantity,
          price: product.price
        }
      }
    }
  });
  
  return order;
});

// Batch transaction
const [updatedUser, newPost] = await prisma.$transaction([
  prisma.user.update({
    where: { id: 1 },
    data: { postsCount: { increment: 1 } }
  }),
  prisma.post.create({
    data: { title: 'New Post', authorId: 1 }
  })
]);

// Logging middleware -- wraps every query with timing information.
// Extremely useful for identifying slow queries during development.
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params); // Execute the actual query
  const after = Date.now();
  
  console.log(`${params.model}.${params.action} took ${after - before}ms`);
  return result;
});

// Soft delete middleware -- intercepts delete operations and converts
// them into updates that set a deletedAt timestamp instead of actually
// removing the row. This lets you "undelete" data later if needed.
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'delete') {
      // Rewrite the delete into an update
      params.action = 'update';
      params.args.data = { deletedAt: new Date() };
    }
    
    if (params.action === 'findMany') {
      // Automatically filter out soft-deleted records
      params.args.where = {
        ...params.args.where,
        deletedAt: null
      };
    }
  }
  return next(params);
});

npx prisma studio

// Singleton pattern for Prisma Client
// lib/prisma.js
//
// Why a singleton? In development, tools like ts-node-dev or nodemon
// restart your server frequently. Each restart creates a new PrismaClient,
// but the old one's connection pool may not be fully closed yet.
// After several restarts you can hit the database connection limit.
// Storing the instance on `global` survives hot-reloads.
const { PrismaClient } = require('@prisma/client');

let prisma;

if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  // Prevent multiple instances in development (hot-reload safe)
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}

module.exports = prisma;

// Graceful shutdown -- close the connection pool when the process exits
// to avoid leaving dangling connections on the database server
process.on('beforeExit', async () => {
  await prisma.$disconnect();
});