About this talk
This talk, presented by independent software engineer Mikuel Franka, explores the capabilities of SQLite beyond basic CRUD operations. He introduces several extensions that enhance SQLite’s functionality, including vector search for semantic queries, HTTP requests for rapid prototyping with APIs, and database synchronization similar to version control systems like Git. The speaker explains how vector search can improve search results by using machine learning to assess semantic meaning, showcases the SQLite HTTP extension for querying APIs efficiently, and demonstrates database synchronization to maintain data consistency across multiple devices. Furthermore, Franka touches upon additional SQLite extensions that enable functionality for zip file management and spell checking, as well as a thriving ecosystem of open-source projects surrounding SQLite.
Full transcript
So for the last uh as for the last uh lecture for today we'll talk about a technology which probably everyone here has used databases. And imagine it or not, they're much more than just storing usernames and emails and just normal cur data. They can do a lot more a lot more than we can expect from them. And that's what mikuel Frankor will talk talk to us today
about. Uh let's give him a round of applause please. >> Awesome. One, two. Thank you very much everyone. Uh my name is Mikuel Franka. I'm a independent software engineer from Canada who just happened to be in Bulgaria while Openfest is happening. So really nice coincidence. I'm super happy to be here and I'm super happy to be talking about SQLite because today what we're going to talk about
is not just SQLite. We're going to talk about what it more it can do than just simple CRUD queries. So CRUD is create, read, update, delete. It's what's usually you you usually do with a database, but today we're going to see all of the other things that are enabled by SQLite's fairly uh actually pretty amazing extensibility. So, we're going to look at HTTP requests, vector search, synchronizing
databases, and we're also going to look at uh all of the little open source projects that form a nebula of communities and projects around Are you ready? >> Are you hungry? >> Oh, that's good. I was expecting a much more yes, a much more positive answer than that. Cool. Five o'clock is is like pretty much dinner time in Canada. Um, all right. So, uh, just so we're
off to a good start and we're all, uh, starting on the same page, SQLite is an embedded database, which means that, uh, it's an it's a library that you use in your application. So, it's not a separate server that you call to like MySQL. It's a library and that library is going to talk to either an in-memory database or a file that's most often on disk and
that file is going to be your whole database. Um, so SQLI basically a library that allows you to manipulate a file with SQL queries. Uh, it's public domain. It's not even licensed Apache or MIT or one of those licenses. It's literally public domain for everyone to fork, use, and enjoy. Um, and it is ubiquitous. Uh, it's natively it exposes a C API, but it's got bindings in
pretty much every language you can think of. Um, so if you use Python, you can use SQLite, if you use Haskell, you can use SQLite. Uh, but natively it's a C API. Awesome. And today we're going to talk about SQLite's extension points and how uh we can use them to our advantage to do cool things. Uh, SQLite allows you to extend it by registering custom collations, uh,
simple functions. So things like the UU ID function. SQLite doesn't have one by default. you just register it and it's super easy. You call a function called I think create function. You name it UU ID and then you pass in a lambda and then whenever SQLite sees a function called UU ID, it's going to it's going to call your lambda instead to accomplish uh to execute the
UU ID function. You can also register window function functions, aggregate functions and super interestingly virtual tables. And those are tables that are not going to be backed by data on disk like a regular table would be, but they're tables that will be backed by an application or an arbitrary library or an arbitrary piece of software, but yet SQLite will still treat them the same as it would
any other table. So that enables some really powerful stuff that we'll look at today. And then finally, you can register custom virtual file systems. And that's a way that you can swap out the storage layer uh in SQLite. Um internally they use it for testing. So the SQLite team uses it for testing. They use it for things like fault injection. Um but uh one use case we'll
look at later today is a very clever one and I'll keep the suspense going for for now until we get there. So the first extension I want to get to is vector search. Uh, vector search is very much adjacent to AI. I'm sure you've all heard of AI. Uh, do we need to go over AI? Okay, awesome. So, vector search, if you're not aware of what it
is, it's a fancy name for semantic search. And if you're not sure what semantic search is, it's another fancy name for searching by meaning or by intent. And an example of that is if I have a store online and I sell uh vacuum cleaners and one of my users enters into my search bar cleaning supplies. Well, chances are that if I do a substring match to search
through my inventory for cleaning supplies, I'm not going to find vacuum cleaners because, you know, presum presumably it's not in either the title or the description of the item. But there's a way that we can feed both the vacuum cleaners and the user search string to a machine learning model and say what is your opinion or what is a m machine's internal representation internal semantic representation of
this and we call this a vector. It's just a very large array of numbers. And then once we have all of the vectors we can do some fancy math that I don't understand completely but it works. And what that math is going to do is it's going to say is the phrase cleaning supplies moderately similar to anything that I have uh in my inventory. So it's going
to look at cleaning supplies. It's going to look at the vector for that. It's going to look at vacuum cleaners. It's going to look at the vector for that. It's going to say are they in their uh hyperdimensional space somewhat similar? And if so, then well, I'll have the opportunity to uh maybe show vacuum cleaners to a user who wants to buy cleaning supplies. Um there's two
extensions that are going to allow us to perform vector search using SQLite. The first one is SQLite Vec. It's by a guy named Alex Garcia who did a ton of interesting um SQLite extensions. It was sponsored by Mosilla during its development. it's still in alpha and they call it fast enough and there's no recent commits which doesn't mean anything by itself. Uh and secondly, there's another extension
called exculite vector. It's by a company called squlite.ai. It's supposedly very optimized and it's still in active development. So without further ado, let's go to my IDE here. I'm using Neovim. So if you're mad that I called Neovim and IDE, then we can talk about it later. Um but I have this uh folder here called vector and it contains one simple application. This application is in rust
but please do not run away. If you can't read Rust that's totally fine. That's not the point of this talk. The point is business logic and what you can do with SQLite. So I'm going to walk you through it and it's a fairly short application and I'm thinking is this big enough? Do I need to make the font bigger? Good. Awesome. It's okay. I heard it's okay.
Is this better? Okay, good. So, you'll see that this application is 112 lines long and uh what I did is I imagined that I have a library of software engineering books and there those titles here those 12 titles are the titles of books on software engineering and you'll see that I have things about uh React here. I have things about CSS. I have things about transformers, Kafka,
Spark. And uh if you'll notice um if you have a really keen eye, I don't have the word database in any of these. But presumably I would like to query my uh book library in my library and say, do I have any books about databases? And of course I do. There's even one here about Postgress. Second last just doesn't have the word databases in it. So what
we can use for that is vector search. So I'm going to walk you through how this works and I'm going to skip some boilerplate because there is some boiler plate and it's not so interesting. So the first thing we do is that we're going to register uh the SQLite vec extension. Let me jump to the function here and it's not working. Yes, it is. And this here
is just copy paste from the documentation. So nothing uh nothing too complicated here. Uh, and then the next interesting thing is we're going to open an in-memory database. And as soon as I do that, there's I have an access to an in-memory ephemeral database that I can perform regular SQL on. I'm saying regular, but I can do more than that since I've already registered the SQLite vec
extension. And the next thing I'm going to do is I'm going to create a table except that I'm not simply going to say create table. I'm going to use a small extension from SQLite and I'm going to say create a virtual table vec demo using vec zero. Vec is simply the internal name that this extension happens to have. And then this line here simply says we're going
to store an embedding. So a very large array of numbers in here. Again, this is almost copy paste from the documentation. And we're also going to store the titles of our books. The next thing we do is we iterate through all of our titles for each of them. Uh we're going to extract an embedding. So we're going to ask a machine learning model, what's your idea of
this string? It's going to give us a long array of numbers. And then we're going to persist this into our um SQLite database using again plain SQL. And then finally, we're ready for the magic trick here. What we're going to do is we're going to take our query here. We're going to get the embedding from that and um we are going to perform what's called a kn
or k nearest neighbor search. In this case, we're going to ask for the eight um eight nearest vectors that we have in our database. And therefore, it's going to return the eight um more relevant eight most relevant books that I have. So, let's see how it works. Um, let me go to my terminal. There we go. And just before I do that, I forgot that I have
to have um Olama running. So, I'm just going to start it in an other tab. There we go. It's now running. So, I have a small server running on my computer that that will respond to the query. What is the embedding of this string? So, let's CD to the vector uh folder. I'm going to do a cargo run on this. You'll see it's not going to do
anything or it'll do something broken because I forgot to pass in a query. But if I pass in the word databases, you'll see that I get Postgress, Kafka, Spark. I'm pretty happy with that, right? Uh to me, this is a good semantic search. It was performed in SQL using SQLite. And what's interesting is let me go back to my application here is since uh everything we did
is in SQL I could have joined this with something else. I could have performed aggregations. I could have said uh what's the average price for the top uh five for the most five five most relevant books uh in my li library. Uh I could have inserted this into something else. So really um um the world is your oyster since we're in SQL we can do everything that
SQL would do. Uh, another example why you want might want to do this through SQLite is for example if you're developing a local first application. let's say a note-taking application and you want to let your user search their notes and say do I have anything about uh I don't know angry people here or do I have anything about contract renewals and then you can expose that functionality
mobile first um on a on a phone on a tablet and people will be able to uh perform semantic search over their personal documents awesome so that's it for SQLite a vec now let's look at an other it's actually my favorite like another very interesting thing we can do with SQLite. We're going to look at doing HTTP requests. Now, why would you want to perform HTTP requests
with a database? It makes absolutely no sense. Well, it kind of does. It makes a little sense and I hope I'm going to be able to make my point and that by the end of this little demo, you'll see the value that uh there is in doing these kinds of So, we're going to use an extension called SQLite HTTP. It's uh again by Alex Garcia. Uh he's
kind of the extensions guy. He did a lot of them. It's preview one and there's no observability. So, please do not use this in production, but it can be good for rapid prototyping, exploratory work. Uh you can see it exposes a nice declarative interface when you're dealing with things like REST APIs. So it can be good for rapid prototyping and especially LLMs are fairly proficient at SQL
and I found that this proficiency also transfer transfers when you're using things like extensions um over So maybe this will be a surprise to you. Uh this is actually a good fit for pageable um extensions or excuse me for pageable APIs because SQL natively so in the SQL standard uh SQL supports recursion. So you can you can declare recur recursive common table expressions and therefore uh query
recursive uh data structures and things like pageable APIs. So, let me show you how it works. And it's going to give me a good opportunity to uh show you the SQLite CLI. So, terminal CDN to the right And I'm going to open up the SQLite CLI. It's called SQLite 3. And just like that, I am dropped into an in-memory database. It's going to vanish as soon as
I exit the CLI. So, it's really good for fast prototyping. Uh you can also start it using a file as a database and I'm going to show you how to do that later. So now we're going to uh make SQLite aware of where the SQLite HTTP extension is because if I give it um query that has some HTTP requests in it, it's not going to understand anything.
So let me just do this and it's pretty easy. If I go to the left here and I go to my http folder, you'll see I have a file called sqlite http.dibib. So I'm on Mac. DIB is the equivalent of ao on Linux or a DLL on Windows. Simply a pre-ompiled library. So I'm going to do load uh SQLite HTTP. And just like that, SQLite is aware
of this extension. Now, let me show you what we're going to do with this. Uh, is anyone here not familiar with Pokemon? Okay, I'm assuming everyone is moderately familiar with Pokemon. So, there's this thing on the internet called the Pokemon API. If I go to my browser here, Poke API. Um, oops. Poke. There we go. It exposes this resource called slash Pokemon. It gives you a list
of every Pokemon there is. And there's a few interesting things about this. And the first one is that it's a pageable API. So if I add a limit five to make it a little more obvious. Um when we query this API, we need to uh take note of the Pokemon that are returned to us and then go to this next link, browse it, take a note of
those Pokemon, go to the next link, browse it, and you know, etc. until you have enough Pokemon or until you're done. What I'm going to show you is how we can automate this um recursive motion of following the next pointers using SQLite. And on top of this, I'm going to show you how we can build a table of every Pokemon with their types. Now, that might sound
simple, but let me show you what we need to do in this API to get this result. So, let's say I want to do this for five Pokemon. I first need to go to the first Pokemon, look at its name, follow this link, then uh drill through the drill down through the JSON uh and then coales its types. It's sometimes it's going to have more than one.
So, I'm going to say grass, poison. And then I need to do this for the next guy and the next guy and the next guy. And that means that if I have 10 Pokemon uh with a limit of five, then I'm going to have to do 12 requests. And you know, if you were to script that using curl, that would probably be a nightmare because you're going
to have to deal with loops and bash and maybe recursion and mutable state. And it's, you know, it's not so complicated, but it's definitely not um comfortable is the word I'm looking for. So it turns out that databases are surprisingly good at doing those kinds of things because the motion of looking at a piece of data and then looking at each row and fetching some data based
on the content of each row in the database world this is called a dependent subquery and it's something that um databases relational databases do all the time including SQLite. So what this means is that if we plug in an HTTP extension into SQLite, we can do we can get the query engine to do all of the heavy lifting for us. So let me show you what it
looks like as an SQL query. And this is the query that gives you a bunch of Pokemon with their comma separated I'm going to make this a little smaller so you can see. It's not as pretty as I would like it to be, but if you consider what you would have to do in a general purpose programming language to get the equivalent result, I think we're better
off if if we're only doing rapid prototyping or exploratory work, I think we're better off with this because uh you have this and as soon as you want to extend it, then it becomes easy to perform other aggregations or other JSON uh manipulation because you have access to everything that a database can do. Make sense? Awesome. So, let me run this and you'll see uh surprisingly how
quickly it runs. It's going to need I think we're going to retrieve something like 15 Pokemon with their types. So, we're going to need to do about 20 requests uh all pretty much at the same time. So, sorry Pokey API. I know that's not super nice for the API. But the way I'm going to do this is I'm not going to copy paste the whole thing, the
whole 33 lines into my uh SQLite CLI. I hate copy pasting things into this the terminal. It's never a good user experience. It's hard to edit. And then, you know, you end up messing up things. So, what I'm going to do is I'm going to use an other dot command. And this one is read. And I'm simply going to give it the file name. So, this one
is called Pokemon. with types.SQL and one, two, three, go. There we go. There's our what somewhat close to 15 Pokemon with their types almost instantaneous. You might say, you know, this is ugly. I don't want to stare at this. Like there's this weird bar in the middle. I It's hard to read. Um, so you just go mode box. So dot mode box. This tells SQLite format it
in a nicer way. And then you read it again. again sorry to the API uh we get a nicer formatting you can also format it as something that's a little more machine readable something like JSON do mode JSON then we run it again now why would we want to do that because we're back to square one and still ugly unformatted JSON is hard to read well one
of the reasons is that in this CLI it's super easy to pipe the results of something to a file so let me show you how you do that there's A couple of ways to do it, but one is going dot once and then you give it the name of a file. So result.json. It's going to remember that the next query should be piped into a file in
the format that you specified. So I'm going to read this query again. Seems to be taking a little longer. Okay. So maybe the API um uh rate limited me. Okay. Come on. once result.json demo gods. Okay, there we go. And now we have our JSON in our file and I can format it and I can do whatever I want with it. So if I go back to
my query, um, today I'm not going to have time to go through the whole query with you, but it's just a simple query with joins and aggregations. In the last slide of my talk, uh there's a QR code if you want to look at it, take inspiration from it. Uh there's going to be a QR code with the links to everything and the code from my demos,
including this. But for now, what I want to leave you with is the idea that um um combining HTTP requests with relational databases can sometimes be surprisingly effective for things like prototyping or exploratory And now I think that I have completely messed up PowerPoint, but I figure everyone can still see this. So, let's keep going. The next extension I want to show you is the one that's
meant for synchronizing And the reason you might want to synchronize databases using SQLite is uh when you use a client server database like MySQL or Postgress, you're going to have various users or clients talking to the to the same database and it's that database's responsibility to make sure that everyone is on the same page to keep data consistency optimal. When you use something like SQLite, uh, it
calls for a mode of collaboration that's closer to what Git was designed for, where everyone has their own copy on the data that they're working on and where everyone has a list of peers or remotes that they can push or pull to in order to synchronize themselves or others. So when you do that uh you need to uh you need a way to synchronize databases and SQLite
is able to uh very similarly to how Unix does it with Unix patches and how does it with how git does it with git merge it's able to reconcile divergent databases yes okay so the way it works is that you simply uh open a session says SQLite please start tracking my changes Then you modify your database. You do whatever you want to it. You work on it.
And while you do that, the session extension will um track will keep track of your modifications. So that when you ask it to create what's called a change set, it's going to be able to dump a file to the disk and that file is going to contain a binary representation of all of the changes that have occurred in your databases. And what you can then do with
this change set, so with this file is you can send it somewhere else and you can then apply it just like you would a Unix batch or you can or you would do with git merge, you can apply it to an other database. It even supports merging conflicts. So if you have two divergent states that have um diverged in ways that cannot be automatically resolved, we'll see
that there's a way we can deal with that using the session extension. And by the way, all of the extensions we we've seen so far were third-party extensions. This one is uh made by the SQLite team because it's such a core requirement for SQLite workflows. Um and one common uh need, one common pattern where you might want to do this is let's say you're uh building an
application, a mobile first application and one of your user has uh two devices. They have a phone and a tablet and they can work offline on those uh on those um devices. So they work offline on their phone, they work off offline on their tablet and when those devices come back online then you'll want to sync that state with the server and maybe deal with some conflicts
there. So let's go to my terminal and play a bit with the session extension. So I have on the left I have this folder called session. And what we're going to do is we're going to start from a base state. And we're going to pretend that we're two different people. We're going to pretend that we're A and that we're B and that both are doing different things.
And at the end we're going to take the changes that were made in B and we're going to merge them or put them or apply them onto database A so that uh we can transfer the changes from one to another. Sounds good. Awesome. Let me go to my terminal. Um, quit this seed into the correct session. Um, what I'm going to do first is I'm going to
create a base state. And I'm going to use this as an opportunity to show you how easy is it to load CSV data into SQLite. On the left here, I have this file called base.csv. It contains the data that we're going to use today. So, it's four rows and we're going to use this to populate a table. And here's how we do it. SQLite 3. We're going
to call our database base db. And we're going to create a table. Uh, and that table is going to contain two columns. One a name. It's going to be a primary key. You need a primary key to work with the session extension. And then the other is going to be the person's age. And it's going to be an int. So once we're done with that, we can
go doimp import CSV. We're going to give it the name of the file. So base.csv. And then the table that we want to import this into. And there we go. And if we look into our person's table, we have our four rows, Superman, myself, Batman, and Robin. And um if you if for those of you you who know me uh you'll know that I'm not 40 like
the data implies I am 35. And we'll use that mistake for our advantage. We're going to fix it and see what that does to uh reconciling divergent data. So now that we have our base state, let's clone this into A and B. Very easy. I'm going to say dot uh clone A. DB and clone B. DB. So let me switch now to A. DB. So from now
on we're going to be person A and we're going to do some modifications to our base data. So dot open A. DB. And let me think a bit because I don't want to mess this up. Okay. So now the first thing we're going to do is we're going to fix the mistake I made in my age. So we're going to set me back to 35 years old.
So, I'm going to say while I'm in a db, I'm going to say update persons set age equals 40 where age equals 35. And the other way around, set age to 35 where age is 40. And just make sure that we're good. There we go. I am now 35. Demo complete. Uh, you can all go home. No, just kidding. Um, so the next thing I'm going to
do is now that I've performed one change. So fixing an age in a DB, I'm going to go to B. DB and I'm going to do an other change and then we're going to reconcile them. So do open B. DB, I am now a person B. And let's confirm that. Select star from persons. And you'll see that in ver in B's version, I am still 40 years
old. And in on this side, what we're going to do is we're going to make everyone 50 years old. But we're going to do that while the session and extension tracks our changes. So the way we do that very simple dot session. And if I just do this and hit enter, it's going to tell me everything I can do. So it's fairly, you know, easy to use.
Um, so first thing I'm going to do is I'm going to open a session. Dot session open main. Main is just the default schema name. So I didn't create any. We're still in main. And then arbitrarily I'm going to call this my session. And we're now ready to perform tracked changes. So update persons 50. Just make sure we're still good. Star from persons. Everyone is now 50
years old. So now I can dump my change set. I'm going to do dot session change set changes from B. And you'll see on the left I now have a new file called changes from B. Uh let's control Z out of this and maybe cat changes from B. See what's in there. And you'll see that it's binary data. It's kind of garbled. It's not meant for human
consumption. So now that we've done this, let me close the CLI. And now we're going to apply the changes from B to changes from A. and we're going to see how we can merge the conflict that will occur. Now, the s the CLI does not um expose the necessary APIs to do this. So, I've had to vibe code a little program that will let me do this
interactively on the CLI just so we get a nicer demo. But in a real world application, your application would be in charge of doing this through either the C API or your languages bindings to SQL 8. So my little program is called interactive change set. And here here's how you use it. We're going to say interactive change set. We're going to apply to A. DB. And what
are we going to apply? That changes from B. And as soon as I hit enter, if I did everything correctly. Yeah, we get a conflict. It's saying, wait a second. I'm not sure what to do about this because it used to be 40 years old. one guy changed it or or you changed it to be 35 and the other changed it to be 50. What do you
want me to do? So really it's exactly like get merge. You you get to choose which one you want. In this case, just for the sake of the demo, I'm going to choose omit. And there we go. If we go esculite 3 A.DB and look at what's in our table now, we see that um our changes and our the changes from B was everyone is now 35
was correctly merged with the changes from A. The changes from A being Mikuel is now 35. Excuse me. Everyone is 50. M is 35. Merge complete. Um and that's how you synchronize databases using the session extension. What I've shown here is just scratching the surface. The documentation is very well written and it has some advanced functionalities. So if you're interested, then please go and have a look
at the documentation. It's super well written. Awesome. Now that we've seen these nice three extensions, I want to talk about two other extensions that are built by the SQLite team. We're not going to have time to look at them today, but I find them very interesting. First one is called zip file. It allows you to query, create, or modify files inside zip archives. And the first time
I played with it, I thought it was mindblowing. So, you perform a select on a zip file and you get a table, a virtual table where each row represents either a directory or a file inside your archive. More interesting more interestingly, you can even get you you even have access to the data in inside each of those files and uh you can update directly through an update
statement uh the files that are inside your zip archives. There's another extension called spellfix. It allows you to do spellch checking from SQLite and has some pretty advanced functionalities like um do a search by words that sound like something. So if you're interested, I encourage you to look at the documentation. It's very So now that we've looked at these extensions, since this is an open source conference,
I want to take the time to uh look with you at all of the nebula of projects and communities that surround SQLite. So all of these open-source communities. And what I did was I looked at I I tried to find everything I could get my hands on, every project that had at least 10 contributors. And I divided them into five categories. and I want to go through
them with you. Some of those those things I find are really interesting and they're a testament to the community that SQLite managed to build. First uh category is forks and rewrites. Uh the two projects in there are headed by a company company called Terso and disclaimer I am a contributor to the second of these projects. So the first one libsql is a fork of SQite. Uh it
does vector search, client server mode, uh replication. It allows you to define functions in web assembly. Uh it has around 150 contributors and 16,000 stars on GitHub. Whereas Terso is uh a rewrite of SQLite in Rust. It adds vector search, materialized views, encryption, concurrent writes, uh change data capture and many more. It also around has around 150 contributors and 14,000 stars. Moving on to editors. SQLite studio
uh is um SQLite specific um graphical editor. It uh it has some nice um SQLite specific functionality uh such as registering custom functions in either JavaScript, TCL or SQL directly from the GUI. Uh it allows you to drag and drop between databases and it has plugins for extensibility. PHP light admin is a management u dashboard for SQLite. It supports 15 human languages. It supports visualizations such as
bars, pies or lines. And it allows you to register custom functions this time in PHP. And then finally, and this is the most impressive project I find of those three uh is data set. It's a advanced tool for uh data set visualization and publication. It allows you to easily navigate complex data sets in a web interface. Uh it does automated faceting which means that if you have
for example a denormalized data set where a row contains uh strings and there's only maybe two or three distinct strings then you can automatically extract a join table from that and it's going to become a clickable link in the web interface. It allows you to publish templatable websites so that other people can see uh the results of your visualizations and it has over 150 plugins. Replication light
FS is a fuse file system meaning it's a user space file system that will sit between your application and uh the kernel. It will capture and replicate transactional rights to the SQLite database. So it's replication in that way is going to be completely transparent to both SQLite and your application. CRSQite allows you to add replication capabilities to SQLite using conflict-free replicated data types or CRDTS. Whereas RQite
by far the most advanced of those project is a distributed database based on SQLite where each node of the database is going to be an SQLite instance and they're going going to coordinate amongst themselves using a raft consensus algorithm. So in other words, you have a high availability SQLite database. CLI's uh Q text as data allows you to easily and frictionlessly perform SQL over CSV uh directly
from the CLI and it's backed by SQLite. SQL ELF did not fit into my 10 contributors criteria but it was so original and powerful that um I could not omit it. Uh it allows you to query symbols inside ELF executables. Um, ELF executables is the executable format in Linux. So, it's an alternative to tools like OB, dump, nm, and read Now, lightream is a backup and recovery
tool. It allows you to back up your database to things like S3 and it works by watching the changes to your write ahead log, which is similar to bin log in MySQL if you've worked with it. And then last but not least, uh the last one uh that I identified is called SQL Torrent. It's a VFS extension, meaning that it works by swapping out the storage layer
inside SQL. And it allows you to perform uh queries while a database is loading, is downloading as a torrent. And the way it works is that each time you do a query, it's going to look at the bite ranges that are required to satisfy that query and it's going to prioritize downloading those bite ranges in your torrent. So this concludes the overview of the uh open-source projects
uh around SQLite. There's many more, but they're smaller. They have less contributors. Uh so please don't take this as this is all that ex that exists. Um I hope that in my talk I gave you a good overview of what you can do with SQLite beyond simple uh CRUD queries. We looked at vector search, HTTP queries, uh synchronizing databases. We mentioned uh unfortunately we didn't have time
to play with them. Extensions to work with zip files and um what was the other one? Spellchecking. And then we com uh we um finished by this overview of um open source projects. What I want to leave you with is well first here's a QR code to everything I discussed today. So the code for my demos is in there and the links for all of those open
source projects is in there. And then at the center uh I told you at the beginning that SQLite is public domain. This is the text that stands in lie of um of a license in the SQLite source code files. Uh they call it the SQLite blessing. I think it's a wonderful gesture from the SQLite team. Says may you do good and not evil. May you find forgiveness
for yourself and forgive others and may you share freely never taking more than you give. Thank you. Hello. Okay. So, if there are any questions uh regarding the lecture, uh anyone can uh stand up in front of the microphone in the middle and ask them for Q for our Q&A section. Any volunteers? We even put a light on it. [sighs] Okay, everyone's pretty shy or very tired
uh due to the long day. So, [snorts] if there are no other questions, another round of applause for And thank you for thank you for coming.
