This is a fantastic approach.

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/

Show 4 replies

This is the API used: https://www.sqlite.org/appfunc.html

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...

Why not use the native functions [0] of the DB? Presumably they're going to be faster. For example, computing the median of a table `nums` with columns `id` and `num` can be done like this:

    WITH ordered_nums AS (
      SELECT num, ROW_NUMBER() OVER (ORDER BY num) as rn,
             COUNT(*) OVER() as total
      FROM nums
    )
    SELECT AVG(num) as median
    FROM ordered_nums
    WHERE rn IN (
      (total + 1) / 2,
      (total + 2) / 2
    );

[0]: https://www.sqlite.org/lang_corefunc.html

Can someone explain to me why you would want to do something like in the example of calculating age based on birthdate? Why wouldn't you do that within an app or within code rather than having a database function?

This is cool. It's very reminiscent of https://github.com/plv8/plv8 for Javascript on Postgresql. Show 1 reply

Nice. Would prefer Lua though.

> Every SQLite Cloud database comes with the sqlite-vec extension pre-installed. sqlite-vec is currently built and optimized for brute-force vector search. This means there is no approximate nearest neighbor search available at this time[1]

Darn, ANN would be awesome to have on the edge.

[1]: https://docs.sqlitecloud.io/docs/vector

Show 1 reply

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.) Show 2 replies

Nice project and cool to see JavaScript embedded with SQL this way, never seen it before. Just wondering how it ended up like this syntax wise and what exactly is going on here?

SELECT js_create_scalar('function_name', 'function_code');

Really cool project! Thanks for sharing.

Q|ery uses SQLite and QuickJS, too, but in Rust.

https://qery.io/

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?) Show 1 reply

Fantastic, would love to see same js support for redis as oposed to lua scripts.

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?

How is the performance? Any docs or benchmarks related to this? Show 1 reply

Can't you already just register a javascript function as a custom user defined SQLite function?

This is such an interesting concept, thanks for sharing!

I wonder how many more decades we need until we realize database and programming language belong together. We can still separate infrastructure in persistence, backend, frontend, that's not the point. Every one of them should have a native local relational database, and these databases, in each layer, should be capable of basic interop out of the box.

Hear me out guys - SQLite, but in a browser! We could call it WebSQL. Show 1 reply

can this work with wasm too? that would open interesting doors of doing it in browser.

Cool, now someone rebuild the magic that was the OLD CouchDB (1.x) with couchapps!

> js_create_aggregate

Reminds me of awk, Nice.

Given that this links to sqlite.ai, did AI write some or all of this project? It's hard to tell anymore. Show 3 replies

    CVE-2024-0418 (and similar recent ones like CVE-2024-32593, CVE-2024-32592): These often relate to how QuickJS handles certain object properties or internal structures, potentially leading to crashes (Denial of Service) or, in more severe cases, memory corruption issues like heap-based buffer overflows or use-after-free vulnerabilities. These types of memory corruption can sometimes be escalated to arbitrary code execution, though it's not always straightforward.

    CVE-2021-40517: A use-after-free vulnerability when handling Array.prototype.concat with a specially crafted proxy object. This could lead to a crash or potentially code execution.

    CVE-2020-13951: An issue in JSON.parse that could lead to a stack overflow (Denial of Service) with deeply nested JSON structures.

It's not V8 or SpiderMonkey, which have dedicated, large security teams and decades of hardening due to their use in browsers handling actively malicious web content. QuickJS is primarily the work of one (albeit brilliant) developer.

This means that while it's well-written, the sheer volume of security research and fuzzing applied to browser engines is likely greater.

The responsibility for security falls on multiple layers:

    Fabrice Bellard for QuickJS itself.

    The sqlite-js developers (
    @marcobambini
    marcobambini Marco Bambini
    @Gioee
    Gioee Gioele Cantoni)

    for how they embed, configure, and update QuickJS, and what APIs they expose.

    The end-user/DBA for controlling who can define JavaScript UDFs and for keeping sqlite-js (and thus its QuickJS version) updated.