SQLite connections returns null in Flutter

Question

I am using SQFLite flutter package in my app. However, I keep getting null as a result of the connection to the DB. Below is my code used in initializing DB, creating table, and methods for adding items and fetching them:

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

  class StoreDBProvider {

       Database db;

       init() async {
          Directory documentsDirectory = await 
          getApplicationDocumentsDirectory();
          final path = join(documentsDirectory.path, "carts.db");
          db = await openDatabase(
               path,
               version: 3,
               onCreate: (Database newDB, int version){
                     newDB.execute('CREATE TABLE Cart (id INTEGER 
                                PRIMARY KEY, name TEXT, price DOUBLE, image TEXT, rating DOUBLE)');
               }
               );
               return db;
        }


        Future<dynamic> fetchItem(int id) async {
             print("DB CONNECTION IS: $db");

             final maps = await db.query(
                          "Cart",
                          columns: null,
                          where: "id = ?",
                          whereArgs: [id],
             );

             print(maps);

             if(maps.length > 0){
                 print("Data exists");
                 return maps.first;
             }

             return null;
        }


        Future<int> addItem(item) async{
             return db.insert("Cart", item);
        }
}  

After some debuggin, I notice that "db" value is null. Here is the error message:

 E/flutter ( 6921): [ERROR:flutter/shell/common/shell.cc(182)] Dart 
 Error: Unhandled exception:
 E/flutter ( 6921): NoSuchMethodError: The method 'insert' was called on null.
 E/flutter ( 6921): Receiver: null
 E/flutter ( 6921): Tried calling: insert("Cart", _LinkedHashMap len:5)
 E/flutter ( 6921): #0      Object.noSuchMethod 
 (dart:core/runtime/libobject_patch.dart:50:5)
 E/flutter ( 6921): #1      StoreDBProvider.addItem 

(file:///Users/Oluwashola/workspace/fashion_style/lib/src/resources/store_db_provider.dart:46:15)

Please, any assistance here is appreciated. Thank you.

Answer 1

If it helps, here is my working code. Note I use an async getter for db and await when using the db in a method. Afraid I'm rather a Flutter newbie too.

Note I interact with DatabaseHelper via a Future. See second code block. I think this requires using FutureBuilder. I suspect you need to use this to cope with (slightly) slow to load async db calls.

class DatabaseHelper {
  final String tableName = "Gear";

  static Database _db;

  Future<Database> get db async {
    if (_db != null) return _db;
    _db = await initDb();
    await importData();
    return _db;
  }

  initDb() async {
    // Get a location using path_provider
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, "gear_log.db");

    await deleteDatabase(path);
    var theDb = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      print('creating db...');

      String sql = await rootBundle.loadString('assets/db/schema.txt');
      for (var s in sql.split(";")) {
        if (s.length > 5) {
          // catching any hidden characters at end of schema
          await db.execute(s + ';');
        }
      }
      // When creating the db, create the table
    });

    return theDb;
  }

  importData() async {
    final datasources =
        'observation_bodyissue.json,observation_observation.json,observation_observation_bodyIssues.json,observation_observation_shoeIssues.json,observation_shoeissue.json,observation_gearissue.json,runner_runner.json,gear_actualpair.json,gear_characteristic.json,gear_shoe.json,gear_family.json,gear_maker.json,gear_gear.json,gear_gear_characteristics.json,users_user.json'
            .split(',');
    var batch = _db.batch();

    for (var datasource in datasources) {
      try {
        String str = await rootBundle.loadString('assets/db/data/$datasource');
        String table = datasource.split('.')[0];

        if (str.length > 0 && str != '[]') {
          List<dynamic> _list = await json.decode(str);
          for (var _json in _list) {
            batch.insert(table, _json);
          }
        }
      } catch (e) {
        print(e.toString());
      }
    }
    print('added db data');
    var results = await batch.commit();
    //print(results);
    //print('imported data');
  }

  Future<List<Item>> getItems() async {
    var dbClient = await db;

    List<Map> list = await dbClient.rawQuery('SELECT *'
        'FROM "gear_actualpair" '
        'DESC, "gear_actualpair"."created" DESC');

    //prob need to specify 'first X' at sometime

    List<Item> items = new List();
    for (int i = 0; i < list.length; i++) {
      Item item = Item.fromMap(list[i]);
      if (list[i]['selected'] == 1) {
        item.observations = await getObservations(list[i]['id']);
      }
      items.add(item);
    }

    return items;
  }
}

Interacting with dbHelper class:

Future<List<Item>> fetchItemsFromDatabase() async {
  var dbHelper = DatabaseHelper();
  Future<List<Item>> items = dbHelper.getItems();
  return items;
}

Answer 2

I have common abstract provider like this:

abstract class DbProvider<T extends DbItem> {
  static Future<String> get localPath async {
    final directory = await getApplicationDocumentsDirectory();
    return directory.path;
  }

  Database db;

  Future open() async {
    String path = await localPath;
    db = await openDatabase(join(path, file_name), version: 1, onCreate: (database, version) async {
      await database.execute(''' create table */code for creating table/* ''');
    });
  }

  Future _update(T item) async {
    return await db.update(getTableName(), item.toMap(), where: "$col_dbId = ?", whereArgs: [item.dbId]);
  }

  Future _insert(T item) async {
    return await db.insert(getTableName(), item.toMap());
  }

  Future delete(String dbId) async {
    return await db.delete(getTableName(), where: "$col_dbId = ?", whereArgs: [dbId]);
  }

  Future close() => db.close();

  Future remove() => db.delete(getTableName());

  String getTableName();

  Future save(T item);

  Future<Iterable<T>> get();
}

And for doing some transactions:

void loadDbData() async {
  FavoriteProvider favoriteProvider = FavoriteProvider();
  await favoriteProvider.open();
  Iterable<DbItem> favorites = await favoriteProvider.getConferences();
  await favoriteProvider.close();
  ...
}