When people say "Just use SQLite. It's almost as good as Postgres and you won't need anything more" I'm trying to understand why I shouldn't just use Postgres. It's not like it's hard to install or has any significant overhead. Please enlighten me.
P.S. but if your project don't need any of that, e.g. it's desktop audio player, just embed sqlite - remove another subsystem to care about
FYI, as of late 2021, you can opt into strictness.
I personally prefer to use abstractions like ORMs for most of my database interactions, and direct SQL when those abstractions get in the way (by generating expensive queries and not finding an easy fix without a large refactor).
This way, starting out with sqlite (good enough for most websites I reckon, easy to backup) doesn't interfere with any necessary migration to postgres (like when the need for scaling arises). This also makes setting up tests easier (except for the manually written SQL) because starting an application with a temporary in-memory database is a lot faster than starting a full container.
Unless I'm doing native apps, I'll probably always want to reserve the ability to use Postgres. Sometimes that means hooking up a Postgres account and such, but often that just means sticking with sqlite and leaving my options open for when sqlite doesn't work anymore.
I don't think Postgres needs to be maintained at all for small databases, which is usually the use case for SQLite. Their default configurations would take care of most things for trivial applications.
> Starting an application with a temporary in-memory database is a lot faster than starting a full container.
Starting a container might be way slower than SQLite, but I would still consider it fast for most, if not all use cases.
> hooking up a Postgres account
You can configure Postgres to start up in trust mode, which doesn't require a password for any user. This is basically the same as the unencrypted SQLite database file but with a fixed connection string: `postgresql://postgres@localhost`
Depends on the environment or lack thereof, postgres is a pain in the ass on windows, and then you need support for software configuration so that it can talk to postgres, and then you have to take care of the features you're using.
If you're deploying a complex server-side system with lots of moving parts, then yes postgres is basically free. But if you're deploying client-side, or want to run it in a VPS, or whatever, postgres might go from not available to extra cost to a huge chore.
> Just use SQLite. It's almost as good as Postgres and you won't need anything more
Can't say I agree with that sentiment in any way though, every time I use it sqlite frustrates me in its limitations compared to postgres, and how weak the defaults are from a safety and consistency perspective.
This is why everyone uses docker and .env files. The problem has already been solved and you can copy/paste starter files from project to project to make it a non issue.