https://avi.im/blag/2024/sqlite-facts/ Index About Now Collection of insane and fun facts about SQLite 30 Dec 2024 1. SQLite is the most deployed and most used database. There are over one trillion (1000000000000 or a million million) SQLite databases in active use. It is maintained by three people. They don't allow outside contributions. 2. SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. It's everywhere. [sqlite-fac] 3. It is also probably one of the top five most deployed software modules. [sqlite-fac] 4. Hwaci is the company behind SQLite. They are also into music(?) [sqlite-fac] 5. SQLite originated from a US warship. D. Richard Hipp (DRH) was building software for the USS Oscar Austin, a Navy destroyer. The existing software would just stop working whenever the server went down (this was in the 2000s). For a battleship, this was unacceptable. So DRH asked the question: what if the database just worked without any server? This was an innovative idea back then. 6. SQLite is not open source in the legal sense, as "open source" has a specific definition and requires licenses approved by the Open Source Initiative (OSI). [sqlite-fac] Instead, SQLite is in the public domain, which means it has even fewer restrictions than any open source license. 7. They don't allow outside contributions. You cannot just send a pull request and hope the patch will be accepted. [sqlite-fac] 8. Open Source, Not Open Contribution [sqlite-fac] Contributing to SQLite is invite-only (I don't have a source). Only after you are invited and have signed an affidavit dedicating your contribution to the public domain can you submit patches. 9. How do they cook? There are over 600 lines of test code for every line of code in SQLite. Tests cover 100% of branches (and 100% MC/DC) in the library. The test suite is extremely diverse, including fuzz tests, boundary value tests, regression tests, and tests that simulate operating system crashes, power losses, I/O errors, and out-of-memory errors. 10. Interestingly, some SQLite tests are proprietary. The test suite called TH3 (Test Harness 3), which achieves 100% branch coverage of the code, is proprietary and is not open to access. I don't know any other project which has made code free, but test suites are paid. However, they could not sell a single copy of TH3. DRH said in a podcast: The 100% MCD tests, that's called TH3. That's proprietary. I had the idea that we would sell those tests to avionics manufacturers and make money that way. We've sold exactly zero copies of that so that didn't really work out. In order to get access, one needs to be part of SQLite Consortium, which costs $120K/yearly. 11. It's an interesting business model. They generate revenue through paid support, maintenance services, consortium membership, and commercial extensions. 12. SQLite does not have a Code of Conduct (CoC), rather Code of Ethics derived from "instruments of good works" from chapter 4 of The Rule of St. Benedict [sqlite-coe] 13. In SQLite: In place of a legal notice, here is a blessing: [sqlite-fac] All the of the source code files come with a blessing. 14. SQLite is so fast, they compete with fopen. For some use cases, you can use SQLite instead of a filesystem, that can be 35% faster. [sqlite-fac] 15. SQLite vs Redis (guess which is faster?) [sqlite-fac] For some usecases, the SQLite can be faster than Redis due to network stack and (de)serialisation overhead. 16. But, unlike most databases, SQLite has a single writer model. You cannot have more than one concurrent writer. This was also changed recently in 2010 by adding WAL mode. Before that, you could have either readers or a writer, but never together. 17. There are other things which are very common in other databases but not in SQLite: + The default is rollback journal mode, which restricts you to have either multiple readers or a single writer + Foreign Keys are disabled; they are opt-in + It is "weakly typed". SQLite calls it "type affinity". Meaning you can insert whatever in a column even though you have defined a type. Strong typed columns are opt-in. + Many of the ALTER commands you expect in other databases don't work. For example, you cannot add a contraint to an existing column. (They recently added ability to rename a column name) There is a whole list of quirks here. 18. I hate that it doesn't have types. It's totally YOLO: CREATE TABLE user(id INTEGER); INSERT into user VALUES ("YOLO!"); --- This works! Not only that, it does not throw any error if you give some random type. CREATE TABLE t(value TIMMYSTAMP); There is no TIMMYSTAMP type, but SQLite accepts this happily. It has five types: NULL, INTEGER, REAL, TEXT, BLOB. Want to know something cursed? The type affinity works by substring match! CREATE TABLE t(value SPONGEBLOB) --- This is BLOB type! 19. This is one my favorite lore. SQLite had to change the default prefix from sqlite_ to etilqs_ when users started calling developers in the middle of the night [sqlite-fac] 20. SQLite takes backward compatibility very seriously All releases of SQLite version 3 can read and write database files created by the very first SQLite 3 release (version 3.0.0) going back to 2004-06-18. This is "backwards compatibility". The developers promise to maintain backwards compatibility of the database file format for all future releases of SQLite 3. 21. But they take backward compatibility so seriously that even if they have shipped a bug, they won't fix it [sqlite-fac] 22. SQLite's author D. Richard Hipp (DRH) did not find existing version control systems suitable. So he wrote his own called Fossil. Fossil is powered by SQLite, of course. This reminds me of how Linus wrote Git. DRH also wrote his own parser generator called Lemon. 23. DRH wrote the B-Tree based on the algorithm in the book TAOCP by Donald Knuth, coding it on a plane while traveling (super based) 24. SQLite is pronounced as "Ess-Cue-El-Lite". There is no official guideline though. DRH mentioned in the SQLite forums: I wrote SQLite, and I think it should be pronounced "S-Q-L-ite". Like a mineral. But I'm cool with y'all pronouncing it any way you want. :-) That's it for today! If I missed any, let me know. Happy holidays and Happy New Year! [?] --------------------------------------------------------------------- 1. Sources: Most Deployed, Public Domain and Contributions, Testing, Paid support, Faster than filesystem, SQLite History. 2. I posted this as thread on Twitter, where bunch of people provided more sources. Thanks to them: 1, 2, 3. icon name icon name