External boundaries

Yes, Testcontainers is worth it for tests of code that sends SQL to a database: run each query test against the production database engine in a container, and start one container for the whole test run instead of one per test.

Yes, test an API client that you wrote for another company's API: serve recorded responses from a fake HTTP server, including error statuses and a second page of results, and assert the request the client sends and the values it returns.

Test the queries you write through an ORM with integration tests against the database engine production runs; do not test the ORM library itself, and do not test queries against a mocked DbContext or an in-memory provider.

Test each query in the repository layer with an integration test against the database engine production runs, using rows that each filter must return and rows it must leave out; do not unit test repositories with a mocked database.

Yes, test a thin wrapper around third-party code minimally: one integration test for each wrapper method, run against the real library or a local copy of its service, that asserts what the library stored or returned, while the callers' unit tests keep mocking the wrapper.

Yes, tests of code that runs SQL should use a real database of the same engine and major version as production, started in a container and built by the real migrations; unit tests of rules that take and return plain values need no database.

Yes, give code that reads or writes files one unit test per file it handles, with real files in a temporary folder that the test framework creates and deletes, and fake the file system only to simulate errors such as a full disk.

No, do not write tests of a web framework's own features, such as routing, model binding or the code a project template generates; test your own endpoints through the real framework, with requests that give each outcome a different response.

No, do not test a third-party library's own features; test your code that uses it, with the real library running and inputs that give a different result if the behaviour you rely on changes, and run those tests on every dependency upgrade.

No, unit tests should not call a real third-party API: replace it with a fake HTTP layer that returns recorded responses, and check the live API in a scheduled run outside the build, plus an alert on production responses that your code cannot parse.