Hacker News Reader

Comment by jmull | Hacker News Reader

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/

View on HN

This doesn’t make sense to me. SQLite is an embedded database, all the app code is already local to the storage.

Well, you can still have an app server if you want -- having app logic that runs local to the storage doesn't preclude you from also having an "app server" (that is, a place app logic runs that isn't local to the user and isn't local to the storage, but somewhere in between.)

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.

You could of course do that, but the main reason to use SQLite in a server application is for the performance characteristics of staying in-process for accessing the database. This makes it cheap to make queries in response to a previous query, which would need to somehow be batched for good latency with a client/server database. Re-adding the client server model defeats almost all of the PROs of SQLite, leaving you with only the CONs.

Traditional client/server databases let you run app logic local to the storage. E.g. stored procedures. There's no particular advantage for sqlite there.

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

I feel like this would be more about validation and constraint enforcement than full on application functions.

Given that, though, JavaScript feels like a bit of an odd choice for language.

Plus if you want to use functions inside queries, SQLite UDFs can be used from any language. E.g. for Node the better-sqlite3 binding has supported UDFs for ages; the new experimental node:sqlite also appears to have support[1][2], though I haven't used it. For Python UDFs are supported out of box. Etc. I can't see myself loading a js extension with a bundled engine exposing the same UDF capability when I can just use UDFs from the host language (which can be js with v8 already there) with access to whatever application state I want to expose, and arbitrary library code, rather than just the bare bones standard library shipped with QuickJS. What's the use case? Use rather awkward js functions in the sqlite3 CLI?

[1] https://nodejs.org/api/sqlite.html#databasefunctionname-opti...

[2] https://nodejs.org/api/sqlite.html#databaseaggregatename-opt...

This stores the UDFs in the database. Which means each database can have its own UDFs.

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.

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.

The other reason to not do this is that most functions someone might want to add are very simple, enough so that even if you don’t know C, you can probably muddle your way through it.

JS in an RDBMS… shudder

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

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.

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

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.

There is also https://github.com/ricomariani/CG-SQL-author that has a powerful stored procedure capabilities that can be transpiled to C/Lua/..., you can try it in your browser here https://mingodad.github.io/CG-SQL-Lua-playground .

the sqlite database engine runs in-process. Whatever language you embed it in, is the "native" compute language for it.

Reply on news.ycombinator.com