SQLite-Wasm: worker1-promiser cant open db
(1.1) By Fake User (FakeUser) on 2024-02-04 23:58:19 edited from 1.0 [link] [source]
Does anyone know how to open a db with Worker1-Promiser.js?
I'm attempting to load and query a db with worker1-promiser.js
I'm using the same folder structure as the demo files,
But I cant seem to open the db.
If you want to take a gander, the full repo is here.
I'm running it with live-server.
You can inspect a live version here.
<script src="./jswasm/sqlite3-worker1-promiser.js"></script>
<script>
// use sqlite3Worker1Promiser.defaultConfig
const promiser = self.sqlite3Worker1Promiser();
// load db
promiser("open", {"filename": "./samples.sqilte"});
async function querydb(query){
// execute query
await promiser("exec", {"sql": query})
// insert res into <p>
.then(res => {document.querySelector("p").innerText = JSON.stringify(res)});
};
// query db
querydb("SELECT * FROM samples;");
</script>
running querydb("SELECT * FROM samples;") in the console gives a very long error,
but the gist is { operation: "exec", message: "DB is not opened.", errorClass: "Error", … }
Use Case / Context
Im working on an audio library. You can take a peek here
The db is read only, & I doubt it will ever have more than 10k rows.
Im currently using the free tier of Turso, which has a really bad cold start.
Statically serving everything would be cheap, fast, and awesome.
(2) By Stephan Beal (stephan) on 2024-02-04 23:58:05 in reply to 1.0 [link] [source]
promiser("open", {"filename": "./samples.sqilte"});
First off, try removing the "." from that. OPFS does not have the concept of "current directory" and may well be confused by telling it to use one with "./". Try "/samples.sqlite" instead.
Secondly:
promiser("open", {"filename": "./samples.sqilte"});
That is run async, so you'll need to "await" it or add your querydb() call to a then() handler on that response's Promise. Something like (untested):
promiser("open", ...).then(()=>querydb("select * from samples"));
which would require moving the querydb() definition above the "open" call.
Please let us know if one or both of those resolve the issue.
(3) By Fake User (FakeUser) on 2024-02-05 00:16:37 in reply to 2 [link] [source]
Woah, super fast reply. Thank you!
I tried both, but neither seem to have fixed it.
pushed the changes to the repo and the live version.
here is the present attempt:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
</head>
<body style="font-size: 2rem; color: #fff; background-color: #111;">
<p>This is where the querydb response should go<br>
<script src="jswasm/sqlite3-worker1-promiser.js"></script>
<script>
// use sqlite3Worker1Promiser.defaultConfig
const promiser = self.sqlite3Worker1Promiser();
async function querydb(query){
// execute query
await promiser("exec", {"sql": query})
// insert res into <p>
.then(res => {document.querySelector("p").innerText = JSON.stringify(res)});
};
// open db & query samples table
promiser("open", {"filename": "/samples.sqilte"}).then(()=>querydb("select * from samples"));
</script>
</body>
</html>
(4) By Stephan Beal (stephan) on 2024-02-05 00:23:58 in reply to 3 [source]
I tried both, but neither seem to have fixed it.
The dev console tells you what the problem is:
Ignoring inability to install OPFS sqlite3_vfs: Cannot install OPFS: Missing SharedArrayBuffer and/or Atomics. The server must emit the COOP/COEP response headers to enable those. See https://sqlite.org/wasm/doc/trunk/persistence.md#coop-coep
(5.1) By Fake User (FakeUser) on 2024-02-05 00:52:53 edited from 5.0 in reply to 4 [link] [source]
Good catch, thank you!
I tried adding COOP/COEP headers with a _headers file.
here is a live version
/*
Cross-Origin-Embedder-Policy: require-corp
Cross-Origin-Opener-Policy: same-origin
but it still errors with:
OPFS syncer: Error initializing OPFS asyncer: [object Event]
and warns with:
Ignoring inability to install OPFS sqlite3_vfs: Loading OPFS async Worker failed for unknown reasons.
(6) By Stephan Beal (stephan) on 2024-02-05 01:32:37 in reply to 5.1 [link] [source]
but it still errors with:
The dev console is telling me that fetching:
https://655e397f.sqlite-wasm-test.pages.dev/jswasm/sqlite3-opfs-async-proxy.js
is failing for an unspecified reason. Without that file, that VFS cannot work.
(7) By Fake User (FakeUser) on 2024-02-05 01:43:59 in reply to 6 [link] [source]
Very odd, its definitely in the repo, and I havent seen that error in Chrome, Firefox, or Safari.
I redownload and replaced all the jswasm & common files from the demo.
The live version wont throw errors on page load,
but it still wont load the db.
(8) By Stephan Beal (stephan) on 2024-02-05 01:45:43 in reply to 7 [link] [source]
I redownload and replaced all the jswasm & common files from the demo.
Trying to load the offending file directly, as opposed to as part of the breaking page, tells me (in the dev console):
The resource from “https://655e397f.sqlite-wasm-test.pages.dev/jswasm/jswasm/sqlite3-worker1-promiser.js” was blocked due to MIME type (“text/html”) mismatch (X-Content-Type-Options: nosniff).
That indicates a server-level configuration problem. It should not be serving .js files with a mime type of text/html (and it doesn't for the other files, so i cannot even speculate as to why it does for this specific one).
(10) By Fake User (FakeUser) on 2024-02-05 02:18:25 in reply to 8 [link] [source]
Very strange, I don't have any MIME errors.
This live version fetches everything fine in the dev tools network tab.
| Status | Method | File |
|---|---|---|
| 200 | GET | / |
| 200 | GET | sqlite3-worker1-promiser.js |
| 200 | GET | favico.ico |
| 200 | GET | sqlite3-worker1.js |
| 200 | GET | sqlite3.js |
| 304 | GET | sqlite3.wasm |
| 200 | GET | sqlite3-opfs-async-proxy.js |
Did you just link cloudflare headers in the COOP/COEP documentation?
If that was you, very clutch 👍
(11) By Stephan Beal (stephan) on 2024-02-05 02:27:37 in reply to 10 [link] [source]
This live version fetches everything fine in the dev tools network tab.
The current version is working (or not failing) for me, but see my response about the VFS.
Did you just link cloudflare headers in the COOP/COEP documentation?
Indeed (thank you for the link), and i added a note to the Worker1 docs to reinforce that specifying a VFS is required if the user wants a persistent db.
(9) By Stephan Beal (stephan) on 2024-02-05 02:02:59 in reply to 7 [link] [source]
The live version wont throw errors on page load,
i should have recognized this sooner, but you are telling it to load a transient db which gets recreated on each request. If you want a persistent db, you need to tell it which VFS to use:
promiser("open", {"filename": ..., "vfs": "opfs"});
(12) By Fake User (FakeUser) on 2024-02-05 02:33:41 in reply to 9 [link] [source]
Here's the newest live version.
Dang, promiser("open", {"filename": "/samples.sqlite", "vfs": "opfs"}) still wont open the db
(13) By Stephan Beal (stephan) on 2024-02-05 02:53:21 in reply to 12 [link] [source]
Dang, promiser("open", {"filename": "/samples.sqlite", "vfs": "opfs"}) still wont open the db
i can reproduce this locally, where the "open" message is sent but no response is ever provided, but am at a complete loss as to how to explain it. The main test/demo app is running fine, so it's not a global problem.
i'm looking into why it's silently failing for this case and will post here when there's new info.
(14.2) By Stephan Beal (stephan) on 2024-02-05 03:06:18 edited from 14.1 in reply to 13 [link] [source]
i'm looking into why it's silently failing for this case and will post here when there's new info.
Found it in a comment in the docs (which i'd long-since forgotten):
The config object is technically optional but its
onreadyproperty is effectively required because it's the only way to be notified when the asynchronous loading and initialization of the sqlite3 module is complete.
What's missing is the onready handler and your example is failing because it's sending the "open" message before the promiser has finished loading and initializing the sqlite3 module.
So, for your example, something like:
<script> const querydb = async function(query){ promiser("exec", {"sql": query}) .then(res => {document.querySelector("p").innerText = JSON.stringify(res)}); }; const run = function(){ promiser("open", {filename: "/samples.sqlite", vfs: "opfs"}). then(()=>querydb("select * from samples")); }; const promiser = globalThis.sqlite3Worker1Promiser(run); </script>
That will fail with:
SQLITE_ERROR: sqlite3 result code 1: no such table: samples
unless you have already installed the OPFS-side db.
(15.1) By Fake User (FakeUser) on 2024-02-05 03:40:55 edited from 15.0 in reply to 13 [link] [source]
Deleted(16.1) By Stephan Beal (stephan) on 2024-02-05 03:41:34 edited from 16.0 in reply to 15.0 [link] [source]
I'll keep this thread up to date if I find a solution
In case you missed it (our messages overlapped), this has been resolved - see the response parallel to the one this post is responding to.
(Edit: nevermind - you apparently caught that as this message was being written!)
(17) By Fake User (FakeUser) on 2024-02-05 03:54:27 in reply to 16.1 [link] [source]
I'm a bit lost, but I'll try and implement your recommendations. Thanks for the all help and sticking to it!
This use case could be nice to have in the docs or bundled with the other demos.
(18) By Fake User (FakeUser) on 2024-02-05 07:51:49 in reply to 16.1 [link] [source]
Alright, I think I've implemented all your recommendations
But I still cant seem to query samples.sqlite
- sqlite3Worker1Promiser() takes a config object with an onready property as a parameter.
- The promiser("open", {filename: "/samples.sqlite", vfs: "opfs"}) message is sent after the onready callback.
- The COOP/COEP headers should be configured correctly on cloudflare.
async function querydb(query){
promiser("exec", {"sql": query})
.then(res => {document.getElementById("insertQuery").innerHTML = JSON.stringify(res)});
};
async function runOnReady(){
promiser("open", {filename: "/samples.sqlite", vfs: "opfs"})
.then(querydb("SELECT * FROM samples;"));
};
const config = {
onready: function(){console.log("ready"); runOnReady();},
worker: function(){return new Worker("jswasm/sqlite3-worker1.js");},
onunhandled: function(unhandled){error("Unhandled worker message:", unhandled.data);},
onerror: function(error){error("worker1 error:", error);}
};
const promiser = globalThis.sqlite3Worker1Promiser(config);
The samples.sqlite db only has one table and that table is called "samples"
So the error --- SQLITE_ERROR: sqlite3 result code 1: no such table: samples --- is a bit of a head scratcher
Are there any examples of opening an existing database? All the demo files just insert rows for testing.
commit
live demo
(19.2) By Stephan Beal (stephan) on 2024-02-05 08:52:33 edited from 19.1 in reply to 18 [link] [source]
The samples.sqlite db only has one table and that table is called "samples"
The OPFS Explorer plugin for Chrome tells me that that file has a size of 0.
Edit:
Are there any examples of opening an existing database? All the demo files just insert rows for testing.
There is no difference between opening an existing database or creating a new one unless you specifically tell it not to create a database on demand1. It is up to the client code to determine (e.g. by running a query) whether or not the required schema exists, and adding it if it's not. One way to tell if your table is installed it to query sqlite_schema:
select count(*) from sqlite_schema where type='table' and name='foo' collate nocase;
That will return a single row with a value of 0 or 1.
- ^ A feature the worker1 API does not currently expose but perhaps should.
(20) By Fake User (FakeUser) on 2024-02-05 08:52:28 in reply to 19.1 [link] [source]
hmm, the samples.sqlite file in the repo is 76kb and has 100 rows
does something besides promiser("open", {filename: "/samples.sqlite", vfs: "opfs"}) need to happen?
(21) By Stephan Beal (stephan) on 2024-02-05 09:05:53 in reply to 20 [link] [source]
hmm, the samples.sqlite file in the repo is 76kb and has 100 rows
If you're suggesting that you're trying to access a database which lives on your web server, that won't work. The JS code can only access databases which are either created in the browser or which have been explicitly imported into the browser's storage.
Your database first needs to be either imported into OPFS or created from scratch there. Since you already have a db, you can import it by following these instructions.
(22.1) By Fake User (FakeUser) on 2024-02-05 23:39:36 edited from 22.0 in reply to 21 [link] [source]
Your database first needs to be either imported into OPFS
Ah ha, there's the ticket!
Unfortunatly the cookbook doesn't cover importing into OPFS
The method shown loads the buffer into an in-memory database that only seems to works on the main thread,
so it isn't compatible with worker1 or worker1-promiser.
The docs for importing into OPFS are pretty minimal.
I've attempted to implement what it seems the describe, but its still erroring.
window.sqlite3InitModule().then(function(sqlite3){
async function initdb(buffer){
console.debug("samples.sqlite fetched and converted to ArrayBuffer:");
console.debug(buffer);
let db = new sqlite3.oo1.OpfsDb("samples.sqlite", "c"); // as described in docs
await sqlite3.oo1.OpfsDb.importDb("samples.sqlite", buffer); // as described in docs
console.debug(db);
};
fetch("/samples.sqlite")
.then(res => res.arrayBuffer())
.then(buffer => {initdb(buffer)})
.then(() => {promiser = globalThis.sqlite3Worker1Promiser(config)})
});
(23.4) By Stephan Beal (stephan) on 2024-02-06 09:20:18 edited from 23.3 in reply to 22.1 [link] [source]
The method shown loads the buffer into an in-memory database that only seems to works on the main thread,
The cookbook shows one of several ways to get an array buffer. That array buffer can then, if necessary, be transferred to another thread using postMessage(), but doing so is necessarily client-specific. An example is provided below.
I've attempted to implement what it seems the describe, but its still erroring
First off, you're loading two copies of the sqlite module there:
- To populate the OPFS storage.
- The promiser loads its own isolated copy of the module for use with the worker1 API.
Though not inherently wrong, it is extremely bloated - the browser has to keep two completely independent copies of the wasm module and JS code in memory. They only thing not independent about them is that they both have access to the same OPFS storage (if they're in Worker threads, otherwise they can't see the OPFS storage).
Secondly, your example cannot work because you're trying to use OpfsDb from the main thread, where it does not (cannot) exist due to a limitation of OPFS's design. OpfsDb is only available when sqlite is loaded into a worker thread (and OPFS is available, which it may not be for any number of reasons).
Thirdly, you're making this far more difficult on yourself than it needs to be by using the worker1/promiser API. Those APIs are toys, limited severely by their restriction of having to use postMessage() for all communication with the client. You'd be far better served by loading the sqlite module into your own worker and performing all work there. That gives you full access to the whole library API, with none of the quirks and limitations imposed by the worker1 API.
A variation of your above example is provided below which demonstrates that advice.
First, a correction:
let db = new sqlite3.oo1.OpfsDb("samples.sqlite", "c"); // as described in docs await sqlite3.oo1.OpfsDb.importDb("samples.sqlite", buffer); // as described in docs
importDb() cannot work on an opened database (and its docs have just been updated to make that explicit) because OPFS only permits each handle to be opened once at a time1.
Secondly, what follows is a tested demo of how it can work, minus the promiser part, which is left as an exercise for those who insist on using that API...
Part 1: the HTML:
Here we start a new worker, load a db byte array, and postMessage() that array to the worker, then wait for a query result...
<pre>This is where the query response will go</pre> <script>(function(){ const w = new Worker('foo.mjs',{type:'module'}); w.onmessage = function(msg){ msg = msg.data; const dbName = 'foo.db'; switch(msg.type){ case 'worker-ready': fetch(dbName).then(res=>res.arrayBuffer()).then((buffer)=>{ console.log("Posting buffer",buffer); w.postMessage({ type:'db-buffer', args:{ name: dbName, buffer } }, [buffer]); }); break; case 'query-result': document.querySelector('pre').innerText = JSON.stringify(msg,undefined,2); break; } }; })();</script>
Part 2: foo.mjs:
import sqlite3InitModule from './jswasm/sqlite3.mjs'; const sqlite3 = await sqlite3InitModule(); // This is where your app logic starts, after the db is imported. const runApp = function(dbName){ const db = new sqlite3.oo1.OpfsDb(dbName); try{ postMessage({ type: 'query-result', data: db.selectValues('select name from sqlite_schema') }); }finally{ db.close(); } }; self.onmessage = function(ev){ ev = ev.data; switch(ev.type){ case 'db-buffer': sqlite3.oo1.OpfsDb.importDb(ev.args.name, ev.args.buffer).then(()=>{ console.log("Imported",ev.args.name); runApp(ev.args.name); }); break; } }; postMessage({type:'worker-ready'});
Notes:
The above example was edited to fix a timing bug since its initial post.
importDb() is only async if it's passed a function as its second argument (the docs will soon be updated to clarify that).Edit: not true! That only applies to the other OPFS VFS.After that importDb() succeeds once time, it would be possible to access that db using the promiser API, provided it's run from the same HTTP origin.
Part 3: Profit!
With that in place, foo.db is now in OPFS storage, as is demonstrated by the results of the worker's runApp(). In the case of my test db, the PRE element's contents look like:
{ "type": "query-result", "data": [ "vvar", "sqlite_autoindex_vvar_1", "vfile", "sqlite_autoindex_vfile_1", "vmerge", "sqlite_stat1", "stash", "stashfile", "sqlite_autoindex_stashfile_1", "vmergex1", "vmerge_ck1", "undo", "sqlite_autoindex_undo_1", "undo_vfile", "undo_vmerge" ] }
- ^ We're ignoring the new/experimental/Chrome-only locking features because they don't apply here