SQLite is likely used more than all other database engines combined

[paradox460]

In the past, I've used it as a file format for an online application. Users often wanted to download bits and bobs of data from the app, not to view it or edit it on their desktop, but more as backups or to share a particular configuration Previously we'd used a json+zip pair, but the lack of enforced schema became a problem. Switching over to a gzipped sqlite db with a "custom" file extension worked incredibly well

[TZubiri]

In terms of installations, sure.

In terms of total data, probably not.

In terms of important data, 100% not. Important data needs guarantees, and its privacy is second to its longevity.

Server side and centralized beats client side decentralized for all but sensitive consumer data.

[simonw]

You may be shocked to learn how much of the world's "important data" lives in Excel files.

[TheRealPomax]

I'm not sure I buy the "important data" one: my Lightroom catalog, to me, constitutes incredibly important data. Losing it and just having the base files would destroy over a decade of work.

It also happens to be a SQLlite3 database. And the same is true for a slew of other applications that (quite rightly) use SQLite databases as their file format.

You might be thinking of things like financial transactions, or medical records, but that's not the only kind of important data there is.

[alganet]

How would you go about measuring that, or determining what's important?

Huge SQL for servers is just a different use case.

Someone's temporary browser data or settings for my android alarm app could be just as important and sensitive as an average record on those big servers.

Fortunatelly, SQL fits the role of a small transactional engine very well to support those small use cases for a large number of applications. It deserves the good reputation.

[arh68]

Right. In terms of installations, I bet textfiles still win. Doesn't mean much, does it?

[II2II]

> In terms of total data, probably not.

> In terms of important data, 100% not.

Choosing not to focus upon quantity or quality is likely the reason why SQLite is so popular. It is a lot easier to manage and develop for SQLite than it is to do the same for large databases that require high availability and reliability under heavy loads. There are many more applications for small databases that contain data that is important to the end user even though that data may not be important to anyone else.

[yndoendo]

Would you classify a nurse call system as requiring important data retention and access? Mapping backbone devices with passive and interactive end user devices with means to communicate whom should provide assistance?

Works great there too with μC/OS-II. Competition was using Windows XP and Microsoft Access.

Only time ever using Oracle has been with internal business analytics, not very important.

[bob1029]

> Server side and centralized beats client side decentralized

SQLite works on the server too. Important data can live in a SQLite database.

[ysabri]

I remember reading or seeing this post about how SQLite can really scale and supports a really high amount of transactions per second. I don't know if it supports distrubuted loads that well, which might be the reason why it's use didn't pick up server side.

[qazxcvbnmlp]

SQLite solves a lot of problems well enough that you can focus on other things.

Have an mvp you’re not sure you’ll ever have more that 2 users - yep.

Storing a little data for an application on the disk and don’t want to write your own schema.

Want to teach someone how databases work without setting up a sever - sure.

[achillean]

A few others:

- Want to distribute data to users that don't want to manage a server? A lot of people don't want to manage a server and don't need the best possible performance.

- Want to take data with you on a thumb drive and work with it offline? It's extremely convenient to be able to use SQLite for an app that has to work offline.

- Does the app mostly just read from the database and fit in memory? It's undervalued to just put the entire database into memory so you don't hit the disk and don't introduce network latency. For example, the following website does all enrichment with in-memory SQLite databases: https://shdn.io/analyze?target=ycombinator.com

At Shodan, we distribute versions of our datasets as SQLite and they're a popular way to consume the data without having to manage infrastructure.

[mharig]

Astonishing no commenter mentions that SQLite unleashes the power (and pain) of SQL for what were in ancient times dark binary blobs or obscure text formats. I.e. guaranteed consistency, easy versioning with better up/down compatibility, excellent complex retrievals, ...

[talkingtab]

For niche uses of database I am sure this is true. But if you are not in that niche, then Postgresql is usually a much better choice. For example Firefox uses SQLite, because it wants to store lots of application specific data. It is good for that.

Personally, I find the tooling, documentation, familiarity and quality of Postgresql makes it my choice even in situations where SQLite might work.

[oneplane]

If I'm not mistaken, Apple's CoreStorage is based on SQLite, so pretty much every Apple device for the last years (or decade, probably) is constantly using tens (or more) SQLite databases, per OS. That includes Macs, Phones, Watches, AppleTV etc. That has got to be more than 3 billion devices (since there are at least 2 billion of the phones in active use).

SQLite is also quite often a CI/CD default when building and testing software where you might want to do some tests without starting an entire RDBMS server.

But I suppose it's different if we think in terms of networked databases, or multiuser.

[saulpw]

I think the headline statistic negates the claim that these are "niche" uses of database.

[0xcde4c3db]

At some point, such local usages were sufficiently non-niche that the temp file extension was changed from "sqlite" to "etilqs" to stem the flood of angry emails about local application databases taking up too much disk space.

(cf. Daniel Stenberg getting angry emails because everybody and their dog embeds libcurl, sometimes multiple times in the same app)

[MattGaiser]

The most frequent use of it is local storage of application data. Postgres is overkill for that.

[mapcars]

The article is about number of installations, instances across all types of devices

[darby_nine]

I'd guess most programs ever written are single-process programs with no need for the benefits postgres provides over sqlite. Postgres doesn't even have analogous functionality for eg an in-memory sql instance!

[foobarian]

I wonder what happened to bdb. I guess maybe authors lost interest/sold to Oracle and the license changed development just didn't keep up with the times. Bdb used to be the sqlite before sqlite existed and ended up used by various embedded applications or even graphical interfaces e.g. to index file thumbnails and such. I'm not sure but I think MacOS at one point used it this way.

[lm411]

I haven't even thought of Berkeley DB in years. Used to use it quite a bit but most notably perhaps with apache mod_auth_db to handle authentication and subscriber information on a fairly high traffic site (yes, this was long long ago ;) ).

[cmur]

I’m curious, is the prevalence of SQLite similar to how PHP is widely used because it’s behind many WordPress sites?

[pkaye]

Couple of major companies like Adobe, Apple, Google, Mozilla use it in their products. That would add up quick.

https://www.sqlite.org/famous.html

[TheRealPomax]

I'm trying to understand this sentence: it seems to suggest that PHP is only popular because of Wordpress, which just... makes no sense? Wordpress uses PHP because PHP existed, and was good enough to build Wordpress on. And it's certainly not just clinging onto life, hanging around just because Wordpress is built on top of it, today?

So it's hard to understand which similarity you're asking about, but SQLite is popular because it does one thing, and does that one thing really well, and that thing also happens to be a perfect fit for complex file formats.

[darby_nine]

I think Sqlite's accessibility and ease of use definitely help. However, the fact you can distribute an entire db with a single file is something that shouldn't be overlooked, nor sqlite's legendary reliability and insane test coverage. Finally the fact you can embed it in memory makes for an amazing testing story.

[yen223]

Kind of, at least in regards to mobile.

When developing iOS and Android apps, the "default" frameworks for storing data (Core Data for iOS, Room for Android) are wrappers around sqlite.

I would guess >50% of apps installed on your phone uses sqlite.

[ethagnawl]

It's also the default database used when creating new projects using many web frameworks, like Rails or (IIRC) Django.

[sargun]

I love SQLite. It’s pluggable interfaces and concise C code make it enjoyable to use (and sometimes to debug - as long as you don’t have to go through the single file version!).

Unfortunately, I wish there were multiple implementations of it, and that the file format was documented and stable.

[ankitrgadiya]

The sqlite file format is documented and probably has the most insane backwards compatibility out there. The developers pledged to keep it backwards compatible until 2050. In fact Library of Congress has recognised it as one of the few data formats for long term archival.

https://sqlite.org/fileformat2.html https://sqlite.org/lts.html https://sqlite.org/locrsf.html

[quasarj]

Most likely, indeed.

[albertopv]

There are far more ants in the world than human beings. So what?

[axlee]

Once that is said, does OSS move forward? Not an inch, good luck getting any change upstream

[spencerchubb]

OSS does not mean anyone can contribute. It means anyone can view source

[tombert]

It's still OSS, you're allowed to fork and maintain Sqlite if you don't like the upstream merge requirements.

[doubloon]

i find it ironic one of the most gatekeepy, closed, walled garden companies in the history of the planet, Bloomberg, is a sponsor of one of the most free most open piece of software ever.

[TZubiri]

Bloomberg sells pacakaged public information, they rely on commonwealth and transparent regulation disclosures, they are agents of such transparency.

They gatekeep their product sure, but you need to do that if you want to sell it.

[mirekrusin]

Sqlite is not open for contributions.

Amazying piece of code whatever you look at it.

I'd also probably win test to code ratio contest.

[foobarian]

Another irony is that according to Wikipedia [1] Bloomberg maintains a fork of BDB.

[1] https://en.wikipedia.org/wiki/Berkeley_DB

[jameshart]

The majority of these SQLite use cases that the article is celebrating are... as the internal data store in an app where it is locking up user data in a proprietary, closed, gate kept wall garden.

This irony has layers. Like an onion.