Is `db_worker.js` needed when `SqliteConnection.synchronousWrapper(db)` is used instead of `SqliteDatabase(path: ..., ...)`?

[andynewman10]

I am currently using the following code:

final sqlite3 = await WasmSqlite3.loadFromUrl(Uri.parse('sqlite3.wasm'));
...
final db = sqlite3.open(...);
final db2 = SqliteDatabase.singleConnection(SqliteConnection.synchronousWrapper(db));
final query = await db2.execute("...."); // works fine

This works on both mobile and web (the code above is for the web).

When looking at your pub.dev page here, I see the following code illustrating the general case of database files:

import 'package:sqlite_async/sqlite_async.dart';

final db = SqliteDatabase(
    path: 'test.db',
    options: SqliteOptions(
        webSqliteOptions: WebSqliteOptions(
            wasmUri: 'sqlite3.wasm', workerUri: 'db_worker.js')));

What is db_worker.js for? When using SqliteDatabase.singleConnection(SqliteConnection.synchronousWrapper(db)), there is no opportunity/parameter to specify WebSqliteOptions. sqlite3.wasm has already been loaded (as shown above in the top code block), but shall db_worker.js be hosted on our application server, too, in this case? My tests show that the browser does not try to download that file, even though things work smoothly.

[simolus3]

I recommend not using SqliteDatabase.singleConnection outside of tests. It's correct that if you're using that API instead of the default open factory, you don't need a database worker.

However, the worker is not just for fun, it enables safer and more performant database access:

1. Workers offload database access to a background thread.
2. Shared workers allow different tabs to share a database and update notifications.
3. The web file system API / OPFS used to store databases on modern browsers requires workers, we have to use IndexedDB as a less-efficient fallback if workers aren't available.
4. Even without workers, it may be necessary to use web locks to guard access to the database across tabs. Your setup does not give you that.
5. With this raw sqlite3.open setup, you need to use special path names and manually register a VFS for this work. This can break easily.

So I recommend you use the default approach for opening databases and ship the worker with your app. Are you running into any issues with that?

[andynewman10]

There are two things I currently need in my app.

• I need to use an InMemoryFileSystem and have two files in there
• I am using ATTACH DATABASE, too. Basically I have 2 VFS database files, one of which is attached to the other one.

The current sqlite_async API is not sufficient to fulfill my needs, is it?

[simolus3]

Alright, I see 👍 For in-memory databases, sqlite_async is not really an appropriate package since it relies on WAL mode which doesn't work for those (we have synchronousWrapper and singleConnection for test purposes).

For the web, you could use the sqlite3_web package directly. That also requires a worker (and you would have to compile that yourself), but you can then call connect with the desired implementation, such as DatabaseImplementation.inMemoryShared (I've removed support for in-memory databases hosted by dedicated workers in the latest version, but I can add it back since you seem to have an actual use case for it).

[andynewman10]

• regarding your solution using sqlite3_web, would there be a solution for mobile, too? If there is, this would be a workable path.
• an alternative method for me would simply be to implement my own isolate (on mobile) and web worker (on the web). Does the sqlite3 package support pure Dart (not Flutter) code?

[simolus3]

sqlite3_web is web-only. On mobile you could use package:sqlite3 and do the isolate management yourself to run queries asynchronously. But it might be easier to add an API to this package to support a single SQLite connection hosted on a background isolate (contributions are welcome!).

Does the sqlite3 package support pure Dart (not Flutter) code?

Of course :) And so does package:sqlite3_web and package:sqlite_async.