Your suite mocks the database, every test passes, and the release still broke. What should you have tested instead?

A mock is your assumption, written down twice

When you patch the database client and assert that your code called it with a particular query, you have verified that the code does what you believe the client requires. If the belief is wrong, the test agrees with the code and both are wrong together. The suite is green because it is comparing your implementation against your own model of the dependency, and the dependency was never consulted.

That is why "all the tests pass and production broke" is the expected outcome of a heavily mocked boundary rather than a surprising one. The tests were never able to fail for the reason production failed. Being able to say that plainly is most of the answer, because the remedy follows from it: some tests have to run against the thing itself, or nothing in your suite has ever observed how it behaves.

What the mock could not have caught

It helps to be concrete about the defect classes that vanish behind a mocked database, because they are the ones that reach production.

Anything about the query text goes first. A syntax error, a column that was renamed, a table the migration never created, an ambiguous reference across a join — a mock returns whatever you told it to return regardless of the SQL it was handed. Then constraint behaviour: unique violations, foreign keys, not-null defaults, and what actually happens on a conflicting insert. Then types and coercion, which is where the interesting bugs live, because the database's idea of a decimal, a timezone-aware timestamp or a JSON column is not always your Python object round-tripped. Then transactional semantics: whether the code holds a transaction open across a network call, whether it retries a serialisation failure, whether a rollback undoes what you think.

There is also a whole category about how much work is done rather than whether it is correct. A mocked ORM cannot show you that a loop triggers a query per row, because the mock answers instantly and never counts. That defect is invisible until the row count grows.

Autospec is the cheap half of the fix

Before changing your testing strategy, close the gap that costs nothing. A plain MagicMock accepts any attribute name and any call signature and returns another mock, so a test keeps passing after the real method is renamed, after a parameter is added, and after a call site starts passing arguments the real function would reject.

# Passes even though the real client has no method called `execut`.
client = MagicMock()
client.execut(sql)

# create_autospec builds the double from the real object, so the same
# call raises AttributeError and a wrong signature raises TypeError.
client = create_autospec(DatabaseClient, instance=True)

Autospeccing derives the double from the real class, so member names and call signatures are checked against reality and the test fails when the code drifts from the interface. What it still cannot check is behaviour: the return values are whatever you configured, so every assumption about what comes back remains yours. Autospec turns a mock from a test of your memory into a test of the interface, and no further.

Test the boundary against the real thing, a few times

The durable answer is not to delete the mocks. It is to accept that one layer of the suite has to exercise the real dependency, and to keep that layer small enough that people still run it.

Concentrate the database access in a narrow layer — repository functions, a data-access module, whatever your codebase calls it — and test that layer against a real instance of the same engine and version you deploy, started for the test run in a container or on a local service. Everything above it can then mock that layer honestly, because the layer's behaviour has actually been verified somewhere. This is the argument for keeping the boundary narrow in the first place: a codebase with SQL scattered through fifty modules has fifty places needing real verification, and one with a data layer has one.

Those tests are worth writing against real data shapes rather than empty tables, and they should cover the operations mocks are blind to: the migration applying cleanly, a conflicting insert, a constraint firing, a decimal and a timestamp surviving a round trip, and the query returning what you claim under a realistic row count.

The fake that lies in a different way

The tempting shortcut is a lighter substitute — an in-memory dictionary standing in for the store, or SQLite standing in for the production engine. Both are legitimate and both need their limits stated.

A hand-written in-memory fake is genuinely useful when it implements the same contract, because it is fast and it makes the collaborator's behaviour explicit rather than configured per test. Its risk is divergence: the real store gains a constraint or changes an ordering guarantee and the fake does not, so the fake drifts into being a second implementation of the interface that nobody verifies. The discipline that keeps it honest is running the same contract test suite against both the fake and the real implementation, so a difference fails a build.

Swapping database engines is the version of this with the worst ratio. SQL dialects differ, constraint enforcement and type coercion differ, transaction isolation and locking behaviour differ, and the differences are concentrated in exactly the areas you were trying to test. A suite that passes on SQLite and deploys to PostgreSQL is testing a program you do not ship.

Where mocks remain the right tool

None of this is an argument against test doubles, and an interviewer will listen for whether you can draw the line rather than swing to the opposite extreme. Mock the things whose behaviour you do not want to invoke: a payment provider, an email sender, a third-party API you would be rate-limited by, the clock, randomness, and anything slow enough to make the suite unpleasant. For an external API, the honest complement to the mock is a small set of tests run against a sandbox on a schedule rather than on every commit, so that a provider change is discovered by your build rather than by a customer.

The rule that generalises is that a double is appropriate where you own the contract or can verify it elsewhere, and inappropriate as the only thing standing between your code and a dependency whose behaviour you have assumed.

Mocks verify your code against your beliefs, so a green suite over a mocked database says nothing about the database. Autospec the doubles so the interface is checked, keep data access in one narrow layer, and test that layer against the real engine.