Database mocks aren’t really worth it

It’s tempting to rely on mocks for database calls. The banter is faster and often feels more direct. However, testing against a real database reveals hidden pitfalls that may appear as the application matures. Issues such as rare constraint violations, default value handling, or even performance bottlenecks may arise only when the code is used against actual data.

The importance of real database testing

Consider a simple example where you create a user in your application. One method uses mocks, while the other uses the real database:

# Mocked approach
it 'creates a user (mocked)' do
  user_repo = double('UserRepository')
  expect(user_repo).to receive(:create).with(
    email: '(email protected)',
    status: 'active'
  ).and_return(User.new)

  service = UserService.new(user_repo)
  service.create_user('(email protected)')
end

# Real database approach
it 'creates a user (real database)' do
  service = UserService.new
  result = service.create_user('(email protected)')

  expect(User.find(result.id)).to be_present
  expect(User.find(result.id).status).to eq('active')
end

A real database approach reveals potential data integrity problems or constraints. It can also highlight how your application handles default values ​​and indexes. By catching these issues early, you can save debugging time and reduce the risk of discovering them too late in production.

Proof of your upcoming tests

Over time, new features or schema changes can affect how the application interacts with the database. If the database is mocked, these changes may not be detected. A test using a real database can catch errors caused by new validations, data type changes, or timestamp precision changes. The following test can easily fail if a new status validation is introduced or if the status timestamps are handled differently:

RSpec.describe OrderService do
  it 'handles order creation with status tracking' do
    order = described_class.create_order(
      user_id: user.id,
      amount: 100
    )

    expect(order.status_changes).to include(
      from: nil,
      to: 'pending'
    )
    expect(order.status_changed_at).to be_present
  end
end

Because it interacts with the real database, this test guards against inconsistencies between your code and the actual schema.

Maintain a realistic state of the database

When you test whether account balances or transaction totals are calculated correctly, a real database can reveal integration, isolation, and aggregate issues. For example:

RSpec.describe AccountBalanceService do
  it 'calculates correct balance after transactions' do
    account = create(:account)
    create(:transaction, account: account, amount: 100)
    create(:transaction, account: account, amount: -30)

    balance = described_class.calculate_balance(account)

    expect(balance).to eq(70)
    expect(account.transactions.count).to eq(2)
  end
end

This approach ensures that transaction management and aggregate calculations remain accurate as they progress, capturing data consistency problems that can hide fraud.

Understanding the boundaries of the testing service

Many applications have multiple layers, such as controllers, services, repositories, and external service integrations. Each layer can focus on its own responsibilities:

RSpec.describe OrderProcessingService do
  it 'creates order with proper database state' do
    service = described_class.new
    result = service.create_order(user_id: 1, amount: 100)

    expect(Order.find(result.id)).to be_present
    expect(Order.find(result.id).status).to eq('pending')
  end
end

RSpec.describe OrdersController do
  let(:order_service) { instance_double(OrderProcessingService) }

  it 'delegates to order service' do
    expect(order_service).to receive(:create_order)
      .with(user_id: '1', amount: 100)
      .and_return(OpenStruct.new(id: 1))

    post '/orders', params: { user_id: '1', amount: 100 }
    expect(response).to be_successful
  end
end

By allowing the service layer to use a real database while controllers mock services, you isolate testing concerns. This keeps your tests focused and ensures that database interactions are verified by the layer that actually handles them.

Testing strategy and layers of responsibility

For the data access layer, real database testing is essential. Verifying foreign keys, referential integrity, and database transactions helps ensure that the foundation remains strong. At the service layer, real database testing reveals how the business logic interacts with the data. In contrast, controllers focus on handling parameters and responses and can mock service calls without sacrificing coverage.

Use cases, or interaction layers, can also mock any lower-level services they orchestrate. It makes it easier to point out errors in the logic that integrates many services, without introducing more dependence on external systems or the database.

Balance real database tests and mocks

Real database tests are especially important for storage procedures, complex data relationships, and performance-sensitive scenarios. Meanwhile, mocking remains valuable for verifying higher-level orchestration and external service interactions. Controllers can focus on incoming and outgoing data without worrying about the complexity of database operations or third-party calls.