Not closing the database with Flutter's SQFlite?

Question

I'm trying to understand database operations using the SQFlite plugin for Flutter. In the usage recommendation docs, the author says:

The API is largely inspired from Android ContentProvider where a typical SQLite implementation means opening the database once on the first request and keeping it open.

Personally I have one global reference Database in my Flutter application to avoid lock issues. Opening the database should be safe if called multiple times.

Keeping a reference only in a widget can cause issues with hot reload if the reference is lost (and the database not closed yet).

Does this mean I make a singleton class (like here) that opens a database connection and then I never close it? That is, I never do this:

await database.close();

I've run into concurrency issues in the past with Android SQLite (as described here) so I generally used a content provider to get around that. However, I was just using it without really understanding what the content provider was doing behind the scenes. Does keeping around a single connection to the database do the same thing? Do I need to close the DB when the app exits? This user seems to think that it doesn't matter.

Answer 1

I'm not closing the database and I haven't ran in to any issues, however it is always best practise to close all not needed DB connections.

You can initialise the DB by overriding onInitState() where you can call you function creating the DB, and close by overriding onDispose() where you can call db.close()

Alternatively, you can open/close connection for every query but that would add extra processing overhead and there is no need for it, especially if you're making frequent DB calls.

For more detailed information you can refer to this post: https://medium.com/@greg.perry/flutter-and-sqlite-f72878bc5859