Flutter: How to best instantiate SQFlite db object when class is created

Question

I'm learning Flutter and am on to my second program. This program uses SQFlite, and I copied most of the db handling from another program which appears to use a common pattern. I have made some modifications to it.

What I don't like, is having to evaluate the db every time to determine if it needs to be created as in "Database db = await this.db;". I would prefer it if it was simply created when the class is created, and then just used as instantiated. However, I'm unfamiliar with "._internal", and unfamiliar with "factory", so I'm unsure of the best way to achieve that aim.

Would appreciate if someone could show me how to best achieve that. IE. Remove the need for "Database db = await this.db;", and just reference the db.

Extract of relevant code for DbHelper is as follows:

import 'package:sqflite/sqflite.dart';
import 'dart:async';
import 'dart:io';
import 'package:path_provider/path_provider.dart';
import 'noteRec.dart';

class DbHelper {
  static final DbHelper _dbHelper = DbHelper._internal();
  static const String sTblNotes = "Notes";
  static const String sColId = "Id";
  static const String sColTitle = "Title";
  static const String sColDetail = "Detail";

  DbHelper._internal();

  factory DbHelper() {
    return _dbHelper;
  }

  static Database _db;

  Future<Database> get db async {
    return _db != null ? _db : await initializeDb();
  }

  Future<Database> initializeDb() async {
    Directory dir = await getApplicationDocumentsDirectory();
    String path = dir.path + "/Notes.db";
    _db = await openDatabase(path, version: 1, onCreate: _createDb);
    return _db;
  }

  void _createDb(Database db, int newVersion) async {
    await db.execute(
        "CREATE TABLE $sTblNotes($sColId INTEGER PRIMARY KEY, $sColTitle TEXT, " +
            "$sColDetail TEXT)");
  }

  Future<List> getNoteRecs() async {
    Database db = await this.db;
    var result =
        await db.rawQuery("SELECT * FROM $sTblNotes ORDER BY $sColTitle ASC");
    return result;
  }

Answer 1

The following appears to do what I want to achieve.

dbHelper.dart

import 'dart:async';
import 'dart:io';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'noteRec.dart';

class DbHelper {
  static final DbHelper _dbHelper = DbHelper._internal();
  static const String sTblNotes = "Notes";
  static const String sColId = "id";
  static const String sColTitle = "title";
  static const String sColDetail = "detail";
  static Database _db;

  DbHelper._internal();

  factory DbHelper() {
    return _dbHelper;
  }

  Future<bool> openDb() async {
    if (_db == null) {
      Directory dir = await getApplicationDocumentsDirectory();
      _db = await openDatabase("${dir.path}/Notes.db",
          version: 1, onCreate: _createDb);
    }
    return (_db != null);
  }

  void _createDb(Database db, int newVersion) async {
    await db.execute(
        "CREATE TABLE $sTblNotes($sColId INTEGER PRIMARY KEY, $sColTitle TEXT, " +
            "$sColDetail TEXT)");
  }

  Future<List> getNoteRecs() async {
    return await _db
        .rawQuery("SELECT * FROM $sTblNotes ORDER BY $sColTitle ASC");
  }

main.dart

import 'package:flutter/material.dart';
import 'dbHelper.dart';
import 'NotesList.dart';

DbHelper _dbHelper = DbHelper();

void main() async {
  await _dbHelper.openDb();
  runApp(MaterialApp(home: NotesList()));
}