Testing Drizzle ORM Components: From Unit to E2E Tests

Testing Drizzle ORM Components: From Unit to E2E Tests

Drizzle ORM has quickly become a favorite among TypeScript developers for its lightweight, SQL-first approach to database interactions. But like any data layer, it needs rigorous testing to ensure your application behaves correctly under real-world conditions. This tutorial walks you through a complete testing strategy for Drizzle ORM components — from isolated unit tests to full end-to-end workflows.

What Is Drizzle ORM Testing?

Testing Drizzle ORM components means validating that your schema definitions, query builders, migrations, and data access layers work as expected. Because Drizzle is designed to be close to SQL, much of your testing focuses on verifying that the generated queries match your intent and that your schema constraints enforce the right rules at the database level.

There are three primary levels of testing you should consider:

• Unit tests — Validate schema shapes, query construction, and pure logic without hitting a real database.
• Integration tests — Run real queries against a test database (often in-memory or containerized) to verify behavior.
• End-to-end (E2E) tests — Exercise the entire stack, from API endpoints down to the database, ensuring everything works together.

Why Testing Drizzle Components Matters

ORMs abstract away SQL, but that abstraction can hide subtle bugs. A misconfigured relation, a missing index, or an incorrect join can silently return wrong data. Without tests, these issues often surface only in production. Testing your Drizzle components gives you confidence that:

• Schema migrations produce the expected table structures.
• Queries return the correct shape and content of data.
• Constraints like unique keys and foreign keys are enforced.
• Transactions roll back correctly on failure.
• Relations and joins resolve as designed.

Additionally, because Drizzle generates TypeScript types from your schema, testing helps catch cases where the runtime behavior diverges from the static types.

Setting Up Your Testing Environment

Before writing tests, you need a solid foundation. We will use vitest as the test runner, but the concepts apply equally to Jest or Mocha. For integration and E2E tests, we will use a containerized PostgreSQL database via testcontainers, which spins up a fresh database for each test run.

Install the necessary dependencies:

npm install -D vitest testcontainers @testcontainers/postgresql drizzle-orm pg

Create a vitest.config.ts file:

import { defineConfig } from 'vitest/config';

export default defineConfig({
  test: {
    globals: true,
    environment: 'node',
    testTimeout: 30000,
  },
});

Next, define a sample schema that we will use throughout this tutorial. Create src/schema.ts:

import { pgTable, serial, text, timestamp, integer, boolean } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  userId: integer('user_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  content: text('content'),
  published: boolean('published').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Unit Testing Drizzle Schemas and Queries

Unit tests focus on logic that can be validated without a database connection. For Drizzle, this includes verifying schema definitions, testing repository methods that build queries, and checking that query builders produce the expected SQL.

Testing Schema Definitions

You can inspect the structure of your schema objects to ensure columns, types, and constraints are defined correctly. Drizzle exposes metadata about each table and column that you can assert against.

import { describe, it, expect } from 'vitest';
import { users, posts } from '../src/schema';

describe('Schema definitions', () => {
  it('users table has the expected columns', () => {
    const columns = Object.keys(users);
    expect(columns).toEqual(
      expect.arrayContaining(['id', 'email', 'name', 'createdAt'])
    );
  });

  it('email column is unique and not null', () => {
    const emailColumn = users.email;
    expect(emailColumn.notNull).toBe(true);
    expect(emailColumn.unique).toBeDefined();
  });

  it('posts table references users with cascade delete', () => {
    const userIdColumn = posts.userId;
    expect(userIdColumn.notNull).toBe(true);
    // The reference config is stored internally; verify the foreign key exists
    expect(userIdColumn.references).toBeDefined();
  });

  it('published column defaults to false', () => {
    expect(posts.published.default).toBe(false);
  });
});

These tests are fast and do not require any external services. They are especially useful when you refactor schemas or add new columns, as they catch accidental removals or type changes.

Testing Query Construction

Drizzle lets you inspect the SQL that a query will produce using the .toSQL() method. This is powerful for unit testing because you can verify the exact query string and parameters without executing it.

import { describe, it, expect } from 'vitest';
import { eq, and, gt } from 'drizzle-orm';
import { db } from '../src/db';
import { users, posts } from '../src/schema';

describe('Query construction', () => {
  it('selects all users with a where clause', () => {
    const query = db
      .select()
      .from(users)
      .where(eq(users.email, 'test@example.com'))
      .toSQL();

    expect(query.sql).toContain('select');
    expect(query.sql).toContain('from "users"');
    expect(query.params).toContain('test@example.com');
  });

  it('joins posts with users', () => {
    const query = db
      .select({
        postId: posts.id,
        title: posts.title,
        userName: users.name,
      })
      .from(posts)
      .innerJoin(users, eq(posts.userId, users.id))
      .where(and(eq(posts.published, true), gt(posts.id, 10)))
      .toSQL();

    expect(query.sql).toContain('inner join');
    expect(query.sql).toContain('"posts"');
    expect(query.sql).toContain('"users"');
    expect(query.params).toContain(true);
    expect(query.params).toContain(10);
  });
});

By asserting on the generated SQL, you catch issues like missing WHERE clauses, incorrect join types, or wrong parameter binding — all without touching a database.

Testing Repository Functions

A common pattern is to wrap Drizzle queries in repository functions. You can unit test these by mocking the database client. Here is an example repository and its tests.

// src/repositories/userRepository.ts
import { db } from '../src/db';
import { users } from '../src/schema';
import { eq } from 'drizzle-orm';

export async function findUserByEmail(email: string) {
  const result = await db
    .select()
    .from(users)
    .where(eq(users.email, email))
    .limit(1);
  return result[0] ?? null;
}

export async function createUser(email: string, name: string) {
  const [user] = await db
    .insert(users)
    .values({ email, name })
    .returning();
  return user;
}

Now test it with a mock:

import { describe, it, expect, vi } from 'vitest';
import { findUserByEmail, createUser } from '../src/repositories/userRepository';

vi.mock('../src/db', () => ({
  db: {
    select: vi.fn(() => ({
      from: vi.fn(() => ({
        where: vi.fn(() => ({
          limit: vi.fn(() => [{ id: 1, email: 'a@b.com', name: 'Alice' }]),
        })),
      })),
    })),
    insert: vi.fn(() => ({
      values: vi.fn(() => ({
        returning: vi.fn(() => [{ id: 2, email: 'b@c.com', name: 'Bob' }]),
      })),
    })),
  },
}));

describe('User repository', () => {
  it('findUserByEmail returns the user if found', async () => {
    const user = await findUserByEmail('a@b.com');
    expect(user).not.toBeNull();
    expect(user?.name).toBe('Alice');
  });

  it('createUser returns the created user', async () => {
    const user = await createUser('b@c.com', 'Bob');
    expect(user.id).toBe(2);
    expect(user.email).toBe('b@c.com');
  });
});

While mocking works, it can become brittle as queries grow more complex. For that reason, integration tests against a real database are often more valuable.

Integration Testing with a Real Database

Integration tests run actual queries against a database. The most reliable approach is to use testcontainers to spin up a fresh PostgreSQL instance for each test suite. This ensures isolation and reproducibility.

Creating a Test Database Helper

Create tests/setup.ts to manage the container lifecycle:

import { PostgreSqlContainer } from '@testcontainers/postgresql';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import * as schema from '../src/schema';

let pool: Pool;
let db: ReturnType;

export async function setupTestDatabase() {
  const container = await new PostgreSqlContainer('postgres:16-alpine')
    .withDatabase('testdb')
    .start();

  pool = new Pool({
    host: container.getHost(),
    port: container.getPort(),
    database: container.getDatabase(),
    user: container.getUsername(),
    password: container.getPassword(),
  });

  db = drizzle(pool, { schema });

  // Run migrations
  await migrate(db, { migrationsFolder: './drizzle' });

  return { db, container, pool };
}

export async function teardownTestDatabase(
  container: Awaited>,
  poolInstance: Pool
) {
  await poolInstance.end();
  await container.stop();
}

export function getDb() {
  return db;
}

Writing Integration Tests

Now write tests that insert, query, and delete data against the real database:

import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { eq } from 'drizzle-orm';
import { setupTestDatabase, teardownTestDatabase, getDb } from './setup';
import { users, posts } from '../src/schema';

let container: any;
let pool: any;

beforeAll(async () => {
  const setup = await setupTestDatabase();
  container = setup.container;
  pool = setup.pool;
});

afterAll(async () => {
  await teardownTestDatabase(container, pool);
});

describe('User and posts integration', () => {
  it('creates a user and retrieves them by email', async () => {
    const db = getDb();

    const [created] = await db
      .insert(users)
      .values({ email: 'integration@test.com', name: 'Integration User' })
      .returning();

    expect(created.email).toBe('integration@test.com');

    const [found] = await db
      .select()
      .from(users)
      .where(eq(users.email, 'integration@test.com'));

    expect(found.name).toBe('Integration User');
  });

  it('enforces unique email constraint', async () => {
    const db = getDb();

    await db
      .insert(users)
      .values({ email: 'unique@test.com', name: 'First' });

    await expect(
      db.insert(users).values({ email: 'unique@test.com', name: 'Second' })
    ).rejects.toThrow();
  });

  it('cascades delete from users to posts', async () => {
    const db = getDb();

    const [user] = await db
      .insert(users)
      .values({ email: 'cascade@test.com', name: 'Cascade User' })
      .returning();

    await db.insert(posts).values({
      userId: user.id,
      title: 'Test Post',
      content: 'Some content',
    });

    await db.delete(users).where(eq(users.id, user.id));

    const remainingPosts = await db
      .select()
      .from(posts)
      .where(eq(posts.userId, user.id));

    expect(remainingPosts).toHaveLength(0);
  });

  it('rolls back transactions on error', async () => {
    const db = getDb();

    await expect(
      db.transaction(async (tx) => {
        await tx
          .insert(users)
          .values({ email: 'tx@test.com', name: 'TX User' });

        // Force an error
        await tx
          .insert(users)
          .values({ email: 'tx@test.com', name: 'Duplicate' });
      })
    ).rejects.toThrow();

    const result = await db
      .select()
      .from(users)
      .where(eq(users.email, 'tx@test.com'));

    expect(result).toHaveLength(0);
  });
});

These tests verify real database behavior, including constraints, cascading deletes, and transaction rollbacks. They are slower than unit tests but provide far greater confidence.

Cleaning Up Between Tests

To keep tests isolated, reset the database state between test cases. A simple approach is to truncate all tables in a beforeEach hook:

import { beforeEach } from 'vitest';
import { sql } from 'drizzle-orm';
import { getDb } from './setup';

beforeEach(async () => {
  const db = getDb();
  await db.execute(sql`TRUNCATE TABLE users, posts CASCADE`);
});

This ensures each test starts with a clean slate, preventing data from one test from affecting another.

End-to-End Testing

E2E tests exercise your application from the outside in — typically through HTTP endpoints — while using a real database behind the scenes. They verify that your API, business logic, and data layer all work together correctly.

Setting Up an E2E Test with a Real Server

Assume you have an Express application. Create src/app.ts:

import express from 'express';
import { eq } from 'drizzle-orm';
import { db } from './db';
import { users, posts } from './schema';

export function createApp() {
  const app = express();
  app.use(express.json());

  app.post('/users', async (req, res) => {
    const { email, name } = req.body;
    if (!email || !name) {
      return res.status(400).json({ error: 'email and name are required' });
    }
    try {
      const [user] = await db
        .insert(users)
        .values({ email, name })
        .returning();
      res.status(201).json(user);
    } catch (err) {
      res.status(409).json({ error: 'User already exists' });
    }
  });

  app.get('/users/:id/posts', async (req, res) => {
    const userId = parseInt(req.params.id, 10);
    const result = await db
      .select()
      .from(posts)
      .where(eq(posts.userId, userId));
    res.json(result);
  });

  return app;
}

Now write an E2E test that starts the server against the test database and makes real HTTP requests:

import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import http from 'http';
import supertest from 'supertest';
import { PostgreSqlContainer } from '@testcontainers/postgresql';
import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import * as schema from '../src/schema';
import { createApp } from '../src/app';

let server: http.Server;
let container: any;
let pool: Pool;

beforeAll(async () => {
  container = await new PostgreSqlContainer('postgres:16-alpine').start();

  pool = new Pool({
    host: container.getHost(),
    port: container.getPort(),
    database: container.getDatabase(),
    user: container.getUsername(),
    password: container.getPassword(),
  });

  const db = drizzle(pool, { schema });
  await migrate(db, { migrationsFolder: './drizzle' });

  // Override the db module so the app uses the test database
  const dbModule = await import('../src/db');
  dbModule.db = db;

  const app = createApp();
  server = app.listen(0);
});

afterAll(async () => {
  server.close();
  await pool.end();
  await container.stop();
});

describe('E2E: User API', () => {
  it('creates a user via POST /users', async () => {
    const port = (server.address() as any).port;
    const res = await supertest(`http://localhost:${port}`)
      .post('/users')
      .send({ email: 'e2e@test.com', name: 'E2E User' });

    expect(res.status).toBe(201);
    expect(res.body.email).toBe('e2e@test.com');
    expect(res.body.id).toBeDefined();
  });

  it('rejects duplicate emails', async () => {
    const port = (server.address() as any).port;
    const request = supertest(`http://localhost:${port}`);

    await request.post('/users').send({ email: 'dup@test.com', name: 'First' });

    const res = await request
      .post('/users')
      .send({ email: 'dup@test.com', name: 'Second' });

    expect(res.status).toBe(409);
  });

  it('returns posts for a user', async () => {
    const port = (server.address() as any).port;
    const request = supertest(`http://localhost:${port}`);

    const createRes = await request
      .post('/users')
      .send({ email: 'posts@test.com', name: 'Posts User' });

    const userId = createRes.body.id;

    // Insert a post directly via the database
    const db = (await import('../src/db')).db;
    await db.insert(schema.posts).values({
      userId,
      title: 'My First Post',
      content: 'Hello world',
      published: true,
    });

    const res = await request.get(`/users/${userId}/posts`);

    expect(res.status).toBe(200);
    expect(res.body).toHaveLength(1);
    expect(res.body[0].title).toBe('My First Post');
  });
});

These E2E tests verify the entire request-response cycle, including how the API handles database errors, validates input, and returns data. They are the most realistic tests in your suite and provide the highest level of confidence that your application works as intended.

Best Practices

Use the Right Test Level for Each Concern

Not everything needs an E2E test. Reserve E2E tests for critical user flows. Use integration tests for repository and data access logic. Use unit tests for schema validation and query construction. This keeps your test suite fast and maintainable.

Always Use a Separate Test Database

Never run tests against your development or production database. Use containerized databases or in-memory alternatives like pg-mem for lightweight scenarios. This prevents data corruption and ensures test isolation.

Reset State Between Tests

Tests should not depend on the order in which they run. Truncate tables or recreate the schema before each test to guarantee a clean state. This eliminates flaky tests caused by leftover data.

Test Migrations, Not Just Schema

Your schema file defines the ideal state, but migrations define how you get there. Test that migrations apply cleanly and that rollback migrations work. This is especially important in production deployments.

import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { PostgreSqlContainer } from '@testcontainers/postgresql';
import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import * as schema from '../src/schema';

let container: any;
let pool: Pool;

beforeAll(async () => {
  container = await new PostgreSqlContainer('postgres:16-alpine').start();
  pool = new Pool({
    host: container.getHost(),
    port: container.getPort(),
    database: container.getDatabase(),
    user: container.getUsername(),
    password: container.getPassword(),
  });
});

afterAll(async () => {
  await pool.end();
  await container.stop();
});

describe('Migrations', () => {
  it('applies all migrations without error', async () => {
    const db = drizzle(pool, { schema });
    await expect(
      migrate(db, { migrationsFolder: './drizzle' })
    ).resolves.not.toThrow();
  });

  it('creates the users table with correct columns', async () => {
    const result = await pool.query(`
      SELECT column_name, data_type, is_nullable
      FROM information_schema.columns
      WHERE table_name = 'users'
      ORDER BY ordinal_position;
    `);

    const columns = result.rows;
    expect(columns).toHaveLength(4);
    expect(columns.find((c) => c.column_name === 'email')?.is_nullable).toBe('NO');
  });
});

Test Edge Cases and Error Paths

It is easy to test the happy path. Make sure you also test constraint violations, null values, empty results, and transaction failures. These edge cases are where most production bugs hide.

Keep Tests Independent and Parallelizable

Avoid shared mutable state between tests. If tests depend on each other, they become brittle and hard to debug. Design your tests so they can run in any order and in parallel.

Use Fixtures and Factories

Instead of manually constructing test data in every test, create reusable factories. This reduces duplication and makes tests easier to read.

// tests/factories.ts
import { getDb } from './setup';
import { users, posts } from '../src/schema';

export async function createUserFixture(overrides: Partial = {}) {
  const db = getDb();
  const [user] = await db
    .insert(users)
    .values({
      email: `user-${Date.now()}@test.com`,
      name: 'Test User',
      ...overrides,
    })
    .returning();
  return user;
}

export async function createPostFixture(userId: number, overrides: Partial = {}) {
  const db = getDb();
  const [post] = await db
    .insert(posts)
    .values({
      userId,
      title: 'Test Post',
      content: 'Test content',
      ...overrides,
    })
    .returning();
  return post;
}

Then use them in your tests:

it('retrieves a user with their posts', async () => {
  const user = await createUserFixture({ name: 'Factory User' });
  await createPostFixture(user.id, { title: 'Factory Post' });

  const db = getDb();
  const result = await db.query.users.findFirst({
    where: eq(users.id, user.id),
    with: {
      posts: true,
    },
  });

  expect(result?.posts).toHaveLength(1);
  expect(result?.posts[0].title).toBe('Factory Post');
});

Conclusion

Testing Drizzle ORM components across all three levels — unit, integration, and end-to-end — gives you a robust safety net that catches bugs early and ensures your data layer behaves predictably. Unit tests validate schema definitions and query construction in milliseconds. Integration tests verify real database behavior including constraints, cascades, and transactions. E2E tests confirm that your entire application stack works together from the HTTP boundary down to the database. By combining these approaches with best practices like test isolation, factory-based fixtures, and migration testing, you can build and maintain a Drizzle-powered application with confidence, knowing that your data layer is thoroughly validated at every level.