5 ms·
SQLite is good quality software, and very heavily tested. There's nothing wrong with using it so long as you don't outright misuse it, and they are clear about
by sli 8y ago
SQLite is good quality software, and very heavily tested. There's nothing wrong with using it so long as you don't outright misuse it, and they are clear about when you shouldn't be using it.
- snazz 8y agoI very rarely run into a situation where I need more than SQLite. The page on the subject is great: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- collyw 8y agoI's not so good for multi user systems as I understand as it has a lock on the whole database when writing.
- snazz 8y agoQueuing works most of the time, does it not?
- collyw 8y agoTo be fair I have only really used SQLite for early development stages so haven't investigated workarounds in much detail.
- cheez 8y agoQueuing 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
- ngrilly 8y agoWhere do you queue: in memory? on disk? in a dedicated queue server (Redis, Kafka, ActiveMQ, etc.)? in another SQLite database?
- cheez 8y agoSo 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.
- ngrilly 8y agoI 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?
- cheez 8y agoAt 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.
- ngrilly 8y agoAre all writes blocked during database migrations? ALTER TABLE is quite limited in SQLite (for example there is no DROP COLUMN). Have you missed this?
- cheez 8y agoAll 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.
- ngrilly 8y agoThanks 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.
- cheez 8y agoMain 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.