title: Dart sqflite clear db and insert
body: <p>I'm having trouble doing a very simple task, delete a DB or drop table and insert new values. Tried several things but I always end up with an error <code>SqfliteDatabaseException (DatabaseException(database_closed 1))</code>. This happens when <code>batchInsert</code> is called. I'd expect the new db connection to be opened after <code>openDatabase</code> after deleting the old one.</p>
  <p>This is my latest code:</p>
  <p><code>sqlite_service.dart</code></p>
  <pre><code>class SQLiteService {
    static final SQLiteService instance = SQLiteService.init();
    static Database? _database;
    SQLiteService.init();
  
    static const String dbName = 'bookies_list.db';
    static const String tableName = 'bookies';
  
    Future&lt;Database&gt; get database async {
      if (_database != null) return _database!;
      _database = await _initDB();
      return _database!;
    }
  
    Future&lt;Database&gt; _initDB() async {
      Directory documentsDirectory = await getApplicationDocumentsDirectory();
      String path = join(documentsDirectory.path, dbName);
      return await openDatabase(path, version: 1, onCreate: _onCreate);
    }
  
    Future _onCreate(Database db, int version) async {
      await db.execute(
          'CREATE TABLE bookies (${BookieFields.id} INTEGER PRIMARY KEY AUTOINCREMENT, ${BookieFields.name} TEXT NOT NULL)');
    }
  
    //* Methods *//
  
    static Future&lt;void&gt; batchInsert(Set bookieSet) async {
      final db = await instance.database;
      Batch batch = db.batch();
      for (var bookie in bookieSet) {
        print('name: $bookie');
        batch.insert(tableName, {'name': bookie});
      }
      List&lt;Object?&gt; result = await batch.commit(noResult: true);
      print('result is $result');
    }
  
    static Future&lt;List&lt;Bookie&gt;&gt; getAllBookies() async {
      final db = await instance.database;
      const orderBy = '${BookieFields.name} ASC';
      final result = await db.query(tableName, orderBy: orderBy);
      return result.map((json) =&gt; Bookie.fromJson(json)).toList();
    }
  
    static Future&lt;int&gt; getDBVersion() async {
      final db = await instance.database;
      final localVersion = await db.getVersion();
      return localVersion;
    }
  
    static Future&lt;void&gt; deleteSQLiteDB() async {
      Directory documentsDirectory = await getApplicationDocumentsDirectory();
      String path = join(documentsDirectory.path, dbName);
      final db = await instance.database;
      await deleteDatabase(path);
      //await db.delete(tableName);
      // // Future _onCreate(Database db, int version) async {
      // //   await db.execute(
      // //       'CREATE TABLE bookies (${BookieFields.id} INTEGER PRIMARY KEY AUTOINCREMENT, ${BookieFields.name} TEXT NOT NULL)');
      // //   await db
      // //       .execute('CREATE TABLE localVersion (version INTEGER PRIMARY KEY )');
      // // }
  
       await openDatabase(path, version:1 , onCreate:_onCreate);
    }
  
    static Future&lt;void&gt; setNewLocalVersion(int newVersion) async {
      final db = await instance.database;
      await db.setVersion(newVersion);
    }
  }
  </code></pre>
  <p><code>some_other_file.dart</code></p>
  <pre><code>  Future&lt;void&gt; _updateBookieList() async {
      final bool updateBookies = await isBookieUpdateNeeded();
      if (updateBookies) {
        print(' update');
        await SQLiteService.deleteSQLiteDB();
        final Set&lt;String&gt;? newBookieSet = await FirebaseRTDB().getBookiesUpdate();
        if (newBookieSet != null &amp;&amp; newBookieSet.isNotEmpty) {
          await SQLiteService.batchInsert(newBookieSet);
          var foo = await SQLiteService.getAllBookies();
          print('bookie list: $foo');
          final int remoteVersion = await FirebaseRTDB().getRemoteVersion();
          SQLiteService.setNewLocalVersion(remoteVersion);
        } else {
          print('update failed');
        }
      } else {
        print('no update');
      }
  </code></pre>