SQLite for beginners: Extensions

SQLite doesn't have as many features as the other popular databases, but don't let that scare you away from using SQLite, because even though there aren't as many built-in features, there are excellent ways of extending SQLite to add any missing features you might need. Want to learn more about extending SQLite? Check out this video.

Transcript

"SQLite doesn't have as many features as the other popular databases, but don't let that scare you away from using SQLite, because even though there aren't as many built-in features, there are excellent ways of extending SQLite to add any missing features you might need.

For example, SQLite doesn't have a uuid function, but if you need this function, there are a couple of ways to create it, or, you can even use open-source libraries to extend SQLite, to add a uuid function, which you'll see an example of in a moment here.

So let's say we really do need a uuid function in SQLite, so how would we go about creating this function?

Well, if you are well versed in SQL, then you're probably thinking we should create this functions by using SQL's data definition language, in other words, you might be tempted to write something like this, a create uuid function, but this won't work, as SQLite doesn't support creating functions in this manner.

Ok, if you can't do this to create the uuid function, then what can we do?

Well, you've got a couple of options, that we'll consider.

The first option is to create an application-defined SQL function.

So, what is an application-defined SQL function?

Well, basically you would write a function, ideally in the programming language of your choice, then you would register the function in SQLite, at which point, you can start using the function in your SQL statements.

Ok, so what does this look like in practice?

Well, it kind of depends on your language and the SQLite library your using, but I'll give you a couple of examples in both JavaScript and Python.

Now, what you should know is, regardless of which language you use, the overall process of adding an application-defined function is pretty similar.

Basically, you'll create a regular function in your language, then you'll register that function with SQLite, and then you can start using the function in your sql statements.

So, in JavaScript, I'll install the excellent better-sqlite3 library.

Next, I'll import our new library along with nodejs's crypto library.

Now I'll open up our SQLite database, then below that I'll register our new function, giving it the name, uuid, and then I'll pass in an anonymous function that returns the result of calling the crypto modules randomUUID function, and with this, we're basically done adding our application-defined SQLite function.

Next, I'll verify this works by preparing a statement that selects our new uuid function.

Then I'll set a result constant equal to the result of calling our statements, pluck function, followed by a call to get, then I'll simply log the result out.

Next, I'll run our script, and as you can see, our new SQLite function worked, cool.

Now, let's take a quick look at how you'd add an application-defined function in python.

So, in our python file, I'll import the sqlite3 and uuid modules.

Next, I'll create a connection to our SQLite database.

Now I'll register a new function, by calling the connection objects, create function method, then I'll pass in the name uuid, followed by the arity of the function, which is zero, and lastly, I'll add a lambda function that simply returns a new uuid, cast as a string, and this is all that's necessary to add our application-defined function in python.

Now let's try out our new function by first creating a cursor, then I'll create a SQL statement that selects our new uuid function.

So now I'll execute the SQL by calling cursor, execute, and passing in our sql statement.

Next, I'll print the result and close everything we opened.

Ok, let's try this out, and as you can see it worked, cool.

Now, there's another way to extend SQLite, and that's via the library extension mechanism.

So, what's this extension mechanism?

Well, you can create extensions, which are shared libraries or DLL's. Then you can load and use the extension in SQLite.

Now, we're not going to create an extension in this video, as it's not something most developers typically need to do, but I do want to show you how you'd use an existing extension, as that's a much more likely scenario for most developers.

In particular, we're going to use an extension from the Sqlean repository.

Now, if you're on a mac, the default version of SQLite that comes preinstalled, unfortunately, doesn't support using extensions, so if you want to use an extension with SQLite's command line tools for the mac, you'll need to install a supported version of SQLite, which you can do with the brew package manager.

So, I want to use SQLeans uuid extension as an example, so I'll download the shared library by going to tags, and clicking on the latest release, then I'll download the appropriate file for my platform, mac os on arm, then I'll unzip the file and I'll copy the uuid shared library and I'll paste it into my project folder.

Now, as a security feature, apple quarantines applications and libraries you download from the internet, and so to remove this file from quarantine, I'll issue the following command.

Next, I'll open up SQLite's command line utility with my database.

Now, first I'll show you that there is no uuid4 function available right now, but it will be available once we load the uuid extension.

So, to use this uuid shared library, I'll simply key in dot load, followed by the path and file name of the shared library file name, without the file extension.

Now, I'll try using the uuid4 function, and it worked, cool.

Ok, it's important to know that, extensions can do more than just add functions. For example, there's a full-text search extension, which you can watch a video of with this link here, and the full-text search extension does way more than just add some functions.

It adds a way of indexing text in what's called a virtual table, and it supplies some functions for searching and ranking the results.

Another example of an extension that's more than just functions, is an extension we're working on here at Mycelial. We're creating an extension called Mycelite, that will synchronize one SQLite database to another.

So, why do I mention these other two examples of extensions?

Well, because, I want you to know, that extensions can do some pretty amazing things, and it's something you can turn to if SQLite is missing a feature that's important to you.

Hey, here at Mycelial, we're big fans of SQLite, and as I said a moment ago, we're building tools around SQLite that will allow you to synchronize an SQLite database to multiple computers without conflict.

If you are interested in SQLite and what we're doing with it, please subscribe to our channel, and consider joining our newsletter to learn more."

‍