Test database interactions using real PostgreSQL in Docker via Testcontainers, Prisma migrations, transaction rollbacks for fast isolation, and data factories for readable test setup.
Testing database code with mocks is insufficient — you can mock a repository perfectly and still ship a SQL query with a wrong column name, a missing index that causes a timeout, or a constraint violation that only appears with real data. Real database tests catch all of these.
Modern tooling makes real database testing practical: Testcontainers spins up a fresh PostgreSQL Docker container in seconds, Prisma migrations ensure the schema matches exactly what production uses, and transaction rollbacks make per-test isolation as fast as an in-memory operation. This lesson builds a complete database testing setup from scratch.
Testcontainers starts a real Docker container for your test run and exposes a connection string you can pass to Prisma.
1npm install --save-dev @testcontainers/postgresql1// src/test/database.ts
2import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';
3import { PrismaClient } from '@prisma/client';
4import { execSync } from 'child_process';
5
6let container: StartedPostgreSqlContainer;
7let prisma: PrismaClient;
8
9export async function startTestDatabase(): Promise<PrismaClient> {
10 container = await new PostgreSqlContainer('postgres:16-alpine')
11 .withDatabase('testdb')
12 .withUsername('test')
13 .withPassword('test')
14 .start();
15
16 const connectionString = container.getConnectionUri();
17 process.env['DATABASE_URL'] = connectionString;
18
19 // Run migrations against the test DB
20 execSync('npx prisma migrate deploy', {
21 env: { ...process.env, DATABASE_URL: connectionString },
22 });
23
24 prisma = new PrismaClient({
25 datasources: { db: { url: connectionString } },
26 });
27
28 await prisma.$connect();
29 return prisma;
30}
31
32export async function stopTestDatabase(): Promise<void> {
33 await prisma.$disconnect();
34 await container.stop();
35}
36
37export function getTestPrisma(): PrismaClient {
38 return prisma;
39}
40
41// jest.integration.config.ts
42const config: Config = {
43 globalSetup: './src/test/global-setup.ts',
44 globalTeardown: './src/test/global-teardown.ts',
45 testTimeout: 60000, // container startup can take 10-20s
46};
47
48// src/test/global-setup.ts
49import { startTestDatabase } from './database';
50export default async () => { await startTestDatabase(); };
51
52// src/test/global-teardown.ts
53import { stopTestDatabase } from './database';
54export default async () => { await stopTestDatabase(); };Wrapping each test in a transaction and rolling it back in afterEach is the fastest isolation strategy — faster than TRUNCATE because no table locks or vacuum needed.
1import { PrismaClient } from '@prisma/client';
2import { getTestPrisma } from '../test/database';
3
4let prisma: PrismaClient;
5
6// For transaction isolation, use Prisma's interactive transactions
7beforeEach(async () => {
8 prisma = getTestPrisma();
9 // Begin a transaction that we will roll back
10 await prisma.$executeRaw`BEGIN`;
11});
12
13afterEach(async () => {
14 await prisma.$executeRaw`ROLLBACK`;
15});
16
17// Alternative: TRUNCATE (simpler, slightly slower)
18beforeEach(async () => {
19 const tableNames = ['User', 'Order', 'Product', 'Session'];
20 for (const table of tableNames) {
21 await prisma.$executeRawUnsafe(`TRUNCATE TABLE "${table}" CASCADE`);
22 }
23});
24
25// Practical: for most projects, TRUNCATE is simpler and fast enough
26// Use transaction rollback when truncation is slow (large FK trees)
27
28// Verify isolation works:
29describe('Transaction isolation', () => {
30 it('data created in test A is not visible in test B', async () => {
31 await prisma.user.create({
32 data: { email: '[email protected]', name: 'Test A', passwordHash: 'hash' },
33 });
34 const users = await prisma.user.findMany();
35 expect(users).toHaveLength(1); // only this test's data
36 });
37
38 it('starts with zero users (previous test rolled back)', async () => {
39 const users = await prisma.user.findMany();
40 expect(users).toHaveLength(0); // previous test was rolled back
41 });
42});Data factories create database records with sensible defaults, accepting only the overrides each test cares about.
1import { PrismaClient, User, Course, Enrollment } from '@prisma/client';
2import { randomUUID } from 'crypto';
3import { hashSync } from 'bcryptjs';
4
5type DeepPartial<T> = { [K in keyof T]?: T[K] extends object ? DeepPartial<T[K]> : T[K] };
6
7export class TestFactory {
8 constructor(private prisma: PrismaClient) {}
9
10 async createUser(overrides: DeepPartial<Parameters<PrismaClient['user']['create']>[0]['data']> = {}): Promise<User> {
11 return this.prisma.user.create({
12 data: {
13 id: randomUUID(),
14 email: `user-${randomUUID().slice(0, 8)}@example.com`,
15 name: 'Test User',
16 passwordHash: hashSync('TestPass1!', 10),
17 role: 'STUDENT',
18 emailVerified: true,
19 createdAt: new Date(),
20 ...overrides,
21 },
22 });
23 }
24
25 async createCourse(instructorId: string, overrides: Record<string, unknown> = {}): Promise<Course> {
26 return this.prisma.course.create({
27 data: {
28 id: randomUUID(),
29 title: 'Test Course',
30 description: 'A course for testing',
31 price: 49.99,
32 published: true,
33 instructorId,
34 ...overrides,
35 },
36 });
37 }
38
39 async createEnrollment(userId: string, courseId: string): Promise<Enrollment> {
40 return this.prisma.enrollment.create({
41 data: { userId, courseId, enrolledAt: new Date() },
42 });
43 }
44}
45
46// Usage in tests:
47let factory: TestFactory;
48
49beforeEach(() => {
50 factory = new TestFactory(getTestPrisma());
51});
52
53test('enrolled users can access course content', async () => {
54 // Arrange — only the relevant properties are specified
55 const instructor = await factory.createUser({ role: 'INSTRUCTOR' });
56 const student = await factory.createUser();
57 const course = await factory.createCourse(instructor.id, { price: 99 });
58 await factory.createEnrollment(student.id, course.id);
59
60 // Act
61 const hasAccess = await courseAccessService.checkAccess(student.id, course.id);
62
63 // Assert
64 expect(hasAccess).toBe(true);
65});Repository tests exercise database queries directly without going through HTTP. They catch SQL errors, constraint violations, and complex query logic.
1// src/modules/users/user.repository.ts
2export class UserRepository {
3 constructor(private prisma: PrismaClient) {}
4
5 async findByEmail(email: string): Promise<User | null> {
6 return this.prisma.user.findUnique({ where: { email } });
7 }
8
9 async findActiveWithEnrollments(userId: string) {
10 return this.prisma.user.findUnique({
11 where: { id: userId, active: true },
12 include: { enrollments: { include: { course: true } } },
13 });
14 }
15
16 async updateLastLogin(userId: string): Promise<User> {
17 return this.prisma.user.update({
18 where: { id: userId },
19 data: { lastLoginAt: new Date() },
20 });
21 }
22}
23
24// src/modules/users/user.repository.integration.test.ts
25describe('UserRepository', () => {
26 let repo: UserRepository;
27 let factory: TestFactory;
28
29 beforeEach(() => {
30 const prisma = getTestPrisma();
31 repo = new UserRepository(prisma);
32 factory = new TestFactory(prisma);
33 });
34
35 describe('findByEmail', () => {
36 it('returns the user when email exists', async () => {
37 const created = await factory.createUser({ email: '[email protected]' });
38 const found = await repo.findByEmail('[email protected]');
39 expect(found?.id).toBe(created.id);
40 });
41
42 it('returns null when email does not exist', async () => {
43 const result = await repo.findByEmail('[email protected]');
44 expect(result).toBeNull();
45 });
46
47 it('is case-insensitive for email lookup', async () => {
48 await factory.createUser({ email: '[email protected]' });
49 // Depends on DB collation — test reveals the actual behaviour
50 const found = await repo.findByEmail('[email protected]');
51 // If this assertion fails, you need to normalise emails before storage
52 expect(found).toBeDefined();
53 });
54 });
55
56 describe('findActiveWithEnrollments', () => {
57 it('includes enrolled courses in the result', async () => {
58 const instructor = await factory.createUser({ role: 'INSTRUCTOR' });
59 const student = await factory.createUser();
60 const course = await factory.createCourse(instructor.id);
61 await factory.createEnrollment(student.id, course.id);
62
63 const result = await repo.findActiveWithEnrollments(student.id);
64
65 expect(result?.enrollments).toHaveLength(1);
66 expect(result?.enrollments[0].course.id).toBe(course.id);
67 });
68
69 it('returns null for inactive users', async () => {
70 const user = await factory.createUser({ active: false });
71 const result = await repo.findActiveWithEnrollments(user.id);
72 expect(result).toBeNull();
73 });
74 });
75});Unique constraints, foreign key violations, and NOT NULL errors are only testable against a real database. These tests prevent silent data corruption.
1describe('Database constraint tests', () => {
2 it('throws on duplicate email (unique constraint)', async () => {
3 await factory.createUser({ email: '[email protected]' });
4
5 await expect(
6 factory.createUser({ email: '[email protected]' })
7 ).rejects.toThrow(); // Prisma throws P2002 on unique violation
8 });
9
10 it('throws P2002 error code on unique constraint violation', async () => {
11 await factory.createUser({ email: '[email protected]' });
12
13 try {
14 await factory.createUser({ email: '[email protected]' });
15 fail('Should have thrown');
16 } catch (error: any) {
17 expect(error.code).toBe('P2002'); // Prisma unique constraint error code
18 expect(error.meta.target).toContain('email');
19 }
20 });
21
22 it('throws P2003 on foreign key violation', async () => {
23 await expect(
24 getTestPrisma().enrollment.create({
25 data: {
26 userId: 'nonexistent-user-id',
27 courseId: 'nonexistent-course-id',
28 enrolledAt: new Date(),
29 },
30 })
31 ).rejects.toMatchObject({ code: 'P2003' });
32 });
33
34 it('enforces NOT NULL constraint on required fields', async () => {
35 await expect(
36 getTestPrisma().user.create({
37 data: {
38 id: randomUUID(),
39 email: null as any, // bypasses TypeScript to test DB constraint
40 name: 'Test',
41 passwordHash: 'hash',
42 },
43 })
44 ).rejects.toThrow();
45 });
46});A course enrollment service tested end-to-end against a real PostgreSQL database with Testcontainers, transaction isolation, data factories, and constraint verification.
1// src/modules/enrollments/enrollment.service.ts
2import { PrismaClient } from '@prisma/client';
3
4export class EnrollmentService {
5 constructor(private prisma: PrismaClient) {}
6
7 async enroll(userId: string, courseId: string): Promise<{ enrollmentId: string }> {
8 // Check user exists and is active
9 const user = await this.prisma.user.findUnique({ where: { id: userId } });
10 if (!user || !user.active) throw new Error('User not found or inactive');
11
12 // Check course exists and is published
13 const course = await this.prisma.course.findUnique({ where: { id: courseId } });
14 if (!course || !course.published) throw new Error('Course not available');
15
16 // Check not already enrolled
17 const existing = await this.prisma.enrollment.findUnique({
18 where: { userId_courseId: { userId, courseId } },
19 });
20 if (existing) throw new Error('Already enrolled in this course');
21
22 const enrollment = await this.prisma.enrollment.create({
23 data: { userId, courseId, enrolledAt: new Date() },
24 });
25
26 return { enrollmentId: enrollment.id };
27 }
28
29 async getEnrollments(userId: string) {
30 return this.prisma.enrollment.findMany({
31 where: { userId },
32 include: { course: { select: { id: true, title: true, price: true } } },
33 orderBy: { enrolledAt: 'desc' },
34 });
35 }
36
37 async unenroll(userId: string, courseId: string): Promise<void> {
38 const enrollment = await this.prisma.enrollment.findUnique({
39 where: { userId_courseId: { userId, courseId } },
40 });
41 if (!enrollment) throw new Error('Enrollment not found');
42 await this.prisma.enrollment.delete({
43 where: { userId_courseId: { userId, courseId } },
44 });
45 }
46}
47
48// src/modules/enrollments/enrollment.service.integration.test.ts
49import { getTestPrisma } from '../../test/database';
50import { TestFactory } from '../../test/factory';
51import { EnrollmentService } from './enrollment.service';
52
53describe('EnrollmentService', () => {
54 let service: EnrollmentService;
55 let factory: TestFactory;
56
57 beforeEach(async () => {
58 const prisma = getTestPrisma();
59 service = new EnrollmentService(prisma);
60 factory = new TestFactory(prisma);
61 await prisma.$executeRaw`TRUNCATE "User", "Course", "Enrollment" CASCADE`;
62 });
63
64 describe('enroll', () => {
65 it('creates an enrollment for an active user and published course', async () => {
66 const instructor = await factory.createUser({ role: 'INSTRUCTOR' });
67 const student = await factory.createUser();
68 const course = await factory.createCourse(instructor.id, { published: true });
69
70 const result = await service.enroll(student.id, course.id);
71
72 expect(result.enrollmentId).toBeDefined();
73 });
74
75 it('throws when user is inactive', async () => {
76 const instructor = await factory.createUser({ role: 'INSTRUCTOR' });
77 const inactive = await factory.createUser({ active: false });
78 const course = await factory.createCourse(instructor.id);
79
80 await expect(service.enroll(inactive.id, course.id)).rejects.toThrow(
81 'User not found or inactive'
82 );
83 });
84
85 it('throws when course is not published', async () => {
86 const instructor = await factory.createUser({ role: 'INSTRUCTOR' });
87 const student = await factory.createUser();
88 const draft = await factory.createCourse(instructor.id, { published: false });
89
90 await expect(service.enroll(student.id, draft.id)).rejects.toThrow(
91 'Course not available'
92 );
93 });
94
95 it('throws when user is already enrolled', async () => {
96 const instructor = await factory.createUser({ role: 'INSTRUCTOR' });
97 const student = await factory.createUser();
98 const course = await factory.createCourse(instructor.id);
99 await factory.createEnrollment(student.id, course.id);
100
101 await expect(service.enroll(student.id, course.id)).rejects.toThrow(
102 'Already enrolled'
103 );
104 });
105 });
106
107 describe('getEnrollments', () => {
108 it('returns enrollments with course details in descending order', async () => {
109 const instructor = await factory.createUser({ role: 'INSTRUCTOR' });
110 const student = await factory.createUser();
111 const course1 = await factory.createCourse(instructor.id, { title: 'Course A' });
112 const course2 = await factory.createCourse(instructor.id, { title: 'Course B' });
113 await factory.createEnrollment(student.id, course1.id);
114 await factory.createEnrollment(student.id, course2.id);
115
116 const enrollments = await service.getEnrollments(student.id);
117
118 expect(enrollments).toHaveLength(2);
119 expect(enrollments[0].course.title).toBeDefined();
120 });
121
122 it('returns empty array for user with no enrollments', async () => {
123 const user = await factory.createUser();
124 const enrollments = await service.getEnrollments(user.id);
125 expect(enrollments).toHaveLength(0);
126 });
127 });
128
129 describe('unenroll', () => {
130 it('removes the enrollment from the database', async () => {
131 const instructor = await factory.createUser({ role: 'INSTRUCTOR' });
132 const student = await factory.createUser();
133 const course = await factory.createCourse(instructor.id);
134 await factory.createEnrollment(student.id, course.id);
135
136 await service.unenroll(student.id, course.id);
137
138 const remaining = await service.getEnrollments(student.id);
139 expect(remaining).toHaveLength(0);
140 });
141
142 it('throws when enrollment does not exist', async () => {
143 const student = await factory.createUser();
144 await expect(
145 service.unenroll(student.id, 'nonexistent-course')
146 ).rejects.toThrow('Enrollment not found');
147 });
148 });
149});Every test in this suite exercises the real database: constraint checking, relationship queries with include, and order-by sorting. The factory creates all prerequisite data (instructor, student, course) with a single readable call per entity. TRUNCATE in beforeEach ensures complete isolation. Notice that the 'already enrolled' test requires creating the enrollment first — this is the kind of state setup that data factories make concise and clear.
The next lesson covers NestJS testing module setup: creating isolated NestJS testing modules with Test.createTestingModule(), overriding providers, and writing end-to-end tests with the NestJS test server.
Your progress is saved in this browser, on this device only.