question: .function API with user defiend async function/Promise ?

[yw662]

Can I make sqlite3 work with user defined async function ?
sth. like

db.function('foo', () => new Promise((s,j)=>setTimeout(s, 1000, 1)));
db.prepare('select foo();').get();

This will result in TypeError: User-defined function foo() returned an invalid value for now. Any work around for this ?

[yw662]

I realised this is hard in better-sqlite3, since it uses sync API. However I still want to see some ideas on this.

[JoshuaWise]

You should never keep SQLite3 transactions open across event loop ticks in Node.js. SQLite3 serializes all statements/transactions. This means, if you try to run an asynchronous function within an SQL statement, you'll block all other statements/transactions across your entire program. better-sqlite3 doesn't support it, and it never will, because it's a very bad idea.

Instead, you should gather all information required for your transaction (reading files, waiting on sockets, etc.) BEFORE you start the transaction. Then, when everything is ready, run the transaction synchronously, within a single event loop tick.

[Prinzhorn]

We might get to jail for this if the JavaScript police catches us, but I'm taking the risk. If you absolutely know what you are doing and there is absolutely no other (proper) way, then you could use https://github.com/ForbesLindesay/sync-rpc which essentially abuses spawnSync to execute asynchronous operations in a new process but makes them look synchronous in the main process.

This has very limited use-cases but it can be useful. For example I would never recommend this at all in a server side application with multiple clients. However, in my case it's an Electron app and the user of said app can do cool things with SQL. On their own computer. In their private space. No JavaScript police in sight. There's a dedicated process/thread with a readonly SQLite client for these user-issued queries (shout outs to #311). So in this case performance is not a key concern. The fact that the thing you're doing is possible at all is the main concern. It doesn't matter much if it takes 10ms or 10s.

You have been warned.

[Prinzhorn]

To whoever it may concern: spawning a process for every single call is not a great idea. Shockingly, I know. https://github.com/mmhunter/node-sync-ipc does a similar thing but uses synchronous pipe operations to communicate with another process.

This is not a proper benchmark but the order of magnitude roughly looks like this (using a SELECT with a few rows and a function call for each row)

• 1ms for a bunch of regular user-defined function calls
• 100ms for the same synchronous function but inside another process
• 10,000ms for the same synchronous function but with a new process for every call

So depending on your use-case these might be acceptable numbers or not.

[Prinzhorn]

Looks like someone found a way to use Worker Threads instead of sub processes. Seems to perform reasonably well if you really need to use an async API and you have no alternative https://github.com/un-ts/synckit

[FunctionDJ]

We might get to jail for this if the JavaScript police catches us, but I'm taking the risk. If you absolutely know what you are doing

when a github comment starts like this, you know it's gonna be good 😎

[punkish]

You should never keep SQLite3 transactions open across event loop ticks in Node.js. SQLite3 serializes all statements/transactions. This means, if you try to run an asynchronous function within an SQL statement, you'll block all other statements/transactions across your entire program. better-sqlite3 doesn't support it, and it never will, because it's a very bad idea.

Instead, you should gather all information required for your transaction (reading files, waiting on sockets, etc.) BEFORE you start the transaction. Then, when everything is ready, run the transaction synchronously, within a single event loop tick.

This is great advice, much appreciated. However, there are legitimate instance when the above is not possible. Here is a case I am currently tackling --

I am loading a lot of text documents in the db in a "source" table, creating an FTS5 index of the text, and also creating a vector database of the embeddings of the text. My problem arises in loading the vectors that have to be requested asynchronously from an LLM model (also running on my laptop).

Being able to do the above in an "after insert in table" trigger (would) greatly simplify my ETL process as I would have to worry about only the loading in the source table. In this case, while I do care about the time take to complete the process, I am trying to balance it with the complexity of the process.

Each document loaded fires off triggers to load about ten related tables including the FTS5 tables. If I could add the vector creation and loading as well, each document would be split into potentially hundreds of chunks, each chunk would be converted to embeddings, and loaded as well. And, I load 5000 documents at a time in a transaction for total of about a million docs.

When I discovered the magic of db.table() virtual table support, it was wonderful. I wrote a test function that split the text and inserted the chunks in a trigger (this was without async). However, I was disappointed when I tried to implement the real function that calls the LLM asynchronously to generate the embeddings. Gathering all the embeddings for potentially 10s of 1000s of text chunks before running the transaction would greatly increase my memory requirements.

Complexity is like a waterbed. I press in one place, it rises in another. What is the point of making my transactions faster when I still have to wait outside the transactions? This, esp. so when running a transaction, albeit one that is blocking and slow, would greatly simplify my code?

db.table('SplitText', {
    columns: [ 'chunk', 'embedding' ],
    rows: async function* (cleanedText) {
        const chunks = await splitter.splitText(cleanedText);
        const model = 'nomic-embed-text';
        const source = { model, input: chunks };
        const res = await ollama.embed(source);
        const embeddings = res.embeddings;
        for (let i = 0, j = embeddings.length; i < j; i++) {
            yield [ chunks[i], embeddings[i] ];
        }
    }
});