8 ms·
There are over one trillion SQLite databases in active use
- claytongulick 5y agoThey'd have one trillion + 1 if they had better JSON support. I'd be using it in a large healthcare project I'm working on now. Unfortunately, the JSON support is basically "serialize to a string". Postgres is miles ahead with jsonb. Shame, I <3 sqlite.
- acatton 5y agoI love PostgreSQL, and would recommend it anytime. Even though jsonb is very powerful, in general if you store JSON in a SQL database you're very likely doing something wrong. (You're breaking first normal form) But if you really wanted to use SQLite to store JSON à la jsonb in PostgreSQL you can use generated fields[1] sqlite> create table t(id integer primary key autoincrement, data text); sqlite> insert into t(data) values ('{"foo": "value", "bar": "other value"}'), ('{"foo": "baz", "bar": "qux"}');' […] sqlite> alter table t add column foo text generated always as (json_extract(data, '$.foo')) virtual; sqlite> select * from t; id data foo -- -------------------------------------- ----- 1 {"foo": "value", "bar": "other value"} value 2 {"foo": "baz", "bar": "qux"} baz You can even use a "stored" (instead of 'virtual') generated field, and create an index on it for fast lookups. It's not as powerful as Postgres, but it does a pretty good job. [1] https://www.sqlite.org/gencol.html https://www.sqlite.org/gencol.html
- nojito 5y agoDisagree storing json in databases is often the best thing to do. Clickhouse for example allows you to quickly pull out relevant data and feed materialized views.
- acatton 5y agoI never said storing json in databases was wrong. I said storing json in *SQL* databases was an anti-pattern. https://en.wikipedia.org/wiki/First_normal_form https://en.wikipedia.org/wiki/First_normal_form
- nojito 5y agoIt's perfectly fine one column for a timestamp and one column to store the json You then build materialized views pulling out whatever json fields you need.
- hobs 5y agoJust because its easier doesn't mean its better - json is just one possible materialization for your set.
- claytongulick 5y agoDatabase optimization and the level of normalization is a complex topic that varies widely based on application and workload. JSON is absolutely a valid approach to data storage when dealing with certain data structures. As is using a relational database and jsonb data types for it. Cherry picking columns gives you the advantages for both a NoSQL document storage database as well as a relational analytics database. This is a pretty common modern technique that Postgres, for example, has made easy. Go try modeling a FHIR database structure using standard normalization rules. You'll quickly discover why no one does it. Even HAPI FHIR server (the most full featured and popular FHIR server) written in Java doesn't attempt it.
- wmanley 5y agoCould you expand on what Postgres offers that SQLite doesn't in this regard? I'm curious to know what I'm missing out on.
- acatton 5y agoNot GP, but PostgreSQL offers jsonb fields[1] Since PostgreSQL allows for functional indexes, you can query and index the data in a structured way. For example you have a table t with "id" and "data" where data is a jsonb field like {foo: ..., bar: ...}. You can do SELECT id, data->'foo' FROM t WHERE data->'bar' > 5 Which will yield the value of "foo" (inside the json field) for rows where "bar" (inside the json field) is greater than 5. [1] https://www.postgresql.org/docs/9.5/functions-json.html https://www.postgresql.org/docs/9.5/functions-json.html
- wmanley 5y agoUnless I'm misunderstanding SQLite can do this as well, but with less nice syntax. The above could be written something like: SELECT id, json_extract(data, '$.foo') FROM t WHERE json_extract(data, '$.bar') > 5 in SQLite. You can also add an index on one of these expressions: https://dgl.cx/2020/06/sqlite-json-support https://dgl.cx/2020/06/sqlite-json-support
- acatton 5y agoThat is true, but SQLite doesn't support functional indexing. This means that you cannot do CREATE INDEX idx_t_bar ON t(json_extract(data, '$.bar')); for fast lookups. But you can do CREATE INDEX idx_t_bar ON t(data->'bar'); in PostgreSQL. You have some workarounds like I explained.[1] Also PostgreSQL has many operators[2] which makes using JSON fields easier. [1] https://news.ycombinator.com/item?id=29461926 https://news.ycombinator.com/item?id=29461926 [2] https://www.postgresql.org/docs/9.5/functions-json.html https://www.postgresql.org/docs/9.5/functions-json.html
- wmanley 5y ago
- literallyaduck 5y agoSqlite is just that, lightweight. If you want better JSON support specialized for what you want, extend it. The product allows extensions easily.
- ccozan 5y agoOk tens of billions I believe, but trillion+ is likely an exaggeration
- peterth3 5y ago> Since SQLite is used extensively in every smartphone, and there are more than 4.0 billion (4.0e9) smartphones in active use, each holding hundreds of SQLite database files, it is seems likely that there are over one trillion (1e12) SQLite databases in active use. Sounds reasonable to me.
- da_chicken 5y agoNo, that still seems inflated. That's like counting installs of Word or Excel by counting the number of documents that exist.
- wmanley 5y agoThat's exactly what's being counted. A database is a SQLite document.
- kevincox 5y agoThe pays says: "Billions and billions of copies of SQLite exist in the wild". Which seems reasonable and is counting the number of installs as you preferred. Later on the page says "it is seems likely that there are over one trillion (1e12) SQLite databases in active use" which is the quoted title and seems like a reasonable claim and is counting "documents" not installs. I don't think "documents" is an uninteresting statistic. It is just a different one.
- weird-eye-issue 5y agoThat's... Exactly what a SQLite database is - a file. This thread isn't about the number of unique devices that potentially use SQLite
- pessimizer 5y ago"Active" is a big word here. Each of these databases is in nearly continuous use. You couldn't say that about the vast majority of excel spreadsheets, or any word documents.
- jerodsanto 5y agoRichard Hipp discussed this (alongside a deep-dive on the philosophies underpinning his Fossil SCM) when we had him back on The Changelog podcast recently: https://changelog.fm/454 https://changelog.fm/454
- andkon 5y agoDon’t forget to count all the cruise missiles, too!
- ntumlin 5y agoThose tend to fall out of the “active use” category not long after entering it, so it’s probably not a big factor. On a more serious note, had there been something special about SQLite supporting military use of their project that prompted this? Otherwise that’s the point of free software, people are free to do (mostly) whatever they want with it. Including building weapons. SQLite is so prevalent, it’s almost like pointing out you have to include cruise missiles in the count of active x86 chips, or $insert_bad_guy as an active user of roads and electricity.
- spicybright 5y agoI'm sure it was picked a long time ago and it's too costly to switch. I imagine any code going into multi-million dollar missile needs to be incredibly audited and understood. (Not that this never happens! https://www.simscale.com/blog/2017/12/nasa-mars-climate-orbiter-metric/ https://www.simscale.com/blog/2017/12/nasa-mars-climate-orbi...)
- deleted 5y ago[deleted]
- _Wintermute 5y agoI remember an interview whereby he mentioned sqlite was initially built for software on warships.
- andkon 5y agoI was just making a funny joke about how it was developed originally for cruise missiles. Though it turns out I was misremembering: it was made for guided missile destroyers. And it makes sense! What a great use case. https://en.wikipedia.org/wiki/SQLite#History https://en.wikipedia.org/wiki/SQLite#History
- starik36 5y ago
- JensRantil 5y agohttps://twitter.com/bagder/status/1467895841541210118?t=pPU8oNHDfb-X4Pc31xmH-w&s=19 https://twitter.com/bagder/status/1467895841541210118?t=pPU8... and https://daniel.haxx.se/blog/2021/10/21/the-most-used-software-components-in-the-world/ https://daniel.haxx.se/blog/2021/10/21/the-most-used-softwar... might be relevant here. TLDR; cURL is likely installed in even more devices (but not listed on SQLite's page).
- dfox 5y agoI would hazard a guess that most used "software module of any description" is dtoa().
- thepasswordis 5y agoSQLlite has also got probably the most well-tested code of conduct of any open source project in the world (aside from the other open source projects which use this CoC) https://sqlite.org/codeofethics.html https://sqlite.org/codeofethics.html
- trasz 5y agoIsn’t SQLite essentially not accepting outside contributions at all? In such a case this CoC really might work just fine. As would a red “GO AWAY” banner.
- wodenokoto 5y ago> This document continues to be used for its original purpose - providing a reference to fill in the "code of conduct" box on supplier registration forms.
- nathcd 5y agoI wonder if they've ever had to enforce "Be not drowsy" or "Be in dread of hell".
- rpmisms 5y agoI don't think anyone can reasonably get angry over someone else following these. Good rules.
- ushakov 5y agoi have a feeling that every time SQLite is mentioned on HN it always gets to the frontpage great for them i guess!
- jacobr1 5y agoThey have a lot of fans, of the type of people that haunt HN. High quality project that is useful for for small hobbyist projects, startups and large companies alike. Persisting data something that is used in probably the majority of software projects across many verticals. It is OSS software that is easy to pick up. It is a values driven project (both technical and ethical). And the team maintaining it has kept it going for a long time, so as it get rediscovered by others it still has relevancy for many. Basically it is good software, with a wide TAM, and just enough quirks to appeal to the folks here.
- sdfghderwg 5y agoLove god? What? What an oddly discriminatory thing to have on the code of ethics... EDIT: I'm convinced this must be some sort of forgotten April fool's remnant.
- anamexis 5y agoDid you read 1. History and 2. Purpose?
- thepasswordis 5y agoThe code of ethics is a 1500 year old "code of ethics" meant to govern the lives of benedictine monks: https://en.wikipedia.org/wiki/D._Richard_Hipp https://en.wikipedia.org/wiki/D._Richard_Hipp That might provide some context. edit: haha, this is what I get for skimming the wikipedia article. He has a PhD in computer science, but the full "doctorate of philosophy" is written out instead of abbreviation on that wikipedia article. My pattern matcher needs tuning, apparently. Thanks to those who pointed this out.
- noblethrasher 5y agoHis PhD is in Computer Science.
- jb1991 5y agoI think you are taking what “Ph.D.” stands for to be his actual area of study.
- thepasswordis 5y agoOkay that's embarassing. Serves me right for just skimming his article. Thanks for the correction, edited my post.
- monopoledance 5y agoHe and his wife do seem religious: https://www.hwaci.com/drh/ https://www.hwaci.com/drh/ I doubt a missionary christian would make that 'code of ethics' as a joke. Guess we need a SQLight fork for those brave enough to query reality.
- _xnmw 5y agoOne of the examples of wildly successful "one-man-army" software (conceived of and built by a single person). See also: Redis, Linux, Doom/Quake, Steve Wozniak, jQuery, Python and countless obscure modern SaaS products.
- ec109685 5y agoTheir extensive test library is proprietary, so they ensure the path of least resistance for companies is to hire themselves to make improvements to the library. That has helped ensure there are no forks given quality would be inferior. It also kept SQLite out of the browser since there are no competing implementations of it.
- Taywee 5y agoSQLite is extensively used in web browser codebases. If you check your Firefox profile, it's well-packed with SQLite databases.
- Someone 5y agoI’m fairly sure that refers to https://en.wikipedia.org/wiki/Web_SQL_Database https://en.wikipedia.org/wiki/Web_SQL_Database: “Web SQL Database is a deprecated web browser API specification for storing data in databases that can be queried using SQL variant. The API is supported by Google Chrome, Opera, and the Android Browser. The W3C Web Applications Working Group ceased working on the specification in November 2010, citing a lack of independent implementations (i.e. using database system other than SQLite as the backend) as the reason the specification could not move forward to become a W3C Recommendation.”
- eternityforest 5y agoSQLite is one of the most amazing pieces of software I've ever seen. It's reliable, it doesn't add any inconvenience to end users, and it's very easy to use.
- deleted 5y ago[deleted]