News for hackers. Stuff that matters.
Re:Showh HN: SQLite JavaScript - extend your database with J... by cal85 on Thursday May 22, 2025 @10:59AM (#44062763)
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?)
Re:Showh HN: SQLite JavaScript - extend your database with J... by sillystu04 on Thursday May 22, 2025 @11:19AM (#44062963)
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.

Re:Showh HN: SQLite JavaScript - extend your database with J... by cal85 on Thursday May 22, 2025 @12:11PM (#44063467)
> 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.)

Re:Showh HN: SQLite JavaScript - extend your database with J... by frollogaston on Thursday May 22, 2025 @01:18PM (#44064263)
First 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.
Re:Showh HN: SQLite JavaScript - extend your database with J... by intalentive on Thursday May 22, 2025 @03:47PM (#44066036)
If you have to pull the data into your application then it's all in memory at once. SQLite streams from disk, so the memory usage at any given time is less. Also, if the application language is slower than C, then you get a performance boost there as well.
Re:Showh HN: SQLite JavaScript - extend your database with J... by sgarland on Thursday May 22, 2025 @09:14PM (#44068870)
Because I/O isn’t free? If you can write code that does fewer things, it will be faster.