SQLite: Past, Present, and Future

Re:SQLite: Past, Present, and Future by polyrand on Thursday September 01, 2022 @12:32PM (#32678875)

Adding user-defined functions to SQLite is not difficult, and the mechanism is quite flexible. You can create extensions and load them when you create the SQLite connection to have the functions available in queries. I wrote a blog post explaining how to do that using Rust, and the example is precisely a `regex_extract` function [0].

If you need them, you also have a "stdlib" implemented for Go [1] and a pretty extensive collection of extensions [2]

[0]: https://ricardoanderegg.com/posts/extending-sqlite-with-rust...

[1]: https://github.com/multiprocessio/go-sqlite3-stdlib

[2]: https://github.com/nalgeon/sqlean

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by ergocoder on Thursday September 01, 2022 @12:56PM (#32679323)

Wow this is helpful. I'm using sqlite for some of my projects and always bothered that some functions are missing. WITH RECURSIVE is too mind bending.

This seems like I can add a lot more functions to it, not just regex extract.

Came here to complain and learned something useful.

[ Parent | on HN ]

Re:SQLite: Past, Present, and Future by Groxx on Thursday September 01, 2022 @01:50PM (#32680264)

Probably also worth noting: you don't need to build (many kinds of) extensions as C-compatible code and separate .so files that you load.

SQLite is an in-process database. You can give it a callback func to execute. So your regex-extract can literally just call a function in your code: https://sqlite.org/appfunc.html

edit: Python's stdlib documentation concisely shows how easy this can be: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... Basically every SQLite library should have something similar. This extreme ease of extending is a big part of why SQLite has so little built-in.

[ Parent | on HN ]