#95: SQLite: the most ubiquitous database on the planet. And beyond!
SQLite is by far the most common SQL database ever deployed. Are you listening to this on iPhone or Android device? It runs SQLite. Or maybe through a web browser? Chrome, Safari, Firefox, Opera and Android Browser all use SQLite underneath. Are you running MacOS or Windows? It’s built-in. Most Linux distributions have it as well. “But I’m listening to your podcast while driving a car”, you say. Well, most automotive systems use SQLite internally. If you happen to listen to my podcast while coding in PHP or Python, they include SQLite out-of-the-box. Oh, and it’s the default choice for Ruby on Rails as well. I’m pretty sure SQLite is used somewhere on the International Space Station and it maybe even landed on other planets? So, what makes this unsung hero so popular to run at least one trillion databases worldwide?
First of all, SQLite is an embedded database. In contrast to databases like PostgreSQL or Oracle, it doesn’t run as a separate server or process. Instead, you include SQLite inside your application, as if it was just another library. It doesn’t require any configuration. This has several benefits. First of all, it’s extremely fast by avoiding inter-process communication, especially over the network. Secondly, the database itself is stored on just a single file on disk.
The lack of client-server mode can be partially emulated. Multiple processes can read from the same database file concurrently. Concurrent writes are also possible with some extra configuration effort. So, if you are writing any type of software that needs local storage, SQLite is a good choice. You get the full power of SQL database, including transactions. The SQLite database files are so abundant that they’re approved by the Library Of Congress as a long-term storage format.
In general, SQLite has a feature set inspired by PostgreSQL. One exception is it’s peculiar type system. Most database engines enforce strict, static type checking. SQLite, on the other hand, prefers weak and dynamic typing by default. It means that you can insert string where integer was expected and the engine will perform some type of coercion. This can be changed with strict mode.
I like SQLite’s catchphrase: Small. Fast. Reliable. Choose any three. It’s not an overstatement. The binary is small, just 2 MiB. It’s fast, to the point where N+1 problem is not an issue when running SQLite. If you are familiar with object-relational mapping software, N+1 is a performance killer for any other non-embedded database. In some benchmarks, querying a binary blob from SQLite is faster than reading a similar binary file from disk. It’s reliability is proven by almost 100 million lines of test code. Tests make sure the database doesn’t fail on I/O failures, out-of-memory errors and other crashes.
What are the shortcomings of SQLite? First of all, it’s not distributed, thus limited to a single disk and machine. Well, to be honest, many non-embedded SQL engines have this limitation as well. Secondly, there is no explicit permission model. SQLite relies on file system permissions. Finally, SQLite suits best for read-heavy applications with limited write concurrency support. But considering its use-cases, these are not much important.
That’s it, thanks for listening, bye!