I'm frustrated by someone reaching the conclusion that there can ever be a "best database". I spent several hours today writing a bash script that jammed CSV files into sqlite3 and ran some queries on them. Using postgres for this would have been insane, and I can imagine writing the exact opposite of this article if I had tried to do so: "It stores everything all over the place! There's no easily configurable in-memory storage option! All the docs and packages expect you to be running it as a service! Running my script takes an entire ansible playbook just to set things up! Why the hell is there a user table already? This thing uses hundreds of megs of memory at rest sometimes!"

Sqlite is not just for embedded data stores. However, it makes trade-offs to achieve ease of use and performance on certain workloads. If it tried to be postgres then it would be an inferior competitor; instead, it is an alternative that suits some use cases better and others worse. If you're trying to build a web app that can be deployed and backed up as two files, an executable and a db, then you'll probably want sqlite. If you're trying not to shoot yourself in the foot with "oh god why can't I do a full outer join and why are all the dates weird" then use postgres.

I imagine programmers would also find cockroachdb frustrating compared to postgres, if they didn't benefit from anything it offered and used it anyway.

I'm curious why you used SQLAlchemy and Alembic when (based on your description) Django's support for SQLite sounds a lot nicer? According to the docs, there are a handful of caveats with the Django ORM and SQLite, but nothing too bad it would seem.

https://docs.djangoproject.com/en/dev/ref/databases/#sqlite-...

Good question! I was (am) using FastAPI, not Django. Another decision that was easy to get started with, but I think is turning out to be more difficult to scale. Maybe.

>> I would stick to the most boring stack

Just use sql. Very straightforward and easy to use program understand maintain.

I think this is the best advice. The further you are away from your data store, the more you are relying on the used cases others have considered. Just because a library needs features that something like SQLite doesn’t provide doesn’t mean that you need that feature. But every extra abstraction in your stack has this danger.

I also think that the closer you are to your DB, more you appreciate the features you actually need are. A developer should know how their data is ultimately stored. And if an ORM hides this from you, that’s a problem.

Note: and ORM doesn’t need to hide this and can be a rational way to manage DB storage. But it’s then up to the developer to manage that abstraction. I’ve done this before where I used an ORM but I still knew exactly what SQL was going to be generated. But for many, especially new devs, the ORM is a black box.

> Just use sql. Very straightforward and easy to use program understand maintain.

In the end SQLAlchemy and Alembic generate and execute SQL. The weird behaviours are due to sqlite's idiosynchasies