Error database_closed when using flutter's sqflite

Question

I try to use sqflite to save some data like my class Movie but when i try to insert or query on the database, see this message:

[ERROR:flutter/lib/ui/ui_dart_state.cc(166)] Unhandled Exception: DatabaseException(error database_closed)

---

my MovieBloc class:

List<Movie> _movies = new List<Movie> ();
class MoviesBloc implements BaseBloc {
  final  _moviesController = StreamController<List<Movie>>();
  Database moviesDB;
  String _moviesPath;
  Stream<List<Movie>> get moviesStream => _moviesController.stream;
  List<Movie> get movies => _movies;

  int getLengthMovieList() {
    return _movies.length;
  }
  clearMovieList() {
    _movies.clear();
  }

  Future<void> openMovieDB({String dbName:'movies.db'}) async {

    var databasesPath = await getDatabasesPath();
    _moviesPath = join(databasesPath, dbName);

    moviesDB = await openDatabase(_moviesPath, version: 1,
        onCreate: (Database db, int version) async {
          await db.execute(
              'CREATE TABLE movies (Id INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT NOT NULL, imdbRating TEXT, Poster TEXT, Plot TEXT, Saved INTEGER DEFAULT 0)');
        });
  }
  Future<void> closeMovieDB() async => moviesDB.close();

  void dispose() {
    _moviesController.close();
  }
}

---

my MovieProvider class:

class MovieProvider extends MoviesBloc {
  String _tableName = 'movies';

  Future<Movie> insert(Movie movie, {conflictAlgorithm: ConflictAlgorithm.ignore}) async {
    await moviesDB.insert(_tableName, movie.toMap(), conflictAlgorithm: conflictAlgorithm);
    return movie;
  }

  Future<bool> insertAll(List<Movie> movies) async {
    await Future.wait(movies.map((movie) async {
      await this.insert(movie);
    }
    ));
    return true;
  }

  Future<List<Movie>> paginate(int page, {int limit: 15}) async {
    print('in Paginate to SQLite');
    List<Map> maps  = await moviesDB.query(_tableName,
        columns: ['Id', 'Title', 'imdbRating', 'Poster', 'Plot', 'Saved'],
        limit: limit,
        offset: page == 1 ? 0 : ((page -1) * limit)
    );
    List<Movie> movies = new List<Movie> ();
      if (maps.length > 0) {
        maps.map((movie) {
          movies.add(Movie.fromJson(movie));
        }
        );
      }
    return movies;
  }
}

---

my save method to sqflite:

static Future<bool> saveAllMoviesIntoSqlite(List<Movie> movies) async {
    var db = new MovieProvider();
    await db.openMovieDB();
    await db.insertAll(movies);
    await db.closeMovieDB();
    return true;
  }

---

my load method from sqflite:

 static Future<Map> getAllMoviesFromSqlite(int page) async {
    var db = new MovieProvider();
    await db.openMovieDB();
    List<Movie> movies = new List<Movie> ();
    movies = await db.paginate(page);
    await db.closeMovieDB();
    return {
      "currentPage": page,
      "movies": movies
    };
  }

---

• I observe that in insertAll method from MovieProvider class for insert any movie from the movies list to sq with await and async:

 Future<bool> insertAll(List<Movie> movies) async {
    await Future.wait(movies.map((movie) async {
      await this.insert(movie);
    }
    ));
    return true;
  }

• I try to close and open database sometimes sequential and other plays with close and open database ...

---

my log:

I/flutter ( 1617): in Paginate to SQLite
E/flutter ( 1617): [ERROR:flutter/lib/ui/ui_dart_state.cc(166)] Unhandled Exception: DatabaseException(error database_closed)
E/flutter ( 1617): #0      SqfliteDatabaseMixin.checkNotClosed (package:sqflite_common/src/database_mixin.dart:282:7)
E/flutter ( 1617): #1      SqfliteDatabaseExecutorMixin._rawQuery (package:sqflite_common/src/database_mixin.dart:125:8)
E/flutter ( 1617): #2      SqfliteDatabaseExecutorMixin.query (package:sqflite_common/src/database_mixin.dart:110:12)
E/flutter ( 1617): #3      MovieProvider.paginate (package:umdb/bloc/movie_provider.dart:24:38)
E/flutter ( 1617): #4      MovieService.getAllMoviesFromSqlite (package:umdb/services/movie_service.dart:54:23)
E/flutter ( 1617): <asynchronous suspension>
E/flutter ( 1617): #5      MovieService.getSavedFromSQ (package:umdb/services/movie_service.dart:95:26)
E/flutter ( 1617): #6      MovieService.fetchSavedMovies (package:umdb/services/movie_service.dart:105:5)
E/flutter ( 1617): <asynchronous suspension>
E/flutter ( 1617): #7      MovieDetailState.build.<anonymous closure>.<anonymous closure> (package:umdb/ui_widgets/movie_detail.dart:168:42)
E/flutter ( 1617): #8      State.setState (package:flutter/src/widgets/framework.dart:1240:30)
E/flutter ( 1617): #9      MovieDetailState.build.<anonymous closure> (package:umdb/ui_widgets/movie_detail.dart:142:27)
E/flutter ( 1617): #10     GestureRecognizer.invokeCallback (package:flutter/src/gestures/recognizer.dart:184:24)
E/flutter ( 1617): #11     TapGestureRecognizer.handleTapUp (package:flutter/src/gestures/tap.dart:524:11)
E/flutter ( 1617): #12     BaseTapGestureRecognizer._checkUp (package:flutter/src/gestures/tap.dart:284:5)
E/flutter ( 1617): #13     BaseTapGestureRecognizer.acceptGesture (package:flutter/src/gestures/tap.dart:256:7)
E/flutter ( 1617): #14     GestureArenaManager.sweep (package:flutter/src/gestures/arena.dart:158:27)
E/flutter ( 1617): #15     GestureBinding.handleEvent (package:flutter/src/gestures/binding.dart:224:20)
E/flutter ( 1617): #16     GestureBinding.dispatchEvent (package:flutter/src/gestures/binding.dart:200:22)
E/flutter ( 1617): #17     GestureBinding._handlePointerEvent (package:flutter/src/gestures/binding.dart:158:7)
E/flutter ( 1617): #18     GestureBinding._flushPointerEventQueue (package:flutter/src/gestures/binding.dart:104:7)
E/flutter ( 1617): #19     GestureBinding._handlePointerDataPacket (package:flutter/src/gestures/binding.dart:88:7)
E/flutter ( 1617): #20     _rootRunUnary (dart:async/zone.dart:1206:13)
E/flutter ( 1617): #21     _CustomZone.runUnary (dart:async/zone.dart:1100:19)
E/flutter ( 1617): #22     _CustomZone.runUnaryGuarded (dart:async/zone.dart:1005:7)
E/flutter ( 1617): #23     _invoke1 (dart:ui/hooks.dart:267:10)
E/flutter ( 1617): #24     _dispatchPointerDataPacket (dart:ui/hooks.dart:176:5)

---

please help me to fix it

Answer 1

It is very hard to deal with opening/closing the database. There are indeed many cases where you might access a closed database and even worse, if you open twice the same database, you get the same instance so the first close will close the database.

Personally I recommend keeping the database open.

If you need to handle multiple databases and open/close them for short actions, make sure you have the proper mutex/lock mechanism so that you control how your database is opened/accessed/closed without any other method trying to access the same database at the same time (ok that part is not clear sorry).

Something like that (although I really don't recommend opening/closing each time):

import 'package:synchronized/synchronized.dart';

final static _lock = Lock();

static Future<bool> saveAllMoviesIntoSqlite(List<Movie> movies) async {
  return _lock.synchronized(() {
    var db = new MovieProvider();
    await db.openMovieDB();
    await db.insertAll(movies);
    await db.closeMovieDB();
    return true;
  });
}

static Future<Map> getAllMoviesFromSqlite(int page) async {
  return _lock.synchronized(() {
    var db = new MovieProvider();
    await db.openMovieDB();
    List<Movie> movies = new List<Movie> ();
    movies = await db.paginate(page);
    await db.closeMovieDB();
    return {
      "currentPage": page,
      "movies": movies
    };
  });
}

Answer 2

You need to set null for your database instance when you close your database. Otherwise it will always return the old instance.

Future close() async {
    final db = await instance.database;
    _database = null;
    return db.close();
}

Answer 3

Keeping database without close, and using Singleton pattern is a good choice to solve this problem.

1- Create new file sql_lite_service.dart, this will contains SqlLiteService class with this full code:

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class SqlLiteService {
  String dBName = 'my_db_name';
  int dBVersion = 1;

  // Singleton pattern
  static final SqlLiteService _databaseService = SqlLiteService._internal();
  factory SqlLiteService() => _databaseService;
  SqlLiteService._internal();
  static Database? _database;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    // Set the version. This executes the onCreate function and provides a
    // path to perform database upgrades and downgrades.
    Database db = await _getDB();

    return db;
  }

  Future<Database> _getDB() async{
    final path = await _getPath(); // Get a location using getDatabasesPath

    return await openDatabase(
      path,
      onCreate: _onCreate,
      version: dBVersion,
      onConfigure: (db) async => await db.execute('PRAGMA foreign_keys = ON'),
    );
  }

  // create tables
  Future<void> _onCreate(Database db, int version) async {
    // Run the CREATE {users} TABLE statement on the database.
    await db.execute(
        'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, created_at datetime default current_timestamp, updated_at datetime default current_timestamp)'
    );
    // todo: Add your code here ...
  }

  Future<String> _getPath() async {
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, dBName);
    return path;
  }

}

2- Now you can get Database instance from any other class by:

final Database database = await SqlLiteService().database;
          
// usage example
await database.rawDelete(
  'DELETE FROM users WHERE id = ?',
  [1]
);

Using this way will solve database_closed error...