SQLite Extensions

[00:00:00]
>> Brian Holt: Let's talk about JSON. Okay, so we're here on JSON. We're gonna go into the SQLite extensions here. So as I mentioned probably a bit earlier, SQLite intentionally maintains a really tight, small core features and they intentionally push everything out. That's not just a database function into extensions.

[00:00:25]
They made it very easy to add extensions, and Dr. Hip himself, as I'm gonna guess, has written dozens of extensions, many much extensions is the technical term for it. And then there's a bunch of other ones that have been written by other really smart people. And there is a cool, unofficial package manager for SQLite called sqlpkg.

[00:00:52]
And you can see here that, there's been one added since I wrote this l last week. So there's now 103 extensions in their unofficial extension store. You can see all these written by Richard Hipp. That's the creator of SQLite. Alex Garcia is another one that's a pretty proficient person, as is this Anton as well.

[00:01:16]
So there's a bunch of them in here. I'm gonna show you how to use this quickly so that you can use it in the future if you want to. So first thing here is we're gonna use one called hello, which I think is written by Alex Garcia. It's just like the Hello World of SQLite extensions.

[00:01:40]
We can really just say, hello. It's really meant for testing, so that you can test that extensions are loading correctly and that kinda stuff. It's also kinda helpful if you're interested in how they work. I am not a C developer, so I'm not going to try and pretend I know how to do this.

[00:01:57]
But yeah, we're gonna load the hello extension. Okay, so first thing we're gonna do is we're gonna go to the, I think this is the installation instructions. Both of these should work. If you're comfortable with like the pipe into shell command, which you probably shouldn't be, but I still do it all the time.

[00:02:20]
You can also just do the download and install manually kinda thing. If we're being honest, I just did this one, and so far, that they've only stolen my credit card, and that's it.
>> Brian Holt: Okay, once you have that installed, let's put my terminal here, you should end up with a, sorry, mute that, you should end up with a SQL package right there.

[00:02:51]
And it's a package manager, cool. And from there, we're just gonna do this, right there, sqlpkg install this repo. And I think that should work. Mine, I have already installed it, so it's gonna say, cool. I did that already. But if you haven't, it should go and install it.

[00:03:15]
And then what you wanna do right after that is you wanna run this which command, which is going to give you the actual pathway to your package.
>> Brian Holt: Cool, interesting that it didn't give me, all right, hold on. I think this is giving me a relative path, which is actually not what I want.

[00:03:39]
So if I go to personal,
>> Brian Holt: sqlite-app. I'd run that again. Yeah, see, it's gonna give me a full path. You want a full path, and I think that's just cuz I was hanging out in my home directory. You want the whole path so that SQLite will load it correctly.

[00:04:00]
So I'm just gonna open my database again here. There we go. And I think it's just.load in that path, if I'm not mistaken. It didn't yell at me, so I'm assuming that's correct. And then I'm gonna do SELECT hello( 'Brian' ), and I should get Hello,, Brian. That's how you know that our extension loaded correctly.

[00:04:23]
So as you can see, pretty straightforward, very light in the sense that it's not that hard. You just point it to this dylib, which is what the file extension for SQLite extensions is. And then you just run a SELECT hello ( 'Brian' ), there you go. So yeah, this might seem like weird syntax.

[00:04:46]
This is just a function. You can also just do SELECT max (1,2,3,4). You can do this if you're just trying to test stuff out, right? I do it all the time. But it has to be phrased in the form of SELECT statement, otherwise, SQLite doesn't know what to do with it, which is why you see this SELECT afterwards.

[00:05:06]
What's really weird is I see a lot of these extensions that are adding additional capabilities, like time series and sync and that kinda stuff. You'll have to run a SELECT do__thing_to db(123, 5), or something like that. And it's a SELECT statement, but this function here will actually go and write to your database.

[00:05:32]
I really don't like that pattern, because you write a SELECT statement and it doesn't make any sense that they would do that. But anyway, you will see that it's becoming more and more common because it's the way you have to run a function within Postgres, within SQL, at any version of SQL.

[00:05:48]
Probably worth poking around, it's kinda fun. There's a bunch of really cool packages on here. Browse all extensions. Protobufs, That's kind of interesting. You can make HTTP request directly from SQLite, which is kinda cool. So you end up doing something like,
>> Brian Holt: Load http_get_body. There's a bunch of stuff in there.

[00:06:11]
And then you can do that, and you can do JSON extraction from SQL commands. I don't know why you wanna do that, but you could, right? I guess it could take your place of your cURL. That seems like a terrible idea, but you could. I don't know, I'm not your mom.

[00:06:27]
You can do whatever you want. Okay, so yeah, have peruse around there. This is the one I'm probably most excited about, vec. This is the one that's coming out from Alex Garcia that it's a vector search, right? So instead of having Pinecone or something like that, you can run this all with an SQLite, which is pretty cool.

[00:06:49]
>> Brian Holt: It's not out yet, but I should mention that, that it is still in alpha or beta or something like that.