Multiple connection support?

[ara4n]

Is there any way to model multiple connections to the same DB in sql.js (with whatever transaction isolation between them), or any way to fake the same effect?

[brody4hire]

Nice idea. I would suggest asking on the SQLite mailing list if there could be any way to support multiple connections to a memory database (it would actually be nice for me as well).

Alternative could be some kind of a virtual shared memory database or "ramdisk". I found some interesting here: https://nedbatchelder.com/blog/201105/running_a_database_from_a_ramdisk.html

[ara4n]

mm, thanks for the hint. Looks like https://www.sqlite.org/inmemorydb.html can indeed support multiple connections, either with or without shared cache. However, I'm not sure that sql.js is using in-memory? From looking at api.coffee, it seems to create a fake dbfile of some kind:

class Database
    # Open a new database either by creating a new one or opening an existing one,
    # stored in the byte array passed in first argument
    # @param data [Array<Integer>] An array of bytes representing an SQLite database file
    constructor: (data) ->
        @filename = 'dbfile_' + (0xffffffff*Math.random()>>>0)
        if data? then FS.createDataFile '/', @filename, data, true, true
        @handleError sqlite3_open @filename, apiTemp

...which doesn't look like it exposes the ability to open the file multiple times. Conversely, it looks like it might be fairly easy to add...

[ara4n]

...completely untested, i think something like this could work, by expanding Database() to take either a byte array or an existing Database object if you're opening a new connection to the same DB.

diff --git a/src/api.coffee b/src/api.coffee
index f11ea80..311a210 100644
--- a/src/api.coffee
+++ b/src/api.coffee
@@ -237,9 +237,14 @@ class Database
     # Open a new database either by creating a new one or opening an existing one,
     # stored in the byte array passed in first argument
     # @param data [Array<Integer>] An array of bytes representing an SQLite database file
-    constructor: (data) ->
-        @filename = 'dbfile_' + (0xffffffff*Math.random()>>>0)
-        if data? then FS.createDataFile '/', @filename, data, true, true
+    # @param database [Database] An existing database we're opening another connection to
+    constructor: (data, database) ->
+        if database?
+            @filename = database.filename
+            @isconn = true
+        else
+            @filename = 'dbfile_' + (0xffffffff*Math.random()>>>0)
+            if data? then FS.createDataFile '/', @filename, data, true, true
         @handleError sqlite3_open @filename, apiTemp
         @db = getValue(apiTemp, 'i32')
         RegisterExtensionFunctions(@db)
@@ -424,7 +429,7 @@ class Database
         removeFunction(func) for _,func of @functions
         @functions={}
         @handleError sqlite3_close_v2 @db
-        FS.unlink '/' + @filename
+        if not @isconn then FS.unlink '/' + @filename
         @db = null
 
     ### Analyze a result code, return null if no error occured, and throw

[brody4hire]

+    constructor: (data, database) ->

What if someone wants to open with existing database object but no data object?

Looks nice, though:)

[ara4n]

it's a hacky API (mainly so we can use it in a demo tomorrow), but you'd pass undefined as the first param.

[lovasoa]

I'm not sure what you mean by "whatever transaction isolation between them". Javascript does not have parallel execution, so I don't see in what circumstances having multiple connections would have any advantage.

[neilalexander]

so I don't see in what circumstances having multiple connections would have any advantage.

It matters because starting transactions often involves opening new connections.

[lovasoa]

Okay, I'm sorry for the misunderstanding. Tagging this as enhancement.