Testing Database Transactions

In this lesson, you will learn how to verify that complex, multi-step database operations strictly adhere to ACID properties when a failure occurs mid-execution.

The Problem: Partial State Corruption

When you perform an operation that spans multiple tables—like deducting a balance in a Wallets table and incrementing a count in a Ledger table—you risk "partial commits." If your code crashes between these two steps, your database might be left in an inconsistent state, violating the Atomicity and Consistency requirements of ACID.

To test this, we don't just test the "happy path"; we explicitly inject failures to ensure the database rolls back to its original state.

Testing for Atomicity

The strategy is to simulate a failure after the first operation but before the final commit. You can achieve this by triggering an error inside a service method that uses a transaction block.

it('should rollback all changes when the second operation fails', async () => {
  const initialBalance = await getBalance(userId);
  
  // We trigger a deliberate error after the first DB write
  await expect(
    transferFunds(userId, targetId, amount, { simulateError: true })
  ).rejects.toThrow('Simulated DB Failure');

  // Verify the rollback: the balance should remain unchanged
  const finalBalance = await getBalance(userId);
  expect(finalBalance).toBe(initialBalance);
});

Verification Tactics

1. State Inspection: After your code throws the expected error, query the database directly. If the transaction was successful in its rollback, the data must match the state it was in before the transaction began.
2. Row Count Validation: If your transaction involves an INSERT, check that the total count of rows remains identical to the pre-test count.
3. Use Integration Tests: Because ACID properties are enforced by the database engine (like PostgreSQL or MySQL), you cannot rely on mocks here. You must use a real database instance (often a ephemeral Docker container) to verify that the database engine correctly handles the ROLLBACK command sent by your Node.js driver.

By verifying that a partial failure leaves your data untouched, you guarantee that your application is resilient against intermittent infrastructure crashes and race conditions.

Key Takeaway: To validate ACID compliance, you must inject failures mid-transaction and verify that the database engine forces a complete rollback to the initial state.