When should I write integration tests?

Write an integration test when code reads or writes a database or calls another service, and a wrong result would look plausible. Start with one test per main path and one per hand-written query.

Are integration tests worth it for code that is not mission critical?

Yes, integration tests pay off for product code whose users would see wrong data, because a mocked unit test never runs the query. Cover each main path with one test against a real database. For a side project that only you run, opening the page after each change is enough.

Should I test all methods with integration tests?

No, integration tests belong on the paths that reach the database or another service. Methods that only compute belong in unit tests, which run faster and cover more input combinations.

Should integration tests use a real database?

Yes, integration tests should use the same database engine as production, because an in-memory substitute treats collation, JSON and dates differently. Testcontainers starts that engine in a container for the test run.

Should I write integration tests?

Verdict

Yes

Yes, write integration tests for the code that talks to your database or to another service: one test per main path against a real database in CI, and one test per hand-written query with rows that each filter must leave out.

Why

Blast radiususers
Change frequencyregularly
Detectabilityeventually
Reversibilitywith-effort
Test costmoderate

Write integration tests for the code where your application meets the database or another service. The typical case is a web backend whose unit tests mock the repository and the HTTP clients, so no test runs a real query. Blast radius is users, and Change frequency is regularly, because most features change a query. Detectability is eventually, because a wrong filter or column mapping returns plausible rows without an error. Reversibility is with-effort, because code acts on the wrong rows until a script repairs them, and Test cost is moderate, because a test against a local database takes about an hour. Rule R11 gives Test.

When the decision changes
When	Decision	Why
The code writes refunds to the payment provider and to the ledger table	Test mandatory: integration tests for each refund path and each provider error response	Blast radius rises to money and Reversibility to costly
The queries add the tenant ID to every read of customer records	Test mandatory: an integration test with records of two tenants for each query	Blast radius rises to safety-or-legal and Detectability to never, because a missing filter leaks records without an error
The failure to catch is a change in a partner's responses, and the partner has no sandbox or test account	Test it differently: alert when the share of failed or empty partner responses changes	Test cost rises to heavy, because only the partner's production API shows the change
The database code is a one-time backfill script	Test it differently: run the backfill on a copy of production data and check the result with a query	Change frequency falls to once, so a test in the suite would never run again
The code only loads records by ID, and a broken mapping shows an error page that users report	Test minimally: one integration test that starts the app and loads one record	Detectability moves to same-day and Reversibility to trivial, because a lookup by ID stores nothing
The app is a side project that only you run on your laptop	Do not write integration tests; open the page after each change	Blast radius falls to none, because you bear every failure alone

What breaks if you don't test

A mocked repository returns the rows each test hands it, so SQL semantics, column mappings, transactions and migrations first run in production. A filter that drops rows with a NULL value shows a shorter list that looks correct, and the customer who misses a record notices weeks later.

What you lose if you over-test

An integration test for every method repeats what unit tests assert and costs more per run: at 300 ms per test with a table reset, 400 tests add two minutes to every CI run. Tests that assert every column break on each schema change. Tests that share rows fail in one order and pass in another.

How to test

Run the tests against your production database engine, started in a container by Testcontainers. An in-memory substitute differs in collation, JSON and date functions.
Write one test per main path through an endpoint, asserting the response and the saved row, with a distinct value in each field.
Write one test per hand-written query, with rows that each filter must leave out, including a NULL in each nullable column it reads.
Give each test its own rows and roll them back after it.
Replace services you do not run with a fake at the HTTP boundary, as the Practical Test Pyramid shows.

Procedure and references

When the answer changes

The code moves money or filters records by tenant or owner.
The other side has no sandbox, so the only realistic test runs in production.
The code runs once, such as a data migration.

Real incident + Code example

The tasks that vanished from My tasks

On a project-tracking product I worked on, the service tests mocked the repository. A pull request added project archiving: an archived column with no default, a backfill that set it to false on existing rows, and p.archived <> true in the query behind the My tasks page. The project form saved false; the CSV importer left the column NULL. In PostgreSQL, NULL <> true yields NULL and the row is dropped, so tasks in projects imported after the release vanished from My tasks while the project pages still showed them. The service tests stayed green. A customer found the gap 19 days later, when a task nobody saw passed its deadline. The fix was p.archived IS NOT TRUE and this test:

// Vitest against a real PostgreSQL started by Testcontainers; no mocks.
test("My tasks includes tasks in imported projects", async () => {
  await db.query(`
    INSERT INTO projects (id, name, archived) VALUES
      (1, 'Website', false),
      (2, 'Old CRM', true),
      (3, 'Imported from CSV', NULL)`);
  await db.query(`
    INSERT INTO tasks (id, project_id, assignee_id, title) VALUES
      (10, 1, 7, 'Update landing page'),
      (11, 2, 7, 'Close old tickets'),
      (12, 3, 7, 'Migrate contacts')`);

  const tasks = await findMyTasks(db, 7);

  // With p.archived <> true the query returned [10].
  expect(tasks.map((t) => t.id)).toEqual([10, 12]);
});

Sources

www.postgresql.org/docs/current/functions-comparison.html

FAQ

When should I write integration tests?: Write an integration test when code reads or writes a database or calls another service, and a wrong result would look plausible. Start with one test per main path and one per hand-written query.
Are integration tests worth it for code that is not mission critical?: Yes, integration tests pay off for product code whose users would see wrong data, because a mocked unit test never runs the query. Cover each main path with one test against a real database. For a side project that only you run, opening the page after each change is enough.
Should I test all methods with integration tests?: No, integration tests belong on the paths that reach the database or another service. Methods that only compute belong in unit tests, which run faster and cover more input combinations.
Should integration tests use a real database?: Yes, integration tests should use the same database engine as production, because an in-memory substitute treats collation, JSON and dates differently. Testcontainers starts that engine in a container for the test run.