Test your database

Overview

In this post I'd like to show you how to test your database. I try to be abstract and general enough, though I want this post to be a basic example for a real life case you can build upon. I assume you're familiar with the official Flutter SQLite cookbook. Stay with me if you haven't tested your database yet.

Setup

First of all, you'll need two dependencies:

sqflite: ^1.3.0
path: ^1.7.0

and one test file in path/to/app/test/db/db_test.dart and one class file in path/to/app/lib/db/db.dart. Path is the dependency of my choice, but there are alternatives to it; sqflite is the most common choice for a SQLite database for Flutter, and there are alternatives to it too, the most interesting one being Moor.

The database class

In db.dart you can define your wrapper around the SQLite database, namely DBManager. I chose to implement it as a singleton, because you want to access the database manager from anywhere in your code:

class DBManager {
  static final DBManager _instance = DBManager._internal();
  _DBHolder _holder; // an holder for getting the database

  factory DBManager() {
    return _instance;
  }

  DBManager._internal() {
    _holder = _DBHolder();
  }
}

The DBManager methods will allow you to interact with the database, so you'd need for instance to delete the whole database, to insert, update, or delete an item, and last but not least to retrieve all the items. Each of the methods is async, because database operations can take long time:

  Future<void> deleteDb() async {}
  Future<void> insertItem(Item i) async {}
  Future<void> updateItem(Item i) async {}
  Future<void> deleteItem(int id) async {}
  Future<List<Item>> items() async {}

Lazy initialization

If you have noticed, I use an holder (namely _DBHolder), from where I get the Database reference, with the only purpose to lazily initialize the internal and only instance of Database. There are other ways to accomplish the same, with different implementations, but this way I can wait at the beginning of any of the DBManager methods:

  Future<void> deleteDb() async {
    final Database db = await _holder.db;
    // delete database
  }

  Future<void> insertItem(Item i) async {
    final Database db = await _holder.db;
    // insert item
  }

This way the first of the DBManager methods which is called by my code will lazily initialize the internal Database instance provided by the holder, while any other call will get it instantly.
_DBHolder is a very simple class:

class _DBHolder {
  Future<Database> _db;

  Future<Database> get db async {
    if (_db == null) {
      // initialize database
    }
    return _db;
  }
}

The data class

You would also need an item to insert and retrieve from the database. Your Item class could be located at path/to/app/lib/data/items.dart, and it can contain anything, like primitive, complex objects, and lists:

class Item {
  final int id;
  final String title;
  final Address address;  // an address structure
  final List<String> descriptions;
  final List<Address> addresses;  // even more addresses

  Item(
      {this.id,
      this.title,
      this.address,
      this.descriptions,
      this.addresses});
}

Depending on how you want to persist the items internally into your Database, you could have an empty() method, and a 'from' and 'to' methods, so that you can parse the items. For instance you could have a fromMap() and toMap() (like in the cookbook example), or a fromJson() and toJson() in case you want to persist the json serialization of the object.

The tests

Now that we have specified the dependencies, defined the DBManager and the Item classes, we are ready to write the tests:

@Skip("sqflite cannot run on the machine")

void main() {
  setUp(() async {
    // clean up db before every test
    await DBManager().deleteDb();
  });

  tearDownAll(() async {
    // clean up db after all tests
    await DBManager().deleteDb();
  });

  group('DBManager', () {
    test('Insert items', () async {
      // at the beginning database is empty
      expect((await DBManager().items()).isEmpty, true);

      // insert one empty item
      DBManager().insertItem(Item.empty());
      expect((await DBManager().items()).length, 1);

      // insert more empty items
      DBManager().insertItem(Item.empty());
      DBManager().insertItem(Item.empty());
      DBManager().insertItem(Item.empty());
      expect((await DBManager().items()).length, 1);

      // insert a valid item
      DBManager().insertItem(Item.fromMap(myMap1));
      expect((await DBManager().items()).length, 2);
      // insert few times more the same item 
      DBManager().insertItem(Item.fromMap(myMap1));
      DBManager().insertItem(Item.fromMap(myMap1));
      DBManager().insertItem(Item.fromMap(myMap1));
      expect((await DBManager().items()).length, 2);

      // insert other valid items
      DBManager().insertItem(Item.fromMap(myMap2));
      DBManager().insertItem(Item.fromMap(myMap3));
      DBManager().insertItem(Item.fromMap(myMap4));
      expect((await DBManager().items()).length, 5);
    });

    test('Update items', () async {});
    test('Delete items', () async {});
    test('Retrieve items', () async {});
    test('Delete database', () async {});
}

Sqflite cannot run as a normal junit test from Android Studio. There are many ways to test it, but the one I found simpler is to run the test suite from command line:

flutter run path/to/app/test/db/db_test.dart

it will run the tests as a regular flutter application, so it takes a little bit longer to run them, it will start app an headless empty screen for the application, and eventually you need to kill it; on the other hand you'll be able to see that all tests are passed, or any failure in the command line output.

Conclusion

In this post I showed how to write a database wrapper class, and write tests for its methods. This is the proper way you can have all the database operations executed in a controlled environment where you can actually verify your database behaves how you assume it to behave. Now you've no more excuses not to have your database under test, as I hope you can use this post as an entry point to build up anything you'd need to test.