Shopping cart

Subtotal 0,00 د.إ Subtotal: 0,00 د.إ

View cartCheckout

Prisma Client CRUD Fundamentals

by SanjewaExpress Js
Front
Back
Right
Left
Top
Bottom
REST API

Generating & Instantiating Prisma Client

Every backend application ultimately boils down to one thing: data. Creating it, reading it, updating it, deleting it. In this blog, we’ll master Prisma Client’s CRUD operations and wire them into a fully functional REST API. By the end, you’ll have a Users API that you’d actually be comfortable shipping.

INSTANCE

Generating & Instantiating Prisma Client

Setup in 60 Seconds​

After defining your schema and running migrations, generate Prisma Client:
💻 
npx prisma generate
This generates type-safe client code in `node_modules/@prisma/client` based on your exact schema. Every model, field, and relation is reflected in the generated types.
SINGLETON
Critical for Express

The Singleton Pattern

In Express apps, each import of `PrismaClient` creates a new database connection pool. During development with hot-reload (nodemon), this causes connection exhaustion. The solution: a singleton.

📘 
// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

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

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}
In Express apps, each import of `PrismaClient` creates a new database connection pool. During development with hot-reload (nodemon), this causes connection exhaustion. The solution: a singleton.
 
Now import this `prisma` instance everywhere — <b>never</b> instantiate `PrismaClient` directly in your route files.
Pro Tip
This singleton pattern is recommended by the official Prisma docs for Next.js and Express apps: prisma.io/docs/guides/other/troubleshooting-orm/help-articles/nextjs-prisma-client-dev-practices
CREATE

CREATE Operations

`create` — Single Record
📘 
// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

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

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}
`createMany` — Bulk Insert
📘 
const result = await prisma.user.createMany({
  data: [
    { email: '[email protected]', name: 'Alice' },
    { email: '[email protected]', name: 'Bob' },
  ],
  skipDuplicates: true, // MySQL: skips rows that violate unique constraints
});
// result.count = number of inserted records
READ

READ Operations

`findUnique` — Exact Match (by `@id` or `@unique`)
📘 
const user = await prisma.user.findUnique({
  where: { email: '[email protected]' },
});
// Returns: User | null
`findFirst` — First Match (any field)
📘 
const user = await prisma.user.findFirst({
  where: { name: { contains: 'John' } },
  orderBy: { createdAt: 'desc' },
});
`findMany` — Multiple Records
📘 
const users = await prisma.user.findMany({
  where: { active: true },
  take: 10,
  skip: 0,
  orderBy: { createdAt: 'desc' },
});
Performance

`findUnique` is faster than `findFirst` — it generates an indexed lookup. Always prefer `findUnique` when querying by `@id` or `@unique` fields. (prisma.io/docs/reference/api-reference/prisma-client-reference#findunique)

UPDATE

UPDATE Operations

`update` — Single Record
📘 
const user = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Jane Doe' },
});
`updateMany` — Bulk Update
📘 
const result = await prisma.user.updateMany({
  where: { active: false },
  data: { deletedAt: new Date() },
});
// result.count = number of updated records
`upsert` — Create or Update

One of Prisma’s most powerful methods — insert if not exists, update if it does:

📘 
const user = await prisma.user.upsert({
  where: { email: '[email protected]' },
  update: { name: 'John Updated' },
  create: { email: '[email protected]', name: 'John New' },
});
DELETE

DELETE Operations

📘 
// Delete single
await prisma.user.delete({ where: { id: 1 } });

// Delete many
await prisma.user.deleteMany({
  where: { createdAt: { lt: new Date('2020-01-01') } }
});
Warning
Never run `deleteMany()` without a `where` clause in production — it deletes ALL records. Always add application-level guards for destructive operations.
SELECTING

Selecting Specific Fields

By default, Prisma returns all scalar fields. Use `select` for performance and security:
📘 
const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: {
    id: true,
    email: true,
    name: true,
    // password: omitted — never returned to clients
  },
});
Excluding Sensitive Fields (Password, Tokens)
Build a reusable utility:
📘 
// src/utils/exclude.ts
export function exclude<T, K extends keyof T>(obj: T, keys: K[]): Omit<T, K> {
  return Object.fromEntries(
    Object.entries(obj as any).filter(([k]) => !keys.includes(k as K))
  ) as Omit<T, K>;
}

// Usage
const fullUser = await prisma.user.findUnique({ where: { id: 1 } });
const safeUser = exclude(fullUser!, ['password', 'resetToken']);
REAL WORLD
Users Resource

Full CRUD REST API

Let’s put it all together. Here’s a production-ready users router:
📘 
// src/routes/users.ts
import { PrismaClient } from '../generated/prisma';
import { PrismaMariaDb } from '@prisma/adapter-mariadb';
import dotenv from 'dotenv';
dotenv.config();

const globalForPrisma = globalThis as unknown as {
    prisma: PrismaClient | undefined;
};

const adapter = new PrismaMariaDb({
    host: process.env.DATABASE_HOST,
    user: process.env.DATABASE_USER,
    password: process.env.DATABASE_PASSWORD,
    database: process.env.DATABASE_NAME,
    connectionLimit: 10,
});

export const prisma =
    globalForPrisma.prisma ??
    new PrismaClient({ adapter });

if (process.env.NODE_ENV !== 'production') {
📘 
// src/routes/users.ts
import { Router, Request, Response, NextFunction } from 'express';
import { prisma } from '../prisma/client';

const router = Router();

// GET /users
router.get('/', async (req: Request, res: Response, next: NextFunction) => {
    try {
        const users = await prisma.user.findMany({
            select: { id: true, email: true, name: true, createdAt: true },
            orderBy: { createdAt: 'desc' },
        });
        res.json({ data: users, count: users.length });
    } catch (err) {
        next(err);
    }
});

// GET /users/:id
router.get('/:id', async (req, res, next) => {
    try {
        const user = await prisma.user.findUnique({
            where: { id: Number(req.params.id) },
            select: { id: true, email: true, name: true, createdAt: true },
        });
        if (!user) return res.status(404).json({ error: 'User not found' });
        res.json(user);
    } catch (err) {
        next(err);
    }
});

// POST /users
router.post('/', async (req, res, next) => {
    try {
        const { email, name } = req.body;
        const user = await prisma.user.create({
            data: { email, name },
            select: { id: true, email: true, name: true },
        });
        res.status(201).json(user);
    } catch (err) {
        next(err);
    }
});

// PATCH /users/:id
router.patch('/:id', async (req, res, next) => {
    try {
        const { name } = req.body;
        const user = await prisma.user.update({
            where: { id: Number(req.params.id) },
            data: { name },
            select: { id: true, email: true, name: true },
        });
        res.json(user);
    } catch (err) {
        next(err);
    }
});

// DELETE /users/:id
router.delete('/:id', async (req, res, next) => {
    try {
        await prisma.user.delete({ where: { id: Number(req.params.id) } });
        res.status(204).send();
    } catch (err) {
        next(err);
    }
});

export default router;
📘 
// Register the router in `src/index.ts`:
import userRouter from './routes/users';
app.use('/users', userRouter);
In future Blog, we’ll add Zod validation and a proper global error handler. For now, this gives you the functional foundation.

Make it work, make it right, make it fast.

Kent Beck Extreme Programming Explained (1999)

Thank You for Spending Your Valuable Time

I truly appreciate you taking the time to read blog. Your valuable time means a lot to me, and I hope you found the content insightful and engaging!
Front
Back
Right
Left
Top
Bottom
FAQ's

Frequently Asked Questions

`findUnique` only works on `@id` or `@unique` fields and generates a single indexed lookup in MySQL. `findFirst` works on any field using a table scan with `WHERE`. Always prefer `findUnique` for performance when the field supports it.

Not directly — Prisma uses `INSERT IGNORE` under the hood with `skipDuplicates: true` in MySQL. For true upsert-many, loop `upsert()` calls inside a `$transaction` for atomicity.

`const count = await prisma.user.count({ where: { active: true } });` Prisma generates `SELECT COUNT(*) FROM users WHERE active = 1` — fully optimized for MySQL.

Absolutely. Import the singleton `prisma` instance in any Express middleware. Never create new `PrismaClient` instances — always use the singleton from `src/lib/prisma.ts`.

It closes all MySQL connections in the pool. In long-running Express servers, you typically never call it manually. Call it in scripts, seed files, and serverless functions after execution completes.

Blogs

Related Blogs

Comments are closed