What is SQLite

[00:00:00]
>> Brian Holt: Just a tiny, brief history here. SQLite was created for the Navy by a contractor named Dr D Richard Hipp in about 2000, he was writing something for a destroyer. I don't remember exactly what it was, it was some piece of the destroyer. He evaluated a bunch of technologies, but he ended up just kind of designing his own to be very small, very limited, and very single purpose, to like, I need a library to be able to write SQLite queries to.

[00:00:34]
And we'll get more into that here in just a second, that is open source. I mean, this was today, so you can see very active development. These are all things that have been checked in today, which is pretty cool. It's also on GitHub, it's just mirrored there. But you can see here 50 minutes ago, they changed the README, which is pretty cool.

[00:01:04]
>> Brian Holt: And its open source license is public domain, which is not something you see very frequently. But it's been around long enough as open source that they released it under the public domain, which is interesting as well. The one thing about SQLite is that it is not open contribution.

[00:01:19]
Whereas normally you can hop on GitHub and you find a problem, you can just submit a fix, they do not accept any outside contribution. There's just a core small set of developers of which Dr. Hipp is one of them. He keeps a really tight control over what is SQLite, what's the meaning for SQLite.

[00:01:39]
He is what they call a BDFL, the benevolent dictator for life, which is what Guido Van Rossum was for Python, and there's been a couple of them. But anyway, obviously he's been a very good steward of SQLite.
>> Brian Holt: Just an interesting fact that I found is he didn't wanna use Git, so he invented his own source control management software called Fossil.

[00:02:08]
It was made for the single purpose of supporting SQLite, and basically the two biggest projects on Fossil are SQLite and Fossil itself. So in other words, not many other people use it. I left a link there if you wanna look at how they differ. I've never even tried to use it myself.

[00:02:28]
But as you might guess, Dr. Hipp is the maintainer of Fossil as well. So anyway, in practice, you don't really need to care. None of this is actually super important to you, I just found all the history here fairly interesting. In theory, SQLite is the most deployed library ever, most deployed software ever, in theory.

[00:02:53]
They can't really prove it, because how do you prove that? But they've estimated that there's a trillion SQLite databases that exist out there, which is pretty wild to say out loud, right? So I left another link in there, they have a more thorough explanation of how they come to that number.

[00:03:10]
But here's just some basic things that are using SQLite. Every iPhone, every Mac, every Android, every Windows 10 computer, all of your browsers, every Skype, iTunes, Dropbox, TurboTax. PHP and Python use it on the internals of the library or of the runtimes. TV sets, set-top boxes, your car very likely runs SQLite.

[00:03:36]
Your fridge might run it if it has software on it, like gets on everything. That's how they get to a trillion, right? Because your iPhone probably has, I'm gonna guess, 100 instances of SQLite running across all of the apps, because it's very common to put it inside of apps as well.

[00:03:53]
So wild of how frequently this library is deployed. In other words, it's probably the most battle-tested piece of software of all time. It runs everywhere, it requires so little resources that it will run on any device. It runs on low power, it runs on high power, it will just run on any architecture.

[00:04:11]
It's one of the first piece of software that they tend to convert when they're creating new operating systems. Cool, so what is SQLite not? It's not a server, and that's probably gonna be one of the most confusing things. If you've never approached SQLite before and you've used a database before, it's not a server.

[00:04:35]
So when you wanna use Mongo or Postgres or MySQL or something like that, you have to start a server, right? You'd say MongoD or Postgres or something like that, and then you just see the output of the server. And then all of a sudden, you can start connecting to your server.

[00:04:52]
That is not what SQLite is, and it's not what it does. Instead, you can think of SQLite as basically a way to read and write to a file. So it's not running a server, it's a library that just allows you to basically spit out input into a file and then read output back out.

[00:05:10]
It's a way more closer to accurate way of thinking what SQLite is gonna do for you. So, in other words, SQLite has no network access. It doesn't have users, it doesn't have passwords and management, and all that kind of things. It doesn't handle any of those kind of things.

[00:05:33]
It's designed to work on the same computer as the app that is using it, right?
>> Student: You don't have an SQLite container that you then connect into, as opposed to like Postgres, where you do, right? You have a node app that then starts reading and writing to a file via the SQLite library.

[00:05:51]
We'll do this over and over and over again, but for me, I found this very confusing when I was getting started with SQLite.
>> Brian Holt: It's a single node, so there's no replication, there's no primaries and secondaries. It's zero configuration to start it, you just run it and it just works.

[00:06:17]
Yeah.
>> Student: If the data is locally, how would you encrypt it?
>> Brian Holt: It's a good question, not something that we would necessarily cover in this course. But there is an extension to SQLite that you can encrypt your files as well. And then you can give it a key and it'll read and write based on the key.

[00:06:40]
>> Brian Holt: Not something that I've actually done myself, so I'm not gonna say a lot more than that, but I know there's a crypto extension for it. In other words, it's not built into escalate itself, I'm pretty sure.
>> Brian Holt: Cool, so single node, there's no replication, there's no consensus, it's designed to be a node of one.

[00:07:01]
You can do backups, and we will actually do backups at the end of this, but there's no concept of primary leader or secondary follower or anything like that. So it's on one hand like a paradigm shift for I imagine how some of you think about databases, or maybe for some of you, this is all you know and you don't know how to do the other style of database.

[00:07:22]
But yeah, it's a library, it's not a server. SQLite is unbelievably fast, it's made to work in performance-sensitive environments. They take great care to make sure that it reads and writes at incredible speeds. That is one of the biggest benefits of SQLite, is it's just very, very, very fast.

[00:07:45]
Particularly cuz you're not hitting the network at all, right? It's basically as fast as your disk can read and write to it. And sometimes they use tricks to make it even faster than it probably could be, right, which is pretty cool.
>> Brian Holt: So can I use SQLite in production?

[00:08:01]
And the answer to that is yes and no, right? Or the most common cop out answer that I just love to give, which it depends. So it depends on what you're storing, it depends on your sensitivity to downtime, it depends on how much you kind of wanna roll your own kind of encryption or user access.

[00:08:22]
Or a bunch of things like that where you can use in production and now they're actually tools that will help you. We'll look at some of them. But if you wanna just use vanilla SQLite and you're trying to run Facebook entirely off of one SQLite node with no help, no, you can't do that.

[00:08:39]
It's just not gonna work the way that you want it to, it's not. You're gonna be so bound to your app servers staying up, right? That is just not gonna work very well, so it depends on what you need. The other thing that we'll find out is that it's intentionally limited.

[00:08:56]
They intentionally keep a very small subsection of features from all of SQL to keep it small, fast, performant, simple. So there's a bunch of features that Postgres and MySQL and Snowflake and Mongo and all your other favorite database pieces of software implement that. SQLite intentionally does not, and you have to be willing to make those trade-offs.

[00:09:29]
I do have another course on here, the complete intro to databases, where we go over several of those databases and how they work. I don't talk about SQLite in particular, but I do talk about Postgres versus Mongo versus Neo4j versus Redis. I think that's it, I think that's all I cover in there, which is cool because that kind of examines all of the various different perspectives on how to write data apps.

[00:09:54]
Okay, so there are several paid services that do SQLite in the cloud and they're all lovely. I've used all of them personally as part of my day job. I'll leave it to you to go figure out which one you like, but they all kind of take it from Spin on those ideas.

[00:10:15]
>> Brian Holt: Cool, any questions so far? Yeah.
>> Student: So is it feasible to start with SQLite and then migrate to Postgres later, or is it an interoperability concern syntax wise?
>> Brian Holt: It's a really good question. I would say most people use it that way, so extremely viable. For example, if you go to the Rails tutorials and try and teach yourself Rails just from their website.

[00:10:41]
They have you start on SQLite, get really far into it, and then migrate over to Postgres, I think. It might be MySQL, it's one of the two. Super viable, lots of people do it, there's tons of tools that help you do that. There's one that's called pgloader where you can take a SQLite database and say, go ahead and dump this directly into Postgres, and it just works, which is pretty cool.

[00:11:06]
It'll work with CSVs and a bunch of stuff like that. I'll tell you that for a decade, that's the way I've used SQLite. I will say now that these tools that exist now can scale that out, right? You can actually just continue on SQLite as well, and that's just a choice that you have.