Any time a blob column is returned, the wrong values are returned for that column.

The pointer to the sqlite3_colum_blob is the same for every row in the result set even when the blobs are different.

I've created a small repository that reproduces the exact issue here:

https://github.com/tantaman/sandbox/tree/main

The interesting bits are in src/main.ts:

await sql`DROP TABLE IF EXISTS foo`;
await sql`CREATE TABLE IF NOT EXISTS foo (a)`

console.log('INSERTING 010203');
await sql`INSERT INTO foo VALUES (X'010203')`

console.log('SELECTING');
let result = await sql`SELECT * FROM foo`;
printBytesResult(result);

console.log('INSERTING 01020304');
await sql`INSERT INTO foo VALUES (X'01020304')`

console.log('SELECTING');
result = await sql`SELECT * FROM foo`;
printBytesResult(result);

console.log('INSERTING 01020304');
await sql`INSERT INTO foo VALUES (X'01020304')`

console.log('SELECTING');
result = await sql`SELECT * FROM foo`;
printBytesResult(result);

cosonle.log('QUOTE SELECTING');
result = await sql`SELECT quote(a) FROM foo`;
printHexResult(result);

Instead of getting a byte array of values 010203 or 01020304 we get:

INSERTING 010203
main.ts:73 SELECTING
main.ts:97 raw bytes Int8Array(3) [-16, -81, 81, buffer: ArrayBuffer(16777216), byteLength: 3, byteOffset: 5353200, length: 3, Symbol(Symbol.toStringTag): 'Int8Array']
main.ts:98 hex f0af51
main.ts:77 INSERTING 01020304
main.ts:80 SELECTING
main.ts:97 raw bytes Int8Array(3) [-16, -81, 81, buffer: ArrayBuffer(16777216), byteLength: 3, byteOffset: 5350640, length: 3, Symbol(Symbol.toStringTag): 'Int8Array']
main.ts:98 hex f0af51
main.ts:97 raw bytes Int8Array(4) [-16, -81, 81, 0, buffer: ArrayBuffer(16777216), byteLength: 4, byteOffset: 5350640, length: 4, Symbol(Symbol.toStringTag): 'Int8Array']
main.ts:98 hex f0af5100
main.ts:84 INSERTING 01020304
main.ts:87 SELECTING
main.ts:97 raw bytes Int8Array(3) [-16, -81, 81, buffer: ArrayBuffer(16777216), byteLength: 3, byteOffset: 5352176, length: 3, Symbol(Symbol.toStringTag): 'Int8Array']
main.ts:98 hex f0af51
main.ts:97 raw bytes Int8Array(4) [-16, -81, 81, 0, buffer: ArrayBuffer(16777216), byteLength: 4, byteOffset: 5352176, length: 4, Symbol(Symbol.toStringTag): 'Int8Array']
main.ts:98 hex f0af5100
main.ts:97 raw bytes Int8Array(4) [-16, -81, 81, 0, buffer: ArrayBuffer(16777216), byteLength: 4, byteOffset: 5352176, length: 4, Symbol(Symbol.toStringTag): 'Int8Array']
main.ts:98 hex f0af5100
main.ts:91 QUOTE SELECTING
main.ts:104 raw bytes (3) [1, 2, 3]
main.ts:105 hex X'010203'
main.ts:104 raw bytes (4) [1, 2, 3, 4]
main.ts:105 hex X'01020304'
main.ts:104 raw bytes (4) [1, 2, 3, 4]
main.ts:105 hex X'01020304'

Notice the byteOffest parameter for each row

E.g., byteOffset: 5352176

Each blob has the same byte offset which is certainly incorrect.

Doing a sqlSELECT quote(a) FROM foo; then casting a from hex to bytes returns the correct result (see console.log('QUOTE SELECTING') section). So for some strange reason sqlite3_column_text is not impacted by this bug.

Hi @rhashimoto and thanks for your awesome work, I have implemented a WebTorrent based read only VFS. Do you want to add it to the examples?

It will probably never be really fast but being able to "serverless" host a database is very interesting. In combination with a webseed this is also very similar to sql.js-httpvfs (Thanks @phiresky by the way for introducing me into this topic).

To make sure the demo always works it also uses a webseed (the GOOG.db file). To truly test the torrenting you have to open a new tab at https://instant.io/ and add the GOOG.db, which then acts as another peer. Also the adding of the webseed url has to be removed in demo/index.js:L64. When using a magnetUri you may have to increase the torrent ready timeout option of WebTorrentVFS, because loading the meta information from peers is rather slow.

Passing BigInt to the bind APIs currently converts them to null as their type is not handled in the switch statement.

https://github.com/rhashimoto/wa-sqlite/blob/4c5fd0e4afd9eb1dadbd621a41db4916fe47ed67/src/sqlite-api.js#L71-L90

Looking at the official WASM build -- looks like it is possible to just expose bind_int64 from the C api and pass a BigInt to it.

wa-sqlite is built as a set of ES6 modules. As ES6 modules generated by Emscripten do not support Node.js very well, we have to employ a couple of workarounds for the SQLiteESMFactory as documented in emscripten-core/emscripten#11792:

• Set globals for __dirname and require()
• Provide a locateFile() in the config object to the factory

The code snippet below illustrates how this is done:

  var SQLite = await import('wa-sqlite')
  var { default: SQLiteESMFactory } = await import('wa-sqlite/dist/wa-sqlite-async.mjs')

  // Remember the original globals
  const currentDirName = globalThis.__dirname
  const originalRequire = globalThis.require

  // Replace this with actual path to wa-sqlite/dist
  const sqliteDistPath = 'node_modules/wa-sqlite/dist' 

  // If we are using node, replace the globals below
  // with values that our module factory will use
  if (typeof process === 'object') {
    const { createRequire } = await import('module')
    globalThis.__dirname = sqliteDistPath
    globalThis.require = createRequire(__dirname + sqliteDistPath + '/wa-sqlite-async.mjs')
  }

  // Invoke the ES6 module factory (with a custom `locateFile` method for node)
  // to create the SQLite Emscripten module. This will fetch and compile the 
  // .wasm file.
  const options = typeof process === 'object'
    ? { locateFile: path => sqliteDistPath + '/' + path }
    : {}
  const module = await SQLiteESMFactory(options)

  // Reinstate the globals after creating the module
  globalThis.require = originalRequire
  globalThis.__dirname = currentDirName

  // Use the module to build the API instance.
  const sqlite3 = SQLite.Factory(module)

WebLocks (and WebLocksShared) associate lock state with the filename. That means it can't distinguish multiple connections to the same database using the same WebLocks instance. The connections would overwrite each other's state.

An easy fix would be to give every open file its own instance of WebLocks, but...

Concurrent access to the same database in the same SQLite context where one access is a writer doesn't work anyway because Asyncify-ed WebAssembly is not re-entrant during an asynchronous callback. It isn't possible for one database call to block and then be able to make another database call, so fixing the bug won't enable any new usages.

Note that you can access the same database in the same SQLite context as long each call into SQLite completes before the next call is made.

There are significant time and space penalties for using WebAssembly SQLite built with Asyncify, which allows SQLite to callback to asynchronous Javascript functions. This is why wa-sqlite provides both synchronous (without Asyncify) and asynchronous builds, so developers can avoid those penalties if they don't need to use asynchronous callbacks.

Asynchronous callbacks are especially useful for calling asynchronous APIs like IndexedDB or fetch. But @phiresky (sql.js-httpvfs) and @jlongster (absurd-sql) independently discovered a clever way to make synchronous calls to asynchronous APIs using Atomics and SharedArrayBuffer. It's pretty cool!

The main catch with these APIs (and why it took so long to get support in Safari again) is they are potentially exploitable by Spectre malware attacks, so they are only enabled on pages served with special COOP/COEP HTTP headers which also heavily restrict including content from a different origin. This may prevent integrating certain third-party features like federated sign-in (e.g. OAuth2) and ads right now.

Implementing a SQLite VFS or module using Atomics and SharedArrayBuffer based on the described methods should be straightforward with the wa-sqlite synchronous build and I would be excited to see it happen. I don't plan to do that myself, however - i.e. I don't plan to add a synchronous IndexedDB (or File System Access) VFS to this repo - at least until it can be made to work with federated sign-in.

The size of the WebAssembly for wa-sqlite is about 507 KB for the synchronous build and 1,032 KB for the Asyncify build, so roughly double the size. The speed penalty is harder to characterize because although the cost of unwinding and rewinding the stack for asynchronous calls is quite high, it may still be relatively small compared to the asynchronous function itself. For example, if you're making a 1500 ms asynchronous call to WebTorrent then you may not care if Asyncify adds another 5 ms (I made these numbers up just for illustration). In general, the slower your wa-sqlite storage medium, the less you should care about Asyncify performance...though you still might want to tune your page size to reduce both Asyncify and non-Asyncify overhead.

Those interested in synchronous WebAssembly filesystems should also track Emscripten's WasmFS which is in development.

The wa-sqlite build produces ES6 modules, which is nice for development. But most applications should put time-consuming operations in a Worker, and support for ES6 modules in a Worker isn't universal yet. Worker code generally needs to be bundled.

Emscripten modules need to load their associated .wasm file, and the generated code for ES6 uses import.meta.url as its default location. import.meta.url is not valid outside an ES6 module, however, so the bundler has to translate this to something else. When the target context is a Worker, that something else needs to be valid in a Worker, so something like document.currentScript.src, which is the substitution my bundler chooses, causes a runtime exception.

The fix I use is to configure a bundler string replacement plug-in (@rollup/plugin-replace in my case) to replace import.meta.url with an empty string. There may be better approaches out there but this is sufficient for me so far.

Hi @rhashimoto 😊 Thank you for your work 🙏🏻 I am hoping that I will be able to get the demo running. However, seems like a couple of files missing . Specifically these 2 files are missing:

~/dist/wa-sqlite-async.mjs
~/dist/wa-sqlite.mjs
~/dist/wa-sqlite-async.wasm
~/dist/wa-sqlite-async.wasm

From where can I get those files ? In the meantime, I just copied them from : https://rhashimoto.github.io/wa-sqlite/dist/

Also it seems to need CSP settings. wasm-unsafe-eval

I notice that blobs, when read out of wa-sqlite, are being returned as int8array rather than uint8array.

Also when inserting blobs -- you expect int8array.

I believe these should be Uint8Arrays instead.

You can't import the main api via

import * as SQLite from "wa-sqlite";

because it throws this error:

File '.../node_modules/wa-sqlite/src/types/sqlite-api.d.ts' is not a module.

The dist file etc are also not typed, import SQLiteAsyncESMFactory from "wa-sqlite/dist/wa-sqlite-async.mjs"; throws a type error, as does import * as VFS from "wa-sqlite/src/VFS";

Using webpack, it tries to load the WASM from a file:/// url and the wasm file is not bundled as an asset. Is it possible to specify the wasm URL or make it work with webpack in some other way?

If multiple connections are using the OriginPrivateFileSystemVFS with PRAGMA journal_mode TRUNCATE or PERSIST, an SQL_IOERR is likely to result. The problem is that in these modes multiple connections can open the journal file and OPFS access handles are exclusive. A workaround is not to use these modes with multiple connections.

This is not a high priority to fix right now. IDBBatchAtomicVFS is better in pretty much every way that matters to application writers - ease of integration, performance, maturity - so the OPFS VFS is mostly a proof of concept.