Testing Drift database in Flutter

Disclaimer: This article assumes you have the basic setup for drift database as outlined here and you know basic drift queries. Let's jump into it.

Unit Testing of a drift database is done with an in-memory version of the database. This ensures that the tests can run as fast as possible and we can be confident the database works as expected since there’s not much difference between the disk version and the in-memory one. In this article, we will go through the steps to set up the database and write some unit tests for a simple todo app. Yes, it’s another todo app but we won’t focus on the todo app part just the database. Let’s jump right into it.

For the in-memory database, we need to have SQLite installed on our machine for the tests to be possible without running on a physical device or emulator. The reasons for this have been outlined here and this snippet from drift documentation adds instructions on how to add SQLite on your machine if not present already.

Installing sqlite

We can’t distribute an sqlite installation as a pub package (at least not as something that works outside of a Flutter build), so you need to ensure that you have the sqlite3 shared library installed on your system.

On macOS, it’s installed by default.

On Linux, you can use the libsqlite3-dev package on Ubuntu and the sqlite3 package on Arch (other distros will have similar packages).

On Windows, you can download ‘Precompiled Binaries for Windows’ and extract sqlite3.dll into a folder that’s in your PATH environment variable. Then restart your device to ensure that all apps will run with this PATH change.

1. Setting up the database

Create a flutter project if you haven’t already to follow along:

1. Under the lib folder add a folder that will host our database-related files in this case I will call mine ‘database’.
2. Create the ‘app_database.dart’ in the folder to host the database.
3. Run the code generator command to generate the necessary code.

2. Unit Tests setup.

Now that we have the base database code we can begin writing the testing code.

1. Under the tests folder mirroring the same folder structure as for the database, let's create app_database_test.dart file under the database folder in the tests folder.
2. Create setUp and tearDown methods on the test file to add logic for opening and closing the database for each test we run.

import 'package:flutter_test/flutter_test.dart';
import 'package:todos/database/app_database.dart';

void main() {
  late AppDatabase appDatabase;

  // Opens the database for each test.
  setUp(() {
    appDatabase = AppDatabase();
  });

  // Closes the database after each test.
  tearDown(() async {
    await appDatabase.close();
  });
}

You will notice by creating the database this way we have not used an in-memory database but the file-based one defined in the _openConnection method above. This is contrary to what we want. Let’s fix that.

In app_database.dart, add a named constructor that enables creating our test database.

Line 15 adds the named constructor that enables us to create the in-memory database. Our test file should look like this now.

import 'package:drift/native.dart';
import 'package:flutter_test/flutter_test.dart';
import 'package:todos/database/app_database.dart';

void main() {
  late AppDatabase appDatabase;

  // Opens the database for each test.
  setUp(() {
    appDatabase = AppDatabase.forTesting(NativeDatabase.memory());;
  });

  // Closes the database after each test.
  tearDown(() async {
    await appDatabase.close();
  });
}

3. Add Todos Table

Let’s add a todos table by extending the Table class.

class Todos extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 6, max: 10)();
  TextColumn get description => text()();
}

True to our TDD nature let's add methods that will interact with the Todos table for simple CRUD actions.

This sets up the methods that we are going to test.

4. Writing the tests

We are going to start with the basic tests. Here’s what the tests will look like:

We could re-use the methods we have defined to add todo in the getTodo tests but I chose to use drift API methods just in case our addTodo function is broken.

If you run the tests at this point they will all fail with unimplemented error that is intended.

Let’s make them pass by adding the necessary code.

1. addTodo

  Future<Todo> addTodo(String title, String description) {
    return into(todos).insertReturning(
      TodosCompanion.insert(title: title, description: description),
    );
  }

You can hover over the test case for adding todos and hit run. This particular test should pass.

2. getTodo

Future<Todo> getTodo(int todoId) {
  return (select(todos)..where((tbl) => tbl.id.equals(todoId))).getSingle();
}

Just like the previous one hover over the test case description and hit run. You should get a green.

3. updateTodo

Future<bool> updateTodo(int todoId, String title, String description) {
   return update(todos)
        .replace(Todo(id: todoId, title: title, description: description));
}

4. deleteTodo

Future<bool> deleteTodo(int todoId) async {
  return await (delete(todos)..where((t) => t.id.equals(todoId))).go() == 1;
}

Delete returns the number of rows affected and in this case, it should be one.

Run all the tests from the main method and you should get the satisfying green check marks. ✅

On a bigger project, you might be using DAOs and will not necessarily have all your methods in the main database class. In the next article, we will look into how testing changes when we defined our queries in DAOs.

If you seek tranquility, do less.

- Marcus Aurelius