
Ask HN: Who uses SQLite database in production? - thunderbong
We&#x27;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.<p>The massive advantages that we see are -<p>1. Extremely quick backups which run twice daily, since they are just file copies.<p>2. Very fast data integrity resolutions as we can quickly download the database and parse it for fixing invalid data.<p>The only downside I see is the extremely rare DB lock error since SQLite uses file based locking. But usually that&#x27;s due to bad programming than anything else.<p>I was wondering if others are doing the same thing.<p>What advantages and drawbacks do you face?
======
dmt314159
"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](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://sqlite.org/th3.html)

[https://www.sqlite.org/qmplan.html](https://www.sqlite.org/qmplan.html)

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

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

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

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

~~~
redhale
> "forever"

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

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

------
stadeschuldt
My little project [http://solarpi.tafkas.net/](http://solarpi.tafkas.net/)
runs on SQLite for over five years now. Never had any issues.

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

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

------
stephen82
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 ^_^ !

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

------
seektable
Self-hosted version of our BI tool
([https://www.seektable.com](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.

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

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

------
taf2
“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...

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

------
Yaa101
Firefox uses sqlite for various things

~~~
ktpsns
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.

