Import database from bytes

[AlexandreAndrade00]

After the answer #2777 (comment) I tried to figure out what I was missing but didn't found. With the browser dev tools I can confirm that the old database is deleted and a new one is saved in "Storage", but the data isn't available in the app.
Code:

// drive code
Future<Database> resetDatabases() async {
  final database = <DriftDatabase>;

  await database.close();

  await DatabaseConnection()
      .deleteDatabase(databaseName: database.databaseName);

  final fileBytes =
      (await httpClient.get(Uri.parse(snapshotUrl))).bodyBytes;

  return Database(fileBytes: fileBytes);
}

// web implementation
class DatabaseConnector implements p.DatabaseConnector {
  final Logger _logger = Logger('WebDatabaseConnector');

  @override
  QueryExecutor createDatabaseConnection({
    required String databaseName,
    QueryInterceptor? interceptor,
    String? path,
    FutureOr<void> Function()? isolateSetup,
    Uint8List? databaseBytes,
  }) {
    return DatabaseConnection.delayed(Future(() async {
      final result = await WasmDatabase.open(
        databaseName: databaseName,
        sqlite3Uri: Uri.parse('sqlite3.wasm'),
        driftWorkerUri: Uri.parse(
          kReleaseMode ? 'drift_worker.dart.min.js' : 'drift_worker.dart.js',
        ),
        initializeDatabase: () => databaseBytes,
      );

      if (result.missingFeatures.isNotEmpty) {
        // Depending how central local persistence is to your app, you may want
        // to show a warning to the user if only unreliable implementations
        // are available.
        _logger
            .info('Using ${result.chosenImplementation} due to missing browser '
                'features: ${result.missingFeatures}');
      }

      DatabaseConnection databaseConnection = result.resolvedExecutor;

      if (interceptor != null) {
        databaseConnection = databaseConnection.interceptWith(interceptor);
      }

      return databaseConnection;
    }));
  }

  @override
  Future<void> deleteDatabase(
      {required String databaseName, String? path}) async {
    final probe = await WasmDatabase.probe(
      databaseName: databaseName,
      sqlite3Uri: Uri.parse('sqlite3.wasm'),
      driftWorkerUri: Uri.parse(
        kReleaseMode ? 'drift_worker.dart.min.js' : 'drift_worker.dart.js',
      ),
    );

    await probe.deleteDatabase(probe.existingDatabases.single);
  }
}

// native implementation
class DatabaseConnector implements p.DatabaseConnector {
  @override
  QueryExecutor createDatabaseConnection({
    required String databaseName,
    QueryInterceptor? interceptor,
    String? path,
    FutureOr<void> Function()? isolateSetup,
    Uint8List? databaseBytes,
  }) {
    return LazyDatabase(() async {
      // put the database file into the documents folder for the app.
      final dbFolder = path ?? (await getApplicationSupportDirectory()).path;

      final databaseFile = File(join(dbFolder, '$databaseName.sqlite'));

      if (databaseBytes != null) {
        await databaseFile.create(recursive: true);

        await databaseFile.writeAsBytes(databaseBytes);
      }

      DatabaseConnection databaseConnection;

      // Also work around limitations on old Android versions
      if (Platform.isAndroid) {
        await applyWorkaroundToOpenSqlCipherOnOldAndroidVersions();
        // We can't access /tmp on Android, which sqlite3 would try by default.
        // Explicitly tell it about the correct temporary directory.
        sqlite3.tempDirectory = (await getTemporaryDirectory()).path;
      }

      databaseConnection = NativeDatabase.createBackgroundConnection(
        databaseFile,
        isolateSetup: isolateSetup ??
            () {
              open.overrideFor(OperatingSystem.android, openCipherOnAndroid);
            },
        setup: (db) {
          // Check that we're actually running with SQLCipher by quering the
          // cipher_version pragma.
          final result = db.select('pragma cipher_version');
          if (result.isEmpty) {
            throw UnsupportedError(
              'This database needs to run with SQLCipher, but that library is '
              'not available!',
            );
          }

          // Then, apply the key to encrypt the database. Unfortunately, this
          // pragma doesn't seem to support prepared statements so we inline the
          // key.
          final escapedKey = _encryptionPassword.replaceAll("'", "''");
          db.execute("pragma key = '$escapedKey'");

          // Test that the key is correct by selecting from a table
          db.execute('select count(*) from sqlite_master');
        },
      );

      if (interceptor != null) {
        databaseConnection = databaseConnection.interceptWith(interceptor);
      }

      return databaseConnection;
    });
  }

  @override
  Future<void> deleteDatabase(
      {required String databaseName, String? path}) async {
    final dbDirectoryPath =
        path ?? (await getApplicationSupportDirectory()).path;

    final dbPath = join(dbDirectoryPath, '$databaseName.sqlite');

    final dbFile = File(dbPath);

    if (dbFile.existsSync()) dbFile.deleteSync();
  }
}

[simolus3]

Do you have a complete (runnable) way to reproduce this? The web implementation looks correct to me, I'm doing something similar and that works. So I'd love to debug this, but it's hard to do with the snippets alone because they're very similar to what I'm also doing (so it might be something outside of that).

[AlexandreAndrade00]

I'm trying to isolate the problem, when I'm done I will send it here. I don't know if this information helps but when the database.close() is called the web workers don't stop and when I open the database again some web worker (I don't know if it is the old or the new) throws an error, but the stack doesn't help nor the message.

Error
dart-sdk/lib/_internal/js_dev_runtime/private/ddc_runtime/errors.dart 330:10  createErrorWithStack
dart-sdk/lib/_internal/js_dev_runtime/patch/core_patch.dart 337:28            _throw
dart-sdk/lib/core/errors.dart 120:5                                           throwWithStackTrace
dart-sdk/lib/async/zone.dart 1386:11                                          callback
dart-sdk/lib/async/schedule_microtask.dart 40:11                              _microtaskLoop
dart-sdk/lib/async/schedule_microtask.dart 49:5                               _startMicrotaskLoop
dart-sdk/lib/_internal/js_dev_runtime/patch/async_patch.dart 181:7            <fn>
Application finished.

[simolus3]

The reason you're getting this uncaught exception is because it happens in the insert in body_widget.dart, which is not awaited.

You can see the actual error when adapting the stream, for instance:

diff --git a/lib/widgets/todos_widget.dart b/lib/widgets/todos_widget.dart
index 13e17df..9106e66 100644
--- a/lib/widgets/todos_widget.dart
+++ b/lib/widgets/todos_widget.dart
@@ -19,6 +19,10 @@ class _TodosWidgetState extends State<TodosWidget> {
     return StreamBuilder(
         stream: db.todoItems.select().watch(),
         builder: (context, snapshot) {
+          if (snapshot.hasError) {
+            return Text(snapshot.error.toString());
+          }
+
           if (!snapshot.hasData) return const SizedBox.shrink();
 
           return ListView.builder(

This reveals the 26 / file is not a database error. That is expected if you try to open an encrypted database on the web, that is not supported (nor should it be necessary since different websites have completely isolated storage).

[AlexandreAndrade00]

I completely forgot that the web database doesn't support encryption 😅 Thank you so much for the help!

[AlexandreAndrade00]

Code to reproduce -> https://github.com/AlexandreAndrade00/drift_replace_db_bug

In the example, I used an asset instead of an HTTP request, but the behavior is equal (discard how the bytes are obtained). To obtain the SQLite file, I ran the application in native (Linux).

[simolus3]

To obtain the SQLite file, I ran the application in native (Linux).

Note that SQLCipher is not available in the sqlite3.wasm that we ship, you can't open encrypted databases on the web (lib/assets/db.sqlite is encrypted in your repository).