* You're just describing arranging the test setup, which is independent of storage medium.
* Is your repository interface untyped?
* Depends on the fidelity of your fake, but generally I find FKs to be an antipattern these days.
* What are you checking primary keys FOR? Uniqueness is easy and I avoid more complex constraints and triggers.
* ... I'm beginning to suspect we have a difference in terminology. I'm not saying a mock. A typical DB fake would be array or hash table backed in memory. So an insert is "real" and an update or delete would be too.
* Well, sure, but I can get 95% fidelity for 1% of the resources.
Even if the fake is super-fast compared to postgres (1%) in CPU resources: Why spend extra engineering effort to build something that has less fidelity?
CPU for test runs is cheap... and if you use any AI agent at all, tests runs faster than the agent does work. Why does it matter how much faster they run?
The goal of a regression test suite is to catch issues before you deploy to prod. That 95% is a nagging source of doubt. CAN you just release the code straight to prod? Or not?
Many times integration tests including the real postgres and real migration catches bugs for me before they go to prod.
Personally I would just never go with 95% fidelity in tests. Testing with the real postgres is just so good.
And just to get test coverage for the migrations themselves?
(In my case I also use stored procedures, RLS etc that needs those; with your setup that is just not on the table I think or you loose coverage of critical code.)