The Untold Story of SQLite
Richard Hipp discusses SQLite's evolution, created to solve database reliability issues. It gained traction through interest from Motorola and AOL, becoming essential in smartphones and aviation, emphasizing rigorous testing.
Read original article
Richard Hipp discusses the evolution of SQLite in a recent episode of the CoRecursive podcast. SQLite, a lightweight database engine, has become integral to various technologies, including web browsers, smartphones, and even commercial aircraft. Hipp's journey began while working on a project for a battleship, where he faced challenges with the existing database system, Informix. Frustrated by its unreliability, he decided to create a new database engine that could operate without a server, leading to the development of SQLite in 2000. Initially, it was a side project, but as he shared it online, it gained traction, particularly after Motorola expressed interest in integrating SQLite into their new cell phone operating system. This marked the beginning of SQLite's widespread adoption. Over the years, Hipp has navigated relationships with major tech companies, refined testing procedures, and ensured the database's reliability, which is crucial in high-stakes environments like aviation. His story highlights the unexpected journey of an open-source project that became a foundational technology in the digital world.
- Richard Hipp created SQLite to address reliability issues with existing database systems.
- SQLite is now widely used in various applications, including smartphones and commercial aircraft.
- The project gained traction after Motorola and AOL expressed interest in using it.
- Hipp emphasizes the importance of rigorous testing and reliability in critical applications.
- SQLite's success illustrates the potential of open-source projects to evolve into essential infrastructure.
Related
First Contact with SQLite
The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.
Gotchas with SQLite in Production
SQLite is popular for web apps but has limitations like single-threaded access, network challenges, and constraints in concurrency and transactions. Consider alternatives like MySQL for complex scenarios.
10 commentsWhatever money Dr. Hipp makes from SQLite it is surely not enough relative to the value that it has provided to millions of people.
I enjoyed this incidental homage to postgres:
"He came up with this huge corpus of SQL statements, and he ran them against every database engine that he could get his hands on. We wanted to make sure everybody got the same answer, and he managed to segfault every single database engine he tried, including SQLite, except for Postgres. Postgres always ran and gave the correct answer. We were never able to find a fault in that. The Postgres people tell me that we just weren’t trying hard enough. It is possible to fault Postgres, but we were very impressed."
If you see value in Corecursive, it's probably an especially considerate time to support him on Patreon.
IIRC one of the best episodes of the Corecursive podcast I've heard so far.
2021-07-03 https://news.ycombinator.com/item?id=27718701 97 comments
2023-01-28 https://news.ycombinator.com/item?id=34559075 92 comments
also
2021-07-02 https://news.ycombinator.com/item?id=27712563 5 comments
SQLite was my gateway drug to data analysis, because I didn't know any better I just started using that for automated tasks that were done in excel up to that point. It was the basis of my career in data, after I decided to get out of academia.
Anyway.
I've recently built a SQLite extension that bakes Lua into SQLite. You can do
SELECT lua('return some_business_logic(...)', col1, col2 FROM some_table;
The idea allows running complex business logic directly inside the SQLite process. This all for speed and efficiency as you don't schlep data in and out of your language calling SQLite, and for better testability as it seems the sweet spot in terms of decoupling business logic from the query writing.
I've been at it on my own too much, I can't really reason about it anymore.
So, dear HN, is this idea completely idiotic or am I onto something that the world would benefit from?
> Richard: I had this crazy idea that I’m going to build a database engine that does not have a server, that talks directly to disk, and ignores the data types, and if you asked any of the experts of the day, they would say, “That’s impossible. That will never work. That’s a stupid idea.” Fortunately, I didn’t know any experts and so I did it anyway, so this sort of thing happens. I think, maybe, just don’t listen to the experts too much and do what makes sense. Solve your problem.
Let’s not dwell on the details, but I had this idea, I’m going to write tests to bring SQLite up to the quality of 100% MCDC, and that took a year of 60 hour weeks. That was hard, hard work. I was putting in 12 hour days every single day. I was just getting so tired of this because with this sort of thing, it’s the old joke of, you get 95% of the functionality with the first 95% of your budget, and the last 5% on the second 95% of your budget. It’s kind of the same thing. It’s pretty easy to get up to 90 or 95% test coverage. Getting that last 5% is really, really hard and it took about a year for me to get there, but once we got to that point, we stopped getting bug reports from Android.
Related
First Contact with SQLite
The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.
Gotchas with SQLite in Production
SQLite is popular for web apps but has limitations like single-threaded access, network challenges, and constraints in concurrency and transactions. Consider alternatives like MySQL for complex scenarios.