It won't help you with database specific differences. But there should be very few of those if you're using a framework that abstracts away the database. Like Django.
But I really want that database-specific behaviour. :) PostgreSQL does so many amazing things (recursive CTEs, jsonb, etc) that actively make our system better. If there was a fork of Django that optimized for leveraging advanced postgres features, I'd use it.
postgres installs easily on WSL2 or whatever Linux distribution you're using.
It's possible to use PGlite from any language using native Postgres clients and pg-gateway, but you lose some of the nice test DX when it's embed directly in the test code.
I'm hopeful that we can bring PGlite to other platforms, it's being actively worked on.
The other thing I hope we can look at at some point is instant forks of in memory databases, it would make it possible to setup a test db once and then reset it to a known state for each test.
(I work on PGlite)
I want to develop on postgres and test on postgres because I run postgres in production and I want to take advantage of all of its features. I don't understand why a person would 1) develop/test on a different database than production or 2) restrict one's self to the lowest common denominator of database features.
Test what you fly, fly what you test.
Sounded good at first but we were quickly overwhelmed with false positives and just opted for Postgres in a VM (this was before Docker was a thing).
I love SQLite to bits but the test harness I have to put around my apps with it is a separate project in itself.
On a philosophical / meta level it's all quite simple: do your damnedest for the computer to do as much of your work for you as possible, really. Nothing much to it.
Strict technologies slap you hard when you inevitably make a mistake so they do in fact do more of your work for you.
See https://www.djangoproject.com/.
If I have to do anything CRUD like, I'll use Django. For reporting apps, I prefer native SQL.
Especially since launching postgres is equally easy and fast as sqlite. Docker can help with sandboxing. What is left to gain? 100ms shorter startup time or keeping your unit test executables as single binaries? Irrelevant.
It's definitely not as fast to start postgres as it is to start sqlite. Pretty much inherently - postgres has to fork a bunch of processes, establishes network connectivity etc. And running trivial queries will always be faster with sqlite, because executing queries via postgres will require intra-process context switches.
That's not to say postgres is bad (I've worked on it for most of my career), but there just are inherent advantages and disadvantages of in-process databases vs out-of-process databases. And lower startup time and lower "dispatch" overhead are advantages of in-process databases.
I wrote about this and some more in https://jmmv.dev/2023/07/unit-testing-a-web-service.html
Does that cover the first two examples brought up by the article? Constraint violations and default value handling.
How if you rely e.g. on CASCADE and foreign keys, which are not on by default kn SQLite? I think then things start getting complicated and testing that layer gets difficult.
That's the reason to use an ORM. It abstracts away things like that.