Users expect apps to work in tunnels, on planes, and anywhere signal drops. Building offline-first means making local storage the source of truth — the network is just the mechanism that keeps that truth in sync across devices. Get this right and users never see a blank screen or an error banner just because they stepped into a basement.
Table of Contents
This guide covers every practical layer of a production-ready offline-first Flutter app: the right SQLite package, designing your data model with sync metadata baked in, safe CRUD patterns, write queuing, connectivity detection, syncing to your backend, and migrating your schema as the app evolves.
Quick Answer
Use sqflite (v2.4.3, a Flutter Favorite) alongside connectivity_plus to build offline-first Flutter apps. Write all data locally first with a synced = 0 flag, then flush those rows to your API whenever the connectivity_plus stream reports an active network interface — always confirming real internet access with a lightweight HEAD request to your API before sending.
Why Offline-First Changes Your Architecture
In an online-first app the network comes first: the UI blocks on a fetch, caches the result, and shows a cached fallback on failure. In an offline-first app the local database comes first: every read and write hits SQLite immediately and the network becomes a background concern. Users get instant responses regardless of signal strength.
The practical implication is that every table needing remote persistence must carry sync metadata — at minimum a synced flag, an updated_at timestamp stored in UTC milliseconds, and ideally a version counter. Deletes should be soft (setting is_deleted = 1) rather than hard, so the sync engine can propagate the removal to the server before the row disappears locally. These two design decisions — sync flags and soft deletes — are what separate a real offline-first app from one that just caches API responses.
Step 1 — Add the Right Packages
Flutter does not use expo-sqlite — that package belongs to the React Native and Expo ecosystem and does not exist on pub.dev. The correct package for Flutter is sqflite, a Flutter Favorite published by Tekartik at v2.4.3. It runs natively on Android, iOS, and macOS. For Linux or Windows desktop targets add sqflite_common_ffi and call sqfliteFfiInit() before opening any database. Experimental web support is available via sqflite_common_ffi_web, which stores data in browser IndexedDB. If you need fully typed, code-generated SQL with first-class web support from day one, drift (formerly moor) is the mature alternative to sqflite.
You also need the path package to resolve the database file location on disk, and connectivity_plus to listen for network state changes. Run: flutter pub add sqflite path connectivity_plus. Then import where needed: import ‘package:sqflite/sqflite.dart’; import ‘package:path/path.dart’; import ‘package:connectivity_plus/connectivity_plus.dart’. One notable advantage of sqflite: it automatically moves database I/O to a background thread on iOS and Android, so you do not need to push operations to a Dart isolate manually.
Step 2 — Design Your Data Model for Sync
Bake sync metadata into every table that needs remote persistence from the start. A minimal schema looks like this: CREATE TABLE tasks(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, updated_at INTEGER NOT NULL, synced INTEGER NOT NULL DEFAULT 0, is_deleted INTEGER NOT NULL DEFAULT 0). The updated_at column stores UTC milliseconds — never local device time — so timestamps from devices in different time zones compare correctly. The is_deleted column enables soft deletes: mark it 1 instead of running DELETE so the sync engine can propagate the removal before the row is purged.
Mirror the schema in a Dart model class with toMap() and fromMap() helpers. The toMap() method feeds directly into db.insert() and db.update(); fromMap() converts raw query results back to typed objects. Keep the model thin — just field mapping, no business logic. Add an index on the synced column so your sync query (WHERE synced = 0) scans only unsynced rows rather than the full table: CREATE INDEX idx_tasks_synced ON tasks(synced). For tables with large row counts this index is the difference between a sub-millisecond lookup and a full-table scan on every sync cycle.
Step 3 — Create the Database Helper
Wrap the database in a singleton so only one connection is open at a time. Use a static nullable _database field and a lazy async getter that calls openDatabase on first access, with the path resolved via join(await getDatabasesPath(), ‘myapp.db’). Always call WidgetsFlutterBinding.ensureInitialized() before opening the database in main() — without this the Flutter engine is not ready to resolve platform paths and the call will throw.
Supply both onCreate and onUpgrade callbacks. onCreate runs on fresh installs and creates all tables and indexes. onUpgrade runs whenever you increment the version integer — this is where schema migrations live. To add a column in a migration: await db.execute(‘ALTER TABLE tasks ADD COLUMN priority INTEGER DEFAULT 0’). Never drop or rename an existing column on a published app without first migrating any data that depends on it, or users will silently lose records on update. Test migrations by opening a v1 database in a test environment, then upgrading to v2 and asserting the schema.
Step 4 — CRUD With Offline Safety
Write to SQLite immediately and never block the UI on a network response. Use db.insert with ConflictAlgorithm.replace to safely upsert rows — this handles both inserts and updates without needing separate code paths. For reads, query the local table directly; results are fast even on low-end devices because SQLite queries hit memory-mapped disk rather than the network. Always use parameterized queries with whereArgs: [id] and never interpolate values into WHERE clause strings — sqflite does not escape interpolated input, making string interpolation a direct SQL injection path.
For ordered replay across operation types, maintain a pending_ops table alongside your data tables: CREATE TABLE pending_ops(id INTEGER PRIMARY KEY AUTOINCREMENT, operation TEXT NOT NULL, table_name TEXT NOT NULL, payload TEXT NOT NULL, created_at INTEGER NOT NULL). Log every insert, update, and soft-delete here so the sync engine replays them in creation order. This matters when the server requires a create before it will accept an update for the same resource ID — without ordered replay you will see 404s on update syncs that race ahead of their parent create.
Step 5 — Detect Connectivity and Trigger Sync
Subscribe to the connectivity_plus stream at app startup: Connectivity().onConnectivityChanged.listen((results) { if (results.any((r) => r != ConnectivityResult.none)) { syncPending(); } }). This fires on every network interface state change. However, the package documentation warns explicitly that a non-none result only means a network interface is active — the device could be connected to a router with no WAN access. For critical syncs, confirm reachability first with a lightweight HEAD request to your API root; only flush the queue on a 2xx response.
Your syncPending() function should query all rows where synced = 0, POST or PATCH each to the backend inside a try/catch, and only set synced = 1 after a confirmed 200 response. Wrap the entire batch in a sqflite transaction so a mid-batch crash does not leave some rows double-marked as synced. If your backend supports batch endpoints, group pending rows in chunks of 50 to 100 per request to cut round trips and avoid oversized payloads. On iOS, debounce the connectivity stream with a short cooldown flag if you do not want multiple concurrent sync runs triggered within seconds of a Wi-Fi-to-cellular handoff.
Conflict Resolution: Choosing a Strategy
Last-write-wins (LWW) using the updated_at timestamp is the simplest default: whichever version carries the larger UTC millisecond value wins. It works well for independent records such as user profile fields or single-user to-do items. LWW is safe as long as all timestamps are stored in UTC — local device time makes cross-timezone comparisons unreliable.
For collaborative data where LWW would silently discard valid edits, add a version counter: increment a version INTEGER on every local save. The server rejects an incoming update whose version is not exactly one more than its own stored version — that mismatch signals a genuine conflict rather than a delayed sync. Surface conflicts to the user and let them choose which version to keep, or implement a field-level merge if your data model supports it.
A third approach is keep-both: assign each conflicting version a new local ID and present both to the user as separate items. This suits note-taking or document apps where no data should ever be discarded silently. Whichever strategy you choose, the is_deleted flag and updated_at timestamp in your data model already give the server what it needs to resolve delete-versus-edit conflicts without extra round trips.
Tips and Common Mistakes
Keep one open database instance for the app lifetime via a static singleton or a dependency-injection container like get_it. Opening sqflite repeatedly is cheap in isolation but the async overhead compounds across thousands of calls in a busy app.
Do not store images or PDFs as SQLite BLOBs. Write binary files to the device documents directory via path_provider and store only the file path in SQLite. This keeps the database file compact and prevents query times from ballooning as attachment sizes grow.
Test offline logic by disabling the network at the OS level in an emulator, not by mocking connectivity in unit tests. Mocked tests frequently pass while real offline flows fail because OS-level network-state notification timing differs significantly from what mocks simulate.
Add a visible sync-status indicator to the UI — even a small icon showing the count of rows pending sync. Users who understand their data is queued locally are far less likely to tap Submit twice and generate duplicate records on the server.
offline-first-flutter-sqlite FAQs
Is expo_sqlite available for Flutter?
No. expo-sqlite is a React Native and Expo package and does not exist on pub.dev. The equivalent for Flutter is sqflite, a Flutter Favorite published by Tekartik.
Which platforms does sqflite support?
sqflite supports Android, iOS, and macOS natively. Linux and Windows desktop targets require the sqflite_common_ffi package. Experimental web support is available via sqflite_common_ffi_web, which uses browser IndexedDB as its storage layer.
How do I handle conflicts when two devices edit the same record offline?
The simplest approach is last-write-wins: store an updated_at timestamp in UTC milliseconds on every row and let the server keep whichever version has the higher value. For collaborative data, add a version counter column — the server rejects incoming updates whose version is not exactly one ahead of its stored version, signalling a true conflict that the user can resolve manually.
How do I migrate a sqflite database schema when I add a new column?
Increment the version integer in your openDatabase call and implement an onUpgrade callback. Inside onUpgrade, run ALTER TABLE your_table ADD COLUMN new_col TYPE DEFAULT value. Never drop or rename an existing column on a published app without first migrating any data that depends on it.
Does connectivity_plus guarantee that the internet is reachable?
No. The package documentation explicitly warns that a non-none ConnectivityResult only means a network interface is active — the device may be connected to a router with no internet. Always verify reachability with a lightweight HEAD request to your API before flushing the sync queue.
Should I use sqflite or drift for a new Flutter project?
sqflite is lower-level with fewer dependencies and is the Flutter Favorite choice for straightforward offline-first apps. drift (formerly moor) adds code generation, type-safe query classes, and full web support out of the box, making it better suited for complex relational schemas or teams that want compile-time query validation.
How do I safely delete records in an offline-first app?
Use soft deletes: add an is_deleted INTEGER DEFAULT 0 column and set it to 1 instead of running DELETE. This lets the sync engine propagate the deletion to the server before the row is purged locally. Hard-delete the row only after the server confirms the removal.
Why should I use a pending_ops table instead of just a synced column?
A synced flag alone cannot capture operation order or type. A pending_ops table records whether each change was an insert, update, or delete, and the order in which those changes happened. This matters when the server requires a create to exist before it will accept an update for the same resource ID — without ordered replay you get 404 errors on syncs that race ahead of their parent operations.
Get More from offline-first-flutter-sqlite
Log the coasters, stadiums, and venues you’ve experienced, rate offline-first-flutter-sqlite, and see what your friends thought. Get the ThrillZing app.