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.)
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.
Very typically, that's how it's done with traditional client/server databases.
There's no built-in "wire-protocol" for clients to connect, but there are reasonable options (it's a pretty common pattern, if fact, for systems to have a data service that provides an app-level HTTP interface to data -- so there you go, it's something you might have implemented anyway).
But I think this project would help in the creation of a full/rich application data service without a need for an intermediate app tier.
There are a few reasons people end up with an intermediate app-level data service, but it's starting to seem like a service based on sqlite (running local to the storage, of course) may be able to provide a decent alternative answer in many cases.
I'm imagining a service light-weight enough to run as a lambda or other serverless environment (including fast cold start) which then opens up some interesting things like one-db per user and maybe user-controled host, etc.
Now, people add unnecessary layers all the time to everything. But there are also good reasons for compute that is between "local to the user" and "local to the storage".
Given that, though, JavaScript feels like a bit of an odd choice for language.
[1] https://nodejs.org/api/sqlite.html#databasefunctionname-opti...
[2] https://nodejs.org/api/sqlite.html#databaseaggregatename-opt...
This makes more sense if you're someone who hosts the databases of others, but I it's still interesting otherwise.
Like, if something like that was included in the CLI, one of the many browser apps, or the online playgrounds, your DBs could carry their own UDFs portably within themselves.
It'd be even more interesting if it supported virtual tables as well.
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.
JS in an RDBMS… shudder
Well, we’re already talking about dyn langs here (plsql, tsql, etc). At least in the js case you have some notion of fp constructs available. It’s not like the constraints of the db vanish!
Nonetheless i generally agree.
Reminds me of awk, Nice.
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.)
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...
Darn, ANN would be awesome to have on the edge.
It is blazing fast, highly optimized, and even performs well on memory-constrained devices. Already tested with 5M 1500-dimensional vectors.
The repo is currently private, and we'll make it public soon: https://github.com/sqliteai/sqlite-vector
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
);
SELECT js_create_scalar('function_name', 'function_code');
Really cool project! Thanks for sharing.
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.
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.> This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path.
Instead we are saddled with garbage IndexedDB so bad nobody uses it natively and thousands of developer hours are spend building shims and wrappers to shield us from the brain rot.
That the commitment to multiple implementations is something the WHATWG takes much less seriously than the W3C once did is another matter.
gwbas1c•5mo ago
marcobambini•5mo ago
datadrivenangel•5mo ago
The latter is easy with SQLites JSON support.