Node.js Database Integration with Prisma

npm install prisma @prisma/client
npx prisma init --datasource-provider postgresql

DATABASE_URL="postgresql://username:password@localhost:5432/mydb?schema=public"

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
  password  String
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts     Post[]
  orders    Order[]
}

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

  author      User     @relation(fields: [authorId], references: [id])
  authorId    Int

  tags        Tag[]
}

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

model Order {
  id        Int         @id @default(autoincrement())
  total     Decimal
  status    OrderStatus @default(PENDING)
  createdAt DateTime    @default(now())

  user      User        @relation(fields: [userId], references: [id])
  userId    Int

  items     OrderItem[]
}

model OrderItem {
  id        Int   @id @default(autoincrement())
  quantity  Int
  price     Decimal

  order     Order @relation(fields: [orderId], references: [id])
  orderId   Int
}

enum Role {
  USER
  ADMIN
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

# Create and apply a migration
npx prisma migrate dev --name init

# Apply migrations in production (no dev data reset)
npx prisma migrate deploy

# View current database state
npx prisma studio   # opens a browser GUI

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

declare global {
  // eslint-disable-next-line no-var
  var prisma: PrismaClient | undefined;
}

export const prisma = global.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['query', 'error'] : ['error'],
});

if (process.env.NODE_ENV !== 'production') {
  global.prisma = prisma;  // prevent hot-reload from creating new connections
}

import { prisma } from '../lib/prisma';

// Create one
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name:  'Alice',
    password: hashedPassword,
  },
});

// Create with relation
const post = await prisma.post.create({
  data: {
    title:    'My First Post',
    content:  'Hello world',
    author:   { connect: { id: userId } },
    // OR: authorId: userId
  },
  include: { author: true },  // return author data
});

// Create many
await prisma.tag.createMany({
  data: [
    { name: 'typescript' },
    { name: 'nodejs' },
  ],
  skipDuplicates: true,
});

// Find one by unique field
const user = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
  select: { id: true, name: true, email: true },  // only fetch these fields
});

// Find first matching
const post = await prisma.post.findFirst({
  where: { published: true, authorId: userId },
  orderBy: { createdAt: 'desc' },
});

// Find many with filters, pagination, relations
const posts = await prisma.post.findMany({
  where: {
    published: true,
    title: { contains: searchTerm, mode: 'insensitive' },
  },
  include: {
    author: { select: { name: true, email: true } },
    tags:   true,
  },
  orderBy:  { createdAt: 'desc' },
  skip:     (page - 1) * pageSize,
  take:     pageSize,
});

// Count
const total = await prisma.post.count({
  where: { published: true },
});

// Update one
const updated = await prisma.user.update({
  where: { id: userId },
  data:  { name: 'Alice Smith' },
});

// Update many
const { count } = await prisma.post.updateMany({
  where: { authorId: userId },
  data:  { published: false },
});

// Upsert — create if not exists, update if exists
const tag = await prisma.tag.upsert({
  where:  { name: 'typescript' },
  update: {},
  create: { name: 'typescript' },
});

// Delete one
await prisma.user.delete({ where: { id: userId } });

// Delete many
const { count } = await prisma.post.deleteMany({
  where: { published: false, createdAt: { lt: thirtyDaysAgo } },
});

// $transaction — all succeed or all rollback
const [user, post] = await prisma.$transaction([
  prisma.user.create({ data: { email: 'bob@example.com', name: 'Bob', password: '' } }),
  prisma.post.create({ data: { title: 'Welcome', authorId: 1 } }),
]);

// Interactive transaction (for conditional logic)
const result = await prisma.$transaction(async (tx) => {
  const order = await tx.order.create({
    data: { userId, total: 99.99, status: 'PROCESSING' },
  });

  // Check inventory, deduct stock, etc.
  await tx.orderItem.createMany({ data: items.map(i => ({ ...i, orderId: order.id })) });

  // If anything throws here, the whole transaction rolls back
  return order;
});

// String filters
where: { name: { contains: 'ali', mode: 'insensitive' } }
where: { name: { startsWith: 'Al' } }
where: { name: { endsWith: 'ce' } }

// Number filters
where: { price: { gte: 10, lte: 100 } }
where: { stock: { gt: 0 } }

// Date filters
where: { createdAt: { gte: new Date('2026-01-01') } }

// IN / NOT IN
where: { status: { in: ['PENDING', 'PROCESSING'] } }
where: { id: { notIn: [1, 2, 3] } }

// Relation filters
where: { author: { email: 'alice@example.com' } }
where: { posts: { some: { published: true } } }
where: { posts: { every: { published: true } } }
where: { posts: { none: { published: false } } }

// AND / OR / NOT
where: {
  AND: [
    { status: 'ACTIVE' },
    { OR: [{ role: 'ADMIN' }, { email: { endsWith: '@company.com' } }] }
  ]
}

import { prisma } from '../lib/prisma';
import { Prisma } from '@prisma/client';

export class UserService {
  async findAll(page: number, pageSize: number) {
    const [users, total] = await prisma.$transaction([
      prisma.user.findMany({
        skip: (page - 1) * pageSize,
        take: pageSize,
        select: { id: true, name: true, email: true, role: true, createdAt: true },
        orderBy: { createdAt: 'desc' },
      }),
      prisma.user.count(),
    ]);
    return { data: users, total, page, pageSize };
  }

  async findById(id: number) {
    return prisma.user.findUnique({
      where: { id },
      include: { orders: { take: 5, orderBy: { createdAt: 'desc' } } },
    });
  }

  async create(data: Prisma.UserCreateInput) {
    return prisma.user.create({ data });
  }

  async update(id: number, data: Prisma.UserUpdateInput) {
    try {
      return await prisma.user.update({ where: { id }, data });
    } catch (e) {
      if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === 'P2025') {
        return null;  // record not found
      }
      throw e;
    }
  }

  async delete(id: number): Promise<boolean> {
    try {
      await prisma.user.delete({ where: { id } });
      return true;
    } catch (e) {
      if (e instanceof Prisma.PrismaClientKnownRequestError && e.code === 'P2025') {
        return false;
      }
      throw e;
    }
  }
}

Install:         npm install prisma @prisma/client
Init:            npx prisma init --datasource-provider postgresql
Migrate:         npx prisma migrate dev --name description
Studio:          npx prisma studio
Client:          new PrismaClient() — use singleton
Create:          prisma.model.create({ data: {} })
Read:            prisma.model.findUnique/findFirst/findMany
Update:          prisma.model.update({ where: {}, data: {} })
Delete:          prisma.model.delete({ where: {} })
Pagination:      skip: (page-1)*size, take: size
Include:         include: { relation: true }
Select:          select: { field: true }
Transaction:     prisma.$transaction([...]) or prisma.$transaction(async tx => {})
Error P2025:     Record not found