Shopping cart

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

View cartCheckout

Input Validation and Error Handling Done Right

by SanjewaExpress Js
Front
Back
Right
Left
Top
Bottom
SKIP

The Layer Most Tutorials Skip

Every tutorial shows you how to make a database query. Very few show you <b>how to structure that layer</b> so it stays maintainable as your codebase grows.

I’ve inherited codebases where database queries were scattered across route handlers, controllers, and services — no structure, no consistency, and zero testability. This post is the guide I wish existed before I wrote my first production database layer.
We’ll cover:
  • Prisma — the schema-first, DX-focused ORM that's become the default for TypeScript teams
  • Drizzle — the lightweight, SQL-first challenger taking over in serverless and performance-critical apps
  • Raw SQL with `pg-pool` — for when you need full control
  • Repository pattern — the architecture layer that makes all three testable
  • Transactions — the part that matters most when money or data integrity is on the line
prisma_orm
PRISMA
Schema-First, Type-Safe, Battle-Tested

Prisma

Prisma ORM is a next-generation Node.js and TypeScript ORM that provides type-safe database access, migrations, and a visual data editor.

It’s built around three components: Prisma Client (type-safe query builder), Prisma Migrate (schema-driven migrations), and Prisma Studio (GUI browser for your data).

Setup
Copy to clipboard
npm install @prisma/client
npm install --save-dev prisma
npx prisma init
This creates `prisma/schema.prisma` and a `.env` file.
Define Your Schema
Copy to clipboard
// prisma/schema.prisma
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?
  role      String   @default("user")
  posts     Post[]
  createdAt DateTime @default(now())
}

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

// Run the migration
// npx prisma migrate dev --name init
Singleton Prisma Client
One critical pattern — create a single shared instance of PrismaClient:
Copy to clipboard
// src/config/prisma.ts
import { PrismaClient } from '@prisma/client';

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

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

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;
Why singleton?
Each `new PrismaClient()` creates a new connection pool. In development with hot-reloading, you can exhaust your database connections within minutes if you create a new instance per module load.
Querying With Prisma
Copy to clipboard
// Find user with their posts
const user = await prisma.user.findUnique({
  where: { email: '[email protected]' },
  include: { posts: { where: { published: true } } }
});

// Create user
const newUser = await prisma.user.create({
  data: { email: '[email protected]', name: 'Bob' }
});

// Update
await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Robert' }
});

// Delete
await prisma.user.delete({ where: { id: 1 } });
When to choose Prisma
  • Teams with mixed SQL experience
  • Projects where developer speed matters more than raw performance
  • Applications needing MongoDB alongside PostgreSQL/MySQL
drizzele_orm
DRIZZLE
The Lightweight SQL-First Alternative

Drizzle ORM

Drizzle is a newer tool explicitly built for TypeScript. It focuses on speed, simplicity, and giving you complete control over how you define and use your database. With Drizzle, you write your database schema directly in TypeScript.

Drizzle is lightweight — ~7.4kb (min+gzip), with no external dependencies. It’s serverless-ready and works with Node.js, Bun, Deno, and Cloudflare Workers.
Setup
Copy to clipboard
npm install drizzle-orm pg
npm install --save-dev drizzle-kit @types/pg
Define Schema in TypeScript
Copy to clipboard
// src/db/schema.ts
import { pgTable, serial, varchar, boolean, timestamp, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 100 }),
  role: varchar('role', { length: 50 }).default('user'),
  createdAt: timestamp('created_at').defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  published: boolean('published').default(false),
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow(),
});
No code generation step. Types update instantly as you modify the schema.
Setup Database Connection
Copy to clipboard
// src/db/index.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema.js';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });
Querying With Drizzle
Copy to clipboard
import { db } from '../db/index.js';
import { users, posts } from '../db/schema.js';
import { eq, and } from 'drizzle-orm';

// Find user
const user = await db.select().from(users).where(eq(users.email, '[email protected]'));

// Join users and their published posts
const result = await db
  .select()
  .from(users)
  .leftJoin(posts, and(eq(posts.authorId, users.id), eq(posts.published, true)));

// Insert
await db.insert(users).values({ email: '[email protected]', name: 'Bob' });

// Update
await db.update(users).set({ name: 'Robert' }).where(eq(users.id, 1));

// Delete
await db.delete(users).where(eq(users.id, 1));
When to choose Drizzle
  • Serverless or edge deployments (Cloudflare Workers, Vercel Edge)
  • Teams who are comfortable with SQL and want to stay close to it
  • Performance-critical services where bundle size matters
  • Performance-sensitive apps (real-time systems, analytics, fintech)
RAW SQL
Full Control When You Need It

Raw SQL With pg-pool

Sometimes an ORM just gets in the way. Complex reporting queries, bulk operations, or database-specific features (like PostgreSQL’s `LATERAL JOIN`) are often cleaner in raw SQL.
Copy to clipboard
npm install pg
npm install --save-dev @types/pg
Copy to clipboard
// Query with parameterized inputs (always use $1, $2 — never string interpolation)
const { rows } = await pool.query(
  'SELECT * FROM users WHERE email = $1 AND role = $2',
  ['[email protected]', 'admin']
);
Copy to clipboard
// src/config/database.ts
import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                // Max connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

pool.on('error', (err) => {
  console.error('Unexpected database error:', err);
  process.exit(-1);
});
Security
Never interpolate user input directly into SQL strings. Always use parameterized queries (`$1`, `$2`) to prevent SQL injection.
REPOSITORY
Testable Database Access

The Repository Pattern

Whether you use Prisma, Drizzle, or raw SQL, the repository pattern gives you one enormous benefit: your business logic never touches the database client directly. This makes testing trivial — mock the repository, not the database.
Copy to clipboard
// src/repositories/user.repository.ts
import { prisma } from '../config/prisma.js';
import type { User } from '@prisma/client';

export interface IUserRepository {
  findById(id: number): Promise<User | null>;
  findByEmail(email: string): Promise<User | null>;
  create(data: { email: string; name: string }): Promise<User>;
  update(id: number, data: Partial<Pick<User, 'name' | 'role'>>): Promise<User>;
  delete(id: number): Promise<void>;
}

export class UserRepository implements IUserRepository {
  async findById(id: number) {
    return prisma.user.findUnique({ where: { id } });
  }

  async findByEmail(email: string) {
    return prisma.user.findUnique({ where: { email } });
  }

  async create(data: { email: string; name: string }) {
    return prisma.user.create({ data });
  }

  async update(id: number, data: Partial<Pick<User, 'name' | 'role'>>) {
    return prisma.user.update({ where: { id }, data });
  }

  async delete(id: number) {
    await prisma.user.delete({ where: { id } });
  }
}
Use it in your service layer:
Copy to clipboard
// src/services/user.service.ts
import { UserRepository } from '../repositories/user.repository.js';
import { AppError } from '../middleware/error.js';

const userRepo = new UserRepository();

export async function getUserById(id: number) {
  const user = await userRepo.findById(id);
  if (!user) throw new AppError('User not found', 404);
  return user;
}
Now in your tests, you mock `UserRepository` — no real database required.
DATABASE
When Data Integrity Is Non-Negotiable

Database Transactions

A transaction is a group of operations that either all succeed or all fail together. This is critical for any operation that touches multiple tables.

Classic example: Deducting money from one account and adding it to another must happen atomically. If the second step fails, the first must be rolled back.

DECISION
Quick Decision Guide

Prisma vs Drizzle

Prisma Drizzle
Schema location Separate .prisma file TypeScript files
Bundle size Larger (engine binary) ~7.4kb, no deps
Query style Object API, abstracted SQL-like, explicit
Serverless Needs Accelerate/adapter Native, zero issues
Learning curve Low Low (if you know SQL)
Migrations Prisma Migrate CLI Drizzle Kit CLI
Best for Teams, DX focus Performance, edge
AVOID

Common Mistakes to Avoid

  • Creating multiple Prisma clients in a hot-reloading environment — use the singleton pattern above
  • No connection pooling for raw SQL — always use `pg.Pool`, never `pg.Client` directly in a server
  • Forgetting to `release()` a raw SQL client from the pool — connection leak that grows until your server crashes
  • Running migrations in application startup code — always run migrations as a separate deployment step

Explore project snapshots or discuss custom web solutions.

More About Me

There are only two hard things in computer science: cache invalidation and naming things.

Phil Karlton, Principal Engineer at Netscape

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

If your team has mixed SQL experience and values developer speed, start with Prisma. If you're building for serverless/edge or need predictable query performance, choose Drizzle. Both are production-ready.

Technically yes, but don't. Pick one. Mixing them adds complexity and maintenance burden.

Creating a database connection is expensive (~50–100ms). A pool maintains open connections and reuses them. Without pooling, every API request pays that cost. With pooling, requests reuse existing connections — response times drop significantly.

Yes, but it requires extra setup — Prisma Accelerate (global connection pooling) or the Neon/PlanetScale adapters. Drizzle handles serverless natively with no additional configuration.

When the query is complex enough that the ORM abstraction works against you — complex multi-table joins, window functions, `EXPLAIN ANALYZE` optimization, or database-specific features (PostgreSQL CTEs, MySQL full-text search). Use raw SQL intentionally, not as a default.

Blogs

Related Blogs

Comments are closed