Drizzle ORM Testing Guide: Unit Tests, Integration, and Transactions
Drizzle ORM has quickly become a favorite among TypeScript developers who want the type safety of a full ORM without sacrificing raw SQL control. But testing Drizzle-powered applications presents unique challenges: the query builder is deeply integrated with the database driver, transactions span multiple operations, and migrations need to stay in sync with your schema. This guide covers practical patterns for unit tests, integration tests, and transaction testing with Drizzle ORM.
Why Drizzle Testing Is Different
Drizzle sits in an interesting position. Unlike Prisma, which generates a heavily abstracted client, Drizzle gives you a thin, SQL-transparent layer. This is great for performance but means your test strategy needs to account for the fact that most of Drizzle's value is in correctly composing SQL — something you can only really verify against a real database.
That said, unit tests still have a place. You can test your business logic independently of the database by mocking Drizzle's query interface, as long as you understand where the seam is.
Setting Up Your Test Environment
Start with a dedicated test database. For local development, a Docker container works well:
docker run --name drizzle-test-db \
-e POSTGRES_PASSWORD=testpass \
-e POSTGRES_DB=testdb \
-p 5433:5432 \
-d postgres:15-alpineInstall your test dependencies:
npm install --save-dev vitest @vitest/coverage-v8 pgCreate a test database configuration that reads from environment variables:
// src/db/test-connection.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';
export function createTestDb() {
const pool = new Pool({
connectionString: process.env.TEST_DATABASE_URL ??
'postgresql://postgres:testpass@localhost:5433/testdb',
});
return drizzle(pool, { schema });
}Unit Testing: Mocking Drizzle Queries
For pure unit tests, you want to isolate your business logic from the database entirely. Drizzle's API surface — db.select(), db.insert(), db.update(), db.delete() — makes it straightforward to mock.
Here is a common pattern using Vitest:
// src/services/user.service.ts
import { db } from '../db';
import { users } from '../db/schema';
import { eq } from 'drizzle-orm';
export async function getUserById(id: number) {
const result = await db
.select()
.from(users)
.where(eq(users.id, id))
.limit(1);
return result[0] ?? null;
}
export async function createUser(data: { name: string; email: string }) {
const [user] = await db.insert(users).values(data).returning();
return user;
}// src/services/user.service.test.ts
import { describe, it, expect, vi, beforeEach } from 'vitest';
import { getUserById, createUser } from './user.service';
// Mock the db module
vi.mock('../db', () => ({
db: {
select: vi.fn(),
insert: vi.fn(),
},
}));
import { db } from '../db';
describe('UserService', () => {
beforeEach(() => {
vi.clearAllMocks();
});
it('returns user when found', async () => {
const mockUser = { id: 1, name: 'Alice', email: 'alice@example.com' };
// Chain mocking for Drizzle's fluent API
const mockChain = {
from: vi.fn().mockReturnThis(),
where: vi.fn().mockReturnThis(),
limit: vi.fn().mockResolvedValue([mockUser]),
};
vi.mocked(db.select).mockReturnValue(mockChain as any);
const result = await getUserById(1);
expect(result).toEqual(mockUser);
expect(mockChain.limit).toHaveBeenCalledWith(1);
});
it('returns null when user not found', async () => {
const mockChain = {
from: vi.fn().mockReturnThis(),
where: vi.fn().mockReturnThis(),
limit: vi.fn().mockResolvedValue([]),
};
vi.mocked(db.select).mockReturnValue(mockChain as any);
const result = await getUserById(999);
expect(result).toBeNull();
});
});The chain-mocking pattern is verbose but reliable. A cleaner alternative is to extract your data access into repository objects, which gives you a cleaner seam to mock:
// src/repositories/user.repository.ts
export class UserRepository {
constructor(private db: ReturnType<typeof createTestDb>) {}
async findById(id: number) {
const result = await this.db
.select()
.from(users)
.where(eq(users.id, id))
.limit(1);
return result[0] ?? null;
}
async create(data: { name: string; email: string }) {
const [user] = await this.db.insert(users).values(data).returning();
return user;
}
}With a repository class, your service tests can inject a mock repository instead of mocking the entire database module.
Integration Testing Against a Real Database
Integration tests are where Drizzle testing really shines. Because Drizzle is so close to SQL, running tests against a real database catches subtle issues that mocks never would — wrong join conditions, missing indexes causing timeouts, constraint violations, and migration drift.
The key to reliable integration tests is database isolation. Use one of these strategies:
Strategy 1: Transaction rollback per test
Wrap each test in a transaction and roll it back at the end. This gives you a clean state with zero truncation overhead:
// src/tests/helpers/db-test.ts
import { createTestDb } from '../../db/test-connection';
export async function withTestTransaction<T>(
fn: (db: ReturnType<typeof createTestDb>) => Promise<T>
): Promise<T> {
const db = createTestDb();
return await db.transaction(async (tx) => {
const result = await fn(tx as any);
// Always rollback — this is a test
throw new Error('ROLLBACK_TEST');
return result; // TypeScript happy
}).catch((err) => {
if (err.message === 'ROLLBACK_TEST') return undefined as T;
throw err;
});
}Strategy 2: Schema-per-test (Postgres)
Create a separate Postgres schema for each test run and drop it afterward:
export async function createIsolatedSchema(testName: string) {
const schemaName = `test_${testName}_${Date.now()}`;
const db = createTestDb();
await db.execute(sql`CREATE SCHEMA ${sql.identifier(schemaName)}`);
await runMigrations(db, schemaName);
return {
db,
cleanup: () => db.execute(
sql`DROP SCHEMA ${sql.identifier(schemaName)} CASCADE`
),
};
}Here is a full integration test using the transaction rollback approach:
// src/repositories/user.repository.integration.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { UserRepository } from './user.repository';
import * as schema from '../db/schema';
describe('UserRepository Integration', () => {
let pool: Pool;
let db: ReturnType<typeof drizzle>;
beforeAll(async () => {
pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
db = drizzle(pool, { schema });
await migrate(db, { migrationsFolder: './drizzle/migrations' });
});
afterAll(async () => {
await pool.end();
});
it('creates and retrieves a user', async () => {
await db.transaction(async (tx) => {
const repo = new UserRepository(tx as any);
const created = await repo.create({
name: 'Test User',
email: 'test@example.com',
});
expect(created.id).toBeDefined();
expect(created.email).toBe('test@example.com');
const found = await repo.findById(created.id);
expect(found).toMatchObject({ name: 'Test User' });
// Rollback to keep DB clean
tx.rollback();
}).catch(() => {});
});
});Testing Transactions
Drizzle's transaction support is one of its strongest features, and it needs dedicated testing. There are two failure modes to cover: transaction commit (happy path) and transaction rollback (error path).
// src/services/transfer.service.ts
export async function transferCredits(
fromId: number,
toId: number,
amount: number
) {
return await db.transaction(async (tx) => {
// Deduct from sender
const [sender] = await tx
.update(accounts)
.set({ balance: sql`balance - ${amount}` })
.where(and(eq(accounts.id, fromId), gte(accounts.balance, amount)))
.returning();
if (!sender) {
throw new Error('Insufficient balance or account not found');
}
// Add to recipient
const [recipient] = await tx
.update(accounts)
.set({ balance: sql`balance + ${amount}` })
.where(eq(accounts.id, toId))
.returning();
if (!recipient) {
throw new Error('Recipient account not found');
}
return { sender, recipient };
});
}// Tests for the transfer service
it('rolls back both accounts on insufficient balance', async () => {
// Set up test data inside a transaction
await db.transaction(async (tx) => {
await tx.insert(accounts).values([
{ id: 1, balance: 100 },
{ id: 2, balance: 50 },
]);
await expect(
transferCredits(1, 2, 200) // More than balance
).rejects.toThrow('Insufficient balance');
// Verify both accounts are unchanged
const [acc1] = await tx.select().from(accounts).where(eq(accounts.id, 1));
expect(acc1.balance).toBe(100);
tx.rollback();
}).catch(() => {});
});Testing Schema Migrations
Drizzle's migration system (drizzle-kit) generates SQL migration files. Test that your migrations apply cleanly and that the resulting schema matches your TypeScript definitions:
// src/db/migrations.test.ts
import { describe, it, expect } from 'vitest';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
it('applies all migrations without error', async () => {
const testDb = createTestDb();
await expect(
migrate(testDb, { migrationsFolder: './drizzle/migrations' })
).resolves.not.toThrow();
});
it('schema matches TypeScript definitions', async () => {
const testDb = createTestDb();
// Introspect the actual DB schema
const result = await testDb.execute(sql`
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position
`);
const columns = result.rows.map(r => r.column_name);
expect(columns).toContain('id');
expect(columns).toContain('email');
expect(columns).toContain('created_at');
});End-to-End Database Testing With HelpMeTest
For teams running end-to-end tests, platforms like HelpMeTest let you write natural-language test scenarios that exercise your full stack — including the database layer. Rather than asserting raw SQL results, you test the behavior the user actually sees, backed by the database operations underneath.
A test scenario might look like:
Given I create a user via the API with email "test@example.com"
When I query the users endpoint with that email
Then the response should include the user's ID and creation timestamp
And the database should contain exactly one record with that emailThis approach complements your unit and integration tests: Drizzle integration tests verify the database layer in isolation, while end-to-end scenarios verify the complete user journey.
Key Takeaways
- Use unit tests with mocked chains for business logic that depends on Drizzle queries, but keep these focused and supplement with integration tests.
- Use transaction rollback or schema isolation to keep integration tests fast and idempotent.
- Test both the happy path and rollback path for every transaction — the rollback case is where most bugs hide.
- Add migration tests to your CI pipeline to catch schema drift before it reaches production.
- Drizzle's SQL transparency means integration tests against a real database are especially valuable — trust them over mocks for database-specific behavior.
The investment in a solid Drizzle testing setup pays off quickly. Schema changes that would have broken production in silence are caught at the PR level, and the confidence to refactor query logic grows with every green test run.
