Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary. When you're using a database to store a few thousand rows of data, Postgres quickly becomes overkill. Postgres is a V8 engine when all you may need to power is a lawn mower.
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.
> Postgres isn't hard to use, but it requires maintenance. You need more scripts, more tooling, more knowledge of DBA, and that may not be necessary.
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`
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.