Taking full advantage of SQLite's extensibility is a center piece of my Go driver.<p>It started due to having to reimplement the OS layer in Go because of tech constraints, but it means you _can_ implement VFSes, UDFs (scalar, aggregates and windows), and virtual tables in Go, with reasonable performance and nice APIs.<p>I also made a point of dogfooding this as much as possible, with a bunch of extensions and a few custom VFSes that use the same APIs available to clients of the library.<p><a href="https://github.com/ncruces/go-sqlite3/tree/main/ext">https://github.com/ncruces/go-sqlite3/tree/main/ext</a><p><a href="https://github.com/ncruces/go-sqlite3/tree/main/vfs#custom-vfses">https://github.com/ncruces/go-sqlite3/tree/main/vfs#custom-v...</a>
You can hypothetically write your entire product in SQL with appropriate bindings to SQLite. For me, SQL with CTEs is a very compelling way to model tricky business logic. Building a SQL-based rules engine is trivial if your host language has a good SQLite library.<p>This thing starts to grow legs once you realize you can recursively get into the rabbit hole by binding something like an Execute_Sql UDF - You can store the actual scripts within the same schema they operate on. Treating your code as data means you can do things like transactional updates of business logic while the system is serving live requests. You also get simple reflection & search over the business logic.
Is this the same thing as create_function for python?<p><a href="https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function" rel="nofollow">https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...</a>
Ive nothing to say but “that’s cool”. I want to try this in NodeJS!<p>I suppose using functions defined by the host with SQLite is cheaper than using similarly defined functions in databases that are separated by a network. I wonder what the overhead is.<p>Also, what’s with the weird UUIDs? Why not use UUIDv7 if you want time-ordered UUIDs?
Also available with LUA <a href="http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#db_create_function" rel="nofollow">http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#db...</a>
There are also some good libs for SQLite. This is the standard set that I use.
<a href="https://github.com/nalgeon/sqlean">https://github.com/nalgeon/sqlean</a>
I happen to work with BigQuery since recently and heard you can do UDFs with JavaScript. Good to know that this is a thing with more databases. I didn't need to use UDFs up until this point but now that I know about it a bit more, I just might as JS happens to be a strong language of mine (currently not using it professionally).
Yes, I remember using that in PHP, very handy before sqlite JSON support, since it also worked with aggregate functions.<p><a href="https://www.php.net/manual/en/sqlite3.createfunction.php" rel="nofollow">https://www.php.net/manual/en/sqlite3.createfunction.php</a>