Accessing SQLite from Javascript

[NecroticNanite]

Just a question here - my use case is fairly specific, but I'd like to access the same database connection from both Javascript and Unity.
I'm assuming this should be possible so long as I can get the pointer across to Javascript.
Anyone have any examples of how i could go about this?

[gilzoide]

Yup, that's specific alright.
My suggestion is: if possible, implement SQL calls in C# and make JavaScript call C#, that's the most straightforward way.

I have alternatives to that, but all of them are much more complex. Please let me know if the JS to C# call works for you. I won't elaborate in case the easy way just works =]

[NecroticNanite]

Lots to try indeed! Will have a look, thanks. In my case, I'm less worried about locking as I only intend read only access (It's a product database - the WebUI shows things like product lists, shopping cart etc, Unity handles 3D rendering and proc gen of the scene. Database has product info as well as references to addressable assets). Re-using the same database memory is ideal, as we're already using a ton of memory and I'd like to avoid having 2 in-memory copies of the database (while it's small right now, it could rapidly increase)

[gilzoide]

I found something here that may work: callbacks to C# code in JS. Maybe you can register such a C# callback as a variable accessible in JS that runs queries and returns the results, then you call this from JS whenever necessary. Results could be stringified as JSON or something like that.

[NecroticNanite]

I guess in theory I can call C++ from Unity to store the intptr in C++ (the handle for the connection). Then I can call the normal sequence of:

sqlite3_prepare_v2 (Probably can skip binding for my use case and just create the string)
sqlite3_step
sqlite3_finalize

But my guess is Unity is obsfucating the wasm, so I'm not sure that will work, and I'd also need to get Unitys wasm context - which I can do.

export default function WasmTester() {
    const {UNSAFE__unityInstance} = useContext(UnityContext);
    if (!UNSAFE__unityInstance) return null;
    var wasm = UNSAFE__unityInstance.Module.asm;
    console.dir(wasm)
}

Looking at the Unity loader.js and framework.js, this 'asm' object is in fact the WebASM exports:

 function receiveInstance(instance, module) {
                var exports = instance.exports;
                Module["asm"] = exports;
                wasmMemory = Module["asm"]["gh"];
                updateMemoryViews();
                wasmTable = Module["asm"]["Ch"];
                addOnInit(Module["asm"]["hh"]);
                removeRunDependency("wasm-instantiate");
                return exports
            }

The output, unfortunately, looks like this:

Object
Ah: ƒ $Ah()
Bh: ƒ $Bh()
Ch: Table {length: 120833}
gh: Memory(16554)
hh: ƒ $hh()
ih: ƒ $ih()
jh: ƒ $jh()
kh: ƒ $kh()
lh: ƒ $lh()
mh: ƒ $mh()
nh: ƒ $nh()
oh: ƒ $oh()
ph: ƒ $ph()
qh: ƒ $qh()
rh: ƒ $rh()
sh: ƒ $sh()
th: ƒ $th()
uh: ƒ $th()
vh: ƒ $vh()
wh: ƒ $wh()
xh: ƒ $xh()
yh: ƒ $yh()
zh: ƒ $zh()

That's likely because no methods are currently exposed to emscripten, so I'm going to try something like this:

#include <emscripten/emscripten.h>

#include <sqlite3.h>

EXTERN EMSCRIPTEN_KEEPALIVE void myFunction(int argc, char ** argv) {
    printf("MyFunction Called\n");
}

[NecroticNanite]

Well, that was an unproductive rabbit hole!

Now trying the callback approach.

mergeInto(LibraryManager.library, 
{
    RegisterQueryMethod : function(callback)
    {
        window.SQL = window.SQL || {};
        
        window.SQL.Query = (query) => {
            // Convert JS string to Unity-compatible memory pointer
            var length = lengthBytesUTF8(query) + 1;  // Get string length in bytes (plus null terminator)
            var stringMessage = _malloc(length);      // Allocate memory
            stringToUTF8(query, stringMessage, length) // Write UTF-8 string to allocated memory
        
            // Call the Unity callback
            var result = {{{ makeDynCall('ii', 'callback') }}}(stringMessage);
            
            // Release the memory for the query            
            _free(stringMessage);

            // Convert result to JS
            var jsString = UTF8ToString(result);
            return jsString;
        };
    }
});

[gilzoide]

That's likely because no methods are currently exposed to emscripten

Maybe you could export the functions with compile flag -sEXPORTED_FUNCTIONS=... in Unity's PlayerSettings.WebGL.emscriptenArgs 🤔 (Emscripten docs)

[NecroticNanite]

Ok, so that seems to be the best bet - Using callbacks.
In Plugins, add a jslib file:

mergeInto(LibraryManager.library, 
{
    RegisterQueryMethod : function(callback)
    {
        window.SQL = window.SQL || {};
        
        window.SQL.Query = (query) => {
            // Convert JS string to Unity-compatible memory pointer
            var length = lengthBytesUTF8(query) + 1;  // Get string length in bytes (plus null terminator)
            var stringMessage = _malloc(length);      // Allocate memory
            stringToUTF8(query, stringMessage, length) // Write UTF-8 string to allocated memory
        
            // Call the Unity callback
            var result = {{{ makeDynCall('ii', 'callback') }}}(stringMessage);
            
            // Release the memory for the query            
            _free(stringMessage);

            // Convert result to JS
            var jsString = UTF8ToString(result);
            return jsString;
        };
    }
});

(Likely need to edit this accordingly and add each query you want to execute to the JS Lib, or add an argument for the return type you are expecting)

Then in C#

[DllImport("__Internal")]
private static extern void RegisterQueryMethod(Func<string, string> callback);

[MonoPInvokeCallback(typeof(Func<string, string>))]
public static string ExecuteQuery(string query)
{
    Debug.Log($"C# callback received \"{query}\"");
    // TODO: Execute query on the connection here
    return "";
}

Usage from JS is

window.SQL.Query("This is a test")

[gilzoide]

Nice! If the callback is already working, turning it into a SQL query runner should be easy enough =]

(Likely need to edit this accordingly and add each query you want to execute to the JS Lib, or add an argument for the return type you are expecting)

SQLite uses flexible typing, so it's ok to read every column as string (that's what sqlite3_exec does) and let JS do the type conversions according to what it expects from the query it just ran. You could have a single method that returns all resulting rows as a string[][]: the Array of rows, where each row is an Array of columns, all of them strings. You could use JSON there to simplify the generation of the result from the C# side, I'm not sure how creating JS Arrays would look like from C# although it should be possible.

[NecroticNanite]

Figure that this works, unless there's a simpler way? Wanted to pool the lists (My own implementation, lists are cleared on release) since this might be called a lot and reducing allocations can't hurt, especially when there's a lot of string allocations already.

private void SimpleStringQuery(string query, List<List<string>> results)
{
    var stmt = SQLite3.Prepare2 (m_connection.Handle, query);
    try
    {
        while (SQLite3.Step(stmt) == SQLite3.Result.Row)
        {
            List<string> row = ListPool<string>.Get();
            for (int i = 0; i < SQLite3.ColumnCount(stmt); i++)
            {
                row.Add(SQLite3.ColumnString(stmt, i));
            }
            results.Add(row);
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        results.Clear();
    }
    finally
    {
        SQLite3.Finalize(stmt);
    }
}

[DllImport("__Internal")]
private static extern void RegisterQueryMethod(Func<string, string> callback);

[MonoPInvokeCallback(typeof(Func<string, string>))]
public static string ExecuteQuery(string query)
{
    using var scopedList = new ListPoolScope<List<string>>();
    
    Instance.SimpleStringQuery(query, scopedList.List);
    string json = JsonUtility.ToJson(scopedList.List);
    for (int i = 0; i < scopedList.Count; i++)
    {
        ListPool<string>.Release(scopedList[i]);
    }
    return json;
}

[gilzoide]

unless there's a simpler way?

Well, simpler is debatable, but one way would be to use a global JS variable for storing results and call JS functions from C# to push the results, something in the lines of void AddRow() and void AddColumn(string). So you maintain a global string[][] in JS, clear it before starting the query, AddRow pushes a new empty array to it, and AddColumn pushes a new string into the last pushed row. This removes JSON marshaling and some memory "duplication" since only JS holds the whole result. Although now you have much more string marshaling from C# to JS...
JS and C# are usually very good at allocating and throwing away stuff, unless you are running several queries every frame from JS, such optimization may not matter that much.

So, speaking about optimizations, I'd say maybe just employ what's easiest and profile to see if it will be a bottleneck or not. Lots of times we worry too much around something and it may not be perfect, but there are other things that bring much more value then what you thought would be necessary and is not really. Of course, it depends a lot, but remember the number 1 rule of optimization: "don't do it". Then there's rule number 2 (for experts only): "don't do it yet". Profile first, because it may not be relevant at all!