Ask HN: Who uses SQLite database in production? | Hacker News Reader

Ask HN: Who uses SQLite database in production?

25 pointsthunderbong19 comments

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?

View on HN

"SQLite is used by literally millions of applications with literally billions and billions of deployments. SQLite is the most widely deployed database engine in the world today."

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 use SQLite in lieu of large REST transfers when transferring large amounts of temporary data internally in our application between services. It means the structures are already in a format where you can work with them, etc. Once the file is in S3 you can use it with an unlimited number of worker processes, it's very easy to add new steps to a pipeline when you don't need to worry about extra impact on your database server.

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 use SQLite, although like you I also do not have huge databases with a huge number of accesses. I run a NNTP server with SQLite for the data storage. Also, Fossil uses SQLite, so anyone (including myself) who is using Fossil repositories also has SQLite. And many other programs also use SQLite for their own data, including Firefox and many others.

Shout out to fossil too. It's hands down the best DVCS I've ever used

I've been there a few times myself. I don't have time now to go into details (weekend) but if you know your app is going to stay small and simple and single server forever

1. 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.

> "forever"

You can always migrate later, and avoid the extra overhead now. No decision is forever.

For website use, I think the right person to ask such question is Pieter Levels (user pieterhg here). If I'm not mistaken he uses it for remoteok.io and pretty much everywhere he can lol; oh I love this guy so much ^_^ !

> However, we keep having to defend that decision

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.

My little project http://solarpi.tafkas.net/ runs on SQLite for over five years now. Never had any issues.

I do for a slack app. I have to queue the writes to avoid lock related errors but writes are relatively rare for the app so that’s no problem.

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.

That's really interesting! SQLite never ceases to amaze. Have to look into that kind of implementation

The new version of TaxPrep, one of thepopular professional tax software in Canada, uses SQLite as part of its save file format. It replaces dbisam which was used in previous versions.

Self-hosted version of our BI tool (https://www.seektable.com) uses Sqlite for storing user accounts, data sources configuration, saved reports and other metadata. It works just fine with .NET Core (we use Microsoft.Data.Sqlite wrapper). Strongly agree that backup is damn simple as this is just a file in docker volume + custom data import/export is quite simple with sqlite3 cli.

Not the same domain but almost all smartphones today probably have at least one application using SQLite. In iOS, for example, Core Data is built on top of SQLite.

We used it for a while, and when we started running into locking and performance issues, we upgraded to Postgres. It was pretty great while it lasted though.

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.

“extremely rare”. At some point is a pretty big number. If it’s one and a million and your doing a million transactions per day .. maybe that’s ok but as it becomes hourly or every minute is it still okay? I guess depends on how much time it takes to recover from the locking event...

The Google Drive mobile apps and enterprise desktop app use SQLite for storing metadata.

Firefox uses sqlite for various things

And many mobile apps do. In such cases, SQLite is merely used as a serialisation format for nonconcurrent applications (like JSON but better tailored to the relational needs). That's a huge productive "market" for SQLite.

As others have said, SQLite can also cope with a server load. The website of SQLite has great documentation about that, too.

Reply on news.ycombinator.com