High Performance SQLite is an excellent and immersive course, featuring real-time typed commands and valuable edge case explanations. I highly recommend it for enhancing your SQLite skills.Han-Hsun Liu
Build with and integrate SQLite into your production applications.
I've made my test data available for you to use and follow along.
Let's start where every good story begins which is at the beginning. What is SQLite? In fact, what do we even call this thing? As you might have guessed, I call it SQLite. I also call it MySQL.
Those are common but maybe wrong ways to pronounce it. I think technically it's supposed to be called S-Q-Lite but boy, that's a mouthful. I think you could get away with SQ-lite, making the l go with the lite instead of the s and q. Everybody is gonna know what you're talking about. I prefer to say SQLite.
I prefer to say MySQL. It keeps us moving and we gotta keep moving. So call it whatever you want. I'm gonna call it SQLite. Now, what is it?
What is it? It's a database a lot like MySQL or Postgres or something that you may be familiar with. There are some pretty big differences though. With something like MySQL or Postgres, you have a database server and you have a database client and you're talking back and forth over some communication protocol. Right?
That's a client server model. With SQLite, no such thing. That's just that is just not how it works. It doesn't use a client server. It's a SQLite is actually a C library that talks directly to the file, the database file on on the disk.
So you don't have any server to make sure that it's running, nothing to monitor, nothing to recover from, nothing to instrument. There's a little bit of configuration we can do with SQLite and that's just changing how the library works. But the the configuration is way way way less with SQLite than something like Postgres or MySQL. The database is a single file. So with something with with one of these other bigger databases, you've got files everywhere.
And honestly, you're not supposed to go in and really muck about with them. With SQLite, it's a single file. There are a few instances where you could end up with maybe 1 or 2 other files and we're gonna look at what those instances are. But at its core, everything is a single file and the C library looks at that single file, opens it, closes it, writes it, does all the stuff with a single file. That means it's extremely portable.
The file format is very, very well defined. It's been backwards compatible for like 20 years and they have a commitment to maintain it through 2050. So we still got a little bit more time there. It is so backwards compatible in fact and they take the the library authors take such great care with it that it it is the official archival format of the library of congress here in the United States. So one of the things that we're gonna look at is, why certain things were written the way that they were and how to modify them for what is now a kind of a new era.
This was written in the the year 2000 and there are some defaults that need to be changed but they're still there to preserve backwards compatibility. This is a good thing. When it comes to databases, we love stability. We really do. And so there are a few things we'll change for performance, but thank goodness we have stability and the commitment to stability going forward.
The SQLite docs would tell you, in fact, they do tell you, that SQLite does not compete with MySQL and Postgres, which seems interesting. So what does it compete with? According to the docs, it competes with f open, opening a file. They think their main competitor is opening a file. I think historically, that has definitely been true.
I think in the modern day and age of SQLite, I just don't think that's true anymore. I think you can use SQLite in the places you would use postgres or my sequel. Now, what I am not going to tell you what I'm not going to tell you is that SQLite is perfect for every single use case. It's not. It's simply not.
It's a file on a disk. There are lots of ways around that particular limitation, but it still has limits. And anyone that tells you that any technology is right for every single use case, they're trying to sell you something. So I'm not trying to sell you anything. I'm trying to, teach you how to use SQLite, show you when it's good, show you where it may fall down and transfer some of that knowledge slash enthusiasm to you.
The last thing I want to talk about in this, introduction to SQLite is a word called the amalgamation. Very cool word. If you look in the docs, you'll see the word the amalgamation a lot. And what what they refer to when they're talking about the amalgamation is just the set of extensions that, sequelite is by default built with. So if you see something that's like well full text search is part of the amalgamation, that just means it's like built in by default.
We can add and we will add lots of extensions into SQLite but I just wanna flag that word for you in case you ever see it and you're like what is that amazing looking word? It just means that is the big chunk of stuff that they ship even if there is other stuff that they could put in there.