skip to content

What does sqflite_common_ffi add to a Flutter project using sqflite, and how do you use it to unit-test database code?

level: middleimportance: nice to knowfreq 20%

answer

  1. the plugin has no Windows or Linux
  2. flutter test has no platform side
  3. sqfliteFfiInit then databaseFactoryFfi
  4. set the global databaseFactory once
  5. host SQLite may differ from the device

basics

~20 s

sqflite_common_ffi is a Dart FFI implementation of the sqflite API over a native SQLite library. It runs on Windows, Linux and macOS and in plain unit tests, where the sqflite plugin has no platform side; call sqfliteFfiInit() and set databaseFactory = databaseFactoryFfi.

solid answer

~40 s

The `sqflite` plugin talks to native code on Android, iOS and macOS only, so it does not work on Windows or Linux, and it does not work under `flutter test`, where no platform plugins run. `sqflite_common_ffi` implements the same `sqflite_common` API with Dart FFI over a SQLite library, on the Dart VM and in Flutter. In a test I call `sqfliteFfiInit()` and set the global `databaseFactory = databaseFactoryFfi` once, then open either `inMemoryDatabasePath` for a scratch database or a temp file when I need to close and reopen — which is how I test that version 1 upgrades to version 2. For a desktop app I do the same in `main` on Windows and Linux. The caveat is that tests use the host's SQLite version, which may differ from the device's.

code

dart · 35 lines
dart
import 'dart:io';

import 'package:flutter_test/flutter_test.dart';
import 'package:path/path.dart' as p;
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

import 'package:ledger/data/ledger_db.dart'; // openLedgerAt(path), version 2

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

  test('v1 ledger upgrades to v2 with a category column', () async {
    final dir = await Directory.systemTemp.createTemp('ledger');
    final path = p.join(dir.path, 'ledger.db');

    final v1 = await openDatabase(
      path,
      version: 1,
      onCreate: (db, _) => db.execute(
        'CREATE TABLE expenses (id INTEGER PRIMARY KEY, amount_cents INTEGER NOT NULL)',
      ),
    );
    await v1.insert('expenses', {'amount_cents': 500});
    await v1.close();

    final v2 = await openLedgerAt(path); // the app's real open, version 2
    final rows = await v2.query('expenses');
    expect(rows.single['category'], isNull);
    await v2.close();
  });
}

go deeper

for a junior

Know that sqflite needs a platform side, and that sqflite_common_ffi lets the same code run in unit tests and on desktop.

for a middle

Explain sqfliteFfiInit, the global databaseFactory, in-memory versus temp-file databases, and how to write a migration test.

for a senior

Build a test harness around real SQL and upgrade files, and know which platform quirks FFI tests cannot reproduce.

for a principal

Decide how database behaviour is verified across platforms — host FFI tests, device integration tests — and what each is trusted for.

## Why a second implementation exists `sqflite` is a **Flutter plugin**: its Dart API forwards SQL over a platform channel to native code, and that native side exists for Android, iOS and macOS. Two common environments have no such side: - **Windows and Linux desktop builds** — the plugin has no implementation there; - **`flutter test` unit and widget tests** — they run on the host Dart VM with no platform plugins, so any sqflite call fails. `sqflite_common_ffi` fills both gaps. It implements the shared `sqflite_common` API — `DatabaseFactory`, `Database`, `Transaction`, `Batch` — using **Dart FFI** over the `sqlite3` package's native SQLite library. Because it is not a plugin, it runs on the plain Dart VM as well as in Flutter apps on Linux, macOS and Windows. Its database access runs in a separate isolate. | Environment | `sqflite` plugin | `sqflite_common_ffi` | |---|---|---| | Android, iOS, macOS app | yes | possible, rarely needed | | Windows, Linux app | no | yes | | `flutter test` / `dart test` | no | yes | | Web | no | the separate, experimental `sqflite_common_ffi_web` | ## Wiring it up 1. Add `sqflite_common_ffi` — as a `dev_dependency` if it is only for tests, a normal dependency for desktop. 2. Call `sqfliteFfiInit()` to load the SQLite library. 3. Either use `databaseFactoryFfi.openDatabase(path, options: OpenDatabaseOptions(...))` directly, or set the global `databaseFactory = databaseFactoryFfi` once so existing `openDatabase(...)` calls use it unchanged. The global setter affects every library that uses sqflite and prints a warning if changed twice, so set it once, before any other sqflite call — in `setUpAll` for tests, early in `main` for desktop. ## Testing patterns - **Scratch database:** open `inMemoryDatabasePath` (`':memory:'`); sqflite forces `singleInstance: false` for it, so each open is a fresh database. - **Migration test:** create a temp file, open it at version 1 with the old schema, insert a row, close; reopen through the app's real open function at version 2 and assert the new column exists and the row survived. - **Repository tests:** run the real SQL of your repository against the FFI database instead of mocking the `Database`, which catches typos and constraint errors mocks never would. ## Caveats - The SQLite version on the host is likely different — often newer — than the one on a user's device, so features and edge cases can differ. Keep an integration test on a real device for anything version-sensitive. - Android's plugin binds arguments as strings and handles corruption differently from iOS; FFI tests will not reproduce such platform quirks. - On Linux the system needs `libsqlite3`; the package documents the setup. ## In the expense ledger The ledger's migration from version 1 to version 2 — adding `category` — is exactly the code most worth testing and hardest to test by hand. With `sqflite_common_ffi`, a unit test builds a real version-1 file, runs the production `openDatabase` call against it, and checks that old rows now have a `NULL` category, in milliseconds on a laptop. ## Desktop apps For a Flutter app that also targets Windows or Linux, the same two lines go at the start of `main`, guarded by a platform check so mobile builds keep the plugin: - `if (Platform.isWindows || Platform.isLinux) { sqfliteFfiInit(); databaseFactory = databaseFactoryFfi; }` Everything else — `openDatabase`, migrations, transactions, batches — stays unchanged, because both implementations share the `sqflite_common` API. On Windows, recent versions of the underlying `sqlite3` package build and bundle the SQLite library for you; on Linux the system library must be installed. On desktop, pass an absolute database path built from an app directory, as the package's own `path_provider` example does, rather than relying on the working directory.

  • Why does a plain sqflite call fail inside flutter test?
    `flutter test` runs on the host VM without the platform side of plugins, so the sqflite plugin's platform-channel calls have nothing to answer them. Switching the factory to `databaseFactoryFfi` routes the same API to a native SQLite library through FFI instead.
  • Why test migrations with a temp file rather than inMemoryDatabasePath?
    A migration test must close a version-1 database and reopen it at version 2. An in-memory database disappears when closed, and sqflite forces `singleInstance: false` for `:memory:`, so each open is a fresh empty database. A temp file persists between the two opens.

saying these in an interview costs you the question

  • The sqflite plugin already works on Windows and Linux.
  • Unit tests should mock Database instead of running real SQL.
  • An in-memory database is ideal for testing close-and-reopen migrations.
  • FFI tests prove behaviour on the device's SQLite version.
  • Setting databaseFactory in every test is harmless.