Comment by DecoPerson

One huge benefit of using SQLite over a traditional server/client DBMS is the ability to easily add SQL functions that call into your host language and memory-space.

For example, we’re using better-sqlite3 which has a convenient API for adding SQL functions [1], and we have dozens of helper methods for dealing with time using the temporal-polyfill module.

We have custom JSON-based serialisation formats for PlainDate, PlainTime, PlainDateTime, ZonedDateTome, etc. Then in SQL we can call ‘isDate_Between__(a, b, c)`.

a, b, and c are deserialised by the JS (TS) function, the logic is run, and the result is returned to SQLite. We’ve had no performance issues with this approach, though we’re only dealing with simple CRUD stuff. No big data.

You can even use these functions with generated columns and indexes, but I haven’t found a proper use for this yet in my work.

[1] https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/ap...