Node.js & ExpressTesting code that touches a real database is one of the harder challenges in backend development. You need real data to verify real behaviour, but you also need tests that are fast, isolated, and repeatable. This lesson covers the patterns that make database testing practical rather than painful.
The core problem: state pollution
Database tests have a fundamental problem. If test A creates a user, and test B runs next and queries all users, test B might see test A's data. That makes test B dependent on test A's execution order, which breaks isolation. One flaky test can cascade into a dozen failures.
The solution is to reset database state between tests. You have a few options:
| Strategy | How it works | Best for |
|---|---|---|
| Transaction rollback | Wrap each test in a transaction, roll back at the end | Unit + integration tests |
| Truncate tables | Delete all rows in afterEach | Simpler setups |
| In-memory database | Use SQLite in-memory per test | Fast, fully isolated |
| Test containers | Spin up a real DB in Docker | True production parity |
TransactionWhat is transaction?A group of database operations that either all succeed together or all fail together, preventing partial updates. rollbackWhat is rollback?Undoing a database migration or deployment to restore the previous state when something goes wrong. pattern
The transaction rollback pattern is elegant because it leaves no trace. Each test starts a transaction, does its work inside it, and then rolls back, as if the test never ran.
// tests/helpers/withTransaction.js
import { db } from '../../database/config.js';
export async function withTransaction(callback) {
const trx = await db.transaction();
try {
const result = await callback(trx);
await trx.rollback(); // Always roll back - we never want to persist test data
return result;
} catch (error) {
await trx.rollback();
throw error;
}
}// Usage in tests
import { withTransaction } from '../helpers/withTransaction.js';
import { createUser, findUser } from '../../services/userService.js';
describe('User database operations', () => {
it('creates a user and retrieves it by id', async () => {
await withTransaction(async (trx) => {
const user = await createUser({ name: 'Alice', email: 'alice@test.com' }, trx);
expect(user.id).toBeDefined();
expect(user.name).toBe('Alice');
const found = await findUser(user.id, trx);
expect(found.email).toBe('alice@test.com');
});
// After this block, the transaction is rolled back.
// Alice does not exist in the database.
});
});trx parameter from the start.Test factories
Copy-pasting user data across every test file is a maintenance nightmare. A test factory is a function that generates valid test data with sensible defaults, letting you override only what matters for a specific test.
// tests/factories/userFactory.js
let counter = 0;
export function makeUser(overrides = {}) {
counter++;
return {
name: `Test User ${counter}`,
email: `user-${counter}@test.com`,
role: 'user',
createdAt: new Date(),
...overrides
};
}
export function makeAdmin(overrides = {}) {
return makeUser({ role: 'admin', ...overrides });
}// In your tests
import { makeUser, makeAdmin } from '../factories/userFactory.js';
it('allows admins to delete users', async () => {
const admin = makeAdmin();
const target = makeUser();
// Each call generates unique email/name, so no conflicts
const createdAdmin = await db('users').insert(admin).returning('*');
const createdTarget = await db('users').insert(target).returning('*');
const result = await UserService.deleteUser(createdAdmin[0].id, createdTarget[0].id);
expect(result.success).toBe(true);
});Using a counter for unique values
The counter variable in the factory ensures every generated user has a unique email. Without this, parallel tests creating users with the same email would collide on your unique constraint.
Setting up a test database
For integration tests, you need a real database, not a mockWhat is mock?A fake replacement for a real dependency in tests that records how it was called so you can verify interactions.. The cleanest approach is a dedicated SQLite file for tests, or an in-memory SQLite database:
// tests/setup.js - runs before the test suite via setupFiles in vitest.config.js
import { db } from '../database/config.js';
beforeAll(async () => {
// Run all migrations on the test database
await db.migrate.latest();
});
afterAll(async () => {
// Close the connection pool when tests finish
await db.destroy();
});// database/config.js - pick the right config based on environment
import knex from 'knex';
const isTest = process.env.NODE_ENV === 'test';
export const db = knex({
client: 'sqlite3',
connection: {
filename: isTest ? ':memory:' : './data/production.db'
},
useNullAsDefault: true
}); for SQLite gives you a fresh, empty database every time your test process starts. It is the fastest isolation strategy, but it only works if you are using SQLite. For PostgreSQL or MySQL, use a dedicated test database with a name like myapp_test`.Seeding specific test data
Some tests need specific data in a particular shape. Rather than relying on other tests to create it, seed it explicitly:
describe('GET /api/users/:id', () => {
let seedUser;
beforeAll(async () => {
// Insert a known user before this suite
const [user] = await db('users')
.insert(makeUser({ name: 'Seeded Alice', email: 'seeded@test.com' }))
.returning('*');
seedUser = user;
});
afterAll(async () => {
// Clean up - delete just what we inserted
await db('users').where({ id: seedUser.id }).delete();
});
it('returns the seeded user', async () => {
const res = await request(app)
.get(`/api/users/${seedUser.id}`)
.expect(200);
expect(res.body.data.name).toBe('Seeded Alice');
});
it('returns 404 for a non-existent id', async () => {
await request(app).get('/api/users/99999').expect(404);
});
});Testing database constraints
Your database schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. enforces rules, unique emails, foreign keys, required fields. Test these constraints explicitly rather than assuming they work:
describe('Database constraints', () => {
it('enforces unique email constraint', async () => {
const userData = makeUser({ email: 'unique@test.com' });
await db('users').insert(userData);
// Inserting same email again should throw
await expect(
db('users').insert({ ...userData, name: 'Duplicate' })
).rejects.toThrow(/unique/i);
// Cleanup
await db('users').where({ email: 'unique@test.com' }).delete();
});
it('rejects null name', async () => {
await expect(
db('users').insert({ email: 'valid@test.com', name: null })
).rejects.toThrow();
});
});Quick reference
| Pattern | When to use it |
|---|---|
| Transaction rollback | Service-level tests where you pass a trx argument |
beforeAll + afterAll cleanup | Integration tests with seeded data |
| In-memory SQLite | Fast, self-contained unit/integration tests |
| Test factory functions | Any test that needs realistic data |
NODE_ENV=test config | Switching between test and dev databases |
db.migrate.latest() in setup | Keeping schema in sync before tests run |
// Test database setup with transactions
import { db } from '../database/config';
export async function withTransaction(callback) {
const trx = await db.transaction();
try {
const result = await callback(trx);
await trx.rollback();
return result;
} catch (error) {
await trx.rollback();
throw error;
}
}
// Usage in tests
it('should create user', async () => {
await withTransaction(async (trx) => {
const user = await createUser(data, trx);
expect(user.id).toBeDefined();
});
});