Add synchronous API for in-memory WASM SQLite

[krolebord]

In memory WASM SQLite supports executing queries synchronously in the main thread, without promises, but currently SQLocal interface expects drivers to be asynchronous

It might be good to have a separate Promise-less SQLocal interface (SQLocalSynchronous or something similar)

export interface SQLocalSynchronous {
    sql: <Result extends Record<string, any>>(queryTemplate: TemplateStringsArray | string, ...params: unknown[]) => Result[];
    batch: <Result extends Record<string, any>>(passStatements: (sql: typeof sqlTag) => Statement[]) => Result[][];
    beginTransaction: () => Transaction;
    transaction: <Result>(transaction: (tx: {
        sql: Transaction["sql"];
        query: Transaction["query"];
    }) => Result) => Result;
    createCallbackFunction: (funcName: string, func: CallbackUserFunction["func"]) => void;
    createScalarFunction: (funcName: string, func: ScalarUserFunction["func"]) => void;
    createAggregateFunction: (funcName: string, func: AggregateUserFunction["func"]) => void;
    getDatabaseInfo: () => DatabaseInfo;
    destroy: () => void;
    [Symbol.dispose]: () => void;
}

I can work on the implementation, if this API makes sense

[DallasHoff]

The idea is that the SQLocal interface should work the same no matter which storage strategy you use. That way, you can easily swap or fall back to a different storage strategy without having to modify any of your other code. What do you suggest is the benefit of having a synchronous version?

[krolebord]

Thank you for the reply
I understand the goal of having a unified API for different storage strategies.

My proposal is about adding a separate lightweight interface for synchronous drivers (currently it is possible only for in-memory db). The existing API will not be changed. It is more about porting existing SQLocal convenience methods (like sql tag, transactions, batch statements, createFunction, etc)

This can be especially useful for UI libraries, allowing to synchronously fetch data during the initial render and display it immediately, and it also eliminates promise overhead, since existing in-memory driver runs queries synchronously in the main thread anyway.

I'm currently building a small library for reactive SQLite queries (similar to PGLite's live module) that re-run when underlying tables change. I use SQLocal for persistent storage and in-memory SQLite for optimistic updates with live queries. Over time I realized that I am just implementing this Sqlocal-like synchronous interface myself and thought that it might make sense to add it as a separate SQLocal feature.