News for hackers. Stuff that matters.
Re:Ask HN: Have you used SQLite as a primary database? by JohnBooty on Monday April 25, 2022 @12:14PM (#31156930)
This was so inspiring to read. It's a very balanced take about the pros and cons of using SQLite vs. Postgres at scale.

I say "inspiring" because using SQLite reminds me of the simplicity and productivity from coding for the "early web" that lost 10-15 years ago. The days when you could spin up a website without worrying about a bunch of ancillary services and focus on the app itself.

For me, SQLite's lack of online schema changes seems like perhaps the biggest blocker to actual production. I've never had a production project where the schema didn't change a lot.

Re:Ask HN: Have you used SQLite as a primary database? by djbusby on Monday April 25, 2022 @12:35PM (#31157249)
I have this beef too. Tooling for dumping and restoring into a new schema are easy/simple/fast. So, these schema migrations can happen w/o issue. Some tricks with the PRAGMA directive in SQLite so you can roll out changes (eg: code supports old/new schema while migrating)
Re:Ask HN: Have you used SQLite as a primary database? by JohnBooty on Monday April 25, 2022 @04:10PM (#31159915)
Interesting, that's super cool to read.

   Tooling for dumping and restoring into a new schema are easy/simple/fast.
Any resources you can point to that expand on this? Is this standard SQLite tooling? I'm curious how it would perform with large-ish databases - a few hundred GB or perhaps several TB.

(This is one of those things where I can "just Google it" but I was wondering if perhaps there was a particularly useful article that points out potential gotchas, etc)

Re:Ask HN: Have you used SQLite as a primary database? by djbusby on Monday April 25, 2022 @08:34PM (#31162627)
I'm just using shell scripts and the SQLite CLI. I dump/restore files to the same FS so I can mv into place when ready.

Checkout the PRAGMA user_version, I just modify on schema change, so app knows where stuff is.

https://www.sqlite.org/pragma.html

Re:Ask HN: Have you used SQLite as a primary database? by tianzhou on Saturday April 30, 2022 @12:32PM (#31217199)
We also used SQLite and leverage PRAGMA user_version to do the trick. But decided to move to PostgreSQL mainly due to the schema migration constraints.

Our experience is SQLite is OK for small to medium projects, but when the business logic/data model becomes more complex, SQLite is not sufficient.

More discussions are captured here https://news.ycombinator.com/item?id=31038614