Ask HN: Who uses SQLite database in production?
We're using SQLite database in production without any issues. However, we keep having to defend that decision. Our databases are not huge. There in the 10s of MBs in size.
The massive advantages that we see are -
1. Extremely quick backups which run twice daily, since they are just file copies.
2. Very fast data integrity resolutions as we can quickly download the database and parse it for fixing invalid data.
The only downside I see is the extremely rare DB lock error since SQLite uses file based locking. But usually that's due to bad programming than anything else.
I was wondering if others are doing the same thing.
What advantages and drawbacks do you face?
19 comments
[ 3.8 ms ] story [ 62.7 ms ] thread1. Congratulations for your foresight skills :-)
2. You can stay with SQLite
In any other case move to PostgreSQL (or MySQL if you really have to) because you'll be hit by things you can't do or that you can do at a cost higher than migrating to a proper db and managing it.
Downsides: simple and few data types, lack of advanced SQL functions (MySQL too) and features, single server, write lock, poor CLI tool.
Be really sure you don't have and want to grow your app.
You can always migrate later, and avoid the extra overhead now. No decision is forever.
Why? Do you encounter legitimate issues when using it that would warrant using a different DB? If so then the criticism is valid and you’re probably using the wrong tool for the job.
As others have said, SQLite can also cope with a server load. The website of SQLite has great documentation about that, too.
We also use SQLite for read-only data caches (i.e. points of interest in a 3d model) and these are exposed via REST API. This saves database space as there are tons of models and we can just effectively store an unlimited number of SQLite databases in S3 or whatever storage mechanism we want.
I think for a situation where you prepare the document once and then read from it as much as you need, it's a really good option. I'm not sure I'd want to do something like what you've described where (I assume) the workload involves reads and writes by users.
Of course, you know your app and customers better, but consider this - an adversary can just write a script to update some data constantly (i.e. changing their password) and with enough threads you can lock the database for updates. You won't have that problem with a database server.
I also use SQLite for data analysis. If the data isn’t very big I always prefer it to ORC of Parquet because it enables full SQL. SQLite let’s you keep every table in a separate file and then use “attach database” to reference them into a query . It’s pretty ideal.
https://www.sqlite.org/famous.html
notably Airbus, which may be (one of) the companies
"At about that same time, some avionics manufacturers were expressing interest in SQLite, which prompted the SQLite developers to design TH3 to support the rigorous testing standards of DO-178B.
The first code for TH3 was laid down on 2008-09-25. An intense effort over the next 10 months resulted in TH3 achieving 100% MC/DC on 2009-07-25. The TH3 code continues to be improved and expanded."
https://sqlite.org/th3.html
https://www.sqlite.org/qmplan.html
We still use it for a bunch of internal apps, which we've mostly forgotten about since it keeps on trucking.
Personally, I love SQLite, and I still use it for crunching data when a CSV/spreadsheet will be too complicated.