libSQL & sqld

[00:00:00]
>> Brian Holt: We did replication, we did backups. This was all straight up Vanilla SQLite, no modifications, stock running as is. There's a lovely group of people that decided that they wanted to build a lot of these features that SQLite lacked into SQLite. The problem with SQLite and for that scenario, is that it's closed contribution.

[00:00:26]
You cannot open a pull request to SQLite, and particularly if you haven't gotten approved by their core team. So, what they decided to do is they created something called libSQL. Looks like that. And it's made It's made by this company called Turso. It's a straight up fork. It started with their base code of SQLite and they started adding a bunch of features that in their view, and I would argue many's view was lacking from SQLite.

[00:00:56]
They turned it into just a file based system to it, it is now a real server that you run, just like Postgres. It has a user management system. It's works totally over HTTP. So if you just want to like do it all via API and like all the reads and writes, so that's totally possible.

[00:01:19]
Luckily they do maintain their own libSQL-js, so that you don't have to do that, you can just use their client. You cannot use SQLite three, the library that we've been using directly, but they do maintain their own version of it, so that you can actually just drop it in as a replacement.

[00:01:38]
I'll show you that here in just a second. And then at that point, once they kind of had all the base features to make SQLite cloud hostable, they went ahead and started adding features that they view were lacking. One that we already talked about today, during this course rather, is that ALTER TABLE only allowed you to add one thing at a time.

[00:02:01]
Well, with libSQL, you can do multiple alter tables, right? So some of those kind of historic things, I think you don't have to do the plagma foreign keys on every single time. I haven't checked that, but I if they haven't, I'm gonna go march down there and yell at them to do it right now.

[00:02:16]
It's the first thing I would change. A bunch of things like that. I think you can go down and they have somewhere in here, like what are the differences and what have they added?
>> Brian Holt: Yeah, I linked the docs in there already.
>> Brian Holt: So hopefully it does all that stuff.

[00:02:39]
Cool, okay, so that is what libSQL is for. And we're gonna give her a shot here. So the first thing you'd have to install for libSQL is a thing called sqld. The D stands for daemon, if that is, I mean, it is just like Postgres D or Mongo D, sqld is for the base SQL.

[00:03:07]
So let's go ahead and get that running. They bless them. They try to make it as straightforward as running SQLite. They recognize that the reason why SQLite is as popular as it is because it's so simple to use and get started with. So I left a link here, go install sqld first.

[00:03:30]
I used Homebrew you can use Docker if you're okay with that. Again you might be in the dark here if you're doing this on Windows? That's a good question. So you'd have to go the route of running it either from WSL2 or doing it from Docker, both which are totally acceptable ways of doing this as well.

[00:03:51]
Luckily with Mac, you can just run it directly from Mac. So I think it's just brew install sqld, if you're gonna do it that way, and I have it installed. And yeah, so if you just do sqld, no options, no configuration, it'll just figure it out and it'll get you started.

[00:04:13]
Which I love that it's like, no config, let's just get this running and get going. You can see here that we are running on port 8080 which is cool. I can stop all this stuff, by the way. Get that started again. There we go. I think I was colliding with other things, so make sure everything else is shut down.

[00:04:39]
Okay, you'll notice that if you try and go to local host, 8080 I don't think it does anything. I don't think there's any web front end to this. Yeah, it's just gonna blink cuz it's not expecting an actual GET request from a browser. So you're gonna have to use some sort of API request library.

[00:04:54]
For me, I use Bruno. Bruno is just like Postman. It's like Insomnia, there's one built into VS code. I'm fine with you choosing any one of them. I am a fan of Bruno. This one, use bruno.com. And it's just a really nice piece of software, it's kind of like how Postman used to be and then how Insomnia was and both of those got, Postman turned into a big company.

[00:05:24]
Insomnia got bought by Kong, and they kind of just started adding stuff that was conducive to their businesses into those two things. And I found Bruno to be more stripped down and more just an API request client, so I like it. Okay, so there's Bruno. And then what I want you to do here, I think if you just click here, it'll.

[00:05:48]
Yeah, it's just a JSON file. Just hit command or save this file somewhere where you can access it from Bruno. I went ahead and wrote all of the various different print queries for you because they were annoying to write and I didn't want you to have to do it, and it's not the point of this course.

[00:06:10]
But once you open this file open collection it'll just work. This is a postman compatible collection. So this will work in Postman. This will work in Insomnia. This will work in Bruno just fine.
>> Brian Holt: And if you just want the JSON that I used to query it, I created that here as well.

[00:06:37]
>> Brian Holt: So you can literally copy and paste all of this and put that directly into a new request here and that would work as well. Okay, assuming that you did it that way, we're gonna find the Chinook query here, where I went and did this by hand, and it was annoying.

[00:06:56]
Actually, I probably could have asked ChatGPT to do it, but I didn't. You'll see here in the body, it's just a ton of statements. So this is actually the way that you communicate directly with sqld. It's just a JSON object, and I just gave it a bunch of statements.

[00:07:12]
And this is literally the statements from the Chinook database, right? Chinook, yeah. And if you go in here and you look, not that, you go to releases, and if you look at this SQLite, this one here, click on that, and I open that, you'll see I literally took this file and I translated it into a sqld compatible JSON object.

[00:07:45]
So, now if I go back to Bruno.
>> Brian Holt: Cool, and I just click run here, which I think is this right arrow, you'll see here that it created all my tables, and down here you'll see, like some of these, it wrote 3,000 rows here, it wrote 720 here, it took out 3 Bitcoin here, it's all very standard things.

[00:08:13]
Cool, so now we can go into basic query here. You can see my body here, cuz these are all posts, right? And you just give it an array of statements that you want it to run. I do this and I click post here, and I got the column largest number and the row is ten.

[00:08:36]
Pretty cool, right? If I wanna do get tracks, I have another one here where it's doing a join and a bunch of stuff like that, and I press play here you'll see that I get all of my tracks, sessions, artists, all those kind of things. Make sense?
>> Student 1: So like semantically why is it a post request to get data, just cuz you wouldn't want it in the query string.

[00:09:08]
>> Brian Holt: I mean, I suppose it could be pedantic about the rest nature of it. And turn it all gets, all selects, must be gets ,all post must be inserts, right? I think they just tried to keep it simple. So is the entire of the API is just post on slash?

[00:09:22]
Yeah.
>> Student 1: Okay.
>> Brian Holt: Yep, generally, you won't be interacting with with HTTP anyway. You'll be using some library to interact with it. I think you can create multiple databases in here, so you could have, slash data.db, and slash user.db and they'll be connecting to different data. So the URL schema can be important, but, yeah, this will all just be statements.

[00:09:50]
There is a way for you to do the parameterized queries where you say, equals question mark, right? And then you can have it go replace it. I forgot the syntax for it though, but you can look it in the docs, and there's a way to do that as well.

[00:10:09]
>> Brian Holt: Cool, other questions?
>> Brian Holt: Cool, so we have this working now we have our database loaded into it, which is great. When you stop this by the way, your sdld, I'm pretty sure we did not write it out to a file, so it's just going to drop everything.

[00:10:30]
So just be aware of that. Okay, so now we wanna get our app working with it.
>> Brian Holt: Cool, so let's go back to my notes here.
>> Brian Holt: And we are going to go clone this repo, and you're gonna go to your terminal. I already have it cloned, but notice here, this is a SQLite app, dash live SQL, so make sure you clone that one.

[00:11:09]
I'm gonna go into SQLite app, dash lite FS, okay? I have this one, I'm gonna to do an MPI. Install, no I did the wrong one, sorry. Sqlite-app-
>> Student 2: libsql.
>> Brian Holt: So sqlite-app-libsql, where in that one, you're gonna run an npm install. And I'm gonna open this in code.

[00:11:41]
And make sure if you're doing this that you have the two lines here about local first commented out. They should already be. We'll come back to that in the next lesson here. And then here you'll see that we're using libsql/sqlite3. This is an sqlite3 compatible SDK, which is cool.

[00:12:05]
We're just gonna drop this into our code base, update the URL and then we are good to fly here. This is nice for existing SQLite databases but I would not suggest starting with this. If you are starting a new project and you're gonna use SQL or Terraso or anything like that, just go ahead and start with their official SDK because that's one that they're updating all the time.

[00:12:29]
This is only good if you are replacing something like we are, okay? So I'm going to say npm run dev. The one thing I will caution you on is I had to comment out, all right? This is not refreshing every time you change the node code. So if you do change node code here just be aware that you have to stop the server, start the server to get the new refresh changes.

[00:12:54]
The reason why I did that is because libsql writes a lot of files locally and it was constantly restarting my server.
>> Brian Holt: Okay, cool. So now our app is listening on port 3000 I had to change it because that's what sqld runs on, and you can see no invites found.

[00:13:17]
>> Student 1: What is our problem here?
>> Brian Holt: So this is all working, it's making a request here.
>> Brian Holt: We can look at our network debugger here, let's say getinvoice, getting a 500 response. You're getting a closed error. Stream expired. Interesting, so I might just restart this server again.
>> Brian Holt: Yeah, okay, it closed the connection for whatever reason.

[00:13:57]
Who knows why, but if you see something like that, just stop your node server, restart the node server.
>> Student 3: Kyle in the chat mentioned that you could add the dash dash watch flag or node dash dash watch server dot js.
>> Brian Holt: I constantly forgot that that's there, but that's a good point.

[00:14:15]
That node will actually do that watching now, I'm old habits die hard that I was like, how do I do that? Mpm installed No Damon, right? So as you can see Lib SQL does require a bit more knowhow a little bit more. Yeah, it's hard to run by yourself.

[00:14:30]
I'll just put it that way right? And I'm gonna say it's not really intended for you to run by yourself. It's cool to develop locally with. If I was building app on Terraso, i'd probably would still just develop with SQLite. Plus you know that's not true, cuz I'd want to use their SDK, so but in any case, if I was really interested in using libSQL, I would just use Terraso, because that's what they do.

[00:14:53]
That's what they're built for. It's a really small startup, and so their documentation is focused on getting their paid product working and not necessarily getting the open source projects adopted outside of their walls. I wanted to demonstrate to you though, there's forward progress here. There's more interesting revolutions coming with SQLite, libSQL, so pretty cool stuff.

[00:15:19]
It's kinda an alternative to running LiteFS on your own. And you can self host libSQL, they've said that that's part of their ethos going forward, they wanna make that available for people. So, solid choice if that's what you wanna do