Is our use case hitting limits of complexity for web support?

[matehat]

Hi,

We've been relying on Drift (and Moor before that) for a very long time, and we can't thank enough all the people working on this library.

Our app (secure E2EE messaging for healthcare) has historically been mostly native (iOS and Android) but our users have been asking for a web version, which we started to work on about a year ago. One of the hurdles was the storage layer so we were delighted when the more modern web implementation came out. Lately, after extensive internal testing, we released the web version out of beta and to our users in a healthcare setting.

Our data architecture is relatively complex:

• our app keeps track of N open sessions at once (only unlocking one at a time of course). Each session has its own set of database files, with content encrypted with distinct keys (encryption is done in the application, not using SQLCipher)
• we split our codebase in M distinct "domains", each having its own database file (e.g. discussions, contacts, patients)
• a common authentication layer has its storage layer with its own database file
• in the web app, users can open multiple tabs, possibly unlocking different sessions in each

So as the users switch between one session and another, they close M databases and open M others. The authentication layer, though, does not move between close and open state at all. It stays open for the lifecycle of the app.

Only on the web since we released it, relatively frequent reports of SQL errors (code 1 and code 10) started to pour in, so we forced "in-memory only" implementation of SQLite except for the authentication layer. We still see these reports coming from the app, but now only for the authentication layer.

Example of error reports:

SqliteException(10): while selecting from statement, disk I/O error, disk I/O error (code 10)
  Causing statement: SELECT * FROM "session_info_identifiers" WHERE "session_id" = ?;, parameters: DEADBEEF
  at StackTrace.current(org-dartlang-sdk:///dart-sdk/lib/_internal/js_runtime/lib/core_patch.dart:835:36)
  at DriftCommunication.request(/opt/hostedtoolcache/flutter/stable-3.16.5-x64/.pub-cache/hosted/pub.dev/drift-2.13.0/lib/src/remote/communication.dart:113:66)
  at _BaseExecutor.runBatched.c_Z.prototype.a6Z(/opt/hostedtoolcache/flutter/stable-3.16.5-x64/.pub-cache/hosted/pub.dev/drift-2.13.0/lib/src/remote/client_impl.dart:97:5)
  at _BaseExecutor.runSelect(/opt/hostedtoolcache/flutter/stable-3.16.5-x64/.pub-cache/hosted/pub.dev/drift-2.13.0/lib/src/remote/client_impl.dart:130:26)
  at _wrapJsFunctionForAsync(org-dartlang-sdk:///dart-sdk/lib/_internal/js_runtime/lib/async_patch.dart:307:19)
  at _wrapJsFunctionForAsync.<anonymous function>(org-dartlang-sdk:///dart-sdk/lib/_internal/js_runtime/lib/async_patch.dart:332:23)
  at _asyncStartSync(org-dartlang-sdk:///dart-sdk/lib/_internal/js_runtime/lib/async_patch.dart:237:3)
  at _BaseExecutor.runBatched.c_Z.prototype.cOP(/opt/hostedtoolcache/flutter/stable-3.16.5-x64/.pub-cache/hosted/pub.dev/drift-2.13.0/lib/src/remote/client_impl.dart:130:11)
  at _BaseExecutor.runBatched.c_Z.prototype.t2(/opt/hostedtoolcache/flutter/stable-3.16.5-x64/.pub-cache/hosted/pub.dev/drift-2.13.0/lib/src/remote/client_impl.dart:130:11)
  at LazyDatabase.runSelect(/opt/hostedtoolcache/flutter/stable-3.16.5-x64/.pub-cache/hosted/pub.dev/drift-2.13.0/lib/src/utils/lazy_database.dart:83:12)

Quickly followed by

CouldNotRollBackException: SqliteException(1): while executing, cannot rollback - no transaction is active, SQL logic error (code 1)
  Causing statement: ROLLBACK TRANSACTION, parameters: . 
For context: The transaction was rolled back because of SqliteException(10): while selecting from statement, disk I/O error, disk I/O error (code 10)
  Causing statement: SELECT * FROM "encrypted_values" WHERE "label" = ? AND "scope" = ?;, parameters: unlockAttempt, DEADBEEF, which was thrown here: 
Error
    at Object.x3 (https://app.braver.net/main-bd1ebec.dart.js:6946:18)
    at bt_.biw (https://app.braver.net/main-bd1ebec.dart.js:239681:51)
    at bck.a6Z (https://app.braver.net/main-bd1ebec.dart.js:239555:10)
    at https://app.braver.net/main-bd1ebec.dart.js:239564:14
    at f5O.a (https://app.braver.net/main-bd1ebec.dart.js:5041:62)
    at f5O.$2 (https://app.braver.net/main-bd1ebec.dart.js:104435:14)
    at Object.f (https://app.braver.net/main-bd1ebec.dart.js:5027:10)
    at bck.cOP (https://app.braver.net/main-bd1ebec.dart.js:239569:10)
    at bck.t2 (https://app.braver.net/main-bd1ebec.dart.js:239560:21)
    at efs.$1 (https://app.braver.net/main-bd1ebec.dart.js:241298:10)

Do you see, in our design, issues that can arise with the current (or recent) web implementation of Drift? Can you think of another DB layout strategy that would be more resilient?

[simolus3]

In previous drift versions, web databases weren't properly closed if hosted on workers. This has been fixed in drift 2.15.0, but you also need the most recent worker to benefit from that. So that might have caused a lot of connections being open at the same time. It should be possible to have multiple databases open at the same time though, and it looks like the amount of databases did not cause the problem as it still appears when the authentication layer is the only thing using a database. So I think the problem comes from drift or the sqlite3 package, not from the way you're using them.

Do you have a way to correlate these reports with the chosenImplementation when the database was opened? With unsafeIndexedDb, corruption issues are possible if multiple tabs are accessing the same database concurrently. But even if this affects other implementations, it would be good to know which ones.

[matehat]

Upgrading to the latest build and upgrading the worker fixed most of our issues! We still encounter some, but they're much rarer. We'll report issues as we encounter and document them.