Queuing and retry works most of the time. Only time I truly get "database is locked" is when I open the shell while other processes are accessing the DB

Are all writes blocked during database migrations?

ALTER TABLE is quite limited in SQLite (for example there is no DROP COLUMN). Have you missed this?

All writes are blocked during database migrations, so there is potential offline time. For example, when I added the index, I needed to be offline. Fortunately, my application is only needed during business hours.

Yes I miss proper ALTER TABLE a lot when it would have been useful. But my experience shows me that I can design software pretty well following YAGNI and the occasional instances of when I need missing functionality is more than made up for the ease of use otherwise.

Thanks for sharing your experience with SQLite :-)

What are the advantages, in your own experience, of using SQLite instead of something like PostgreSQL? With PostgreSQL, you could run database migrations during business hours and use a proper ALTER TABLE.

Main reason is that SQLite is embedded with my application. There isn't a second, third or fourth "microservice" to spin up. I know Terraform/Docker, love them, but the gymnastics I need to orchestrate for my particular situation is not worth it. So for the tradeoff of complexity for functionality, SQLite hits a very good sweet spot for me.

Where do you queue: in memory? on disk? in a dedicated queue server (Redis, Kafka, ActiveMQ, etc.)? in another SQLite database?

So far, haven't needed queuing outside of a single thread in the application (I know, what happens if it crashes!) Hasn't happened in ~8 years. Won't cry if it does.

I have an app that uses an in-memory queue too in one thread :-)

I'm curious: why do you queue instead of directly writing to the database?

At the moment, I queue to batch insert real time data at ~5 second intervals. It seems less wasteful of resources, but quite frankly is probably not needed.

To be fair I have only really used SQLite for early development stages so haven't investigated workarounds in much detail.