Lock error using existing database

[dpmccabe]

Is there a way to initialize a database using an existing SQLite file? For instance, here's what it might look like if I combine your example repo with a sql.js example to load an existing database from a URL:

import initSqlJs from '@jlongster/sql.js';
import { SQLiteFS } from 'absurd-sql';
import IndexedDBBackend from 'absurd-sql/dist/indexeddb-backend';

async function init() {
  let SQL = await initSqlJs({ locateFile: file => file });
  let sqlFS = new SQLiteFS(SQL.FS, new IndexedDBBackend());
  SQL.register_for_idb(sqlFS);

  SQL.FS.mkdir('/sql');
  SQL.FS.mount(sqlFS, {}, '/sql');

  // host an example file somewhere with permissive CORS settings
  const data = await fetch("https://example.com/chinook.db").then(res => res.arrayBuffer());
  const db = new SQL.Database(new Uint8Array(data));
  return db;
}

async function runQueries() {
  let db = await init();

  const stmt = db.prepare("SELECT Name FROM artists;");
  stmt.step(); // Execute the statement
  console.log(stmt.getAsObject());
}

runQueries();

This is the error that gets generated:

index.js:149 Uncaught (in promise) TypeError: Cannot read property 'lock' of undefined
    at SQLiteFS$1.lock (index.js:149)
    at eval (sql-wasm.js:93)
    at sql-wasm.wasm:0xf7cec
    at sql-wasm.wasm:0x9e651
    at sql-wasm.wasm:0x2a775
    at sql-wasm.wasm:0xd83d
    at sql-wasm.wasm:0xe7e5
    at sql-wasm.wasm:0xebb41
    at sql-wasm.wasm:0x986a
    at sql-wasm.wasm:0x27d84

Maybe it makes sense that it doesn't work. Your version of SQL.Database doesn't let you specify a filename if you give it a data array instead of a filename. There might be other limitations preventing this pattern from working, but I'm not sure.

This might be a trivial fix to your sql.js fork, but I'm unable to try it out since I've had all sorts of problems getting local versions of absurd-sql and sql.js to work with the example project.

[jlongster]

I am so sorry for the delay here! Work got super busy right after I launched this because I had put off some stuff for this, but I'll be going through the issues soon. Thanks so much!

[dpmccabe]

No worries, it's FOSS after all. Thanks again for making this library. Being able to import/export an existing SQLite database would basically make irrelevant the one thing PouchDB still has going for it (replicated sync), and I'd love to get out of PouchDB hell...

[urdeveloper]

@dpmccabe , In case you're still looking for a solution, I've made a slight change to @jlongster/sql.js and published it as @urdeveloper/sql.js. You'll be able to create the db from an existing one:

import initSqlJs from '@urdeveloper/sql.js';
import { SQLiteFS } from 'absurd-sql';
import IndexedDBBackend from 'absurd-sql/dist/indexeddb-backend';

async function init() {
  let SQL = await initSqlJs({ locateFile: file => file });
  let sqlFS = new SQLiteFS(SQL.FS, new IndexedDBBackend());
  SQL.register_for_idb(sqlFS);

  SQL.FS.mkdir('/sql');
  SQL.FS.mount(sqlFS, {}, '/sql');

  // host an example file somewhere with permissive CORS settings
  const data = await fetch("https://example.com/chinook.db").then(res => res.arrayBuffer());
  const db = new SQL.Database(new Uint8Array(data), {filename: '/sql/chinook.db'});
  return db;
}

async function runQueries() {
  let db = await init();

  const stmt = db.prepare("SELECT Name FROM artists;");
  stmt.step(); // Execute the statement
  console.log(stmt.getAsObject());
}

runQueries();

[dpmccabe]

Thanks for the fix. I was able to get that code to work with absurd-example-project, although only if I used 0.0.49 of absurd-sql or earlier. Starting with 0.0.50, I get this error:

sql-wasm.js:108 Uncaught (in promise) RuntimeError: abort(TypeError: Cannot read properties of undefined (reading 'constructor')). Build with -s ASSERTIONS=1 for more info.
    at Q (sql-wasm.js:108)
    at F (sql-wasm.js:187)
    at sql-wasm.wasm:0xf4a4c
    at sql-wasm.wasm:0xf1a64
    at sql-wasm.wasm:0x5c411
    at sql-wasm.wasm:0xf233a
    at Module._sqlite3_open (sql-wasm.js:202)
    at Ia (sql-wasm.js:102)
    at sql-wasm.js:205
    at new c (sql-wasm.js:75)

Maybe this is just a problem with the distributed wasm files on npm?

[urdeveloper]

From which package you're using sql-wasm.wasm?

[dpmccabe]

Hmm, never mind. When I copied a fresh copy of sql-wasm.wasm from @urdeveloper/sql.js/dist/ it worked this time. Feel free to close this issue.

[awmuncy]

@urdeveloper Are you planning on submitting your change in a PR? I find your changes very useful.

[urdeveloper]

@awmuncy, I'd love to but I'm pretty sure, it's not going to go anywhere. jlongster himself also made a PR on the upstream sql.js and which never been merged. Th good thing is you have your own fork because both absurd-sql and sql.js authors were very generous with the licensing. Cheers!