[eximius]

I've yet to be convinced all of this effort is worth it, compared to the ease of using a repository pattern and just using a fake for tests.

And, like, I don't think we shouldn't be doing these efforts, I guess, as they may still pay technical advancement dividends down the road or help with cheaper, faster QA envs, all-in-one e2e envs, etc... but for the unit test and service test layers, those bottom several layers of your testing pyramid, fakes for your repository interfaces is so much easier and orders of magnitude cheaper.

[brandur]

Some very significant disadvantages to that approach:

* It's common these days for a single operation to manipulate dozens or even hundreds of database records. Often these records are interrelated because they reference each other. So with fakes, you're faking initial inserts and then faking inserts based on other fake inserts, creating a fragile tree structure of fakes, which models reality very poorly.

* No data type validation on data inserts or updates. Put a string in the integer field? Find out in production.

* No foreign key validation (or just general capacity for checking referential integrity) so you don't find out that you're rows aren't referencing each other correctly until production.

* Similarly, no checks on primary keys, check constraints, triggers do not run, etc.

* Since you're not doing real inserts, you're not doing real updates or deletes on inserted rows. So if those latter operations are referencing the wrong ID, you don't find out until ... you guessed it, production.

* You can try to build up the fidelity of your repository/fake framework, but the more effort you put into it, the closer you are to just rebuilding a database and the slower it'll get. You'll also never achieve actual parity with what your database is doing.

* Building out these big fake frameworks is a lot of work relatively speaking (you didn't need to build out anything for your DB because your non-test code is already using it), and gets you negative gain.

There was a time a long time ago when disk I/O was a lot slower than it is now and maybe there was some argument for a repository/stub system, but that was at least a decade ago, and even then the rationale was thin. These days we have NVMes, and if you're a real speed demon and think those are too slow, you can just put an in-memory SQLite or Postgres in place for your testing and get all the performance advantages with none of the downside.

[eximius]

* 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.

[dagss]

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.)

[dagss]

If you are unit testing something then sure mock out everything you are not concerned with.

But for service layer tests I don't agree with you, including the actual production repository implementation and the DB is very useful.

It is like e2e tests, just leaving the frontend out. I really like having a lot of such tests to be productive with backend development.

To have a 100 such e2e tests complete quickly, spinning up SQL DBs cheaply is essential.

Also for actual e2e tests with frontend I prefer cheap DB clones rather than reusing DB between tests and having to worry about state between tests.

I think perfect layering was more relevant in the 2000-2010 with less powerful machines. Just making pseudo-integration tests and including more than may be strictly needed is fine. It completes quickly enough. When it breaks it is usually obvious what broke without limiting was code is included in the test.

[bbkane]

Once you've done it a time or two, setting up the "clone the db"/"erase the db for each test" pattern isn't that much work (plenty of libraries to help too).

And of course once its set up for a project, adding more tests to it is pretty straightforward. It is slower for each test run, but I had hundreds of tests running serially erasing a MySQL DB before each one and it only took a minute or so, which was well within my tolerance.

So overall I'm a fan; I think there's more benefits than drawbacks. Especially if it's SQLite, where setup is even easier.