Testing sqflite code with sqflite_common_ffi

The native sqflite plugin cannot run in dart test or flutter test. package:sqflite_common_ffi provides databaseFactoryFfi, a real SQLite implementation (via package:sqlite3) that runs on the developer machine and on Linux/macOS/Windows CI runners, so database code is tested against SQLite itself rather than mocks.

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

void main() {
  sqfliteFfiInit();
  test('insert and query', () async {
    var db = await databaseFactoryFfi.openDatabase(inMemoryDatabasePath);
    await db.execute('CREATE TABLE Test (id INTEGER PRIMARY KEY, value TEXT)');
    await db.insert('Test', {'value': 'a'});
    expect(await db.query('Test'), [
      {'id': 1, 'value': 'a'},
    ]);
    await db.close();
  });
}

Guidelines

Setup

• Add sqflite_common_ffi to dev_dependencies (to dependencies if the app itself uses it on desktop). sqlite3 >= 3 bundles SQLite through build hooks: run tests with dart test / flutter test, nothing to install.
• Import package:sqflite_common_ffi/sqflite_ffi.dart in tests. It exports the whole sqflite_common API (Database, OpenDatabaseOptions, inMemoryDatabasePath, DatabaseException, global databaseFactory, openDatabase...). In Flutter tests it replaces package:sqflite/sqflite.dart (importing both triggers the unnecessary_import lint); both expose the same global databaseFactory.
• Call sqfliteFfiInit() once, at the top of main() or in setUpAll. Required on Windows, harmless elsewhere.
• Mark Dart VM test files with @TestOn('vm') (before library;) when the package also runs tests on the web: databaseFactoryFfi throws UnsupportedError in a browser.

Choosing the factory

• Code that receives a DatabaseFactory (recommended design): pass databaseFactoryFfi and open inMemoryDatabasePath.
• Code that calls the global openDatabase() / deleteDatabase() (typical Flutter code): set databaseFactory = databaseFactoryFfi; in setUpAll (or at the top of main). Do it once per test process; setting it again only prints a warning.
• testWidgets (Flutter widget tests): use databaseFactoryFfiNoIsolate. The isolate-based factory can hang under the fake async zone of WidgetTester; the no-isolate factory runs SQLite synchronously in the test isolate.
• Plain test() inside a Flutter package works with either factory; databaseFactoryFfi is closest to production.

Isolation between tests

• Prefer inMemoryDatabasePath: each openDatabase after a close() gives an empty database and nothing is left on disk. Always close() in the test (or in tearDown) so the next test starts fresh.
• For file-based tests (migrations, onUpgrade, reopen scenarios) use a unique relative name per test: it resolves under .dart_tool/sqflite_common_ffi/databases, is created on open and is ignored by git in most setups. Call factory.deleteDatabase(path) in setUp so a previous run cannot leak state.
• Do not share one Database across tests through a global; open it in setUp and close it in tearDown.

Assertions

• Errors are DatabaseException: assert with isNoSuchTableError('Test'), isUniqueConstraintError('Test.value'), isSyntaxError(), isDatabaseClosedError() or getResultCode(), never on message text.
• expect(await db.query(...), [...]) works: rows are List<Map<String, Object?>> and deep-equal to literal maps.
• await db.getVersion() returns the user_version and is a quick check that onCreate / onUpgrade ran.

CI

• Linux, macOS and Windows runners all work with sqlite3 >= 3 without extra packages. Only the legacy sqlite3 v2 setup needed sudo apt-get install libsqlite3-dev on Ubuntu.
• Keep test paths relative or under a temp directory; avoid absolute developer-machine paths.

Examples

Testing a class that takes a DatabaseFactory

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

class NoteStore {
  NoteStore(this.factory);
  final DatabaseFactory factory;
  late Database db;

  Future<void> open(String path) async {
    db = await factory.openDatabase(
      path,
      options: OpenDatabaseOptions(
        version: 1,
        onCreate: (db, _) => db.execute(
          'CREATE TABLE Note (id INTEGER PRIMARY KEY, text TEXT NOT NULL)',
        ),
      ),
    );
  }

  Future<int> add(String text) => db.insert('Note', {'text': text});
  Future<List<String>> texts() async =>
      (await db.query('Note', orderBy: 'id')).map((r) => r['text'] as String).toList();
}

void main() {
  sqfliteFfiInit();
  late NoteStore store;

  setUp(() async {
    store = NoteStore(databaseFactoryFfi);
    await store.open(inMemoryDatabasePath);
  });
  tearDown(() => store.db.close());

  test('add', () async {
    await store.add('hello');
    expect(await store.texts(), ['hello']);
  });

  test('not null constraint', () async {
    try {
      await store.db.insert('Note', {'text': null});
      fail('should throw');
    } on DatabaseException catch (e) {
      expect(e.isNotNullConstraintError('Note.text'), isTrue);
    }
  });
}

Flutter test for code using the global openDatabase

import 'package:flutter_test/flutter_test.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

void main() {
  setUpAll(() {
    sqfliteFfiInit();
    databaseFactory = databaseFactoryFfi;
  });

  test('openDatabase works in flutter test', () async {
    var db = await openDatabase(
      inMemoryDatabasePath,
      version: 1,
      onCreate: (db, version) async {
        await db.execute('CREATE TABLE Test (id INTEGER PRIMARY KEY, value TEXT)');
      },
    );
    await db.insert('Test', {'value': 'my_value'});
    expect(await db.query('Test'), [
      {'id': 1, 'value': 'my_value'},
    ]);
    await db.close();
  });
}

Widget test: no isolate

import 'package:flutter_test/flutter_test.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

void main() {
  sqfliteFfiInit();
  databaseFactory = databaseFactoryFfiNoIsolate;

  testWidgets('database in a widget test', (tester) async {
    var db = await openDatabase(inMemoryDatabasePath);
    await db.execute('CREATE TABLE Test (id INTEGER PRIMARY KEY)');
    expect(await db.query('Test'), isEmpty);
    await db.close();
  });
}

Migration test with a file that is reopened

@TestOn('vm')
library;

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

void main() {
  sqfliteFfiInit();
  final factory = databaseFactoryFfi;
  const path = 'migration_test.db'; // under .dart_tool/sqflite_common_ffi/databases

  setUp(() => factory.deleteDatabase(path));

  test('upgrade from 1 to 2 adds a column', () async {
    var db = await factory.openDatabase(
      path,
      options: OpenDatabaseOptions(
        version: 1,
        onCreate: (db, _) => db.execute('CREATE TABLE Test (id INTEGER PRIMARY KEY)'),
      ),
    );
    await db.close();

    db = await factory.openDatabase(
      path,
      options: OpenDatabaseOptions(
        version: 2,
        onUpgrade: (db, oldVersion, newVersion) async {
          if (oldVersion < 2) {
            await db.execute('ALTER TABLE Test ADD COLUMN name TEXT');
          }
        },
      ),
    );
    expect(await db.getVersion(), 2);
    await db.insert('Test', {'name': 'ok'});
    await db.close();
  });
}

Expecting a missing table error

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

void main() {
  sqfliteFfiInit();
  test('no such table', () async {
    var db = await databaseFactoryFfi.openDatabase(inMemoryDatabasePath);
    try {
      await db.query('Missing');
      fail('should throw');
    } on DatabaseException catch (e) {
      expect(e.isNoSuchTableError('Missing'), isTrue);
    } finally {
      await db.close();
    }
  });
}

Common mistakes

• Forgetting sqfliteFfiInit(): tests pass on macOS/Linux and fail on Windows.
• Using databaseFactoryFfi inside testWidgets: use databaseFactoryFfiNoIsolate.
• Calling the global openDatabase() without setting databaseFactory first: StateError: databaseFactory not initialized.
• Not closing databases between tests, so singleInstance returns the previous test's database (same path) with stale tables.
• Reusing a file path across tests without deleteDatabase in setUp.
• Running the test file in a browser configuration: add @TestOn('vm').
• Matching on exception messages instead of DatabaseException helpers.