Flutter: Using sqflite with provider

I've been tinkering with flutter for 2 months now. I love using the provider package for state management. I'm using the sqflite package on pub.dev, other than wrapping sqlite functionality, it provide built in api to get the database path, so we don't have to deal with it manually and it save a couple lines of code. The only thing i cannot find was how implementation of provider with sqflite works. So this is my workaround.

Btw, if you have question, need some help or feedback, don't hesitate to reach me on twitter.

This is how i integrate sqflite with provider package to have one single reference to the database and open it only once.

Advantages

Database and data provider are not tightly coupled. You can easily swap it during unit test and widget test.

Improvement that can be done

  • Proper error handling if needed.

Test Consideration

  • Unit Test are not possible except to mock it (if you aspire to achieve that 100% mark)
  • Exclude the provider during widget test and pass empty list instead
  • If you still want to test it automatically, write an integration test intead

Define Database Provider

import 'package:flutter/foundation.dart';
import 'package:sqflite/sqflite.dart' as sql;
import 'package:path/path.dart' as path;

class DBHelper with ChangeNotifier {
  static final tableName = 'my_table';
  sql.Database db;

  DBHelper() {
    // this will run when provider is instantiate the first time
    init();
  }

  void init() async {
    final dbPath = await sql.getDatabasesPath();
    db = await sql.openDatabase(
      path.join(dbPath, 'places.db'),
      onCreate: (db, version) {
        final stmt = '''CREATE TABLE IF NOT EXISTS $tableName (
            id TEXT PRIMARY KEY,
            title TEXT,
            image TEXT
        )'''.trim().replaceAll(RegExp(r'[\s]{2,}'), ' ');
        return db.execute(stmt);
      },
      version: 1,
    );
    // the init funciton is async so it won't block the main thread
    // notify provider that depends on it when done
    notifyListeners();
  }

  Future<void> insert(String table, Map<String, Object> data) async {
    await db.insert(table, data, conflictAlgorithm: sql.ConflictAlgorithm.replace);
  }

  Future<List<Map<String, dynamic>>> getData(String table) async {
    return await db.query(table);
  }
}

Other provider that depends on Database provider

import 'dart:io';

import 'package:flutter/foundation.dart';
import '../models/data.dart';
import '../helper/db_helper.dart';

class DataProvider with ChangeNotifier {
  final DBHelper dbHelper;
  List<Data> _items = [];
  final tableName = 'my_table';

  DataProvider(this._items, {this.dbHelper}) {
    if (dbHelper != null)
      fetchAndSetData();
  }

  List<Data> get items => [..._items];

  void addPlace(String title, File selectedImage) {
    if (dbHelper.db != null) { // do not execute if db is not instantiate
      final newPlace = Place(
          id: DateTime.now().millisecondsSinceEpoch.toString(),
          title: title,
          location: null,
          image: selectedImage
        );
      _items.add(newPlace);
      notifyListeners();
      dbHelper.insert(tableName,
        {'id': newPlace.id, 'title': newPlace.title, 'image': newPlace.image.path});
    }
  }

  Future<void> fetchAndSetData() async {
    if (dbHelper.db != null) { // do not execute if db is not instantiate
      final dataList = await dbHelper.getData(tableName);
      _items = dataList.map((item) => Data(
        id: item['id'],
        title: item['title'],
        image: File(item['image'])
      )).toList();
      notifyListeners();
    }
  }

}

Provider declaration

// main.dart
class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MultiProvider(
      providers: [
        ChangeNotifierProvider(create: (context) => DBHelper()),
        ChangeNotifierProxyProvider<DBHelper, DataProvider>(
          create: (context) => DataProvider([], dbHelper: null),
          update: (context, db, previous) => DataProvider(previous.items, dbHelper: db),
        ),
      ],
      child: MaterialApp(
        ...
      ),
    );
  }
}

References