You can build really powerful domain-specific SQL scripting engines using this interface. The functions bound to SQL can be anything. They do not have to be deterministic or free of side effects.
Microsoft has a really good provider & docs around how to use this with .NET/C#:
BTW, it looks like the js engine is "QuickJS" [0]. (I'm not familiar with it myself.)
I like it because sqlite by itself lacks a host language. (e.g., Oracle's plsql, Postgreses pgplsql, Sqlserver's t-sql, etc). That is: code that runs on compute that is local to your storage.
That's a nice flexible design -- you can choose whatever language you want. But quite typically you have to bring one, and there are various complications to that.
It's quite powerful, BTW, to have the app-level code that acts on the app data live with the data. You can present cohesive app-level abstraction to the client (some examples people will hopefully recognize: applyResetCode(theCode) or authenticateSessionToken(), or whatever), which can be refined/changed without affecting clients. (Of course you still have to full power and flexibility of SQL and relational data for the parts of your app that need it.)
QuickJS came out a few years ago, and it was really exciting at the time, being a Lua-style embeddable JS in contrast to V8 which is a beast but very fast and much more comparable to LuaJIT. Which basically meant you could stop using Lua for scripting and start using JS. But by the time it came out, TypeScript was already king of JS land, and QuickJS didn't have native TypeScript type stripping support last time I checked (~2023), though he did port the `tsc` compiler to it I think? so you can build this functionality yourself. I wonder how QuickJS compares to JSCore which Bun uses.
jmull · 1h ago
You could use jsdoc, or 'tsc' your .ts files on the way to loading them into the db.
Typescript has a fairly limited utility here though. It's a static type checker. Your types are mostly going to be SQL parameters and the result of running SQL, which, by design/nature are resolved at runtime. You could build a bunch of external controls to help ensure the runtime data is contained to your static types, but you're really swimming upstream. Like you can use a screwdriver as a hammer, but there are better approaches. (I think typescript would be much better used client-side, in your app server code that is above the data layer.)
90s_dev · 47m ago
I tried jsdoc for so many years before just giving up and going full TypeScript.
But you're right, the TS layer would be static, and you would compile to JS and just use that... I guess.
Until the types-proposal is inevitably implemented, of course.
crabbone · 12m ago
When I needed to interface with SQLite from Ada, I discovered that SQLite library provided with the language lacked regular expressions, and I had to add that functionality myself. During this exercise, I learned that you can relatively easily add Ada functions to be executed by SQLite. From performance and ability to debug standpoint, I'd prefer this to a whole language runtime added to the database.
In general, I'd prefer to minimize non-SQL code that runs in the database because it's hard to reason about its implications on the already complicated planning and execution of SQL code. Especially if such code can observe or change the state of the variables involved in a transaction. I feel like to not have this feature backfire, I'd want to have a way to either disallow access to the variables, or create a framework for the foreign code where its made to comply with transaction rules and have to make promises about its own code to not violate them.
Looks interesting. Is there a performance benefit to pushing this kind of logic into SQLite, compared with doing similar logic as a series of steps from a Node process? Or are the motivations for this library more ergonomic/practical? (Or does it enable you to do things you actually couldn’t do from Node at all?)
sillystu04 · 56m ago
There are performance benefits to using DB functions in situations where you'd otherwise have to pull lots of data out of the DB.
For example, if you wanted to find which of your sessions where created with iPV6 addresses you could select them all out and perform the logic in your application code, potentially at the cost of pulling millions of records out of your DB. But doing it in a DB function allows you to skip this as your app code never needs to do the calculations.
This kind of optimisation is generally more important when the DB is running on a separate machine to the application code because of the overhead of big network requests in getting large amounts of data out, but even on a local SQLite DB there is likely some non zero benefit to minimising the amount of data retrieved.
I suppose DB functions could of course be implemented in SQL or similar, but that can be quite unfriendly for complex logic. So in a sense there is an advantage ergonomic as well.
cal85 · 4m ago
> even on a local SQLite DB there is likely some non zero benefit to minimising the amount of data retrieved.
Why though? I get why it can be a big perf win to push that kind of logic into a remote DB - fewer and smaller payloads being sent over the network. But what makes you say there is likely a non-zero benefit even with local SQLite? (I don’t really have a clear mental model of how embedded SQLite works, so you may well be right, I just want to understand why.)
gwbas1c · 2h ago
Question: How easy / hard is it to replace a SQL query with a join, to a SQL query that returns a JSON object? (IE, a foreign key relationship is turned into a JSON array.)
As in return the relation behind the join or return the results of that as a json object?
The latter is easy with SQLites JSON support.
9dev · 1h ago
JS is a great choice for this. I wonder if one could stack a bytecode compiler on top, to optimise performance even further? Or add WASM support, and compile the JS to WASM when creating the function?
datadrivenangel · 1h ago
Can't you already just register a javascript function as a custom user defined SQLite function?
rileytg · 2h ago
How is the performance? Any docs or benchmarks related to this?
You can build really powerful domain-specific SQL scripting engines using this interface. The functions bound to SQL can be anything. They do not have to be deterministic or free of side effects.
Microsoft has a really good provider & docs around how to use this with .NET/C#:
https://learn.microsoft.com/en-us/dotnet/standard/data/sqlit...
BTW, it looks like the js engine is "QuickJS" [0]. (I'm not familiar with it myself.)
I like it because sqlite by itself lacks a host language. (e.g., Oracle's plsql, Postgreses pgplsql, Sqlserver's t-sql, etc). That is: code that runs on compute that is local to your storage.
That's a nice flexible design -- you can choose whatever language you want. But quite typically you have to bring one, and there are various complications to that.
It's quite powerful, BTW, to have the app-level code that acts on the app data live with the data. You can present cohesive app-level abstraction to the client (some examples people will hopefully recognize: applyResetCode(theCode) or authenticateSessionToken(), or whatever), which can be refined/changed without affecting clients. (Of course you still have to full power and flexibility of SQL and relational data for the parts of your app that need it.)
[0] https://bellard.org/quickjs/
Typescript has a fairly limited utility here though. It's a static type checker. Your types are mostly going to be SQL parameters and the result of running SQL, which, by design/nature are resolved at runtime. You could build a bunch of external controls to help ensure the runtime data is contained to your static types, but you're really swimming upstream. Like you can use a screwdriver as a hammer, but there are better approaches. (I think typescript would be much better used client-side, in your app server code that is above the data layer.)
But you're right, the TS layer would be static, and you would compile to JS and just use that... I guess.
Until the types-proposal is inevitably implemented, of course.
In general, I'd prefer to minimize non-SQL code that runs in the database because it's hard to reason about its implications on the already complicated planning and execution of SQL code. Especially if such code can observe or change the state of the variables involved in a transaction. I feel like to not have this feature backfire, I'd want to have a way to either disallow access to the variables, or create a framework for the foreign code where its made to comply with transaction rules and have to make promises about its own code to not violate them.
For example, if you wanted to find which of your sessions where created with iPV6 addresses you could select them all out and perform the logic in your application code, potentially at the cost of pulling millions of records out of your DB. But doing it in a DB function allows you to skip this as your app code never needs to do the calculations.
This kind of optimisation is generally more important when the DB is running on a separate machine to the application code because of the overhead of big network requests in getting large amounts of data out, but even on a local SQLite DB there is likely some non zero benefit to minimising the amount of data retrieved.
I suppose DB functions could of course be implemented in SQL or similar, but that can be quite unfriendly for complex logic. So in a sense there is an advantage ergonomic as well.
Why though? I get why it can be a big perf win to push that kind of logic into a remote DB - fewer and smaller payloads being sent over the network. But what makes you say there is likely a non-zero benefit even with local SQLite? (I don’t really have a clear mental model of how embedded SQLite works, so you may well be right, I just want to understand why.)
The latter is easy with SQLites JSON support.
Reminds me of awk, Nice.