What I've been doing in some of my recent projects is to support both SQlite and PostgreSQL. I do this via an abstraction layer that hides the two databases behind it, and I run the exact same set of unit tests against the two implementations.
The reason I do this is for testing. In fact, the SQLite backend is only compiled for test builds. With that, I can test my application logic against the SQLite implementation and be reasonably confident that things work with the PostgreSQL backend as well. And this is infinitely easier and faster than relying on a "real database server". No installation involved, and the same tests run in 300ms as opposed to 8+ seconds.
Yes, it's significantly more work to develop around these two implementations, but the benefits in build/test iteration times are immense. I haven't ever had to touch docker yet. And the abstraction layer also keeps me honest by forbidding me from poking into database internals.
Lowest common denominator database feature set makes a DBA sad. I love SQLite, but Postgres is just so much more feature full, it's no contest.
If all you need is what SQLite can provide, go for it! It's lightning fast and astonishingly low overhead.
But other times you just need those event triggers and advanced indexing and range types and rollup and grouping sets and writable CTEs and lateral joins and materialized views and row-level security and…
Yes, you can't possibly write triggers and checks on columns across different databases to the point you should be doing all data checks in the app if you're going to support multiple databases.
These days, there's docker, so I see less point supporting multiple databases.
I tried that but moved completely away from it. Too many issues around sql dialect and type differences for starters. I’d much rather run postgres on my dev/testing boxes, use a single set of sql-based migrations and not have to maintain an abstraction layer.
The reason I do this is for testing. In fact, the SQLite backend is only compiled for test builds. With that, I can test my application logic against the SQLite implementation and be reasonably confident that things work with the PostgreSQL backend as well. And this is infinitely easier and faster than relying on a "real database server". No installation involved, and the same tests run in 300ms as opposed to 8+ seconds.
Yes, it's significantly more work to develop around these two implementations, but the benefits in build/test iteration times are immense. I haven't ever had to touch docker yet. And the abstraction layer also keeps me honest by forbidding me from poking into database internals.