Thread by @iavins

Collection of insane and fun facts about SQLite. Let's go!

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.

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!

It is also probably one of the top five most deployed software modules!

Hwaci is the company behind SQLite - hwaci.com

SQLite originated from a US warship. Dr. 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.

SQLite is not open source in the strict legal sense, as "open source" has a specific legal definition and requires licenses approved by the Open Source Initiative (OSI).

Instead, SQLite is in the public domain, which means it has even fewer restrictions than any open source license.

They don't allow outside contributions. You *cannot* just send a pull request and hope the patch will be accepted.

Open Source, Not Open Contribution

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.

So, 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.

Interestingly, some SQLite tests are proprietary. The test suite called TH3 (Test Harness 3), which achieves 100% branch coverage of the code, is paid.

I don't know any other project which has made code free, but test suites are paid.

It's an interesting business model. Along with the paid test suite, they generate revenue through paid support, maintenance services, and commercial extensions.

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 is so fast, they compete with `fopen`. For some use cases, you can use SQLite instead of a filesystem, that can be 35% faster.

Check this out, SQLite vs Redis (guess which is faster?)

https://x.com/iavins/status/1849422515027763227

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.

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
- Types are disabled; they are opt-in!
- Many of the ALTER commands you expect in other databases don't work. For example, you cannot rename a column

I hate that types are disabled. 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.

Check this insane lore

https://x.com/iavins/status/1865746403072389612

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.

But they take backward compatibility so seriously that even if they have shipped a bug, they won't fix it

https://x.com/iavins/status/1851276312876326980

SQLite's author Dr. 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.

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)

SQLite is pronounced as "Ess-Cue-El-Lite". There is no official guideline though.

Here are the sources:

1. Most Deployed - sqlite.org/mostdeployed.h…

2. Open Source vs Public Domain opensource.org/blog/public-do…

3. Public Domain and Contributions sqlite.org/copyright.html

4. Testing - sqlite.org/testing.html

5. TH3 - sqlite.org/th3.html and sqlite.org/prosupport.html

6. Code of Ethics - sqlite.org/codeofethics.h…

7. Faster than FS - sqlite.org/fasterthanfs.h…

That's it for today! If I missed any, let me know.

Happy holidays and Happy New Year! 🎄 ☃️

here is the first tweet for you to retweet

https://x.com/iavins/status/1873382770203844884

This thread is doing numbers. I don’t have Soundcloud, but you can follow me on WhatsApp / Telegram for more serious database internals stuff

WhatsApp - whatsapp.com/channel/0029Va…

Telegram - t.me/databases_v