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 😎