Database Testing with Jest

Mocking the Database Layer

For unit tests, mocking your data access layer (a repository class, an ORM model, or a query function) keeps tests fast and avoids any real database dependency. This is ideal for testing business logic that happens to call the database, without re-testing the database itself.

Design your code so database access goes through a dedicated layer (a repository or data access object) that's easy to mock, rather than scattering raw queries throughout your business logic.

// orderService.js
class OrderService {
  constructor(orderRepository) {
    this.orderRepository = orderRepository;
  }

  async getOrderTotal(orderId) {
    const order = await this.orderRepository.findById(orderId);
    if (!order) throw new Error('Order not found');
    return order.items.reduce((sum, item) => sum + item.price, 0);
  }
}

// orderService.test.js
test('calculates the total from order items', async () => {
  const mockRepository = {
    findById: jest.fn().mockResolvedValue({
      items: [{ price: 10 }, { price: 20 }],
    }),
  };
  const service = new OrderService(mockRepository);

  const total = await service.getOrderTotal('order-1');

  expect(total).toBe(30);
});

The business logic (calculating a total) is tested without any real database connection involved.

Using an In-Memory or Test Database

beforeAll(async () => {
  db = await connectToTestDatabase(); // e.g. SQLite in-memory, or a Docker test container
});

beforeEach(async () => {
  await db.migrate.latest();
  await db.seed.run();
});

afterAll(async () => {
  await db.destroy();
});

• Integration tests against a real (test-only) database catch issues mocks can't, like actual SQL query bugs.
• SQLite in-memory databases are popular for fast, disposable integration tests.
• Reset/reseed data before every test to keep tests independent of execution order.
• Never run tests against a shared staging or production database.

Database Testing Cheat Sheet

Choosing between mocking and a real test database.

Isolating Tests with Transactional Rollback

A common pattern for integration tests: wrap each test in a database transaction that's rolled back afterward, so data written during the test never persists, keeping every test independent without needing a full reseed each time.

beforeEach(async () => {
  await db.query('BEGIN');
});

afterEach(async () => {
  await db.query('ROLLBACK');
});

Testing Both the Repository and the Business Logic

A balanced strategy: unit test business logic with a mocked repository (fast, focused), and separately write a smaller set of integration tests specifically for the repository layer itself against a real test database, to confirm the actual queries work correctly.

Common Mistakes

• Testing business logic against a real database when a mocked repository would be faster and equally effective.
• Never writing any real integration tests, missing actual SQL/query bugs entirely.
• Letting tests share and mutate the same database records, causing order-dependent failures.
• Running tests against a shared staging database instead of an isolated test instance.

Key Takeaways

• Mock the data access layer for fast, focused business logic unit tests.
• Use a real (in-memory or containerized) test database for integration tests of the data layer itself.
• Transactional rollback keeps integration tests isolated without full reseeding.
• A balanced suite includes both mocked unit tests and real database integration tests.