bun:sqlite custom function extension

[liburno]

What is the problem this feature would solve?

I've been working with your SQLITE module in the bin project and noticed that the function method, similar to what's available in better-sqlite3, is missing. This method is essential for my use case, as it allows registering custom functions to be called directly from SQL queries.

Having the capability to extend SQLite's functionalities with custom operations directly from SQL is immensely valuable and streamlines the development process. It reduces the need to pull out data, manipulate it in JavaScript, and then reinsert it back.

It would be great if the bin project's SQLITE module could incorporate a similar function method. This would surely enhance the module's usability and provide flexibility to the developers.

Thank you for considering this suggestion. I believe it would be a significant improvement to the project.

What is the feature you are proposing to solve the problem?

Feature Proposal: Custom SQL Function Registration

I propose the addition of a function method, akin to what exists in better-sqlite3. This method would allow users to register custom functions that can be called directly within SQL queries.

Benefits:

• Extended Functionality: Developers can enhance SQLite's native capabilities with functions tailored to their specific needs without resorting to external manipulations.
• Streamlined Development: Direct integration of custom functions can simplify code, reduce the need to extract data for external manipulations in JavaScript, and then reinsert it, leading to more efficient and readable code.
• Flexibility: This feature empowers developers to build more dynamic and versatile applications using the bun:sqlite module. Implementation:

The method could look something like this:

//  example Register a custom function that doubles a value
db.function('DOUBLE', (value) => value * 2);

Once registered, the custom function would be available to be called directly from SQL queries:

// Now you can use this function in your SQL queries
const row = db.prepare('SELECT DOUBLE(10) AS result').get();
console.log(row.result);  // Outputs 20

What alternatives have you considered?

Honestly, I haven't considered any alternatives within the bun framework because the ability to register custom SQL functions is a critical requirement for my projects.

If this feature isn't supported, I might have to stay with node.js , despite really wanting to use bun. It's crucial for my projects to seamlessly integrate custom SQL functions directly.

[neves]

I would like to vote up this feature. UDF is a huge advantage of sqlite over other databases

[parweb]

oh yeah

[Dan1ve]

Really needing this, too. In case it helps, this is the implementation in better-sqlite3

[nikeee]

I've started working on this in https://github.com/oven-sh/bun/compare/main...nikeee:bun:feature/sqlite-udf-5051. Still figuring out how to handle the callback (first time wrinting for JSC). Note to myself: better-sqlite3's implementation for v8 Before I can continue, I have to finish my dev setup, so I can also contribute proper tests. Maybe someone else wants to pick it up in the meantime.

[Vanilagy]

Still waiting on any maintainer's feedback for your PR, @nikeee. Alternatively, the Bun team could also just expose the pure SQLite C API, which would allow anybody to do anything. Still blocked on using Bun until this thing gets fixed 💔

[yg-i]

Same. It would be very useful to be able to do this like with better-sqlite3:

	db.function('REGEX', (pattern, str) => new RegExp(pattern).test(str) ? 1 : 0 )

[Dan1ve]

Still the main blocker for our adoption of Bun. Maybe exposing the C API as suggested by @Vanilagy could be a quick(er) fix, @Jarred-Sumner ?

[Vanilagy]

There is an advantage to not operating on the raw C API but instead on wrapper functions provided by Bun, mainly due to performance improvements. When manually using the C API, all the operations like stepping over a statement and reading column values have to be done from the JS side in order to create objects from the query result, meaning a lot of performance goes down the drain. Bun achieves a performance gain by doing this step in Zig. So, in general, it's still good to have a higher-level API around for performance.

But the flipside is a lack of capabilities. We're limited to using exactly those higher-level primitives provided and cannot do anything else. Exposing a lower-level API would at least give us the ability to fully utilize everything SQLite has to offer. The C API is a bit clunky to work with as it doesn't map neatly onto JavaScript (JavaScript doesn't have a pointer primitive, so you end up passing around numbers) - but it's been done before!