bun:sqlite custom function extension

liburno commented 9 months ago

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 commented 9 months ago

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

parweb commented 8 months ago

oh yeah

Dan1ve commented 7 months ago

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

nikeee commented 5 months ago

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 commented 1 month ago

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 commented 2 weeks ago

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 )

oven-sh / bun