Back to blog

Deep Dive: Database & ORMs with TypeScript

typescriptdatabaseprismadrizzlepostgresql
Deep Dive: Database & ORMs with TypeScript

You know how to build APIs in TypeScript. Now comes the layer everything depends on: the database. Choosing the wrong abstraction, missing an index, or triggering N+1 queries can silently kill your application's performance and reliability.

This post is a practical guide to the entire database stack in TypeScript — from ORM selection through schema design, migrations, relations, transactions, and query optimization.

Prerequisite: Deep Dive: Node.js API Development Time commitment: 2–3 hours

What You'll Learn

✅ Understand the ORM landscape: Prisma vs Drizzle vs raw SQL
✅ Design schemas with Prisma and Drizzle with full type safety
✅ Run migrations safely in development and production
✅ Query relations without N+1 problems
✅ Write complex queries with type-safe raw SQL
✅ Use transactions for data integrity
✅ Optimize queries with indexes and query analysis
✅ Configure connection pooling for production

The TypeScript Database Landscape

PrismaDrizzleKyselyRaw SQL
Type Safety✅ Auto-generated✅ Inferred from schema✅ Query-level⚠️ Manual
Bundle SizeLarge (query engine)SmallSmallMinimal
MigrationsPrisma MigrateDrizzle KitManual/third-partyManual
Raw SQL$queryRawsql templateNativeNative
Edge/Serverless⚠️ Needs adapter✅ Native✅✅
Learning CurveLowMediumMediumHigh

When to choose what:

  • Prisma: Most teams, relational apps, need for speed of development
  • Drizzle: Serverless/edge, want SQL transparency, bundle size concerns
  • Raw SQL / Kysely: Complex reporting queries, DBA-level control needed

This post covers Prisma and Drizzle — the two most widely adopted in the TypeScript ecosystem — with raw SQL patterns throughout.

Part 1: Prisma

Installation and Setup

npm install prisma @prisma/client
npx prisma init  # Creates prisma/schema.prisma and .env

Configure your database URL in .env:

DATABASE_URL="postgresql://user:password@localhost:5432/myapp?schema=public"

Defining Your Schema

The Prisma schema is your source of truth. Types are generated from it automatically.

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
  posts    Post[]
  profile  Profile?
  comments Comment[]
 
  @@index([email])
}
 
model Profile {
  id     String  @id @default(cuid())
  bio    String?
  userId String  @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
}
 
model Post {
  id          String    @id @default(cuid())
  title       String
  content     String
  published   Boolean   @default(false)
  publishedAt DateTime?
  authorId    String
  author      User      @relation(fields: [authorId], references: [id])
  tags        Tag[]
  comments    Comment[]
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
 
  @@index([authorId])
  @@index([published, publishedAt(sort: Desc)])
}
 
model Comment {
  id        String   @id @default(cuid())
  content   String
  authorId  String
  postId    String
  author    User     @relation(fields: [authorId], references: [id])
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now())
 
  @@index([postId])
}
 
model Tag {
  id    String @id @default(cuid())
  name  String @unique
  posts Post[]
}
 
enum Role {
  USER
  ADMIN
  MODERATOR
}

Running Migrations

# Development: create and apply migration
npx prisma migrate dev --name add-user-role
 
# Production: apply existing migrations (never generates new ones)
npx prisma migrate deploy
 
# Inspect current DB state (useful when schema drifts)
npx prisma db pull
 
# Push schema changes without migration history (prototyping only)
npx prisma db push

Migration file structure:

prisma/
  migrations/
    20240120_init/
      migration.sql
    20240125_add-user-role/
      migration.sql
  schema.prisma

Production rule: Always run prisma migrate deploy (not dev) in CI/CD pipelines. migrate dev is for development only.

Setting Up the Client

Create a singleton to avoid exhausting the connection pool:

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';
 
const globalForPrisma = global as unknown as { prisma: PrismaClient };
 
export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  });
 
if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

Why a singleton? In development with hot reloading (Next.js, ts-node-dev), without this pattern you'd create a new PrismaClient on every hot reload and quickly exhaust the database connection pool.

CRUD Operations

import { prisma } from './lib/prisma';
 
// CREATE
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    profile: {
      create: { bio: 'Software engineer' }, // Nested create
    },
  },
  include: { profile: true }, // Include relation in response
});
// user is fully typed: User & { profile: Profile | null }
 
// READ — single record
const found = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
  select: {
    id: true,
    email: true,
    name: true,
    // profile: false — NOT selected (sensitive fields excluded)
  },
});
 
// READ — multiple with filtering
const publishedPosts = await prisma.post.findMany({
  where: {
    published: true,
    author: {
      role: 'USER',       // Filter by nested relation
    },
    publishedAt: {
      gte: new Date('2024-01-01'), // Date range
    },
  },
  orderBy: { publishedAt: 'desc' },
  take: 10,   // LIMIT
  skip: 0,    // OFFSET (for pagination)
  include: {
    author: { select: { id: true, name: true } },
    tags: true,
    _count: { select: { comments: true } }, // Count without loading all comments
  },
});
 
// UPDATE
const updated = await prisma.user.update({
  where: { id: user.id },
  data: { name: 'Alice Smith' },
});
 
// UPSERT — create or update
const upserted = await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice Updated' },
  create: { email: 'alice@example.com', name: 'Alice New' },
});
 
// DELETE
await prisma.user.delete({ where: { id: user.id } });
 
// Soft delete pattern (if you add deletedAt to schema)
await prisma.user.update({
  where: { id: user.id },
  data: { deletedAt: new Date() },
});

The N+1 Problem and How Prisma Solves It

The N+1 problem is one of the most common performance killers in web apps. It happens when you load a list of records and then query for each one's relations separately.

// ❌ N+1 PROBLEM: 1 query for posts + N queries for each author
const posts = await prisma.post.findMany({ take: 20 });
 
for (const post of posts) {
  // This fires a separate DB query for EVERY post!
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
  console.log(`${post.title} by ${author?.name}`);
}
// Total: 21 queries for 20 posts
// ✅ SOLVED: include fetches all relations in 1-2 queries
const posts = await prisma.post.findMany({
  take: 20,
  include: {
    author: { select: { id: true, name: true, email: true } },
    tags: true,
    _count: { select: { comments: true } },
  },
});
// Prisma batches relation queries — typically 2-3 total queries
 
// ✅ ALTERNATIVE: select gives you more precise control
const posts2 = await prisma.post.findMany({
  take: 20,
  select: {
    id: true,
    title: true,
    publishedAt: true,
    author: { select: { name: true } },      // Only name
    _count: { select: { comments: true } },
    tags: { select: { name: true } },         // Only tag names
  },
});

Transactions

Use transactions when multiple operations must succeed or fail together:

// Sequential transactions (good for dependent operations)
const result = await prisma.$transaction(async (tx) => {
  // All operations use `tx`, not `prisma`
  const sender = await tx.user.findUnique({
    where: { id: senderId },
    select: { balance: true },
  });
 
  if (!sender || sender.balance < amount) {
    throw new Error('Insufficient balance'); // Rolls back automatically
  }
 
  const updatedSender = await tx.user.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } },
  });
 
  const updatedReceiver = await tx.user.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } },
  });
 
  await tx.transaction.create({
    data: { senderId, receiverId, amount, status: 'COMPLETED' },
  });
 
  return { sender: updatedSender, receiver: updatedReceiver };
});
 
// Batch transactions (for independent operations, runs in parallel)
const [users, posts] = await prisma.$transaction([
  prisma.user.findMany({ take: 5 }),
  prisma.post.findMany({ take: 5, where: { published: true } }),
]);

Raw SQL with Prisma

When the ORM abstraction isn't enough:

// Type-safe raw queries with tagged template literals
const users = await prisma.$queryRaw<{ id: string; email: string; postCount: bigint }[]>`
  SELECT
    u.id,
    u.email,
    COUNT(p.id) AS "postCount"
  FROM "User" u
  LEFT JOIN "Post" p ON p."authorId" = u.id
  WHERE u."createdAt" > ${new Date('2024-01-01')}
  GROUP BY u.id, u.email
  ORDER BY "postCount" DESC
  LIMIT ${10}
`;
 
// Note: Prisma returns BigInt for COUNT — convert if needed
const formatted = users.map(u => ({
  ...u,
  postCount: Number(u.postCount),
}));
 
// Raw mutations (no type inference — use carefully)
await prisma.$executeRaw`
  UPDATE "Post"
  SET "publishedAt" = NOW()
  WHERE "published" = true AND "publishedAt" IS NULL
`;

Part 2: Drizzle ORM

Drizzle takes a different approach: it's SQL-first, meaning your TypeScript definitions look like SQL and the types are inferred from your schema definitions — no separate schema file, no code generation step.

Installation and Setup

npm install drizzle-orm postgres
npm install -D drizzle-kit
// drizzle.config.ts
import type { Config } from 'drizzle-kit';
 
export default {
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Defining Your Schema

// src/db/schema.ts
import {
  pgTable, pgEnum, text, varchar, boolean, timestamp,
  uniqueIndex, index, primaryKey
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const roleEnum = pgEnum('role', ['USER', 'ADMIN', 'MODERATOR']);
 
export const users = pgTable('users', {
  id:        text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  email:     varchar('email', { length: 255 }).notNull().unique(),
  name:      varchar('name', { length: 255 }),
  role:      roleEnum('role').default('USER').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}));
 
export const posts = pgTable('posts', {
  id:          text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  title:       varchar('title', { length: 500 }).notNull(),
  content:     text('content').notNull(),
  published:   boolean('published').default(false).notNull(),
  publishedAt: timestamp('published_at'),
  authorId:    text('author_id').notNull().references(() => users.id),
  createdAt:   timestamp('created_at').defaultNow().notNull(),
  updatedAt:   timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  authorIdx:    index('posts_author_idx').on(table.authorId),
  publishedIdx: index('posts_published_idx').on(table.published, table.publishedAt),
}));
 
export const comments = pgTable('comments', {
  id:        text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  content:   text('content').notNull(),
  authorId:  text('author_id').notNull().references(() => users.id),
  postId:    text('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  postIdx: index('comments_post_idx').on(table.postId),
}));
 
// Define relations for join queries
export const usersRelations = relations(users, ({ many, one }) => ({
  posts:    many(posts),
  comments: many(comments),
}));
 
export const postsRelations = relations(posts, ({ one, many }) => ({
  author:   one(users, { fields: [posts.authorId], references: [users.id] }),
  comments: many(comments),
}));
 
export const commentsRelations = relations(comments, ({ one }) => ({
  author: one(users, { fields: [comments.authorId], references: [users.id] }),
  post:   one(posts, { fields: [comments.postId], references: [posts.id] }),
}));
 
// Export types inferred from schema
export type User    = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post    = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Database Client Setup

// src/db/index.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
 
const connectionString = process.env.DATABASE_URL!;
 
// For query execution (disable prefetch for serverless)
const client = postgres(connectionString, {
  max: process.env.NODE_ENV === 'production' ? 10 : 1,
});
 
export const db = drizzle(client, { schema });
export type Database = typeof db;

Migrations with Drizzle Kit

# Generate migration files from schema changes
npx drizzle-kit generate
 
# Apply migrations to the database
npx drizzle-kit migrate
 
# View schema in Drizzle Studio (web UI)
npx drizzle-kit studio

Querying with Drizzle

Drizzle's API maps closely to SQL, which makes it easy to reason about what query is being executed:

import { db } from './db';
import { users, posts, comments } from './db/schema';
import { eq, and, gte, desc, count, isNull, like, inArray } from 'drizzle-orm';
 
// INSERT
const [newUser] = await db
  .insert(users)
  .values({ email: 'alice@example.com', name: 'Alice' })
  .returning(); // Returns the inserted row
// newUser is typed as User
 
// SELECT with filtering
const publishedPosts = await db
  .select({
    id:          posts.id,
    title:       posts.title,
    publishedAt: posts.publishedAt,
    authorName:  users.name,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(
    and(
      eq(posts.published, true),
      gte(posts.publishedAt, new Date('2024-01-01'))
    )
  )
  .orderBy(desc(posts.publishedAt))
  .limit(10);
 
// UPDATE
await db
  .update(users)
  .set({ name: 'Alice Smith', updatedAt: new Date() })
  .where(eq(users.id, userId));
 
// DELETE
await db.delete(comments).where(eq(comments.postId, postId));
 
// Query with relations (using the relational query API)
const userWithPosts = await db.query.users.findFirst({
  where: eq(users.email, 'alice@example.com'),
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: desc(posts.createdAt),
      limit: 5,
      with: {
        comments: { limit: 3 },
      },
    },
  },
});
// Fully typed result including nested relations

Aggregations and Complex Queries

// Count with GROUP BY
const postCounts = await db
  .select({
    authorId:  posts.authorId,
    postCount: count(posts.id),
  })
  .from(posts)
  .where(eq(posts.published, true))
  .groupBy(posts.authorId)
  .orderBy(desc(count(posts.id)));
 
// Subqueries
import { sql } from 'drizzle-orm';
 
const activeAuthors = await db
  .select({
    id:    users.id,
    email: users.email,
    name:  users.name,
    publishedPostCount: sql<number>`
      (SELECT COUNT(*) FROM ${posts}
       WHERE ${posts.authorId} = ${users.id}
       AND ${posts.published} = true)
    `.as('published_post_count'),
  })
  .from(users)
  .having(sql`published_post_count > 0`);

Transactions with Drizzle

const result = await db.transaction(async (tx) => {
  const [sender] = await tx
    .select({ balance: users.balance })
    .from(users)
    .where(eq(users.id, senderId))
    .for('update'); // SELECT FOR UPDATE — row-level lock
 
  if (!sender || sender.balance < amount) {
    tx.rollback(); // Explicitly rollback
  }
 
  await tx
    .update(users)
    .set({ balance: sql`balance - ${amount}` })
    .where(eq(users.id, senderId));
 
  await tx
    .update(users)
    .set({ balance: sql`balance + ${amount}` })
    .where(eq(users.id, receiverId));
 
  return { success: true };
});

Part 3: Query Optimization

Good schema design and correct ORM usage gets you far. But production apps need deliberate optimization.

Understanding the Query Plan

Always check what queries your ORM generates. In PostgreSQL:

-- Run EXPLAIN ANALYZE to see the query plan
EXPLAIN ANALYZE
SELECT u.email, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id;
 
-- Look for:
-- Seq Scan (bad on large tables) → needs an index
-- Index Scan (good)
-- Hash Join / Nested Loop (understand the cost)

With Prisma, enable query logging in development:

new PrismaClient({ log: ['query'] })
// Every SQL query is printed to console

With Drizzle, it's transparent — you write SQL-like code, you know what runs.

Indexing Strategy

Indexes are the most impactful optimization you can make. Here's when to add them:

// Prisma: add indexes to your schema
model Post {
  // ...
  @@index([authorId])                          // Foreign key (always index!)
  @@index([published, publishedAt(sort: Desc)]) // Composite: filter + sort
  @@index([title])                             // If you search by title
}
// Drizzle: define indexes in table definition
export const posts = pgTable('posts', {
  // column definitions...
}, (table) => ({
  authorIdx:    index('posts_author_idx').on(table.authorId),
  publishedIdx: index('posts_pub_idx').on(table.published, table.publishedAt),
  // For full-text search:
  titleSearchIdx: index('posts_title_search_idx')
    .on(table.title)
    .using('gin', sql`to_tsvector('english', ${table.title})`),
}));

Index rules of thumb:

  • Always index foreign keys (Prisma and Drizzle do NOT do this automatically for you)
  • Index columns used in WHERE clauses that filter large tables
  • Use composite indexes when you filter by multiple columns together
  • Index columns used in ORDER BY for large result sets
  • Don't over-index — each index slows down writes

Pagination: Offset vs Cursor

// ❌ OFFSET pagination gets slow on large datasets
// Database must scan and discard `skip` rows every time
const page3 = await prisma.post.findMany({
  skip: 200,   // page 3 of 100 per page — scans 200 rows first
  take: 100,
  orderBy: { createdAt: 'desc' },
});
 
// ✅ CURSOR pagination is O(1) — always fast
// "Give me items after this cursor"
const afterCursor = await prisma.post.findMany({
  take: 100,
  cursor: { id: lastSeenId },   // Start from where we left off
  skip: 1,                       // Skip the cursor itself
  orderBy: { createdAt: 'desc' },
});
 
// Drizzle cursor pagination
const { lastId } = req.query;
 
const results = await db
  .select()
  .from(posts)
  .where(lastId ? sql`${posts.id} < ${lastId}` : undefined)
  .orderBy(desc(posts.createdAt))
  .limit(100);
 
const nextCursor = results.length === 100 ? results[99].id : null;

Select Only What You Need

// ❌ Loads ALL columns including large content field
const posts = await prisma.post.findMany({ take: 20 });
 
// ✅ Select only what the listing page needs
const posts = await prisma.post.findMany({
  take: 20,
  select: {
    id: true,
    title: true,
    publishedAt: true,
    author: { select: { name: true } },
    _count: { select: { comments: true } },
    // content: NOT included — saves bandwidth on large text fields
  },
});

Connection Pooling

Each Node.js process maintains a pool of database connections. Too few → requests queue. Too many → database overwhelmed.

// Prisma: configure pool size
new PrismaClient({
  datasources: {
    db: {
      url: `${process.env.DATABASE_URL}?connection_limit=10&pool_timeout=20`,
    },
  },
});
 
// Drizzle (postgres.js): configure pool
const client = postgres(process.env.DATABASE_URL!, {
  max:         10,   // Max connections
  idle_timeout: 20,  // Close idle connections after 20s
  connect_timeout: 10,
});

For serverless/edge (Vercel, Cloudflare Workers):

Use a connection pooler — either Neon's built-in pooling, PgBouncer, or Supabase's pgBouncer:

// Neon serverless driver — no persistent connections needed
import { neon } from '@neondatabase/serverless';
 
const sql = neon(process.env.DATABASE_URL!);
const result = await sql`SELECT * FROM users WHERE id = ${userId}`;
 
// Or with Drizzle on top:
import { drizzle } from 'drizzle-orm/neon-http';
const db = drizzle(sql, { schema });

Part 4: Practical Patterns

Repository Pattern

Centralizing database access makes testing easier and keeps business logic out of queries:

// src/repositories/post.repository.ts
import { prisma } from '../lib/prisma';
import type { Prisma } from '@prisma/client';
 
export class PostRepository {
  async findPublished(options: {
    cursor?: string;
    limit?: number;
    authorId?: string;
  }) {
    const { cursor, limit = 20, authorId } = options;
 
    return prisma.post.findMany({
      take: limit,
      ...(cursor && { cursor: { id: cursor }, skip: 1 }),
      where: {
        published: true,
        ...(authorId && { authorId }),
      },
      orderBy: { publishedAt: 'desc' },
      select: {
        id:          true,
        title:       true,
        publishedAt: true,
        author:      { select: { id: true, name: true } },
        _count:      { select: { comments: true } },
      },
    });
  }
 
  async findById(id: string) {
    return prisma.post.findUnique({
      where: { id },
      include: {
        author:   { select: { id: true, name: true } },
        tags:     true,
        comments: {
          orderBy: { createdAt: 'desc' },
          take: 50,
          include: { author: { select: { id: true, name: true } } },
        },
      },
    });
  }
 
  async create(data: Prisma.PostCreateInput) {
    return prisma.post.create({ data });
  }
 
  async publish(id: string) {
    return prisma.post.update({
      where: { id },
      data: { published: true, publishedAt: new Date() },
    });
  }
}
 
export const postRepository = new PostRepository();

Soft Deletes

Instead of permanently deleting rows (breaking foreign keys, losing audit trails):

// Add to any model that needs soft delete
model Post {
  // ...
  deletedAt DateTime?
 
  @@index([deletedAt]) // For filtering out deleted records efficiently
}
// Always filter out deleted records
const activePosts = await prisma.post.findMany({
  where: {
    published: true,
    deletedAt: null, // ← Exclude soft-deleted
  },
});
 
// Soft delete
async function softDelete(id: string) {
  await prisma.post.update({
    where: { id },
    data: { deletedAt: new Date() },
  });
}
 
// Hard delete (admin only, with confirmation)
async function hardDelete(id: string) {
  await prisma.post.delete({ where: { id } });
}

Seeding the Database

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
import bcrypt from 'bcrypt';
 
const prisma = new PrismaClient();
 
async function main() {
  // Use upsert so seed is idempotent (safe to run multiple times)
  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email:        'admin@example.com',
      name:         'Admin User',
      role:         'ADMIN',
      passwordHash: await bcrypt.hash('dev-password', 10),
    },
  });
 
  await prisma.post.createMany({
    data: [
      { title: 'First Post', content: '...', published: true, authorId: admin.id },
      { title: 'Draft Post', content: '...', published: false, authorId: admin.id },
    ],
    skipDuplicates: true,
  });
 
  console.log('Seed completed');
}
 
main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());
// package.json — register the seed script
{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}
npx prisma db seed

Part 5: Testing Database Code

Database code is notoriously hard to test. Here are the patterns that work.

Using a Test Database

// jest.config.ts or vitest.config.ts
export default {
  globalSetup:    './test/setup.ts',
  globalTeardown: './test/teardown.ts',
};
 
// test/setup.ts
import { execSync } from 'child_process';
 
export async function setup() {
  // Point to a test database
  process.env.DATABASE_URL = process.env.TEST_DATABASE_URL;
 
  // Run migrations on test DB
  execSync('npx prisma migrate deploy', { stdio: 'inherit' });
}
 
// test/teardown.ts
export async function teardown() {
  // Clean up — optional if you use a fresh DB per test run
}

Resetting Between Tests

// test/helpers/db.ts
import { prisma } from '../../lib/prisma';
 
export async function cleanDatabase() {
  // Delete in reverse dependency order (respect foreign keys)
  await prisma.$transaction([
    prisma.comment.deleteMany(),
    prisma.post.deleteMany(),
    prisma.profile.deleteMany(),
    prisma.user.deleteMany(),
  ]);
}
 
// In your test file:
import { cleanDatabase } from '../helpers/db';
 
beforeEach(async () => {
  await cleanDatabase();
});
 
afterAll(async () => {
  await prisma.$disconnect();
});

Integration Test Example

// test/post.repository.test.ts
import { postRepository } from '../../src/repositories/post.repository';
import { prisma } from '../../src/lib/prisma';
import { cleanDatabase } from '../helpers/db';
 
describe('PostRepository', () => {
  beforeEach(cleanDatabase);
  afterAll(() => prisma.$disconnect());
 
  describe('findPublished', () => {
    it('returns only published posts', async () => {
      const user = await prisma.user.create({
        data: { email: 'test@example.com', name: 'Test User' },
      });
 
      await prisma.post.createMany({
        data: [
          { title: 'Published',  content: '...', published: true,  authorId: user.id, publishedAt: new Date() },
          { title: 'Draft',      content: '...', published: false, authorId: user.id },
        ],
      });
 
      const posts = await postRepository.findPublished({});
 
      expect(posts).toHaveLength(1);
      expect(posts[0].title).toBe('Published');
    });
 
    it('supports cursor-based pagination', async () => {
      const user = await prisma.user.create({
        data: { email: 'test@example.com', name: 'Test User' },
      });
 
      const created = await prisma.post.createManyAndReturn({
        data: Array.from({ length: 5 }, (_, i) => ({
          title:       `Post ${i}`,
          content:     '...',
          published:   true,
          publishedAt: new Date(Date.now() - i * 1000),
          authorId:    user.id,
        })),
      });
 
      const firstPage = await postRepository.findPublished({ limit: 2 });
      expect(firstPage).toHaveLength(2);
 
      const secondPage = await postRepository.findPublished({
        limit:  2,
        cursor: firstPage[1].id,
      });
      expect(secondPage).toHaveLength(2);
      expect(secondPage[0].id).not.toBe(firstPage[1].id);
    });
  });
});

Summary and Key Takeaways

Learning Outcomes:
✅ Prisma is the right choice for most TypeScript projects — great DX, auto-generated types, strong ecosystem
✅ Drizzle shines in serverless/edge environments and when you want SQL transparency without overhead
✅ N+1 queries are always solved the same way: use include/join to load relations in bulk, not per-row
✅ Index every foreign key — ORMs don't do this automatically and unindexed FKs cause full table scans
✅ Cursor pagination is always faster than offset pagination for large datasets
✅ Connection pooling configuration matters for production — match pool size to your DB's max connections
✅ Integration tests against a real test database are the most valuable tests for database code
✅ Repository pattern keeps query logic centralized and business logic clean

The database layer is the foundation of your application's correctness and performance. Get the schema right, index deliberately, and let the ORM handle the repetitive parts while you reach for raw SQL when you need precision.

What's Next

  • TS-10: Deep Dive: Testing & DevOps — the final post in the TypeScript series, covering Vitest, Playwright E2E, CI/CD with GitHub Actions, and Docker deployment
  • DB Series: The upcoming SQL & NoSQL roadmap will go deeper on PostgreSQL internals, MongoDB, Redis, and database scaling patterns

This is post #9 in the TypeScript Full-Stack Roadmap series.

📬 Subscribe to Newsletter

Get the latest blog posts delivered to your inbox every week. No spam, unsubscribe anytime.

We respect your privacy. Unsubscribe at any time.

💬 Comments

Sign in to leave a comment

We'll never post without your permission.