11 ms·
Show HN: SQLite JavaScript - extend your database with JavaScript
- gwbas1c 1y agoQuestion: 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.)
- datadrivenangel 1y agoAs 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.
- marcobambini 1y agoSQLite has built-in JSON support: https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html
- rileytg 1y agoHow is the performance? Any docs or benchmarks related to this?
- marcobambini 1y agohttps://bellard.org/quickjs/bench.html https://bellard.org/quickjs/bench.html
- abirch 1y agoThis is cool. It's very reminiscent of https://github.com/plv8/plv8 https://github.com/plv8/plv8 for Javascript on Postgresql.
- tehlike 1y agoI use plv8 - it's great!
- jmull 1y agoThis 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/ https://bellard.org/quickjs/
- 90s_dev 1y agoQuickJS 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 1y agoYou 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.)
- 9dev 1y agoJS 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?
- porridgeraisin 1y ago> js_create_aggregate Reminds me of awk, Nice.
- 90s_dev 1y ago[flagged]
- marcobambini 1y agoAll code written by me. I am a human.
- giancarlostoro 1y agoIs there some background why the domain implies AI is involved? I'm sure I'm not the only one confused / curious about it.
- ncruces 1y agoIt's just SQLite Cloud trying to get in on the AI fever: https://sqlitecloud.io/ai https://sqlitecloud.io/ai
- 90s_dev 1y ago[flagged]
- deleted 1y ago[deleted]
- cal85 1y agoLooks 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 1y agoThere 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 1y 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.)
- frollogaston 1y agoFirst thing that comes to mind is you've got a complex query with some of these UDFs involved in the middle, rather than just transforming the end result. Doing the equivalent without UDFs would be an entirely different query with a different plan, or more likely you'd have to split into separate queries.
- datadrivenangel 1y agoCan't you already just register a javascript function as a custom user defined SQLite function?
- bob1029 1y agoThis is the API used: https://www.sqlite.org/appfunc.html 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/sqlite/user-defined-functions https://learn.microsoft.com/en-us/dotnet/standard/data/sqlit...
- mcflubbins 1y agoCool, now someone rebuild the magic that was the OLD CouchDB (1.x) with couchapps!
- gcv 1y agoQ|ery uses SQLite and QuickJS, too, but in Rust. https://qery.io/ https://qery.io/
- hbcondo714 1y ago> 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 https://docs.sqlitecloud.io/docs/vector
- marcobambini 1y agoWe'll soon announce a new extension: sqlite-vector. 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 https://github.com/sqliteai/sqlite-vector
- mholt 1y agoHow does it compare to https://github.com/asg017/sqlite-vec https://github.com/asg017/sqlite-vec ? Already using this in a big project.
- marcobambini 1y agoOur implementation is much faster and does not require to store vectors into a virtual table (which forces the user to write complex join statements)
- intalentive 1y agoHave you benchmarked against the usearch extension?
- pdyc 1y agocan this work with wasm too? that would open interesting doors of doing it in browser.
- mike_r_parsons 1y agoYou can already do this ... https://sql.js.org/#/?id=api-documentation:~:text=//%20You%20can%20also%20use%20JavaScript%20functions%20inside%20your%20SQL%20code%0A//%20Create%20the%20js%20function%20you%20need%0Afunction%20add(a%2C%20b)%20%7Breturn%20a%2Bb%3B%7D%0A//%20Specifies%20the%20SQL%20function%27s%20name%2C%20the%20number%20of%20it%27s%20arguments%2C%20and%20the%20js%20function%20to%20use%0Adb.create_function(%22add_js%22%2C%20add)%3B https://sql.js.org/#/?id=api-documentation:~:text=//%20You%2...
- lostb1t 1y ago[flagged]
- sgarland 1y agoWhy 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 https://www.sqlite.org/lang_corefunc.html
- gorm 1y agoNice 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.
- orliesaurus 1y agoThis is such an interesting concept, thanks for sharing!
- neuroelectron 1y agoCVE-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.
- timz 1y agoFantastic, would love to see same js support for redis as oposed to lua scripts.
- rasz 1y agoHear me out guys - SQLite, but in a browser! We could call it WebSQL.
- pwdisswordfishz 1y agoThat's pretty much why it failed: https://www.w3.org/TR/webdatabase/ https://www.w3.org/TR/webdatabase/ > 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.
- rasz 1y agoIt stopped due to politics, specifically LOL arguments about "inelegant developer aesthetics" coming from Mozilla https://hacks.mozilla.org/2010/06/beyond-html5-database-apis-and-the-road-to-indexeddb/ https://hacks.mozilla.org/2010/06/beyond-html5-database-apis.... Somehow everyone and their dog using same Chrome codebase is fine, but oh no not the SLQ backend! 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.
- pwdisswordfishz 1y agoBut back then it wasn't a Chromium monoculture yet – Presto, Trident and KHTML were still going strong. That the commitment to multiple implementations is something the WHATWG takes much less seriously than the W3C once did is another matter.
- rcarmo 1y agoNice. Would prefer Lua though.
- 3cats-in-a-coat 1y agoI 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.
- Wheaties466 1y agoCan 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?