Shipping a database with your Flutter app: offline catalogs and instant search

Also in 中文. Flutter implementation notes for Trading app architecture; the principle is in Local-first data.

The design from the local-first article — ship a snapshot, sync deltas by version — has a specific and slightly fiddly Flutter implementation. Here it is concretely.

Choosing the store

sqflite gives you SQLite with raw SQL. drift sits on top of it (or on its own engine) and adds typed queries, migrations, and first-class isolate support. For a catalog with a schema you will evolve, drift’s migration story alone justifies it; for a handful of key-value settings, shared_preferences and nothing else is correct.

The mistake worth avoiding is reaching for a document store because it looks simpler, then discovering you need prefix search over several hundred thousand rows with ranking. That is a relational problem with a mature answer, and SQLite is on the device already.

Bundle the snapshot, copy once

Ship the prebuilt database as an asset and copy it into the app’s documents directory on first launch. The copy is necessary because assets are read-only and SQLite needs a writable file.

Future<File> ensureCatalog() async {
  final dir = await getApplicationDocumentsDirectory();
  final file = File(p.join(dir.path, 'catalog.db'));
  final shippedVersion = 812;                     // bumped when you re-cut the asset

  if (await file.exists() && await _dataVersion(file) >= shippedVersion) {
    return file;                                  // user's copy is newer — keep it
  }
  final bytes = await rootBundle.load('assets/db/catalog.db');
  await file.writeAsBytes(bytes.buffer.asUint8List(), flush: true);
  return file;
}

The comparison in the middle is the part people get wrong. After an app update you must not blindly overwrite: a user who has been syncing deltas for months may hold a newer catalog than the one you just shipped. Copy only when the bundled snapshot is actually ahead, and let delta sync handle the rest.

The other detail is that the copy happens on first launch, which is the moment you least want a long block. Copying tens of megabytes takes real time, so do it off the UI isolate and show a determinate first-run state rather than a frozen splash.

Two versions, never conflated

Keep these separate, as the principle article insists:

• Data version — how fresh the contents are. It moves when the server publishes changes, and drives delta sync. Store it in a metadata table you control.
• Schema version — the shape of the tables. It moves when your app changes, and drives migrations. SQLite’s user_version, or drift’s schemaVersion, is the natural home.

Conflating them produces one of two failures: an app update that discards a synced catalog to reinstall the snapshot, or a migration that never runs because the version counter was busy tracking data.

@override
MigrationStrategy get migration => MigrationStrategy(
      onUpgrade: (m, from, to) async {
        if (from < 3) await m.addColumn(instruments, instruments.board);
        // data version untouched — the catalog contents survive
      },
    );

Search: FTS, plus the columns you actually search

Instant search over a few hundred thousand rows is what SQLite’s full-text search is for. Create an FTS5 table alongside the main one and query it, rather than running LIKE '%...%', which cannot use an index and degrades linearly.

The interesting part is multilingual matching. Users search a Chinese instrument by its Chinese name, its English name, its ticker, its pinyin, or the initials of its pinyin — and they expect all five to work. That is not a tokenizer problem; it is a data problem. Generate the variants at build time and store them as searchable columns:

Then one FTS query with prefix matching covers every entry style, and ranking is a matter of ordering by which column matched and by a popularity score you ship with the catalog. Doing this at build time rather than on the device keeps startup free and makes the behavior identical across platforms.

Keep queries off the UI isolate

A query over a large table will block whatever isolate runs it, and on the UI isolate that means dropped frames during typing — the worst possible place, since search-as-you-type is exactly when responsiveness is judged.

Drift supports running the database in a background isolate, which is the clean answer (drift 2.x, NativeDatabase.createInBackground):

final db = AppDatabase(NativeDatabase.createInBackground(file));

Then debounce input (a query per keystroke is wasted work), cancel superseded queries, and keep result sets small with LIMIT — nobody scrolls past the first few dozen matches, and fetching a thousand rows to display twenty is pure cost.

Sync deltas, not the world

Once the snapshot is in place, the client only asks what changed:

final since = await meta.dataVersion();
final delta = await api.catalogChanges(since: since);
await db.transaction(() async {
  await db.upsertAll(delta.changed);
  await db.deleteAll(delta.removed);
  await meta.setDataVersion(delta.version);   // same transaction, always
});

The transaction boundary is the whole correctness argument: apply the rows and advance the marker atomically, or a crash between them leaves you permanently skipping a range. Express deltas as upserts and tombstones so re-applying an overlapping range is harmless.

What stays on the server

The catalog is safe to hold locally because it is public, slow-moving, and read-only. Balances, positions, and order status are none of those things. Cache them for display if you like, but they are a rendering of a remote fact — refresh on resume, and never let a locally cached number participate in a decision.

The user’s own data — a watchlist, notes — is a third category: small, user-owned, and written on the device. Give it its own table and its own two-way sync rather than mixing writes into the replicated catalog, which keeps the big replication one-way and conflict-free.

References

API and renderer claims checked against Flutter 3.47.0 / Dart 3.13 (August 2026).

• drift and its Isolates guide — NativeDatabase.createInBackground, DriftIsolate, computeWithDatabase; sqflite for the raw-SQL alternative.
• SQLite, FTS5 Extension; The LIKE optimization — why LIKE '%…%' cannot use an index; PRAGMA user_version — the natural home for the schema version.
• M. Kleppmann, A. Wiggins, P. van Hardenberg, M. McGranaghan, “Local-first software: You own your data, in spite of the cloud”, Ink & Switch / Onward! 2019 — the source of the term and the seven ideals behind it.

Previous: Charts in Flutter · Next: Project structure and add-to-app