Custom DB functions?

[twoxfh]

Great project! I do not see a discussions tab so I will ask the question here.

In SQL-JS I use custom database functions, but I do not see a way to add these without building a custom package, am I missing something? Is there a way to interact directly with the sqlitedb instance?

Appreciate the feedback.

[WebReflection]

you're right, both .close() and .create_function(name, func) are not exposed/available, but I guess these should.

[onlyfortesting]

+1

I need the classic db.prepare because I usually create function like this:

function insertNote(toInsert) {
    const keys = Object.keys(toInsert)

    // Doing this because some columns are optional
    const info = db.prepare(`INSERT INTO note
        (${keys.join(',')})
        VALUES
        (${keys.map(k => '@' + k).join(',')})
    `).run(toInsert)

    ...
}

I cannot make it work with sqlite-tag. Are there any workaround?

[WebReflection]

@onlyfortesting what has prepare to do with exposure of close and create function? that + the fact you don't need to prepare anything with sqlite-tag, that's the whole idea, but transaction with commit eventually works.

[WebReflection]

@twoxfh both close and create_function are in. Bear in mind when used through the Worker create_function can accept only pure functions without any outer scope access, because functions don't survive the post message dance. This limitation does not exist when used directly through the initializator.

[twoxfh]

Great..I tested as well. Not sure if it needs mentioning, if an error is thrown in a custom function the current and all queries after, stop.. You can use it to count running time on queries and throw errors for long running queries. Of course if it's a long running row evaluation it does not help. Emscripten is working on WASMFS which will use pthreads and the you can kill the thread for row evaluation issues.

[WebReflection]

if an error is thrown in a custom function the current and all queries after, stop

uhm ... I should likely wrap that in a try/catch then .. would that fix the issue?

[onlyfortesting]

@WebReflection Yep. It has nothing to do with those methods. But, since OP also asked if there is a way to interact directly with the sqlitedb instance (which I also wanted to do) so I thought I'd rather comment on this issue as well than creating a new one.

Anyway, I have found solution to my problem. Basically, I'm trying to do this query with sqlite-tag

const keys = Object.keys(toInsert)
db.query`
  INSERT INTO notes (${db.raw`${keys.join(',')}`})
  VALUES (${keys.map(c => toInsert[c]).joins(db.raw`,`)}) -- this part throws`

Which doesn't work. So, I originally thought it can't be done without exposing .prepare. Turns out I just don't completely understand how tagged template works 😝. Solved by removing .joins(db.raw`,`) part.

Thanks for such a great lib, btw