Higher fidelity?

What does that mean in this context?

What higher fidelity do you get with a real postgres over a SQLite in memory or even pglite or whatever.

The point isn’t you shouldn’t have a database, the point is what are your concerns? For me and my teams, we care about our code, the performance of that code, the correctness of that code, and don’t test against a live database so that we understand the separation of concerns between our app and its storage. We expect a database to be there. We expect it to have such and such schema. We don’t expect it to live at a certain address or a certain configuration as that is the databases concern.

We tell our app at startup where that address is or we don’t. The app should only care whether we did or not, if not, it will need to make one to work.

This is the same logic with unit testing. If you’re unit testing against a real database, that isn’t unit testing, that’s an integration test.

If you do care about the speed of your database and how your app scales, you aren’t going to be doing that on your local machine.

There is your idealization, and there is reality. Mocks are to be avoided. I reserve them for external dependencies.

> What higher fidelity do you get with a real postgres over a SQLite in memory or even pglite or whatever

You want them to have the same syntax and features, to the extent that you use them, or you'll have one code path for testing and another for production. For example, sqlite does not support ARRAYs or UUIDs natively, so you'll have to write a separate implementation. This is a vector for bugs.