Hopefully, at this point we're starting to get our heads a little bit around SQLite and its relation to MySQL and Postgres and some of its unique, call them features, unique quirks maybe of of SQLite. I'm not gonna try to convince you to use SQLite for everything. I think that's a fool's errand. I think that would be disingenuous. That would be a lie.

I am going to try to convince you SQLite is better in some use cases than one of these other databases or potentially equal but easier to run which I still think counts as better. Anyway, let's look at the first use case. I think the first use case for SQLite shines is embedded applications. So getting, MySQL or Postgres running on an Arduino or a Raspberry Pi. Raspberry Pi would be easier but getting it running on an Arduino or your phone or somewhere out in the field it's gonna be pretty difficult.

With SQLite, it's just there always. There's no overhead. There's no there's no fear that it could go down which is pretty important when you're dealing with something out on the edge, out in in the actual field or on somebody's phone or desktop or something like that. You need a database that you're a 100% sure is not going to crash and because there's no server this is not going to crash. Going the complete opposite direction, embedded devices, websites, web apps, developers, myself included.

I'm not watching this. I'm recording this, but I'm also a web developer and so that's kind of the place that I live. SQLite does amazing there. It is constrained for the most part. We'll talk about the nuance later.

It is constrained to one machine. Those machines can get super big and you can serve hundreds of thousands of requests a day, many thousands of requests per second just using SQLite. There are some settings that that I'll show you to tune but I think most people's web applications easily fit within the bounds of what SQLite can handle. The SQLite website itself I think gets half a 1000000 hits a day, maybe 20% hit the database and they just use SQLite. And they haven't optimized it at all.

Sometimes they run 200 queries per request but it simply doesn't matter because there's no network overhead. The database the database is right there. There's not even a a 1 or a 2 millisecond round trip between the database and back to the application. It's just it's just there. And so having the database be fully available with no network round trip overhead just opens up this this crazy set of use cases including the the n plus one problem is just not a problem anymore.

A lot of performance concerns go away because you're not calling out to the database and waiting for it to come back over the network. The speed of light still exists but with SQLite it's it's just kind of irrelevant because it's right there next to you. Another good use case for this is, shipping data around. Unbelievably cross platform compatible file format that you can just ship around. My personal website, aaronfrancis.com, very good website.

My personal website, all of the content is written to a SQLite database in a GitHub action and then it's shipped off to Lambda. Even though Lambda has an ephemeral file system, I don't care because it's a read only set of data that my application pulls from to display content. That's a good way to package up data and and ship it around. Whether that's static or updated once a night or whatever, it's a great way to ship data around. The other thing that's kinda similar to that, maybe a little bit different, is using it as an application file format.

Instead of inventing your own custom application file format, you could just use a SQLite database. This is what a lot of applications actually do. There's no stipulation that your file has to be named database .SQLite. Right? It could be named, anything .Figma or .Screenflow or .whatever and that can still be a SQLite database file.

A lot of desktop applications actually use SQLite as the file format because it is so robust, it's so well tested and it's gonna be better than anything that you just make up on the fly that really doesn't have a spec. That's another place that's another place that you can use it. Also, it's better than, it's better than having 1,000 and thousands and thousands of individual little files because putting all of those files in a SQLite database and reading out of there can be up to 35% faster than going straight to the disk. Let me say that again. Reading from a SQLite database can be 35 percent faster than reading similar files from a disk.

Now, there's a chart that I will show you at some point, that shows kind of the trade offs of that. You know, there's a certain like how big are the files, how many files are there. But the interesting thing is it can be better to package up a bunch of small files and put them in a SQLite database because then you only open that file once. You can read out of it a whole bunch of times before closing that file back again. Another reason that SQLite really shines is because of its low operational overhead.

Right? You don't have to spin up something and keep it running. Let's say you've got a database and a queue and a cache, all 3 of those could be SQLite. They could be different databases. You could have a database.SQLite, a queue.SQLite, and a cache.SQLite.

There's a project in the works that is compatible with Redis, but it's backed by SQLite which is which is pretty cool. Bringing down that operational, This is just, this is just some of them. As we go through we'll continue to talk about it but those are some places it shines. Let's talk about some of the limitations of SQLite.