Sqlite onCreate isn't invoked after dropping/deleting the table from the database/storage

Question

Minimal reproducible code:

class Helper {
  Database _db;

  Future<Database> initDb() async {
    if (_db == null) {
      final directory = await getApplicationDocumentsDirectory();
      _db = await openDatabase(join(directory.path, 'foo.db'), version: 1, onCreate: _onCreate);
    }
    return _db;
  }

  Future<void> _onCreate(Database db, _) async {
    print('onCreate');
    await db.transaction((txn) async {
      await txn.execute('CREATE TABLE tableName(abc TEXT)');
    });
  }
}

Here's my main method:

void main() async {
  final directory = await getApplicationDocumentsDirectory();
  final file = File(join(directory.path, 'foo.db'));
  if (await file.exists()) {
    await file.delete();
  }

  // If there had been a file, it's deleted now.
  final helper = Helper();
  await helper.initDb(); // This must fire `onCreate` but it doesn't.
}

Every time you run the main method, it should execute the onCreate method in Helper class but it only does that once. What am I doing wrong here?

Answer 1

The issue description has changed since the beginning and it is not easy to make a proper explanation in a comment so here is yet another answer.

All existing responses remain valid but the issue now is moving to something like onCreate not called after deleting the database.

Every time you run the main method, it should execute the onCreate method in Helper class but it only does that once. What am I doing wrong here?

You don't really specify how you run (i.e. do you stop the application before), so I'm assuming you are just pressing the run button on your IDE which performs a hot-restart.

While you might be enclined to simply delete the file, you should however use deleteDatabase to properly delete a database.

// Do not call File.delete, it will not work in a hot restart scenario
await File(path).delete();

// Instead do
await deleteDatabase(path);

• it will properly close any existing database connection
• it will properly handle the hot-restart scenario which put SQLite in a weird state (basically the 'dart' side think the database is closed while the database is in fact open on the native side)

If you call File.delete, while you might think it work (i.e. the file does not exist anymore), since the database might still be opened in a hot restart scenario the next open will re-use the open connection and at some point will get written with the old data and onCreate will not be called the next time you open the database.