Ask HN: Have you used SQLite as a primary database?

Many of the replies here attest to the simplicity and fast performance of SQLite particularly for serving pages or data. But how well does SQLite fare in concurrent write/insert situations?

Although SQLite is not designed for this type of scenario, this discussion higlights there's a strong demand for a concurrent client/server RDMS that is simple, performant and easy to deploy. PostgreSQL is powerful and feature-rich, but not simple or easy to delploy. Hence the appeal of SQLite.

For example, could SQLite power a discussion forum of moderate (or more) activity i.e. users posting comments? The Nim language forum is powered by SQLite, but activity in the forum is fairly low. [1]

Between the simplicity of SQLite and the complex, heavyweight that is PostgreSQL, there is a wide gap between these database opposites. It's a shame there is no concurrent RDMS to fill that gap.

(Note: Another poster mentions the concurrent Firebird RDMS as a possible alternative, but I haven't used it. [2])

[1] https://forum.nim-lang.org/

[2] https://firebirdsql.org/en/features/

I've used SQLite with HashBackup for 13 years and it's been awesome. A lot of the stories I read about "data corruption" with SQLite are, IMO, really about faulty applications that do database commits with the data in an inconsistent state from the application point of view. I've done it myself - it's an easy sin.

I've migrated database versions 35 times over the last 13 years, ie, every individual HB database has been migrated 35 times. You don't always need to make a new table, do a copy, and switch over. In the latest migration, I added new columns, initialized them, dropped columns, etc. without doing a db copy.

For this migration I wanted to switch to strict tables, where typing is strict. I could have done this by just altering the schema (it's just a bunch of text in the SQLite db) and then using SQL to make sure existing columns had the right data (using CAST). But instead, I created a general framework to allow me to migrate data from one schema to another, mainly so I could reorder columns if I wanted. That can't be done with ALTER statements, so I did end up doing a complete copy, but I've done many migrations without a copy.

I found this paper interesting on "zero downtime migrations".

https://postgres.ai/blog/20210923-zero-downtime-postgres-sch...

After reading it, the bottom line is that changes happen within transactions (true for SQLite too), and the key to zero downtime migrations is to use short transactions, use timeouts, and use retries on all database operations, including the migration commands. You can do all these with SQLite.

I used SQLite and come custom python for a client solution, a web application for teaching and thought process. It was a large application (many steps and user screens, some admin functions), probably a good fit for Django but I don't like Django. I used python sqlite interfaces, running on a single Debian base server. The server ran like a tank, no problems whatsoever, but the application had slow performance at times. The client did a complete re-write later with different consultants and they started over without SQLIte. In summary, the clients had no clue what SQLite is or why you would want it, and my efforts to explain the benefits in detail, did not sink in, while the slow performance was very important and caused them to dislike the entire product. All things considered, I would re-write it the same way, since I enjoyed the tech stack personally, but debugging those rough spots instead of adding a dozen more GUI features, would have been better for the project.

For those who've missed the announcement, here are some past links on the topic:

    - https://www.sqlite.org/stricttables.html
    - https://news.ycombinator.com/item?id=28259104
    - https://news.ycombinator.com/item?id=29363054

are you doing multi tenancy in your application by creating different SQLite database for each customer? I'm curious to know more about your approach