48 ms·

The Untold Story of SQLite

548 points by signa11 5y ago

unixhero 5y ago
The story of SQlite was told by Dr Hipp himself a few years ago. Great talk https://youtu.be/Jib2AmRb_rk https://youtu.be/Jib2AmRb_rk
- zerr 5y ago
  That was more like a marketing speech.
  - unixhero 5y ago
    No
- chasil 5y ago
  Thanks for that video. Interesting to hear that SQLite is supported on the A350 for life service life of the design. And Microsoft contracting for changes for Windows 10 bundling? That's shocking, considering how many small databases they have developed or purchased over the years.
agbell 5y ago
Host Here. Thanks for sharing. Richard is a great story teller and adventures that SQLite has taken him on pretty interesting. I think he in on hn as well ( @SQLite). One interesting thing I found about how SQLite is developed is the amount of testing that is done. Between unit tests and parameterized tests and fuzzer tests they are doing billions of tests per release and spending days running them before each release. I know I don't like to wait for a long CI build before I deploy a service but something like SQLite needs whole different category of resilience.
- radiator 5y ago
  Very good podcast, thank you. What I personally missed is a question about the future plans for SQLite. Also about version 4.
- sigzero 5y ago
  He is on HN. It was a great segment.
  - agbell 5y ago
    Thanks for listening! I'm glad you enjoyed it.
- not-my-account 5y ago
  Great podcast! I have been following it for a while. Can I ask how ended up starting it?
  - agbell 5y ago
    CoRecursive started because I did some episodes as a host on software engineering daily and it was lots of fun so I decided to start one of my own. The first 4 episodes were originally on software engineering daily. Originally the idea was to give some platform to the areas of computing I was interested in, heavy use of types and functional programming but I think I really found my place as a podcast when I started to focus on peoples stories. So rather than tell me about SQLite, it is tell me about the story behind creating SQLite.
    - iso8859-1 5y ago
      You are so polite, I am impressed with your ability not to push back on the interviewees. In this episode I would have asked "would sqlite really be worse today if you hadn't done your own VCS.". But I think I wouldn't be half as successful as you, people don't wanna be implicitly criticised by their interviewer :P You are doing so great, favorite podcast by far!
      - neolog 5y ago
        I quite disagree. It's boring when they just say their talking points. The guests are experts in their field, they can handle some pushback and it would be much more educational for the rest of us to hear the answers to the questions we're really wondering about. It doesn't have to be "implicit criticism", but an open mind doesn't blindly accept stuff, ask for justification.
        sitkack 5y ago
        The justification is where the story is, the tension and relief. This is where you really learn from the interview.
      - 5y ago
- macintux 5y ago
  Thanks, that really is a great conversation. I'm going to have to dig to see if he describes somewhere why Fossil is such a great fit for SQLite.
  - bch 5y ago
    Couple things: * immutable * built in first-class ticketing system Happens to use SQLite as its storage engine too (SQLInception?), which brings at least a couple (off the top of my head) features: * SQL is used to query things, which is very powerful * the repository is a single SQLite file. Easy to store, copy, and… * one can checkout multiple working copies into various directories. Or, in other words, I can have three discrete, independent workflows happening simultaneously, but only one copy of the repository backing them all. It’s also got a built-in server that you can use locally or publish for remote people to access via their web browser that’s essentially GitHub-in-a-box. Also… (kidding. I’m just going to stop here). Edit: formatting
    - krylon 5y ago
      I've migrated to git for my personal projects, mainly because magit (emacs frontend for git) is awesome. But fossil is a pleasure to work with, especially on Windows, because a) it comes as a single executable that one can just copy to another machine, and b) it does not require ssh or anything else to be installed. The web interface is amazing, especially considering the size of the executable file.
    - Cyph0n 5y ago
      Regarding the multiple working copies feature, sounds the same as Git worktrees.
      - smartmic 5y ago
        Here is a summary of similarities and differences on Git and Fossil functionalities: https://fossil-scm.org/home/doc/trunk/www/gitusers.md https://fossil-scm.org/home/doc/trunk/www/gitusers.md Also quite interesting the general comparison: https://fossil-scm.org/home/doc/trunk/www/fossil-v-git.wiki https://fossil-scm.org/home/doc/trunk/www/fossil-v-git.wiki Both sites are written from Fossil guys, so they may not be 100% neutral.
- bsaul 5y ago
  That part really made me laugh. A few months ago, i developed a wrapper in swift for sqlite on a project. A coworker of mine had a bug on what sqlite returned, and i couldn't find anything wrong. He told me "it has to be a bug on sqlite", and i said "no, sqlite has no bugs… but i don't really understand what's going on". And so we posted a bug report on the sqlite forums. I told my friend "if you found a bug on sqlite, on your first try, i'll buy you a bottle of champaign". Of course, it was a bug with my wrapper (included a \0 char at the end of strings). I just forwarded him the link to your podcast, saying "we really had no chance"
  - deleted 5y ago
    [deleted]
  - thechao 5y ago
    SQLite is a great way to find bugs in your fuzzer: if your fuzzer finds bug, it’s time to debug your fuzzer!
- collaborative 5y ago
  Really enjoyed this. Is it possible to subscribe using an email address? The subscribe page seems to ask for a bunch of apps (Apple Podcast etc) that I don't use. Would be great to just receive an email and listen on a browser
  - agbell 5y ago
    If you scroll to the bottom of the page and sign up for the newsletter, I will email you whenever there is a new episode out. The newsletter will come out once a month when there is a new episode and has a bit of personal take on the episode and maybe some links to other things I've been up to. If you just want a pure notification, I'm sure you could hook the RSS feed up to an RSS to email service. Thanks for listening!
- joeyh 5y ago
  I enjoyed this episode. I knew about all the tests before, but what came clear in this interview is that Sqlite inc is using their tests as a unique way to keep contracts on Sqlite development flowing. Since they can test it so much better than anyone else, it would be crazy not to hire them, rather than hiring someone else to develop a patch. While they're keeping those tests proprietary, and relatedly have a closed development model, this strikes me as still better than typical open core strategies for monetizing free software, or at least pointing in better directions. Agbell, since you're here, could I ask you add a standard link to the RSS feed for your podcast? <link rel="alternate" title="Corecursive" type="application/rss+xml" href="https://link.chtbl.com/corecursive?platform=rss https://link.chtbl.com/corecursive?platform=rss" />
  - zinekeller 5y ago
    > Sqlite inc Minor nitpick: technically, the primary company that supports SQLite is an incorporated partnership named Hipp [ed note: yes, that's Mr. Richard Hipp], Wyrick [ed note: Hipp's wife] & Company, Inc. dba Hwaci (https://www.hwaci.com/ https://www.hwaci.com/).
  - agbell 5y ago
    Will do!
    - agbell 5y ago
      Done, joeyh.
  - deleted 5y ago
    [deleted]
seibelj 5y ago
SQLite is one of the finest pieces of software ever made. It’s one of those quiet innovations in the world that has multiplied the advancement of society and the chattering classes have absolutely no idea. I hope this story gets told more often, to more people. Kudos to Dr. Hipp!
andrewl 5y ago
He says he's going to write his own mail server. I use SQLite all the time, and I'm going to try Fossil when I get a bit of time to dig into it. I will definitely give his mail system a look when it's out.
- desine 5y ago
  I use SQLite pretty frequently. I switched to Fossil (from git). I'm happy. The overall architecture of having your repo be a single binary stored in one location, and your working directories being just working directories and not repos themselves is a breath of fresh air. Branching seems a little different, but my projects aren't big enough scope to have encountered any hiccups. I did it mostly for the built in project management (wiki, bug tracking), and to get away from the big SaaS environments. But the sqlite binary file repo sold me on the architecture quickly. Dr. Hipp is a fantastic software engineer and product designer. Both SQLite and Fossil do what they need, have only the bells and whistles needed, and have clean, consistent designs. I've browsed the source for SQLite before, and it's also clean, legible, and quality.
anoncept 5y ago
What a lovely treat — and how fascinating to learn about the hidden role that DO—178B has played in this most ubiquitous of programs!
- eesmith 5y ago
  It makes me want to read DO—178B. Hmmm, worldcat says the nearest library with a copy is several hundred miles away. I wonder if I can ILL it...
alberth 5y ago
I’d love it if Dr Hipp created a client/server db. He could call it SQLMajor instead of SQLite :)
- systems 5y ago
  or you can use postgresql
- afiori 5y ago
  fun fact: the `ite` ending in SQLite is a reference to the `ite` ending in minerals (granite, magnetite, pyrite, bauxite...) so it is not a lightweight SQL but rather a rock-stable SQL
  - yzombinator 5y ago
    This is very interesting!
  - Seattle3503 5y ago
    Aight
  - exikyut 5y ago
    I don't care if this is canon or not, I like it enough that this is mine now and I'm using it
    - divs1210 5y ago
      you're going to mine some sqlite?
  - np_tedious 5y ago
    So have we all been saying it with the wrong emphasis? Sequelite vs Sequel-lite How does creator pronounce it in podcast?
    - sigzero 5y ago
      He pronounces it "ess-que-el ite".
beatthatflight 5y ago
Bizarre, listening to the episode and then open this here!
1vuio0pswjnm7 5y ago
"I'm going to write my own mail server. I was making notes on that even as we were setting up this call. That's a big problem, and that's at least as difficult if not more difficult than writing a database engine, but I don't want to be beholden to Gmail. I don't want them controlling my destiny. I don't want them controlling the record of all of my conversations. I want to control that myself, and so I'm going to go through a lot of pain and a lot of work and a lot of effort to come up with some solution that I can control myself. I can go out and lease a virtual machine out there in the cloud and run it myself and not depend on a third party to control my email."
- umbrellaguy 5y ago
  >I can go out and lease a virtual machine out there in the cloud and run it myself and not depend on a third party to control my email. Emphasis here?
  - harry8 5y ago
    Yeah. Counter-argument moving vms to different providers, ips etc is a lot more straightforward so maybe they don't "control" your mail..?" And a third way of looking at is every mail has two endpoints. You don't control the server of who sent it to you or who you send it to. Google does. So they still control your mail..?
- ddlutz 5y ago
  Does anybody have any insight into " at least as difficult if not more difficult than writing a database engine"? It would seem to me that a mail server would be much easier than a database engine.
  - Lex-2008 5y ago
    It might be difficult on a different level. Dr.Hipp mentioned GMail bouncing email sent to him - but hosting email yourself, will just move you to a different side of the bounce - now GMail will be bouncing emails sent by you! See, for example, first comments on the recent HN thread here: https://news.ycombinator.com/item?id=27707857 https://news.ycombinator.com/item?id=27707857 (ctrl+f for "pain in the" if it's not the first one). So it looks like convincing GMail to respect your small server is a non-technical problem, which might be an issue for a person with "only engineering" mindset. On the other side, Dr.Hipp looks like a right person to try to convince GMail to respect smaller mailservers!
MrBuddyCasino 5y ago
> Here’s warning to all your listeners whenever a politician comes to you and says, “Oh, we’re going to take care of this problem for you.” What they’re really saying is, we’re going to take away some of your freedoms. The exercise for you, here, is to figure out what freedoms they’re going to take away in order to solve this problem, and they’re often well-disguised, but yeah, if you want to be free, that means doing things yourself. Easily the most important take away in this excellent interview, but unfortunately, also the most contentious.
- j-pb 5y ago
  So what you are saying is we should hold big petro, chemical, and car companies accountable ourselves when they threaten billions of lives through ecosystem collapse level polution and climate catastrophe? Great! Pick up a gun then! Or maybe, the whole, "legislation is curbing everyones freedom!" thing is just a lie, fed to you by the most wealthy and powerfull to keep you from voting in a way that protects your freedom, and curbs theirs.
EMM_386 5y ago
> 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. This is impressive for Postgres. SQLite is incredible. I've used it over the years in a variety of important projects.
ridruejo 5y ago
I had the opportunity to interact with Dr. Richard Hipp earlier in my career. He’s a wonderful human being.
jokoon 5y ago
So what are the advantages of using DB like mysql, postgre?
- tyrex2017 5y ago
  access management, and scalability: can better do simultaneous writes, and use several copies horizontally BUT: you probably need 1k+ or 10k+ requests per second to start getting advantages
- hanche 5y ago
  Take a look at the link below, scroll down to the headline “Situations Where A Client/Server RDBMS May Work Better”. https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html
- xet7 5y ago
  At "Ask HN: Do you self-host your database?" https://news.ycombinator.com/item?id=27671376 https://news.ycombinator.com/item?id=27671376 is this comment: https://news.ycombinator.com/item?id=27673359 https://news.ycombinator.com/item?id=27673359 "We're currently transitioning from a multi-tenant 2TB postgres DB hosted on AWS RDS to using sqlite instead, a separate database for each client. We're doing this for multiple reasons: a) As our DB grew the service became very expensive, one of the biggest items in our AWS invoice; b) Keeping the PG servers up to date is a pain, we simply don't have time for this; c) We wanted to be able to migrate to other clouds and even be able to offer a self-hosted version of our platform."
- xet7 5y ago
  SQLite was created, because client-server database Informix (similar to MySQL and PosgreSQL) did crash and and was unavailable to connect to. There are some edge cases where MySQL/PostgreSQL can start vacuum, be corrupted, etc making client/server database unavailable. SQLite is tested with billions of test cases. In interview: "The idea for SQLite actually came out of his frustrations with an existing database called Informix that was installed on a literal battleship." "Why do we even need a server? Why can’t I pull this directly off the disk drive? That way if the computer is healthy enough, it can run our application at all, we don’t have dependencies that can fail and cause us to fail, and I looked around and there were no SQL database engines that would do that, and one of the guys I was working with says, “Richard, why don’t you just write one?” “Okay, I’ll give it a try.” "
cassepipe 5y ago
Also to note : The fossil project contains a makeheaders.c file that can be compiled as an executable. What it does is that it scans all the .c files and the .h files you give it and generates the necessary headers for each file. I am surprised it's the only simple solution I could find for this purpose. I'm grateful not to have to update .h files any more. Check out https://www.hwaci.com/sw/mkhdr/ https://www.hwaci.com/sw/mkhdr/
- therealcamino 5y ago
  Sounds like the old makedepend program.
  - cassepipe 5y ago
    Never heard of it. The only other thing I found was something called lzz but it seemed more complicated and C++ oriented. Looking up makedepend taught me that it had been superseded by Gnu Automake... Which seems to be a big machinery and another rabbit hole I am not sure I want to go into : I've already had too much fights with makefiles already. I really wonder how most C programmers do.
polyrand 5y ago
For those who liked the podcast, I would also recommend this one: https://softwareengineeringdaily.com/2015/11/13/sqlite-with-d-richard-hipp/ https://softwareengineeringdaily.com/2015/11/13/sqlite-with-... Dr. Richard Hipp is truly an inspiration.
mrlonglong 5y ago
Time I got myself a copy of this Art of Computer Programming book. See if it can make me a better coder.
- agbell 5y ago
  It's interesting that when I interviewed him, he has all these built-it book cases behind him in his office. The idea of not needing the internet but just occasionally grabbing a book from behind you to look something up when developing software is very appealing to me. When I interviewed Brian Kernighan one of his complaints was how so much of modern programming is just looking things up rather than building things and I think that Richard has escaped that fate by building things himself.
  - geocrasher 5y ago
    I really enjoyed those parts of each interview. They are very well done.
  - mrlonglong 5y ago
    Before the internet, this is what we did. Read documentation and books. Now it's just a matter of applying Google-fu. I worry that this could be stunting our problem solving skills.
bob1029 5y ago
SQLite is the one technology that saved our software stack. Back in 2016 we were struggling with management of separately-hosted SQL Server instances (network, firewall, user accounts, license, explaining shit to customers, etc.) Installing our software in a new customer environment would take days because of all of the extra mechanics involved. There were 8+ other windows services as well. Fast forward to 2019 - We now use SQLite embedded inside the application instance. The entire system has been collapsed into a single binary distribution & service. Deploying our software to a completely blank windows server involves emailing the customer a ~120 megabyte zip file, asking them to extract it to C:\myapp and run sc.exe install. This shift in how we manage persistence of business data allowed for us to salvage an otherwise completely lost ship. Our team is still incredibly small (<10 employees), and we are responsible for managing hundreds of customer environments. If we didn't have a reliable, self-contained persistence mechanism that "just works", we wouldn't have time to do anything else of value.
- zepearl 5y ago
  > ...involves emailing the customer a ~120 megabyte zip file... Hehe, nice test for their email servers & antivirus (which probably wants to unpack it to see if any nasty payload is included) :)
  - bob1029 5y ago
    Good catch. To clarify - we email a link to the distribution (somewhere in an AWS S3 bucket), not attach the actual zip file. This would clearly be a nightmare for 99% of email providers.
    - zepearl 5y ago
      Aha, that sounds a lot better, thx - indeed I was wondering if nowadays common max email size increased a lot since I set up my private email server ~10 years ago... :)
      - teddyh 5y ago
GuB-42 5y ago
I remember in the early 2000s, I just got started with SQL and one of my reaction was "SQL is great, but why do we need a server? Why not use a simple library and files?" So I asked my coworkers if such a thing existed. And I had a lot of reactions along the lines of "it is stupid why do you want that? Just install a server on localhost" few people were convinced. It wasn't even a case of "nice idea, someone else should do it it", I think people just associated SQL with servers and by being new to it, I didn't have that preconceived idea. A couple of years later sqlite was everywhere. It was exactly what I had in mind (but with nowhere enough skills to do it myself).
uDontKnowMe 5y ago
Thanks Adam, I love the podcast!
wirthjason 5y ago
Any good recommendations for books on SQLite? I’ve never spent much time with SQLite directly. My main interaction has been through Django and the ORM layer. My day job uses a custom object database. While I’m familiar with SQL and relational DB concepts I haven’t worked heavily with them.
- jlelse 5y ago
  The SQLite website itself is pretty well written.
- webmobdev 5y ago
  If you know SQL, all you got to do is understand the specific quirks of the database you use (e.g. In SQLite, specifying a data type for a column acts like a "hint" rather than a real constraint). The SQLite website has a pretty thorough manual that should more than suffice for this.
twotwotwo 5y ago
> The 100% MCD [modified decision/condition coverage] 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. That'd be an awesome contribution to open source, particularly at a time that there's a lot of collective awareness of how hard it is to secure C programs. (There's probably still a business reason to keep it proprietary: it's a reason for people to go to them for changes, at least if they want the final product to be thoroughly tested. Alas, 'cause it would be cool to see.) It's funny that he references preppers, survivalists, and dependency on society near the end, when talking about writing all of your own software dependencies. A while back someone thinking about it from the other end (i.e. saying we generally should accept some dependencies) said almost exactly the same thing in thread at https://twitter.com/hdevalence/status/1214234305754329088 https://twitter.com/hdevalence/status/1214234305754329088 . I draw no deep insight from this, but fun to see the same analogy from two very different perspectives. (Kind of closing the circle, the "let's be real about dependencies" article linked, https://wiki.alopex.li/LetsBeRealAboutDependencies https://wiki.alopex.li/LetsBeRealAboutDependencies , mentions in passing "SQLite is basically everywhere (for good reason)".)
krakov 5y ago
> You couldn’t just Google how to build a database > Fortunately, I didn’t know any experts and so I did it anyway He obviously lives by a certain ethos of self-reliance so not sure he would have talked to experts if could. But makes me wonder: given it so easy now to find the experts compared to 2000, to google what they think - it is easier to get discouraged?
- johannes1234321 5y ago
  The question is: If he had asked experts early on, would he have ended up less self reliant? And would we have gotten an SQLite as good as it is now or would he have been told "oh that's too complex" and be discouraged by experts? :)
drummer 5y ago
> Richard:It involves doing without. Here’s warning to all your listeners whenever a politician comes to you and says, “Oh, we’re going to take care of this problem for you.” What they’re really saying is, we’re going to take away some of your freedoms. The exercise for you, here, is to figure out what freedoms they’re going to take away in order to solve this problem, and they’re often well-disguised, but yeah, if you want to be free, that means doing things yourself.
pauljurczak 5y ago
> DDG-79 Oscar Austin. That is a battleship No, it is a destroyer, see https://www.surflant.usff.navy.mil/ddg79/ https://www.surflant.usff.navy.mil/ddg79/. Other than Hollywood, no one had built a battleship since around the end of WWII.
cstork12 5y ago
Maybe a bit off-topic, but I noticed that https://www.sqlite.org/cpu.html https://www.sqlite.org/cpu.html hasn't been updated in over two years and given the consistently high development speed I'd be curious how the "CPU Usage" deals with all the new features. :-)

548 points by signa11 5y ago

The story of SQlite was told by Dr Hipp himself a few years ago. Great talk https://youtu.be/Jib2AmRb_rk https://youtu.be/Jib2AmRb_rk

zerr 5y ago
That was more like a marketing speech.
- unixhero 5y ago
  No
chasil 5y ago
Thanks for that video. Interesting to hear that SQLite is supported on the A350 for life service life of the design. And Microsoft contracting for changes for Windows 10 bundling? That's shocking, considering how many small databases they have developed or purchased over the years.

agbell 5y ago

Host Here. Thanks for sharing. Richard is a great story teller and adventures that SQLite has taken him on pretty interesting. I think he in on hn as well ( @SQLite). One interesting thing I found about how SQLite is developed is the amount of testing that is done. Between unit tests and parameterized tests and fuzzer tests they are doing billions of tests per release and spending days running them before each release. I know I don't like to wait for a long CI build before I deploy a service but something like SQLite needs whole different category of resilience.

radiator 5y ago
Very good podcast, thank you. What I personally missed is a question about the future plans for SQLite. Also about version 4.
sigzero 5y ago
He is on HN. It was a great segment.
- agbell 5y ago
  Thanks for listening! I'm glad you enjoyed it.
not-my-account 5y ago
Great podcast! I have been following it for a while. Can I ask how ended up starting it?
- agbell 5y ago
  CoRecursive started because I did some episodes as a host on software engineering daily and it was lots of fun so I decided to start one of my own. The first 4 episodes were originally on software engineering daily. Originally the idea was to give some platform to the areas of computing I was interested in, heavy use of types and functional programming but I think I really found my place as a podcast when I started to focus on peoples stories. So rather than tell me about SQLite, it is tell me about the story behind creating SQLite.
  - iso8859-1 5y ago
    You are so polite, I am impressed with your ability not to push back on the interviewees. In this episode I would have asked "would sqlite really be worse today if you hadn't done your own VCS.". But I think I wouldn't be half as successful as you, people don't wanna be implicitly criticised by their interviewer :P You are doing so great, favorite podcast by far!
    - neolog 5y ago
      I quite disagree. It's boring when they just say their talking points. The guests are experts in their field, they can handle some pushback and it would be much more educational for the rest of us to hear the answers to the questions we're really wondering about. It doesn't have to be "implicit criticism", but an open mind doesn't blindly accept stuff, ask for justification.
      - sitkack 5y ago
        The justification is where the story is, the tension and relief. This is where you really learn from the interview.
    - 5y ago
macintux 5y ago
Thanks, that really is a great conversation. I'm going to have to dig to see if he describes somewhere why Fossil is such a great fit for SQLite.
- bch 5y ago
  Couple things: * immutable * built in first-class ticketing system Happens to use SQLite as its storage engine too (SQLInception?), which brings at least a couple (off the top of my head) features: * SQL is used to query things, which is very powerful * the repository is a single SQLite file. Easy to store, copy, and… * one can checkout multiple working copies into various directories. Or, in other words, I can have three discrete, independent workflows happening simultaneously, but only one copy of the repository backing them all. It’s also got a built-in server that you can use locally or publish for remote people to access via their web browser that’s essentially GitHub-in-a-box. Also… (kidding. I’m just going to stop here). Edit: formatting
  - krylon 5y ago
    I've migrated to git for my personal projects, mainly because magit (emacs frontend for git) is awesome. But fossil is a pleasure to work with, especially on Windows, because a) it comes as a single executable that one can just copy to another machine, and b) it does not require ssh or anything else to be installed. The web interface is amazing, especially considering the size of the executable file.
  - Cyph0n 5y ago
    Regarding the multiple working copies feature, sounds the same as Git worktrees.
    - smartmic 5y ago
      Here is a summary of similarities and differences on Git and Fossil functionalities: https://fossil-scm.org/home/doc/trunk/www/gitusers.md https://fossil-scm.org/home/doc/trunk/www/gitusers.md Also quite interesting the general comparison: https://fossil-scm.org/home/doc/trunk/www/fossil-v-git.wiki https://fossil-scm.org/home/doc/trunk/www/fossil-v-git.wiki Both sites are written from Fossil guys, so they may not be 100% neutral.
bsaul 5y ago
That part really made me laugh. A few months ago, i developed a wrapper in swift for sqlite on a project. A coworker of mine had a bug on what sqlite returned, and i couldn't find anything wrong. He told me "it has to be a bug on sqlite", and i said "no, sqlite has no bugs… but i don't really understand what's going on". And so we posted a bug report on the sqlite forums. I told my friend "if you found a bug on sqlite, on your first try, i'll buy you a bottle of champaign". Of course, it was a bug with my wrapper (included a \0 char at the end of strings). I just forwarded him the link to your podcast, saying "we really had no chance"
- deleted 5y ago
  [deleted]
- thechao 5y ago
  SQLite is a great way to find bugs in your fuzzer: if your fuzzer finds bug, it’s time to debug your fuzzer!
collaborative 5y ago
Really enjoyed this. Is it possible to subscribe using an email address? The subscribe page seems to ask for a bunch of apps (Apple Podcast etc) that I don't use. Would be great to just receive an email and listen on a browser
- agbell 5y ago
  If you scroll to the bottom of the page and sign up for the newsletter, I will email you whenever there is a new episode out. The newsletter will come out once a month when there is a new episode and has a bit of personal take on the episode and maybe some links to other things I've been up to. If you just want a pure notification, I'm sure you could hook the RSS feed up to an RSS to email service. Thanks for listening!
joeyh 5y ago
I enjoyed this episode. I knew about all the tests before, but what came clear in this interview is that Sqlite inc is using their tests as a unique way to keep contracts on Sqlite development flowing. Since they can test it so much better than anyone else, it would be crazy not to hire them, rather than hiring someone else to develop a patch. While they're keeping those tests proprietary, and relatedly have a closed development model, this strikes me as still better than typical open core strategies for monetizing free software, or at least pointing in better directions. Agbell, since you're here, could I ask you add a standard link to the RSS feed for your podcast? <link rel="alternate" title="Corecursive" type="application/rss+xml" href="https://link.chtbl.com/corecursive?platform=rss https://link.chtbl.com/corecursive?platform=rss" />
- zinekeller 5y ago
  > Sqlite inc Minor nitpick: technically, the primary company that supports SQLite is an incorporated partnership named Hipp [ed note: yes, that's Mr. Richard Hipp], Wyrick [ed note: Hipp's wife] & Company, Inc. dba Hwaci (https://www.hwaci.com/ https://www.hwaci.com/).
- agbell 5y ago
  Will do!
  - agbell 5y ago
    Done, joeyh.
- deleted 5y ago
  [deleted]

seibelj 5y ago

SQLite is one of the finest pieces of software ever made. It’s one of those quiet innovations in the world that has multiplied the advancement of society and the chattering classes have absolutely no idea. I hope this story gets told more often, to more people. Kudos to Dr. Hipp!

andrewl 5y ago

He says he's going to write his own mail server. I use SQLite all the time, and I'm going to try Fossil when I get a bit of time to dig into it. I will definitely give his mail system a look when it's out.

desine 5y ago
I use SQLite pretty frequently. I switched to Fossil (from git). I'm happy. The overall architecture of having your repo be a single binary stored in one location, and your working directories being just working directories and not repos themselves is a breath of fresh air. Branching seems a little different, but my projects aren't big enough scope to have encountered any hiccups. I did it mostly for the built in project management (wiki, bug tracking), and to get away from the big SaaS environments. But the sqlite binary file repo sold me on the architecture quickly. Dr. Hipp is a fantastic software engineer and product designer. Both SQLite and Fossil do what they need, have only the bells and whistles needed, and have clean, consistent designs. I've browsed the source for SQLite before, and it's also clean, legible, and quality.

anoncept 5y ago

What a lovely treat — and how fascinating to learn about the hidden role that DO—178B has played in this most ubiquitous of programs!

eesmith 5y ago
It makes me want to read DO—178B. Hmmm, worldcat says the nearest library with a copy is several hundred miles away. I wonder if I can ILL it...

alberth 5y ago

I’d love it if Dr Hipp created a client/server db. He could call it SQLMajor instead of SQLite :)

systems 5y ago
or you can use postgresql
afiori 5y ago
fun fact: the `ite` ending in SQLite is a reference to the `ite` ending in minerals (granite, magnetite, pyrite, bauxite...) so it is not a lightweight SQL but rather a rock-stable SQL
- yzombinator 5y ago
  This is very interesting!
- Seattle3503 5y ago
  Aight
- exikyut 5y ago
  I don't care if this is canon or not, I like it enough that this is mine now and I'm using it
  - divs1210 5y ago
    you're going to mine some sqlite?
- np_tedious 5y ago
  So have we all been saying it with the wrong emphasis? Sequelite vs Sequel-lite How does creator pronounce it in podcast?
  - sigzero 5y ago
    He pronounces it "ess-que-el ite".

beatthatflight 5y ago

Bizarre, listening to the episode and then open this here!

1vuio0pswjnm7 5y ago

"I'm going to write my own mail server. I was making notes on that even as we were setting up this call. That's a big problem, and that's at least as difficult if not more difficult than writing a database engine, but I don't want to be beholden to Gmail. I don't want them controlling my destiny. I don't want them controlling the record of all of my conversations. I want to control that myself, and so I'm going to go through a lot of pain and a lot of work and a lot of effort to come up with some solution that I can control myself. I can go out and lease a virtual machine out there in the cloud and run it myself and not depend on a third party to control my email."

umbrellaguy 5y ago
>I can go out and lease a virtual machine out there in the cloud and run it myself and not depend on a third party to control my email. Emphasis here?
- harry8 5y ago
  Yeah. Counter-argument moving vms to different providers, ips etc is a lot more straightforward so maybe they don't "control" your mail..?" And a third way of looking at is every mail has two endpoints. You don't control the server of who sent it to you or who you send it to. Google does. So they still control your mail..?
ddlutz 5y ago
Does anybody have any insight into " at least as difficult if not more difficult than writing a database engine"? It would seem to me that a mail server would be much easier than a database engine.
- Lex-2008 5y ago
  It might be difficult on a different level. Dr.Hipp mentioned GMail bouncing email sent to him - but hosting email yourself, will just move you to a different side of the bounce - now GMail will be bouncing emails sent by you! See, for example, first comments on the recent HN thread here: https://news.ycombinator.com/item?id=27707857 https://news.ycombinator.com/item?id=27707857 (ctrl+f for "pain in the" if it's not the first one). So it looks like convincing GMail to respect your small server is a non-technical problem, which might be an issue for a person with "only engineering" mindset. On the other side, Dr.Hipp looks like a right person to try to convince GMail to respect smaller mailservers!

MrBuddyCasino 5y ago

> Here’s warning to all your listeners whenever a politician comes to you and says, “Oh, we’re going to take care of this problem for you.” What they’re really saying is, we’re going to take away some of your freedoms. The exercise for you, here, is to figure out what freedoms they’re going to take away in order to solve this problem, and they’re often well-disguised, but yeah, if you want to be free, that means doing things yourself. Easily the most important take away in this excellent interview, but unfortunately, also the most contentious.

j-pb 5y ago
So what you are saying is we should hold big petro, chemical, and car companies accountable ourselves when they threaten billions of lives through ecosystem collapse level polution and climate catastrophe? Great! Pick up a gun then! Or maybe, the whole, "legislation is curbing everyones freedom!" thing is just a lie, fed to you by the most wealthy and powerfull to keep you from voting in a way that protects your freedom, and curbs theirs.

EMM_386 5y ago

> 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. This is impressive for Postgres. SQLite is incredible. I've used it over the years in a variety of important projects.

ridruejo 5y ago

I had the opportunity to interact with Dr. Richard Hipp earlier in my career. He’s a wonderful human being.

jokoon 5y ago

So what are the advantages of using DB like mysql, postgre?

tyrex2017 5y ago
access management, and scalability: can better do simultaneous writes, and use several copies horizontally BUT: you probably need 1k+ or 10k+ requests per second to start getting advantages
hanche 5y ago
Take a look at the link below, scroll down to the headline “Situations Where A Client/Server RDBMS May Work Better”. https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html
xet7 5y ago
At "Ask HN: Do you self-host your database?" https://news.ycombinator.com/item?id=27671376 https://news.ycombinator.com/item?id=27671376 is this comment: https://news.ycombinator.com/item?id=27673359 https://news.ycombinator.com/item?id=27673359 "We're currently transitioning from a multi-tenant 2TB postgres DB hosted on AWS RDS to using sqlite instead, a separate database for each client. We're doing this for multiple reasons: a) As our DB grew the service became very expensive, one of the biggest items in our AWS invoice; b) Keeping the PG servers up to date is a pain, we simply don't have time for this; c) We wanted to be able to migrate to other clouds and even be able to offer a self-hosted version of our platform."
xet7 5y ago
SQLite was created, because client-server database Informix (similar to MySQL and PosgreSQL) did crash and and was unavailable to connect to. There are some edge cases where MySQL/PostgreSQL can start vacuum, be corrupted, etc making client/server database unavailable. SQLite is tested with billions of test cases. In interview: "The idea for SQLite actually came out of his frustrations with an existing database called Informix that was installed on a literal battleship." "Why do we even need a server? Why can’t I pull this directly off the disk drive? That way if the computer is healthy enough, it can run our application at all, we don’t have dependencies that can fail and cause us to fail, and I looked around and there were no SQL database engines that would do that, and one of the guys I was working with says, “Richard, why don’t you just write one?” “Okay, I’ll give it a try.” "

cassepipe 5y ago

Also to note : The fossil project contains a makeheaders.c file that can be compiled as an executable. What it does is that it scans all the .c files and the .h files you give it and generates the necessary headers for each file. I am surprised it's the only simple solution I could find for this purpose. I'm grateful not to have to update .h files any more. Check out https://www.hwaci.com/sw/mkhdr/ https://www.hwaci.com/sw/mkhdr/

therealcamino 5y ago
Sounds like the old makedepend program.
- cassepipe 5y ago
  Never heard of it. The only other thing I found was something called lzz but it seemed more complicated and C++ oriented. Looking up makedepend taught me that it had been superseded by Gnu Automake... Which seems to be a big machinery and another rabbit hole I am not sure I want to go into : I've already had too much fights with makefiles already. I really wonder how most C programmers do.

polyrand 5y ago

For those who liked the podcast, I would also recommend this one: https://softwareengineeringdaily.com/2015/11/13/sqlite-with-d-richard-hipp/ https://softwareengineeringdaily.com/2015/11/13/sqlite-with-... Dr. Richard Hipp is truly an inspiration.

mrlonglong 5y ago

Time I got myself a copy of this Art of Computer Programming book. See if it can make me a better coder.

agbell 5y ago
It's interesting that when I interviewed him, he has all these built-it book cases behind him in his office. The idea of not needing the internet but just occasionally grabbing a book from behind you to look something up when developing software is very appealing to me. When I interviewed Brian Kernighan one of his complaints was how so much of modern programming is just looking things up rather than building things and I think that Richard has escaped that fate by building things himself.
- geocrasher 5y ago
  I really enjoyed those parts of each interview. They are very well done.
- mrlonglong 5y ago
  Before the internet, this is what we did. Read documentation and books. Now it's just a matter of applying Google-fu. I worry that this could be stunting our problem solving skills.

bob1029 5y ago

SQLite is the one technology that saved our software stack. Back in 2016 we were struggling with management of separately-hosted SQL Server instances (network, firewall, user accounts, license, explaining shit to customers, etc.) Installing our software in a new customer environment would take days because of all of the extra mechanics involved. There were 8+ other windows services as well. Fast forward to 2019 - We now use SQLite embedded inside the application instance. The entire system has been collapsed into a single binary distribution & service. Deploying our software to a completely blank windows server involves emailing the customer a ~120 megabyte zip file, asking them to extract it to C:\myapp and run sc.exe install. This shift in how we manage persistence of business data allowed for us to salvage an otherwise completely lost ship. Our team is still incredibly small (<10 employees), and we are responsible for managing hundreds of customer environments. If we didn't have a reliable, self-contained persistence mechanism that "just works", we wouldn't have time to do anything else of value.

zepearl 5y ago
> ...involves emailing the customer a ~120 megabyte zip file... Hehe, nice test for their email servers & antivirus (which probably wants to unpack it to see if any nasty payload is included) :)
- bob1029 5y ago
  Good catch. To clarify - we email a link to the distribution (somewhere in an AWS S3 bucket), not attach the actual zip file. This would clearly be a nightmare for 99% of email providers.
  - zepearl 5y ago
    Aha, that sounds a lot better, thx - indeed I was wondering if nowadays common max email size increased a lot since I set up my private email server ~10 years ago... :)
    - teddyh 5y ago

GuB-42 5y ago

I remember in the early 2000s, I just got started with SQL and one of my reaction was "SQL is great, but why do we need a server? Why not use a simple library and files?" So I asked my coworkers if such a thing existed. And I had a lot of reactions along the lines of "it is stupid why do you want that? Just install a server on localhost" few people were convinced. It wasn't even a case of "nice idea, someone else should do it it", I think people just associated SQL with servers and by being new to it, I didn't have that preconceived idea. A couple of years later sqlite was everywhere. It was exactly what I had in mind (but with nowhere enough skills to do it myself).

uDontKnowMe 5y ago

Thanks Adam, I love the podcast!

wirthjason 5y ago

Any good recommendations for books on SQLite? I’ve never spent much time with SQLite directly. My main interaction has been through Django and the ORM layer. My day job uses a custom object database. While I’m familiar with SQL and relational DB concepts I haven’t worked heavily with them.

jlelse 5y ago
The SQLite website itself is pretty well written.
webmobdev 5y ago
If you know SQL, all you got to do is understand the specific quirks of the database you use (e.g. In SQLite, specifying a data type for a column acts like a "hint" rather than a real constraint). The SQLite website has a pretty thorough manual that should more than suffice for this.

twotwotwo 5y ago

> The 100% MCD [modified decision/condition coverage] 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. That'd be an awesome contribution to open source, particularly at a time that there's a lot of collective awareness of how hard it is to secure C programs. (There's probably still a business reason to keep it proprietary: it's a reason for people to go to them for changes, at least if they want the final product to be thoroughly tested. Alas, 'cause it would be cool to see.) It's funny that he references preppers, survivalists, and dependency on society near the end, when talking about writing all of your own software dependencies. A while back someone thinking about it from the other end (i.e. saying we generally should accept some dependencies) said almost exactly the same thing in thread at https://twitter.com/hdevalence/status/1214234305754329088 https://twitter.com/hdevalence/status/1214234305754329088 . I draw no deep insight from this, but fun to see the same analogy from two very different perspectives. (Kind of closing the circle, the "let's be real about dependencies" article linked, https://wiki.alopex.li/LetsBeRealAboutDependencies https://wiki.alopex.li/LetsBeRealAboutDependencies , mentions in passing "SQLite is basically everywhere (for good reason)".)

krakov 5y ago

> You couldn’t just Google how to build a database > Fortunately, I didn’t know any experts and so I did it anyway He obviously lives by a certain ethos of self-reliance so not sure he would have talked to experts if could. But makes me wonder: given it so easy now to find the experts compared to 2000, to google what they think - it is easier to get discouraged?

johannes1234321 5y ago
The question is: If he had asked experts early on, would he have ended up less self reliant? And would we have gotten an SQLite as good as it is now or would he have been told "oh that's too complex" and be discouraged by experts? :)

drummer 5y ago

> Richard:It involves doing without. Here’s warning to all your listeners whenever a politician comes to you and says, “Oh, we’re going to take care of this problem for you.” What they’re really saying is, we’re going to take away some of your freedoms. The exercise for you, here, is to figure out what freedoms they’re going to take away in order to solve this problem, and they’re often well-disguised, but yeah, if you want to be free, that means doing things yourself.

pauljurczak 5y ago

> DDG-79 Oscar Austin. That is a battleship No, it is a destroyer, see https://www.surflant.usff.navy.mil/ddg79/ https://www.surflant.usff.navy.mil/ddg79/. Other than Hollywood, no one had built a battleship since around the end of WWII.

cstork12 5y ago

Maybe a bit off-topic, but I noticed that https://www.sqlite.org/cpu.html https://www.sqlite.org/cpu.html hasn't been updated in over two years and given the consistently high development speed I'd be curious how the "CPU Usage" deals with all the new features. :-)