Delete web sqlite database from Dart

[davidmartos96]

Hello, I was wondering if it was possible to delete the database programatically on web. The same way you would do with the Chrome/Firefox devtools or with the Javascript API.

[simolus3]

Are you using package:drift/wasm.dart? That provides deleteDatabase, available through WasmDatabase.probe().

[davidmartos96]

Yes, I have this essentially, with a dart vm counter part.

import 'dart:async';

import 'package:drift/drift.dart';
import 'package:drift/wasm.dart';
import 'package:flutter/foundation.dart';

const webDbName = "todo-app";

/// Obtains a database connection for running drift on the web.
DatabaseConnection connect() {
  return DatabaseConnection.delayed(Future(() async {
    final db = await WasmDatabase.open(
      databaseName: webDbName,
      sqlite3Uri: Uri.parse('/sqlite3.wasm'),
      driftWorkerUri: Uri.parse('/drift_worker.js'),
    );

    if (db.missingFeatures.isNotEmpty) {
      debugPrint('Using ${db.chosenImplementation} due to unsupported '
          'browser features: ${db.missingFeatures}');
    }

    print("Chosen Web sqlite implementation: ${db.chosenImplementation}");

    return db.resolvedExecutor;
  }));
}

Future<void> deleteTodosDbFile() async {
  throw UnimplementedError();
}

[davidmartos96]

I've just tried the following, but it looks like the existingDatabases list is empty

Future<void> deleteTodosDbFile() async {
  final probeRes = await WasmDatabase.probe(
    sqlite3Uri: _sqlite3Uri,
    driftWorkerUri: _driftWorkerUri,
  );

  final db = _firstWhereOrNull(
    probeRes.existingDatabases,
    (dbInfo) => dbInfo.$2 == webDbName,
  );

  print(probeRes.existingDatabases);

  if (db != null) {
    await probeRes.deleteDatabase(db);
  }
}

[davidmartos96]

@simolus3 Found the problem, I wasn't providing the database name to probe. There is one additional issue, though.
The probeRes.deleteDatabase future never completes. From what I have researched, it is because for indexeddb, delete waits for all connections to close.
The problem is that even if drfitDb.close() is called, the shared worker still holds the native db connection. Is there any way to close it also in the worker?

[simolus3]

The problem is that even if drfitDb.close() is called, the shared worker still holds the native db connection. Is there any way to close it also in the worker?

I've implemented this in 09c6cf0, but it also requires a newer worker. It's not 100% reliable (if a second tab opens a database connection and gets closed, it can't tell the database server that the connection is dead). But the common case of opening a database, closing it and then deleting it should work.

[davidmartos96]

@simolus3 Thanks Simon, I will try it out next week. If I understood correctly, the limitation is that the worker is not able to detect when a tab closes, so it cannot tell when the number of clients reaches 0?

[simolus3]

Exactly, and I'm not aware of a good workaround for that. Perhaps we could send pings periodically but I'm not sure whether that's worth it.

[miguelcmedeiros]

@simolus3 I was trying the approach mentioned above to delete a database on web, but I get the following error when await probeRes.deleteDatabase(db) is called:

InvalidStateError: Failed to read the 'error' property from 'IDBRequest': The request has not finished.
package:drift/src/web/wasm_setup/shared.dart 440:31                               <fn>
dart-sdk/lib/_internal/js_dev_runtime/private/ddc_runtime/operations.dart 598:37  _checkAndCall
dart-sdk/lib/_internal/js_dev_runtime/private/profile.dart 117:39                 dcall
package:web/src/helpers/events/streams.dart 165:63                                <fn>
dart-sdk/lib/_internal/js_dev_runtime/patch/js_allow_interop_patch.dart 224:27    _callDartFunctionFast1

This happens when handling blockedEvent for IDBRequest, which in this case the pending event is IDBVersionChangeEvent. This happens most of the times, but not always, so it seems to be a race-condition.

Before attempting to delete the database I'm closing it but it seems that according to this doc, the connection might still be open.

What I also noticed is that event when the blockedEvent is triggered, the successEvent is also triggered afterwards. Also, from the stacktrace above, note that the error property is not set, which makes me wonder the request should complete with error when handling blockedEvent.

What do you think?

[simolus3]

Also, from the stacktrace above, note that the error property is not set, which makes me wonder the request should complete with error when handling blockedEvent.

From a quick look at the spec, it's not clear to be if a blocked event is supposed to be terminal or if it can be followed by a success event. If we know that we'll get a blocked/error event after the blocked, we should probably just ignore the blocked event. But I don't think that's the case.

What do you think?

It looks like deleting the database will trigger a version change event on other databases which have the option to close at that point. Maybe that's something worth doing.