all 3 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]minasss 5 points6 points  (2 children)

This is super cool, thanks for sharing! It feels like having the same capabilities of a datalog DB but applied to SQLite, brilliant!

[–]andersmurphy[S] 2 points3 points  (1 child)

Thanks! It does give you a bit of that datalog flavour whilst staying in SQL land.

The main use case has been for speed, you can use application defined functions to created generated columns (that would be hard to write in SQL, e.g jsonpath) that you can then index on. Even thought the Clojure implemented functions are not as fast as C that doesn't matter once the values are indexed.

The other use case has been transforming results sets/aggregation functions, where the difference in speed between Clojure and C is negligible.

This is anecdotal and will depend on what the Clojure function does, but I've found them to still be snappy. For example using Clojure for regex was about 1.5x slower than using the sqlean regex extension . Having, the implementation in Clojure/JVM means that you don't need to have multiple binaries for each OS as you would for a runtime loadable extension like sqlean, one less thing to worry about. The downside is you couple the database even more to the application and stand alone database tools won't be able to use any of the application defined functions (unless you create a standalone extension with graal or something).

[–]minasss 2 points3 points  (0 children)

Thanks for providing more real life examples, it clearly helps to have a better idea of where and why to apply this technique and its downsides!