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.


Comments

  1. You have an repository gitHub with an exemple implemented?

    ReplyDelete
    Replies
    1. Hi Leonard,
      I've more than one, but they're all private. All the code of this post is a very light obfuscation of real life tests I wrote. That should be enough to get you going.

      Delete
  2. From what I see, your _DBHolder._db is always null.
    How did you instantiate the db?
    I have a database working in my production app, but I can't use the same initialization in my tests since they don't run on a machine(path is completely different).
    Did you mock this and might share it?

    ReplyDelete
    Replies
    1. Hi, everything you're looking after is in the post. Let me point it all out for you explicitly.

      1. _DBHolder._db is never null. As you can see, the first time is accessed via its getter, it gets initialized:

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

      and you'll await when retrieving it:

      final Database db = await _holder.db;

      This way I implemented lazy initialization of the db, which is the main reason to have the holder in first place.

      2. In my tests I don't mock the db, I actually wanted to test the real instance of the database, which is the main reason for this post. But I can't run it as a normal junit test from Android Studio, so I run it instead from command line:

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

      this way it starts an headless screen for the application, and all outputs are prompted to the command line.
      Hope it helps!

      Delete
  3. Hi! Awesome post and thanks but I still cannot test because of this:

    MissingPluginException(No implementation found for method getDatabasesPath on channel com.tekartik.sqflite)
    package:flutter/src/services/platform_channel.dart 157:7 MethodChannel._invokeMethod


    I've tried to add it in the MainActivity.kt but I'm not sure if I did it properly:

    package com.example.keep_track

    import io.flutter.embedding.android.FlutterActivity
    import com.tekartik.sqflite.SqflitePlugin

    class MainActivity: FlutterActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    SqflitePlugin.registerWith(registrarFor("com.tekartik.sqflite.SqflitePlugin"))
    }
    }

    Thanks!

    ReplyDelete
    Replies
    1. Hi,
      try to follow the official tutorial first:

      https://flutter.dev/docs/cookbook/persistence/sqlite

      so that you have something running, with a very simple and basic data class that you can insert and remove. Once you get that working, try to add the test as I explain here, literally step by step, and run the test from command line.

      The SQLite is a Flutter dependency, so you'll add it to the pubspec.yaml file; these are the only two dependencies that you'd need to run my example:

      sqflite: ^1.3.0
      path: ^1.7.0

      Delete

Post a Comment