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.

Solution 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;
}

Solution 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();
  ...
}

Comments

[Deshola]

Andyw, thank you for your time and effort. I was missing two aspects here: First, I wasn't calling the connect to DB (which is the "db" object) on the methods. Second, when I called the methods, I needed to treat them as async since they return Future object. I implemented those two and everything works pretty well now. One more thing to mention here, in this scenario, it helps tremendously to change SQFLite version in the code (when specifying the command) while recreating the table.

[Deshola]

Thank you for your time and effort. I was missing two aspects here: First, I wasn't calling the connect to DB (which is the "db" object) on the methods. Second, when I called the methods, I needed to treat them as async since they return Future object. I implemented those two and everything works pretty well now. One more thing to mention here, in this scenario, it helps tremendously to change SQFLite version in the code (when specifying the command) while recreating the table.