SQLite in Flutter with sqflite

Local persistence in a Flutter app is usually a file and a schema decision you make once and regret later if you get it wrong. sqflite is the plugin most Flutter projects start with: it gives you a real SQLite database on Android, iOS, and macOS, runs its work on a background thread on those platforms, and exposes both raw SQL and small query helpers. This post covers the parts that decide whether your database survives contact with version 2 of your app: setup and paths, versioned migrations, CRUD, transactions and batches, what happens under concurrency, where isolates fit, and when a different package is the better answer.

Setup

Add the package from your project root:

flutter pub add sqflite

That produces:

dependencies:
  sqflite: ^2.4.4+2

The current release is a federated plugin over sqflite_common, with sqflite_android and sqflite_darwin providing the native implementations. The README also names the rest of the matrix:

• Linux, Windows, and plain Dart VM via sqflite_common_ffi
• Experimental web support via sqflite_common_ffi_web

So “sqflite” is Android/iOS/macOS out of the box, and an FFI shim everywhere else.

Opening a database and choosing the path

A SQLite database is just a file. If you pass a relative path, it resolves against getDatabasesPath() - the default database directory on Android and the documents directory on iOS/macOS. Create the directory first, because a missing folder is a common open failure:

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

Future<Database> openNotesDb() async {
  final dir = await getDatabasesPath();
  try {
    await Directory(dir).create(recursive: true);
  } catch (_) {}
  final path = join(dir, 'notes.db');
  return openDatabase(path, version: 1, onCreate: _createSchema);
}

Two rules from the docs keep you out of trouble. First, open a database once: by default singleInstance: true means re-opening the same file hands you the same Database, while singleInstance: false is a reliable way to hit database is locked on Android. Second, guard against the async race where two callers both start opening:

class NotesRepo {
  Future<Database>? _db;
  Future<Database> get db => _db ??= _open();

  Future<Database> _open() async {
    final database = await openNotesDb();
    // any extra init here runs exactly once
    return database;
  }
}

Close with await db.close() when you genuinely need to release resources; most apps keep one database open until the process ends.

Versioned migrations with openDatabase

Pass a version and sqflite gives you Android-style migration callbacks: onConfigure, onCreate, onUpgrade, onDowngrade, and onOpen. Their contract, straight from the guide:

• onConfigure runs first - enable PRAGMA foreign_keys = ON here.
• onCreate runs when the file does not exist.
• onUpgrade runs when the stored version is lower than the one you passed (or when onCreate is undefined and the file is new, with oldVersion = 0).
• onDowngrade runs when you ship a lower version than the file’s - avoid it by always incrementing, or use the special onDatabaseDowngradeDelete callback to delete and recreate.
• All three of onCreate/onUpgrade/onDowngrade run inside a transaction, immediately before the version is stamped, so you never wrap them yourself.
• onOpen runs after the version is set, before openDatabase returns.

Future<void> _createSchema(Database db, int version) async {
  await db.execute('''
    CREATE TABLE notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      body TEXT NOT NULL DEFAULT '',
      pinned INTEGER NOT NULL DEFAULT 0,
      created_at INTEGER NOT NULL
    )
  ''');
  await db.execute('CREATE INDEX idx_notes_created ON notes(created_at)');
}

Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
  if (oldVersion < 2) {
    await db.execute('ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0');
  }
  if (oldVersion < 3) {
    await db.execute('CREATE INDEX idx_notes_created ON notes(created_at)');
  }
}

final db = await openDatabase(
  path,
  version: 3,
  onCreate: _createSchema,
  onUpgrade: _onUpgrade,
  onDowngrade: onDatabaseDowngradeDelete,
  onConfigure: (db) => db.execute('PRAGMA foreign_keys = ON'),
);

Write onUpgrade as an ordered ladder of if (oldVersion < n) steps rather than a single rewrite, and every released schema stays reproducible from any old install.

CRUD: raw SQL and query helpers

sqflite offers both raw statements and helpers, and the helpers are the ergonomic default. Everything user-supplied goes through ? placeholders:

Future<int> insertNote(Database db, Map<String, Object?> row) =>
    db.insert('notes', row);

Future<List<Note>> recentNotes(Database db, {int limit = 50}) async {
  final rows = await db.query(
    'notes',
    columns: ['id', 'title', 'pinned', 'created_at'],
    orderBy: 'created_at DESC',
    limit: limit,
  );
  return rows.map(Note.fromMap).toList();
}

Future<int> renameNote(Database db, int id, String title) => db.update(
      'notes',
      {'title': title},
      where: 'id = ?',
      whereArgs: [id],
    );

Future<int> deleteNote(Database db, int id) =>
    db.delete('notes', where: 'id = ?', whereArgs: [id]);

Raw SQL is there for anything the helpers do not express:

final count = Sqflite.firstIntValue(
  await db.rawQuery('SELECT COUNT(*) FROM notes WHERE pinned = ?', [1]),
);

Watch two sharp edges. Query results are read-only maps; copy before mutating (Map<String, Object?>.from(row)). And SQLite has no native bool or DateTime: store booleans as INTEGER 0/1 and timestamps as millis-since-epoch or ISO-8601 strings, which is exactly what the README recommends. If you name a table or column something that is a SQL keyword (like table or group), escape it with double quotes in raw statements - the helpers escape the table name, but not every raw fragment.

Transactions and batches

Inside a transaction you use the Transaction object exclusively. Touching the Database object from within the callback deadlocks - the README is explicit about this, so build every statement in the callback on txn:

await db.transaction((txn) async {
  await txn.insert('notes', a);
  await txn.update('notes', b, where: 'id = ?', whereArgs: [b['id']]);
});

Commit and rollback semantics are simple: a transaction commits if the callback does not throw, and rolls back if it does. Rollback by throwing, or swallow an expected error inside the callback if only that statement should be skipped:

try {
  await db.transaction((txn) async {
    await txn.insert('notes', note);
    await txn.rawInsert('INSERT INTO audit(msg) VALUES(?)', ['created']);
  });
} catch (e) {
  // rolled back: neither statement is visible
}

For bulk work, batches cut the Dart-to-native round trips:

final batch = db.batch();
for (final row in rows) {
  batch.insert('notes', row);
}
batch.rawUpdate('UPDATE notes SET pinned = 1 WHERE created_at < ?', [cutoff]);
final results = await batch.commit(noResult: true);

noResult: true skips collecting per-operation results (ids, change counts), which matters on Android where each result costs an extra SQL request. continueOnError: true runs every operation even if one fails. Inside a transaction, build the batch on txn.batch() and note that batch.commit() only stages the work - the real commit happens when the transaction commits.

Concurrency: plan for a single writer

sqflite’s README documents the core limitation under “current issues”: because of how SQLite transactions work across threads, concurrent read and write transactions are not supported. All calls are serialized and transaction blocks are exclusive. Opening the same file twice does not buy parallelism (it works on iOS but Android reuses the same database object), and the plugin’s own advice is to open a database once.

In practice: keep one Database, put multi-statement writes in a transaction, keep transaction bodies short and synchronous-heavy work outside them, and expect reads to queue behind a long write. If you see database is locked, look for a second open (singleInstance: false) or a transaction left open by an early return before you blame SQLite.

Heavy work: where isolates fit

The plugin already moves database execution off the UI thread on iOS and Android, so you do not spawn isolates to make queries non-blocking. What isolates are for is the Dart-side cost around a query: mapping tens of thousands of rows, decoding JSON blobs, running search or aggregation over a big result. Flutter’s concurrency docs list “reading data from a local database” as a common isolate use case, with the rule that you use an isolate when a computation outlasts the frame gap.

The simple form is Isolate.run:

Future<List<Note>> loadAndShape(Database db) async {
  final rows = await db.query('notes');
  return Isolate.run(() {
    return rows.map((r) => Note.fromMap(Map<String, Object?>.from(r)))
        .where((n) => n.body.length > 200)
        .toList();
  });
}

Isolate.run spawns, computes, returns, and shuts down; both it and compute use Isolate.exit under the hood, so the result is passed by reference rather than copied. Prefer compute if you also ship to web, where isolates do not exist and it runs on the main thread. Two limits to remember: messages between isolates are copied (except immutable values), and spawned isolates cannot touch rootBundle or dart:ui, so load assets before you spawn. For repeated work, a long-lived worker with ReceivePort/SendPort avoids the spawn overhead.

Error patterns worth encoding

Common failures map straight to checks:

• Open fails: the directory does not exist, or the path points at a file that is not a SQLite database.
• database is locked (code 5): multiple opens of the same file, or an open transaction.
• Silent wrong data: storing true/false or DateTime where SQLite expects INTEGER/TEXT, or mutating a query result map instead of copying it first.
• Corrupt file behavior differs by platform - iOS fails on first access, Android removes the existing file - so treat “database is unusable” as a recoverable state and be ready to re-run onCreate.

When to choose something else

sqflite is thin and predictable, which is also its ceiling. Grounded alternatives:

• Desktop and tests: sqflite_common_ffi is the documented path for Linux, Windows, and the Dart VM, so repository code stays unchanged while unit tests run against a real file database.
• Reactive, type-safe relational layer: Drift sits on top of SQLite, generates type-safe code that catches query mistakes at compile time, turns any SQL query into an auto-updating stream, and ships built-in threading across isolates. If your UI is a live view of queries, sqflite means re-implementing that by hand.
• Document/object storage: Isar is a fully async NoSQL store with indexes, compiled queries, and watchers that fire after a commit; the stable line is 3.1.0 with a 4.0.0 prerelease. Choose it when your data is object-shaped and you want queries without SQL.
• Web: sqflite_common_ffi_web is explicitly experimental, so prototype early if the browser is a target.

Key Takeaways

• Add sqflite with flutter pub add sqflite; Android/iOS/macOS are native, desktop and VM go through sqflite_common_ffi, web through the experimental sqflite_common_ffi_web.- Open once with singleInstance semantics, create the directory before opening, and memoize the Future<Database> to avoid a double-open race.
• Drive schema changes with version plus an if (oldVersion < n) ladder in onUpgrade; those callbacks already run in a transaction, so never wrap them again.
• Use helpers with ? placeholders for CRUD, store bool as 0/1 and DateTime as epoch millis or ISO-8601, and copy query result maps before mutating.
• In transactions, use only the Transaction object (the Database object deadlocks), roll back by throwing, and batch bulk writes with commit(noResult: true).
• Expect a single writer; use Isolate.run/compute for the Dart-side shaping of large result sets, and reach for Drift, Isar, or the FFI shim when the requirement changes shape.

References:

• https://pub.dev/packages/sqflite
• https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_db.md
• https://github.com/tekartik/sqflite/blob/master/sqflite/doc/how_to.md
• https://docs.flutter.dev/perf/isolates
• https://pub.dev/packages/drift
• https://pub.dev/packages/isar