Database Integration Testing — Integration Testing at… | OneNoughtOne

0/86

System Design (LLD)Integration Testing at LLD Level

Integration Testing at LLD Level

LevelIntermediate

Duration60 mins

TopicIntegration Testing at LLD Level

3 / 4

Database Integration Testing

Testing the Data Layer: Where Objects Meet Persistence

In object-oriented systems, the database is the most critical integration point. Rich domain objects must be correctly persisted, complex queries must return accurate results, transactions must maintain consistency, and migrations must safely evolve the schema. Unit tests with mock repositories cannot verify any of this—only tests against real databases can. Database integration testing verifies that your persistence layer works correctly with actual database systems. It's where object-relational mapping meets reality, where SQL queries prove their correctness, and where transaction boundaries demonstrate their integrity.

What You Will Learn

By the end of this page, you will master strategies for testing with real databases, testing repository implementations, verifying complex queries, testing database migrations, managing test data lifecycle, and handling database-specific edge cases.

Why Test Against Real Databases?

Many teams skip database integration tests, relying on mocked repositories and hoping the ORM handles everything correctly. This is a dangerous assumption. Here's what mocked repository tests cannot verify:

What Mock Repositories Cannot Catch

•SQL syntax errors: Your query might have a typo that only fails at runtime
•ORM mapping errors: Column names, types, or relationships might be misconfigured
•Query performance: An N+1 query or missing index won't appear in mocks
•Constraint violations: Foreign keys, unique constraints, check constraints
•Transaction behavior: Rollback, isolation levels, deadlock scenarios
•Database-specific behavior: PostgreSQL vs MySQL differences in NULL handling, sorting, etc.
•Migration correctness: Schema changes might break existing queries
•Connection management: Pool exhaustion, connection leaks, timeout handling

The false confidence of mocked repositories: // This test passes but tells us nothing about database correctness it('should save user', async () => { const mockRepo = mock<UserRepository>(); mockRepo.save.mockResolvedValue(undefined); const service = new UserService(mockRepo.object); await service.createUser({ name: 'Alice' }); expect(mockRepo.save).toHaveBeenCalled(); // ✓ But so what? }); This test verifies that save() was called, but not that: The SQL generated is valid The user entity is correctly mapped to the table Unique constraints are enforced The saved data can be retrieved Real database tests provide actual confidence.

Test Database Strategies

There are several approaches to running tests against databases, each with distinct tradeoffs:

Test Database Strategy Comparison
Strategy	Speed	Realism	Setup Complexity	Best For
In-Memory DB (SQLite)	Fastest	Low	Minimal	Simple CRUD, rapid feedback
Shared Test DB	Fast	High	Low	Local development
Transactional Rollback	Fast	High	Medium	Isolated tests on shared DB
Docker Containers	Medium	Highest	Medium	CI/CD, production parity
Ephemeral Cloud DB	Slow	Highest	High	Cloud-native development

In-memory databases like SQLite provide the fastest feedback but sacrifice realism. They're useful for rapid iteration but should not be your only database tests.

InMemorySetup.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
// Using SQLite in-memory for fast tests
import { DataSource } from 'typeorm';
 
let testDataSource: DataSource;
 
beforeAll(async () => {
    testDataSource = new DataSource({
        type: 'sqlite',
        database: ':memory:',
        entities: [User, Order, OrderItem],
        synchronize: true, // Auto-create schema
    });
    await testDataSource.initialize();
});
 
afterAll(async () => {
    await testDataSource.destroy();
});
 
// Fast but limited - no Postgres-specific features
it('should save and retrieve user', async () => {
    const repo = testDataSource.getRepository(User);
    await repo.save({ name: 'Alice', email: 'alice@example.com' });
    
    const user = await repo.findOneBy({ email: 'alice@example.com' });
    expect(user?.name).toBe('Alice');
});

In-Memory Limitations

SQLite doesn't support many PostgreSQL/MySQL features: JSONB, arrays, CTEs, window functions, specific index types. Tests may pass on SQLite but fail in production.

Testing Repository Implementations

Repositories are the primary interface between domain objects and the database. Thorough repository testing ensures that your domain objects persist and retrieve correctly.

RepositoryIntegrationTest.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
describe('OrderRepository Integration Tests', () => {
    let orderRepository: OrderRepository;
    let customerRepository: CustomerRepository;
    let testDb: TestDatabase;
    
    beforeAll(async () => {
        testDb = await TestDatabase.start();
        orderRepository = new PostgresOrderRepository(testDb.pool);
        customerRepository = new PostgresCustomerRepository(testDb.pool);
    });
    
    beforeEach(async () => {
        await testDb.truncateAll();
        // Seed required foreign key data
        await customerRepository.save(
            Customer.create({ id: 'cust-123', name: 'Test Customer' })
        );
    });
 
    // ===== BASIC CRUD OPERATIONS =====
    describe('save()', () => {
        it('should persist a new order with all fields', async () => {
            const order = Order.create({
                id: 'order-001',
                customerId: 'cust-123',
                items: [
                    { sku: 'SKU-001', quantity: 2, unitPrice: 29.99 },
                    { sku: 'SKU-002', quantity: 1, unitPrice: 49.99 },
                ],
                status: 'PENDING',
                createdAt: new Date('2024-01-15T10:00:00Z'),
            });
            
            await orderRepository.save(order);
            
            // Verify directly with raw SQL to ensure ORM working correctly
            const row = await testDb.pool.query(
                'SELECT * FROM orders WHERE id = $1',
                ['order-001']
            );
            
            expect(row.rows[0]).toEqual(expect.objectContaining({
                id: 'order-001',
                customer_id: 'cust-123',
                status: 'PENDING',
            }));
            
            // Verify items saved to junction table
            const items = await testDb.pool.query(
                'SELECT * FROM order_items WHERE order_id = $1',
                ['order-001']
            );
            expect(items.rows).toHaveLength(2);
        });
        
        it('should update existing order on save', async () => {
            const order = Order.create({
                id: 'order-001',
                customerId: 'cust-123',
                status: 'PENDING',
            });
            await orderRepository.save(order);
            
            // Modify and re-save
            order.updateStatus('CONFIRMED');
            await orderRepository.save(order);
            
            const retrieved = await orderRepository.findById('order-001');
            expect(retrieved?.status).toBe('CONFIRMED');
        });
    });
    
    // ===== QUERY METHODS =====
    describe('findById()', () => {
        it('should return null for non-existent order', async () => {
            const order = await orderRepository.findById('non-existent');
            expect(order).toBeNull();
        });
        
        it('should reconstruct complete aggregate with items', async () => {
            // Setup - create order with items
            await createOrderWithItems('order-001', [
                { sku: 'SKU-001', quantity: 2 },
                { sku: 'SKU-002', quantity: 3 },
            ]);
            
            const order = await orderRepository.findById('order-001');
            
            expect(order).toBeDefined();
            expect(order?.items).toHaveLength(2);
            expect(order?.items[0].sku).toBe('SKU-001');
            expect(order?.items[1].quantity).toBe(3);
        });
    });
    
    describe('findByCustomerId()', () => {
        it('should return all orders for customer in date order', async () => {
            await createOrder('order-001', 'cust-123', new Date('2024-01-15'));
            await createOrder('order-002', 'cust-123', new Date('2024-01-10'));
            await createOrder('order-003', 'cust-456', new Date('2024-01-12'));
            
            const orders = await orderRepository.findByCustomerId('cust-123');
            
            expect(orders).toHaveLength(2);
            // Most recent first
            expect(orders[0].id).toBe('order-001');
            expect(orders[1].id).toBe('order-002');
        });
        
        it('should return empty array for customer with no orders', async () => {
            const orders = await orderRepository.findByCustomerId('no-orders');
            expect(orders).toEqual([]);
        });
    });
    
    // ===== CONSTRAINT ENFORCEMENT =====
    describe('database constraints', () => {
        it('should reject order with non-existent customer', async () => {
            const order = Order.create({
                id: 'order-orphan',
                customerId: 'non-existent-customer',
            });
            
            await expect(orderRepository.save(order))
                .rejects.toThrow(/foreign key constraint/i);
        });
        
        it('should reject duplicate order ID', async () => {
            await createOrder('order-001', 'cust-123');
            
            await expect(createOrder('order-001', 'cust-123'))
                .rejects.toThrow(/unique constraint|duplicate key/i);
        });
    });
    
    // ===== EDGE CASES =====
    describe('edge cases', () => {
        it('should handle special characters in data', async () => {
            const order = Order.create({
                id: 'order-special',
                customerId: 'cust-123',
                notes: "O'Malley's order — contains "quotes" & ampersands",
            });
            
            await orderRepository.save(order);
            const retrieved = await orderRepository.findById('order-special');
            
            expect(retrieved?.notes).toBe("O'Malley's order — contains "quotes" & ampersands");
        });
        
        it('should handle null optional fields', async () => {
            const order = Order.create({
                id: 'order-minimal',
                customerId: 'cust-123',
                notes: null,
                shippingAddress: null,
            });
            
            await orderRepository.save(order);
            const retrieved = await orderRepository.findById('order-minimal');
            
            expect(retrieved?.notes).toBeNull();
            expect(retrieved?.shippingAddress).toBeNull();
        });
        
        it('should handle timezone conversions', async () => {
            const utcDate = new Date('2024-01-15T15:30:00Z');
            const order = Order.create({
                id: 'order-tz',
                customerId: 'cust-123',
                createdAt: utcDate,
            });
            
            await orderRepository.save(order);
            const retrieved = await orderRepository.findById('order-tz');
            
            // Verify no timezone drift
            expect(retrieved?.createdAt.toISOString()).toBe('2024-01-15T15:30:00.000Z');
        });
    });
});

Test Domain Invariants After Retrieval

After retrieving an entity from the database, verify that its domain invariants still hold. This catches mapping issues where persistence breaks business rules (e.g., an Order is retrieved with 0 items when at least 1 is required).

Testing Complex Queries

Simple CRUD operations are straightforward to test. Complex queries—with joins, aggregations, filtering, and sorting—require more careful testing to ensure correctness.

ComplexQueryTests.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
describe('Order Analytics Repository - Complex Queries', () => {
    let analyticsRepo: OrderAnalyticsRepository;
    
    beforeEach(async () => {
        await testDb.truncateAll();
        await seedTestData();
    });
    
    // Test data setup for complex scenarios
    async function seedTestData() {
        // Create customers
        await customerRepo.saveMany([
            { id: 'cust-001', tier: 'GOLD', region: 'US' },
            { id: 'cust-002', tier: 'SILVER', region: 'EU' },
            { id: 'cust-003', tier: 'GOLD', region: 'EU' },
        ]);
        
        // Create orders with different dates and totals
        await orderRepo.saveMany([
            { id: 'ord-1', customerId: 'cust-001', total: 500, createdAt: '2024-01-15' },
            { id: 'ord-2', customerId: 'cust-001', total: 300, createdAt: '2024-01-20' },
            { id: 'ord-3', customerId: 'cust-002', total: 150, createdAt: '2024-01-18' },
            { id: 'ord-4', customerId: 'cust-003', total: 1200, createdAt: '2024-01-10' },
            { id: 'ord-5', customerId: 'cust-003', total: 250, createdAt: '2024-02-01' },
        ]);
    }
    
    // ===== AGGREGATION QUERIES =====
    describe('getCustomerLifetimeValue()', () => {
        it('should correctly sum all order totals per customer', async () => {
            const ltv = await analyticsRepo.getCustomerLifetimeValue('cust-001');
            
            expect(ltv.totalOrders).toBe(2);
            expect(ltv.totalSpent).toEqual(Money.of(800)); // 500 + 300
            expect(ltv.averageOrderValue).toEqual(Money.of(400));
        });
        
        it('should return zero for customer with no orders', async () => {
            await customerRepo.save({ id: 'cust-empty', tier: 'BRONZE' });
            
            const ltv = await analyticsRepo.getCustomerLifetimeValue('cust-empty');
            
            expect(ltv.totalOrders).toBe(0);
            expect(ltv.totalSpent).toEqual(Money.zero());
        });
    });
    
    // ===== JOIN QUERIES =====
    describe('getTopCustomersByRegion()', () => {
        it('should rank customers by spend within each region', async () => {
            const rankings = await analyticsRepo.getTopCustomersByRegion();
            
            // US Region
            const usRanking = rankings.find(r => r.region === 'US');
            expect(usRanking?.customers[0].id).toBe('cust-001'); // $800 total
            
            // EU Region
            const euRanking = rankings.find(r => r.region === 'EU');
            expect(euRanking?.customers[0].id).toBe('cust-003'); // $1450 total
            expect(euRanking?.customers[1].id).toBe('cust-002'); // $150 total
        });
    });
    
    // ===== WINDOW FUNCTION QUERIES =====
    describe('getOrderRankingsForCustomer()', () => {
        it('should rank orders by total within customer history', async () => {
            const rankings = await analyticsRepo.getOrderRankingsForCustomer('cust-001');
            
            // Highest order should be rank 1
            const highestOrder = rankings.find(r => r.orderId === 'ord-1');
            expect(highestOrder?.rank).toBe(1);
            expect(highestOrder?.percentile).toBeCloseTo(100);
            
            const secondOrder = rankings.find(r => r.orderId === 'ord-2');
            expect(secondOrder?.rank).toBe(2);
        });
    });
    
    // ===== FILTER + PAGINATION =====
    describe('searchOrders()', () => {
        it('should filter by date range and customer tier', async () => {
            const results = await analyticsRepo.searchOrders({
                dateFrom: new Date('2024-01-15'),
                dateTo: new Date('2024-01-31'),
                customerTier: 'GOLD',
                page: 1,
                pageSize: 10,
            });
            
            // Only GOLD customers, within date range
            expect(results.orders).toHaveLength(2);
            expect(results.orders.map(o => o.id)).toEqual(
                expect.arrayContaining(['ord-1', 'ord-2'])
            );
            expect(results.totalCount).toBe(2);
        });
        
        it('should correctly paginate large result sets', async () => {
            // Seed 50 more orders
            for (let i = 0; i < 50; i++) {
                await orderRepo.save({
                    id: `bulk-ord-${i}`,
                    customerId: 'cust-001',
                    total: 100,
                    createdAt: new Date('2024-02-01'),
                });
            }
            
            const page1 = await analyticsRepo.searchOrders({
                customerId: 'cust-001',
                page: 1,
                pageSize: 20,
            });
            
            const page2 = await analyticsRepo.searchOrders({
                customerId: 'cust-001',
                page: 2,
                pageSize: 20,
            });
            
            expect(page1.orders).toHaveLength(20);
            expect(page2.orders).toHaveLength(20);
            expect(page1.totalCount).toBe(52); // 2 original + 50 bulk
            
            // No overlap between pages
            const page1Ids = new Set(page1.orders.map(o => o.id));
            const page2Ids = new Set(page2.orders.map(o => o.id));
            const overlap = [...page1Ids].filter(id => page2Ids.has(id));
            expect(overlap).toHaveLength(0);
        });
    });
    
    // ===== VERIFYING SQL CORRECTNESS =====
    describe('query correctness verification', () => {
        it('should use index for customer lookup', async () => {
            // Verify the query uses the expected index
            const explanation = await testDb.pool.query(
                'EXPLAIN ANALYZE ' + analyticsRepo.getQueryForCustomerOrders()
            );
            
            const plan = explanation.rows.map(r => r['QUERY PLAN']).join('
');
            expect(plan).toContain('Index Scan');
            expect(plan).not.toContain('Seq Scan');
        });
    });
});

Testing Query Performance

Integration tests can verify that queries use expected indexes via EXPLAIN ANALYZE. However, performance testing with realistic data volumes typically requires separate load testing—integration test datasets are too small to reveal performance issues.

Testing Database Migrations

Schema migrations are one of the riskiest operations in production systems. Testing migrations before deployment can prevent data corruption and downtime.

MigrationTests.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
describe('Database Migration Tests', () => {
    let migrator: DatabaseMigrator;
    
    beforeEach(async () => {
        // Start with clean database
        await testDb.dropAllTables();
        migrator = new DatabaseMigrator(testDb.pool);
    });
    
    describe('Migration Sequencing', () => {
        it('should apply migrations in order', async () => {
            await migrator.migrateToLatest();
            
            const appliedMigrations = await migrator.getAppliedMigrations();
            
            expect(appliedMigrations).toEqual([
                'V001__create_users_table',
                'V002__create_orders_table',
                'V003__add_order_status',
                'V004__add_user_preferences',
            ]);
        });
        
        it('should be idempotent - running twice has no effect', async () => {
            await migrator.migrateToLatest();
            await migrator.migrateToLatest();
            
            const appliedMigrations = await migrator.getAppliedMigrations();
            // No duplicates
            const uniqueMigrations = [...new Set(appliedMigrations)];
            expect(uniqueMigrations.length).toBe(appliedMigrations.length);
        });
    });
    
    describe('Data Migration Correctness', () => {
        it('should preserve existing data during schema changes', async () => {
            // Apply migrations up to V003
            await migrator.migrateTo('V003');
            
            // Insert data in old schema
            await testDb.pool.query(`
                INSERT INTO orders (id, customer_id, total)
                VALUES ('order-1', 'cust-1', 100.00)
            `);
            
            // Apply V004 which adds status column with default
            await migrator.migrateTo('V004');
            
            // Verify data preserved and new column populated
            const result = await testDb.pool.query(
                'SELECT * FROM orders WHERE id = $1',
                ['order-1']
            );
            
            expect(result.rows[0].customer_id).toBe('cust-1');
            expect(result.rows[0].total).toBe('100.00');
            expect(result.rows[0].status).toBe('PENDING'); // Default value
        });
    });
    
    describe('Rollback Testing', () => {
        it('should cleanly rollback failed migration', async () => {
            await migrator.migrateTo('V003');
            
            // V004_broken intentionally fails mid-migration
            const brokenMigration = {
                name: 'V004_broken',
                up: async (db: Pool) => {
                    await db.query('ALTER TABLE orders ADD COLUMN temp INT');
                    throw new Error('Intentional failure');
                },
                down: async (db: Pool) => {
                    await db.query('ALTER TABLE orders DROP COLUMN temp');
                }
            };
            
            // Migration should fail
            await expect(migrator.apply(brokenMigration))
                .rejects.toThrow('Intentional failure');
            
            // Table should be in original state
            const columns = await testDb.getTableColumns('orders');
            expect(columns).not.toContain('temp');
        });
    });
    
    describe('Production-Like Migration Testing', () => {
        it('should handle migration with large dataset', async () => {
            await migrator.migrateTo('V003');
            
            // Seed significant data volume
            const batchSize = 1000;
            for (let batch = 0; batch < 10; batch++) {
                const values = Array.from({ length: batchSize }, (_, i) => 
                    `('order-${batch}-${i}', 'cust-1', ${100 + i})`
                ).join(',');
                
                await testDb.pool.query(`
                    INSERT INTO orders (id, customer_id, total)
                    VALUES ${values}
                `);
            }
            
            // Run migration that adds index (can be slow on large tables)
            const startTime = Date.now();
            await migrator.migrateTo('V005'); // Adds index
            const duration = Date.now() - startTime;
            
            console.log(`Migration completed in ${duration}ms`);
            
            // Verify index was created
            const indexes = await testDb.getIndexes('orders');
            expect(indexes).toContain('idx_orders_customer_id');
        });
    });
});

Test with Production-Like Data Volumes

Migrations that work instantly on 100 rows may timeout on 10 million rows. Test migrations with realistic data volumes to catch performance issues before they cause production outages.

Managing Test Data Lifecycle

Effective test data management is essential for reliable database integration tests. Tests must be isolated, reproducible, and maintainable.

TestDataManagement.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
// ===== PATTERN: Test Data Builder with Seeding =====
class TestDatabaseSeeder {
    constructor(private readonly db: TestDatabase) {}
    
    // Seed minimal required data - foreign key dependencies
    async seedEssentials(): Promise<EssentialData> {
        const [defaultCustomer, defaultProduct] = await Promise.all([
            this.db.customers.create({
                id: 'test-customer-default',
                name: 'Default Test Customer',
                email: 'test@example.com',
            }),
            this.db.products.create({
                id: 'test-product-default',
                sku: 'DEFAULT-SKU',
                price: Money.of(99.99),
            }),
        ]);
        
        return { defaultCustomer, defaultProduct };
    }
    
    // Scenario-specific seeding
    async seedOrderScenario(options: {
        orderCount: number;
        customerId?: string;
        dateRange?: { from: Date; to: Date };
    }): Promise<Order[]> {
        const customerId = options.customerId ?? 'test-customer-default';
        const orders: Order[] = [];
        
        for (let i = 0; i < options.orderCount; i++) {
            const order = await this.db.orders.create({
                customerId,
                items: [{ productId: 'test-product-default', quantity: 1 }],
                createdAt: options.dateRange
                    ? randomDateInRange(options.dateRange)
                    : new Date(),
            });
            orders.push(order);
        }
        
        return orders;
    }
}
 
// ===== PATTERN: Test Data Factory with Relationships =====
class TestDataFactory {
    private counter = 0;
    
    customer(overrides: Partial<Customer> = {}): Customer {
        const id = this.nextId('customer');
        return Customer.create({
            id,
            name: `Test Customer ${id}`,
            email: `customer-${id}@test.com`,
            tier: 'STANDARD',
            ...overrides,
        });
    }
    
    order(customer: Customer, items: OrderItem[] = []): Order {
        if (items.length === 0) {
            items = [this.orderItem()];
        }
        
        return Order.create({
            id: this.nextId('order'),
            customerId: customer.id,
            items,
            status: 'PENDING',
        });
    }
    
    orderItem(overrides: Partial<OrderItem> = {}): OrderItem {
        return {
            productId: this.nextId('product'),
            quantity: 1,
            unitPrice: Money.of(19.99),
            ...overrides,
        };
    }
    
    private nextId(prefix: string): string {
        return `${prefix}-${++this.counter}`;
    }
}
 
// ===== PATTERN: Cleanup Strategies =====
class DatabaseCleanup {
    constructor(private readonly pool: Pool) {}
    
    // Fast: Truncate all tables (resets autoincrement)
    async truncateAll(): Promise<void> {
        const tables = await this.getAllTables();
        await this.pool.query(
            `TRUNCATE ${tables.join(', ')} CASCADE`
        );
    }
    
    // Selective: Delete specific data without resetting sequences
    async deleteTestData(testRunId: string): Promise<void> {
        await this.pool.query(
            'DELETE FROM orders WHERE id LIKE $1',
            [`${testRunId}%`]
        );
        await this.pool.query(
            'DELETE FROM customers WHERE id LIKE $1',
            [`${testRunId}%`]
        );
    }
    
    // Isolation: Use unique prefixes per test for parallel execution
    static generateTestPrefix(): string {
        return `test-${Date.now()}-${Math.random().toString(36).slice(2, 8)}`;
    }
}
 
// ===== USAGE IN TESTS =====
describe('Order Processing', () => {
    let seeder: TestDatabaseSeeder;
    let factory: TestDataFactory;
    let cleanup: DatabaseCleanup;
    
    beforeAll(async () => {
        seeder = new TestDatabaseSeeder(testDb);
        cleanup = new DatabaseCleanup(testDb.pool);
        await seeder.seedEssentials();
    });
    
    beforeEach(async () => {
        factory = new TestDataFactory();
        // Clean test-specific data, keep essentials
        await cleanup.truncateAll();
        await seeder.seedEssentials();
    });
    
    it('should process order correctly', async () => {
        const customer = factory.customer({ tier: 'GOLD' });
        await customerRepo.save(customer);
        
        const order = factory.order(customer, [
            factory.orderItem({ quantity: 5 }),
        ]);
        
        const result = await orderService.processOrder(order);
        expect(result.status).toBe('CONFIRMED');
    });
});

Test Data Management Best Practices

•Unique IDs per test: Avoid collisions when tests run in parallel
•Factory methods: Generate valid test data with sensible defaults
•Relationship-aware creation: Factories should handle foreign key dependencies
•Minimal seeding: Only seed what's necessary for each test
•Cleanup between tests: Use TRUNCATE or transaction rollback for isolation
•Avoid shared mutable state: Don't depend on data created by other tests

Summary: Database Integration Testing

We've explored the essential practices for testing database integrations in object-oriented systems. Let's consolidate the key principles:

Key Takeaways

•Real database tests catch what mocks miss — SQL syntax, ORM mapping, constraints, and database-specific behavior.
•Choose the right test database strategy — In-memory for speed, Docker for realism, transactional rollback for isolation.
•Test repositories comprehensively — CRUD operations, edge cases, constraint enforcement, and data integrity.
•Complex queries need thorough testing — Aggregations, joins, window functions, and pagination require dedicated tests.
•Migrations are high-risk and must be tested — Verify ordering, data preservation, rollback capability, and performance.
•Manage test data lifecycle carefully — Use factories, unique IDs, and proper cleanup for reliable tests.

What's next: With a solid understanding of database integration testing, we'll now explore broader integration test strategies. The next page covers how to design and organize your overall integration testing approach, including test pyramids, environment management, and CI/CD integration.

Page Complete

You now have practical expertise in database integration testing. You can test repositories, complex queries, and migrations effectively, with proper test data management. Next, we'll zoom out to integration test strategies at the organizational level.

System Design (LLD)Integration Testing at LLD Level

Integration Testing at LLD Level

LevelIntermediate

Duration60 mins

TopicIntegration Testing at LLD Level

3 / 4

Database Integration Testing

Testing the Data Layer: Where Objects Meet Persistence

In object-oriented systems, the database is the most critical integration point. Rich domain objects must be correctly persisted, complex queries must return accurate results, transactions must maintain consistency, and migrations must safely evolve the schema. Unit tests with mock repositories cannot verify any of this—only tests against real databases can. Database integration testing verifies that your persistence layer works correctly with actual database systems. It's where object-relational mapping meets reality, where SQL queries prove their correctness, and where transaction boundaries demonstrate their integrity.

What You Will Learn

By the end of this page, you will master strategies for testing with real databases, testing repository implementations, verifying complex queries, testing database migrations, managing test data lifecycle, and handling database-specific edge cases.

Why Test Against Real Databases?

Many teams skip database integration tests, relying on mocked repositories and hoping the ORM handles everything correctly. This is a dangerous assumption. Here's what mocked repository tests cannot verify:

What Mock Repositories Cannot Catch

•SQL syntax errors: Your query might have a typo that only fails at runtime
•ORM mapping errors: Column names, types, or relationships might be misconfigured
•Query performance: An N+1 query or missing index won't appear in mocks
•Constraint violations: Foreign keys, unique constraints, check constraints
•Transaction behavior: Rollback, isolation levels, deadlock scenarios
•Database-specific behavior: PostgreSQL vs MySQL differences in NULL handling, sorting, etc.
•Migration correctness: Schema changes might break existing queries
•Connection management: Pool exhaustion, connection leaks, timeout handling

The false confidence of mocked repositories: // This test passes but tells us nothing about database correctness it('should save user', async () => { const mockRepo = mock<UserRepository>(); mockRepo.save.mockResolvedValue(undefined); const service = new UserService(mockRepo.object); await service.createUser({ name: 'Alice' }); expect(mockRepo.save).toHaveBeenCalled(); // ✓ But so what? }); This test verifies that save() was called, but not that: The SQL generated is valid The user entity is correctly mapped to the table Unique constraints are enforced The saved data can be retrieved Real database tests provide actual confidence.

Test Database Strategies

There are several approaches to running tests against databases, each with distinct tradeoffs:

Test Database Strategy Comparison
Strategy	Speed	Realism	Setup Complexity	Best For
In-Memory DB (SQLite)	Fastest	Low	Minimal	Simple CRUD, rapid feedback
Shared Test DB	Fast	High	Low	Local development
Transactional Rollback	Fast	High	Medium	Isolated tests on shared DB
Docker Containers	Medium	Highest	Medium	CI/CD, production parity
Ephemeral Cloud DB	Slow	Highest	High	Cloud-native development

In-memory databases like SQLite provide the fastest feedback but sacrifice realism. They're useful for rapid iteration but should not be your only database tests.

InMemorySetup.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
// Using SQLite in-memory for fast tests
import { DataSource } from 'typeorm';
 
let testDataSource: DataSource;
 
beforeAll(async () => {
    testDataSource = new DataSource({
        type: 'sqlite',
        database: ':memory:',
        entities: [User, Order, OrderItem],
        synchronize: true, // Auto-create schema
    });
    await testDataSource.initialize();
});
 
afterAll(async () => {
    await testDataSource.destroy();
});
 
// Fast but limited - no Postgres-specific features
it('should save and retrieve user', async () => {
    const repo = testDataSource.getRepository(User);
    await repo.save({ name: 'Alice', email: 'alice@example.com' });
    
    const user = await repo.findOneBy({ email: 'alice@example.com' });
    expect(user?.name).toBe('Alice');
});

In-Memory Limitations

SQLite doesn't support many PostgreSQL/MySQL features: JSONB, arrays, CTEs, window functions, specific index types. Tests may pass on SQLite but fail in production.

Testing Repository Implementations

Repositories are the primary interface between domain objects and the database. Thorough repository testing ensures that your domain objects persist and retrieve correctly.

RepositoryIntegrationTest.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
describe('OrderRepository Integration Tests', () => {
    let orderRepository: OrderRepository;
    let customerRepository: CustomerRepository;
    let testDb: TestDatabase;
    
    beforeAll(async () => {
        testDb = await TestDatabase.start();
        orderRepository = new PostgresOrderRepository(testDb.pool);
        customerRepository = new PostgresCustomerRepository(testDb.pool);
    });
    
    beforeEach(async () => {
        await testDb.truncateAll();
        // Seed required foreign key data
        await customerRepository.save(
            Customer.create({ id: 'cust-123', name: 'Test Customer' })
        );
    });
 
    // ===== BASIC CRUD OPERATIONS =====
    describe('save()', () => {
        it('should persist a new order with all fields', async () => {
            const order = Order.create({
                id: 'order-001',
                customerId: 'cust-123',
                items: [
                    { sku: 'SKU-001', quantity: 2, unitPrice: 29.99 },
                    { sku: 'SKU-002', quantity: 1, unitPrice: 49.99 },
                ],
                status: 'PENDING',
                createdAt: new Date('2024-01-15T10:00:00Z'),
            });
            
            await orderRepository.save(order);
            
            // Verify directly with raw SQL to ensure ORM working correctly
            const row = await testDb.pool.query(
                'SELECT * FROM orders WHERE id = $1',
                ['order-001']
            );
            
            expect(row.rows[0]).toEqual(expect.objectContaining({
                id: 'order-001',
                customer_id: 'cust-123',
                status: 'PENDING',
            }));
            
            // Verify items saved to junction table
            const items = await testDb.pool.query(
                'SELECT * FROM order_items WHERE order_id = $1',
                ['order-001']
            );
            expect(items.rows).toHaveLength(2);
        });
        
        it('should update existing order on save', async () => {
            const order = Order.create({
                id: 'order-001',
                customerId: 'cust-123',
                status: 'PENDING',
            });
            await orderRepository.save(order);
            
            // Modify and re-save
            order.updateStatus('CONFIRMED');
            await orderRepository.save(order);
            
            const retrieved = await orderRepository.findById('order-001');
            expect(retrieved?.status).toBe('CONFIRMED');
        });
    });
    
    // ===== QUERY METHODS =====
    describe('findById()', () => {
        it('should return null for non-existent order', async () => {
            const order = await orderRepository.findById('non-existent');
            expect(order).toBeNull();
        });
        
        it('should reconstruct complete aggregate with items', async () => {
            // Setup - create order with items
            await createOrderWithItems('order-001', [
                { sku: 'SKU-001', quantity: 2 },
                { sku: 'SKU-002', quantity: 3 },
            ]);
            
            const order = await orderRepository.findById('order-001');
            
            expect(order).toBeDefined();
            expect(order?.items).toHaveLength(2);
            expect(order?.items[0].sku).toBe('SKU-001');
            expect(order?.items[1].quantity).toBe(3);
        });
    });
    
    describe('findByCustomerId()', () => {
        it('should return all orders for customer in date order', async () => {
            await createOrder('order-001', 'cust-123', new Date('2024-01-15'));
            await createOrder('order-002', 'cust-123', new Date('2024-01-10'));
            await createOrder('order-003', 'cust-456', new Date('2024-01-12'));
            
            const orders = await orderRepository.findByCustomerId('cust-123');
            
            expect(orders).toHaveLength(2);
            // Most recent first
            expect(orders[0].id).toBe('order-001');
            expect(orders[1].id).toBe('order-002');
        });
        
        it('should return empty array for customer with no orders', async () => {
            const orders = await orderRepository.findByCustomerId('no-orders');
            expect(orders).toEqual([]);
        });
    });
    
    // ===== CONSTRAINT ENFORCEMENT =====
    describe('database constraints', () => {
        it('should reject order with non-existent customer', async () => {
            const order = Order.create({
                id: 'order-orphan',
                customerId: 'non-existent-customer',
            });
            
            await expect(orderRepository.save(order))
                .rejects.toThrow(/foreign key constraint/i);
        });
        
        it('should reject duplicate order ID', async () => {
            await createOrder('order-001', 'cust-123');
            
            await expect(createOrder('order-001', 'cust-123'))
                .rejects.toThrow(/unique constraint|duplicate key/i);
        });
    });
    
    // ===== EDGE CASES =====
    describe('edge cases', () => {
        it('should handle special characters in data', async () => {
            const order = Order.create({
                id: 'order-special',
                customerId: 'cust-123',
                notes: "O'Malley's order — contains "quotes" & ampersands",
            });
            
            await orderRepository.save(order);
            const retrieved = await orderRepository.findById('order-special');
            
            expect(retrieved?.notes).toBe("O'Malley's order — contains "quotes" & ampersands");
        });
        
        it('should handle null optional fields', async () => {
            const order = Order.create({
                id: 'order-minimal',
                customerId: 'cust-123',
                notes: null,
                shippingAddress: null,
            });
            
            await orderRepository.save(order);
            const retrieved = await orderRepository.findById('order-minimal');
            
            expect(retrieved?.notes).toBeNull();
            expect(retrieved?.shippingAddress).toBeNull();
        });
        
        it('should handle timezone conversions', async () => {
            const utcDate = new Date('2024-01-15T15:30:00Z');
            const order = Order.create({
                id: 'order-tz',
                customerId: 'cust-123',
                createdAt: utcDate,
            });
            
            await orderRepository.save(order);
            const retrieved = await orderRepository.findById('order-tz');
            
            // Verify no timezone drift
            expect(retrieved?.createdAt.toISOString()).toBe('2024-01-15T15:30:00.000Z');
        });
    });
});

Test Domain Invariants After Retrieval

After retrieving an entity from the database, verify that its domain invariants still hold. This catches mapping issues where persistence breaks business rules (e.g., an Order is retrieved with 0 items when at least 1 is required).

Testing Complex Queries

Simple CRUD operations are straightforward to test. Complex queries—with joins, aggregations, filtering, and sorting—require more careful testing to ensure correctness.

ComplexQueryTests.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
describe('Order Analytics Repository - Complex Queries', () => {
    let analyticsRepo: OrderAnalyticsRepository;
    
    beforeEach(async () => {
        await testDb.truncateAll();
        await seedTestData();
    });
    
    // Test data setup for complex scenarios
    async function seedTestData() {
        // Create customers
        await customerRepo.saveMany([
            { id: 'cust-001', tier: 'GOLD', region: 'US' },
            { id: 'cust-002', tier: 'SILVER', region: 'EU' },
            { id: 'cust-003', tier: 'GOLD', region: 'EU' },
        ]);
        
        // Create orders with different dates and totals
        await orderRepo.saveMany([
            { id: 'ord-1', customerId: 'cust-001', total: 500, createdAt: '2024-01-15' },
            { id: 'ord-2', customerId: 'cust-001', total: 300, createdAt: '2024-01-20' },
            { id: 'ord-3', customerId: 'cust-002', total: 150, createdAt: '2024-01-18' },
            { id: 'ord-4', customerId: 'cust-003', total: 1200, createdAt: '2024-01-10' },
            { id: 'ord-5', customerId: 'cust-003', total: 250, createdAt: '2024-02-01' },
        ]);
    }
    
    // ===== AGGREGATION QUERIES =====
    describe('getCustomerLifetimeValue()', () => {
        it('should correctly sum all order totals per customer', async () => {
            const ltv = await analyticsRepo.getCustomerLifetimeValue('cust-001');
            
            expect(ltv.totalOrders).toBe(2);
            expect(ltv.totalSpent).toEqual(Money.of(800)); // 500 + 300
            expect(ltv.averageOrderValue).toEqual(Money.of(400));
        });
        
        it('should return zero for customer with no orders', async () => {
            await customerRepo.save({ id: 'cust-empty', tier: 'BRONZE' });
            
            const ltv = await analyticsRepo.getCustomerLifetimeValue('cust-empty');
            
            expect(ltv.totalOrders).toBe(0);
            expect(ltv.totalSpent).toEqual(Money.zero());
        });
    });
    
    // ===== JOIN QUERIES =====
    describe('getTopCustomersByRegion()', () => {
        it('should rank customers by spend within each region', async () => {
            const rankings = await analyticsRepo.getTopCustomersByRegion();
            
            // US Region
            const usRanking = rankings.find(r => r.region === 'US');
            expect(usRanking?.customers[0].id).toBe('cust-001'); // $800 total
            
            // EU Region
            const euRanking = rankings.find(r => r.region === 'EU');
            expect(euRanking?.customers[0].id).toBe('cust-003'); // $1450 total
            expect(euRanking?.customers[1].id).toBe('cust-002'); // $150 total
        });
    });
    
    // ===== WINDOW FUNCTION QUERIES =====
    describe('getOrderRankingsForCustomer()', () => {
        it('should rank orders by total within customer history', async () => {
            const rankings = await analyticsRepo.getOrderRankingsForCustomer('cust-001');
            
            // Highest order should be rank 1
            const highestOrder = rankings.find(r => r.orderId === 'ord-1');
            expect(highestOrder?.rank).toBe(1);
            expect(highestOrder?.percentile).toBeCloseTo(100);
            
            const secondOrder = rankings.find(r => r.orderId === 'ord-2');
            expect(secondOrder?.rank).toBe(2);
        });
    });
    
    // ===== FILTER + PAGINATION =====
    describe('searchOrders()', () => {
        it('should filter by date range and customer tier', async () => {
            const results = await analyticsRepo.searchOrders({
                dateFrom: new Date('2024-01-15'),
                dateTo: new Date('2024-01-31'),
                customerTier: 'GOLD',
                page: 1,
                pageSize: 10,
            });
            
            // Only GOLD customers, within date range
            expect(results.orders).toHaveLength(2);
            expect(results.orders.map(o => o.id)).toEqual(
                expect.arrayContaining(['ord-1', 'ord-2'])
            );
            expect(results.totalCount).toBe(2);
        });
        
        it('should correctly paginate large result sets', async () => {
            // Seed 50 more orders
            for (let i = 0; i < 50; i++) {
                await orderRepo.save({
                    id: `bulk-ord-${i}`,
                    customerId: 'cust-001',
                    total: 100,
                    createdAt: new Date('2024-02-01'),
                });
            }
            
            const page1 = await analyticsRepo.searchOrders({
                customerId: 'cust-001',
                page: 1,
                pageSize: 20,
            });
            
            const page2 = await analyticsRepo.searchOrders({
                customerId: 'cust-001',
                page: 2,
                pageSize: 20,
            });
            
            expect(page1.orders).toHaveLength(20);
            expect(page2.orders).toHaveLength(20);
            expect(page1.totalCount).toBe(52); // 2 original + 50 bulk
            
            // No overlap between pages
            const page1Ids = new Set(page1.orders.map(o => o.id));
            const page2Ids = new Set(page2.orders.map(o => o.id));
            const overlap = [...page1Ids].filter(id => page2Ids.has(id));
            expect(overlap).toHaveLength(0);
        });
    });
    
    // ===== VERIFYING SQL CORRECTNESS =====
    describe('query correctness verification', () => {
        it('should use index for customer lookup', async () => {
            // Verify the query uses the expected index
            const explanation = await testDb.pool.query(
                'EXPLAIN ANALYZE ' + analyticsRepo.getQueryForCustomerOrders()
            );
            
            const plan = explanation.rows.map(r => r['QUERY PLAN']).join('
');
            expect(plan).toContain('Index Scan');
            expect(plan).not.toContain('Seq Scan');
        });
    });
});

Testing Query Performance

Integration tests can verify that queries use expected indexes via EXPLAIN ANALYZE. However, performance testing with realistic data volumes typically requires separate load testing—integration test datasets are too small to reveal performance issues.

Testing Database Migrations

Schema migrations are one of the riskiest operations in production systems. Testing migrations before deployment can prevent data corruption and downtime.

MigrationTests.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
describe('Database Migration Tests', () => {
    let migrator: DatabaseMigrator;
    
    beforeEach(async () => {
        // Start with clean database
        await testDb.dropAllTables();
        migrator = new DatabaseMigrator(testDb.pool);
    });
    
    describe('Migration Sequencing', () => {
        it('should apply migrations in order', async () => {
            await migrator.migrateToLatest();
            
            const appliedMigrations = await migrator.getAppliedMigrations();
            
            expect(appliedMigrations).toEqual([
                'V001__create_users_table',
                'V002__create_orders_table',
                'V003__add_order_status',
                'V004__add_user_preferences',
            ]);
        });
        
        it('should be idempotent - running twice has no effect', async () => {
            await migrator.migrateToLatest();
            await migrator.migrateToLatest();
            
            const appliedMigrations = await migrator.getAppliedMigrations();
            // No duplicates
            const uniqueMigrations = [...new Set(appliedMigrations)];
            expect(uniqueMigrations.length).toBe(appliedMigrations.length);
        });
    });
    
    describe('Data Migration Correctness', () => {
        it('should preserve existing data during schema changes', async () => {
            // Apply migrations up to V003
            await migrator.migrateTo('V003');
            
            // Insert data in old schema
            await testDb.pool.query(`
                INSERT INTO orders (id, customer_id, total)
                VALUES ('order-1', 'cust-1', 100.00)
            `);
            
            // Apply V004 which adds status column with default
            await migrator.migrateTo('V004');
            
            // Verify data preserved and new column populated
            const result = await testDb.pool.query(
                'SELECT * FROM orders WHERE id = $1',
                ['order-1']
            );
            
            expect(result.rows[0].customer_id).toBe('cust-1');
            expect(result.rows[0].total).toBe('100.00');
            expect(result.rows[0].status).toBe('PENDING'); // Default value
        });
    });
    
    describe('Rollback Testing', () => {
        it('should cleanly rollback failed migration', async () => {
            await migrator.migrateTo('V003');
            
            // V004_broken intentionally fails mid-migration
            const brokenMigration = {
                name: 'V004_broken',
                up: async (db: Pool) => {
                    await db.query('ALTER TABLE orders ADD COLUMN temp INT');
                    throw new Error('Intentional failure');
                },
                down: async (db: Pool) => {
                    await db.query('ALTER TABLE orders DROP COLUMN temp');
                }
            };
            
            // Migration should fail
            await expect(migrator.apply(brokenMigration))
                .rejects.toThrow('Intentional failure');
            
            // Table should be in original state
            const columns = await testDb.getTableColumns('orders');
            expect(columns).not.toContain('temp');
        });
    });
    
    describe('Production-Like Migration Testing', () => {
        it('should handle migration with large dataset', async () => {
            await migrator.migrateTo('V003');
            
            // Seed significant data volume
            const batchSize = 1000;
            for (let batch = 0; batch < 10; batch++) {
                const values = Array.from({ length: batchSize }, (_, i) => 
                    `('order-${batch}-${i}', 'cust-1', ${100 + i})`
                ).join(',');
                
                await testDb.pool.query(`
                    INSERT INTO orders (id, customer_id, total)
                    VALUES ${values}
                `);
            }
            
            // Run migration that adds index (can be slow on large tables)
            const startTime = Date.now();
            await migrator.migrateTo('V005'); // Adds index
            const duration = Date.now() - startTime;
            
            console.log(`Migration completed in ${duration}ms`);
            
            // Verify index was created
            const indexes = await testDb.getIndexes('orders');
            expect(indexes).toContain('idx_orders_customer_id');
        });
    });
});

Test with Production-Like Data Volumes

Migrations that work instantly on 100 rows may timeout on 10 million rows. Test migrations with realistic data volumes to catch performance issues before they cause production outages.

Managing Test Data Lifecycle

Effective test data management is essential for reliable database integration tests. Tests must be isolated, reproducible, and maintainable.

TestDataManagement.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
// ===== PATTERN: Test Data Builder with Seeding =====
class TestDatabaseSeeder {
    constructor(private readonly db: TestDatabase) {}
    
    // Seed minimal required data - foreign key dependencies
    async seedEssentials(): Promise<EssentialData> {
        const [defaultCustomer, defaultProduct] = await Promise.all([
            this.db.customers.create({
                id: 'test-customer-default',
                name: 'Default Test Customer',
                email: 'test@example.com',
            }),
            this.db.products.create({
                id: 'test-product-default',
                sku: 'DEFAULT-SKU',
                price: Money.of(99.99),
            }),
        ]);
        
        return { defaultCustomer, defaultProduct };
    }
    
    // Scenario-specific seeding
    async seedOrderScenario(options: {
        orderCount: number;
        customerId?: string;
        dateRange?: { from: Date; to: Date };
    }): Promise<Order[]> {
        const customerId = options.customerId ?? 'test-customer-default';
        const orders: Order[] = [];
        
        for (let i = 0; i < options.orderCount; i++) {
            const order = await this.db.orders.create({
                customerId,
                items: [{ productId: 'test-product-default', quantity: 1 }],
                createdAt: options.dateRange
                    ? randomDateInRange(options.dateRange)
                    : new Date(),
            });
            orders.push(order);
        }
        
        return orders;
    }
}
 
// ===== PATTERN: Test Data Factory with Relationships =====
class TestDataFactory {
    private counter = 0;
    
    customer(overrides: Partial<Customer> = {}): Customer {
        const id = this.nextId('customer');
        return Customer.create({
            id,
            name: `Test Customer ${id}`,
            email: `customer-${id}@test.com`,
            tier: 'STANDARD',
            ...overrides,
        });
    }
    
    order(customer: Customer, items: OrderItem[] = []): Order {
        if (items.length === 0) {
            items = [this.orderItem()];
        }
        
        return Order.create({
            id: this.nextId('order'),
            customerId: customer.id,
            items,
            status: 'PENDING',
        });
    }
    
    orderItem(overrides: Partial<OrderItem> = {}): OrderItem {
        return {
            productId: this.nextId('product'),
            quantity: 1,
            unitPrice: Money.of(19.99),
            ...overrides,
        };
    }
    
    private nextId(prefix: string): string {
        return `${prefix}-${++this.counter}`;
    }
}
 
// ===== PATTERN: Cleanup Strategies =====
class DatabaseCleanup {
    constructor(private readonly pool: Pool) {}
    
    // Fast: Truncate all tables (resets autoincrement)
    async truncateAll(): Promise<void> {
        const tables = await this.getAllTables();
        await this.pool.query(
            `TRUNCATE ${tables.join(', ')} CASCADE`
        );
    }
    
    // Selective: Delete specific data without resetting sequences
    async deleteTestData(testRunId: string): Promise<void> {
        await this.pool.query(
            'DELETE FROM orders WHERE id LIKE $1',
            [`${testRunId}%`]
        );
        await this.pool.query(
            'DELETE FROM customers WHERE id LIKE $1',
            [`${testRunId}%`]
        );
    }
    
    // Isolation: Use unique prefixes per test for parallel execution
    static generateTestPrefix(): string {
        return `test-${Date.now()}-${Math.random().toString(36).slice(2, 8)}`;
    }
}
 
// ===== USAGE IN TESTS =====
describe('Order Processing', () => {
    let seeder: TestDatabaseSeeder;
    let factory: TestDataFactory;
    let cleanup: DatabaseCleanup;
    
    beforeAll(async () => {
        seeder = new TestDatabaseSeeder(testDb);
        cleanup = new DatabaseCleanup(testDb.pool);
        await seeder.seedEssentials();
    });
    
    beforeEach(async () => {
        factory = new TestDataFactory();
        // Clean test-specific data, keep essentials
        await cleanup.truncateAll();
        await seeder.seedEssentials();
    });
    
    it('should process order correctly', async () => {
        const customer = factory.customer({ tier: 'GOLD' });
        await customerRepo.save(customer);
        
        const order = factory.order(customer, [
            factory.orderItem({ quantity: 5 }),
        ]);
        
        const result = await orderService.processOrder(order);
        expect(result.status).toBe('CONFIRMED');
    });
});

Test Data Management Best Practices

•Unique IDs per test: Avoid collisions when tests run in parallel
•Factory methods: Generate valid test data with sensible defaults
•Relationship-aware creation: Factories should handle foreign key dependencies
•Minimal seeding: Only seed what's necessary for each test
•Cleanup between tests: Use TRUNCATE or transaction rollback for isolation
•Avoid shared mutable state: Don't depend on data created by other tests

Summary: Database Integration Testing

We've explored the essential practices for testing database integrations in object-oriented systems. Let's consolidate the key principles:

Key Takeaways

•Real database tests catch what mocks miss — SQL syntax, ORM mapping, constraints, and database-specific behavior.
•Choose the right test database strategy — In-memory for speed, Docker for realism, transactional rollback for isolation.
•Test repositories comprehensively — CRUD operations, edge cases, constraint enforcement, and data integrity.
•Complex queries need thorough testing — Aggregations, joins, window functions, and pagination require dedicated tests.
•Migrations are high-risk and must be tested — Verify ordering, data preservation, rollback capability, and performance.
•Manage test data lifecycle carefully — Use factories, unique IDs, and proper cleanup for reliable tests.

What's next: With a solid understanding of database integration testing, we'll now explore broader integration test strategies. The next page covers how to design and organize your overall integration testing approach, including test pyramids, environment management, and CI/CD integration.

Page Complete

You now have practical expertise in database integration testing. You can test repositories, complex queries, and migrations effectively, with proper test data management. Next, we'll zoom out to integration test strategies at the organizational level.

Database Integration Testing

Testing the Data Layer: Where Objects Meet Persistence

Why Test Against Real Databases?

Test Database Strategies

Testing Repository Implementations

Testing Complex Queries

Testing Database Migrations

Managing Test Data Lifecycle

Summary: Database Integration Testing

There’s more to understand.

Database Integration Testing

Testing the Data Layer: Where Objects Meet Persistence

Why Test Against Real Databases?

Test Database Strategies

Testing Repository Implementations

Testing Complex Queries

Testing Database Migrations

Managing Test Data Lifecycle

Summary: Database Integration Testing

There’s more to understand.