Right now I can create the database, and do tests on that one.

However, I want all the tests to be independant of eachtoher, meaning, I want each test to start with a clean database.

How can I do this?

CodePudding user response：

you may want to use stored procedure on this and can be reuse multiple times as needed

CodePudding user response：

I can think of 2 solutions:

1. Use an in memory database

SQLite supports an in memory database that is always empty:

import 'dart:async';

import 'package:sqflite_common/sqlite_api.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:test/test.dart';

Future main() async {
  // Setup sqflite_common_ffi for unit test
  sqfliteFfiInit();
  var factory = databaseFactoryFfi;
  test('test1', () async {
    var db = await factory.openDatabase(inMemoryDatabasePath,
        options: OpenDatabaseOptions(
            version: 1,
            onCreate: (db, version) async {
              await db.execute(
                  'CREATE TABLE Task (id INTEGER PRIMARY KEY, name TEXT)');
            }));
    // Empty!
    expect(await db.query('Task'), hasLength(0));
    await db.close();
  });
  test('test2', () async {
    var db = await factory.openDatabase(inMemoryDatabasePath,
        options: OpenDatabaseOptions(
            version: 1,
            onCreate: (db, version) async {
              await db.execute(
                  'CREATE TABLE Task (id INTEGER PRIMARY KEY, name TEXT)');
            }));
    // Empty!
    expect(await db.query('Task'), hasLength(0));
    await db.close();
  });
}

2. Delete the database during setUp()

An alternative if you want a real database (file) and test more complex scenarios like schema upgrade is to simply delete the database before, for example in the setUp() function:

Future main() async {
  // Setup sqflite_common_ffi for unit test
  sqfliteFfiInit();
  var factory = databaseFactoryFfi;
  var dbPath = 'test.db';
  setUp(() async {
    await factory.deleteDatabase(dbPath);
  });
  test('test1', () async {
    var db = await factory.openDatabase(dbPath,
        options: OpenDatabaseOptions(
            version: 1,
            onCreate: (db, version) async {
              await db.execute(
                  'CREATE TABLE Task (id INTEGER PRIMARY KEY, name TEXT)');
            }));
    // Empty!
    expect(await db.query('Task'), hasLength(0));
    await db.close();
  });
  test('test2', () async {
    var db = await factory.openDatabase(dbPath,
        options: OpenDatabaseOptions(
            version: 1,
            onCreate: (db, version) async {
              await db.execute(
                  'CREATE TABLE Task (id INTEGER PRIMARY KEY, name TEXT)');
            }));
    // Empty!
    expect(await db.query('Task'), hasLength(0));
    await db.close();
  });
}