Storing Data in Flutter with SQLite

Most Flutter apps eventually need to store real, structured data on the device — a list of expenses, saved records, offline content, anything the user would be upset to lose when the network drops. For anything beyond a few simple values, SQLite is the standard answer: a fast, reliable, embedded database that ships inside the app with no server required. It's the storage engine behind an enormous share of the apps already on your phone, including the platform frameworks themselves. My budget-tracking app runs on exactly this stack, and years of production use have left me with strong opinions about what matters — most of which is discipline, not code. This post covers when to reach for SQLite and how to use it well.

First: do you even need SQLite?

Not all local storage needs a database. Match the tool to the shape of the data:

A few simple values — a theme setting, an onboarding flag, the last-selected tab? Use lightweight key-value storage (shared_preferences). A database here is ceremony with no payoff.
A security-sensitive value — a session token? Secure storage, not either of the above.
Structured, queryable, related data — many records you need to filter, sort, sum, or relate to each other? That's SQLite's job, and doing it with anything less hurts.

The failure modes run both directions. Reaching for SQLite to store one boolean is over-engineering; but the reverse — serializing a growing list of records to a JSON blob in preferences — is the one that actually bites people. It works at 50 records, gets slow at 500, and at 5,000 every tiny write rewrites the whole blob and every query is a full deserialize-and-loop. If you're filtering or sorting data in Dart that a database could filter for you, you've outgrown the blob.

Why SQLite fits mobile so well

Embedded and offline. The database is a single file on the device. No network, no server, no latency, works on a plane. Offline isn't a feature you add — it's the default state.
Structured queries. You get real SQL — filtering, sorting, aggregating, joining — instead of hand-rolling logic over deserialized JSON. "Total spending per category this month" is one query, not a loop with a map of running sums.
Fast and battle-tested. SQLite is among the most widely deployed software on earth — it's in every phone, browser, and operating system you've touched today. Its reliability engineering is legendarily thorough. Your edge case is not going to be the one that breaks it.
Scales to real data. Tens of thousands of rows are routine, and queries stay fast with proper indexing. Whatever your app accumulates in a user's lifetime, SQLite has seen worse.

The shape of the code

With sqflite, the standard Flutter plugin, you define tables, then insert and query rows:

// create a table (runs once, on database creation)
await db.execute('''
  CREATE TABLE expenses(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    amount REAL NOT NULL,
    created_at TEXT NOT NULL
  )
''');

// insert
await db.insert('expenses', expense.toMap());

// query
final rows = await db.query('expenses', orderBy: 'created_at DESC');
final expenses = rows.map(Expense.fromMap).toList();

The important habit is the toMap/fromMap pair: convert between your Dart model objects and database rows in exactly one place, so the rest of the app works with clean typed objects and the string-keyed maps never escape the data layer. A typo in a column name should be a bug in one file, not a scavenger hunt.

Two conventions save later pain: store dates as ISO 8601 strings (they sort correctly as text) or epoch milliseconds, and store money as integer cents rather than floating-point currency — REAL arithmetic on money eventually produces a total that's off by a cent, and users notice cents.

Plan for schema changes

The mistake that bites everyone eventually: you ship version 1, then need a new column in version 2. Existing users already have the version-1 file sitting on their devices. If you don't handle the upgrade, the app crashes on launch for exactly your most loyal users — the ones who installed early.

sqflite provides a database version number and migration hooks (onCreate, onUpgrade) for precisely this. Every schema change bumps the version and adds a migration that upgrades old databases to the new shape:

onUpgrade: (db, oldV, newV) async {
  if (oldV < 2) await db.execute('ALTER TABLE expenses ADD COLUMN note TEXT');
  if (oldV < 3) await db.execute('CREATE INDEX idx_exp_date ON expenses(created_at)');
},

Treat migrations as append-only history — never edit a shipped migration, always add a new one, because a user might jump from any old version to the newest and every path has to work. And test the upgrade path before each release: install the store version, add data, then install the new build over it. It's ten minutes, and it's the difference between a smooth update and a one-star review titled "update deleted everything."

Keep the database layer separate

Don't scatter SQL across your widgets. Put all database code behind a small repository — a class whose methods speak your domain: getExpenses(), addExpense(), deleteExpense(), totalByCategory(month). The benefits compound:

Your UI works with meaningful methods and typed objects, not SQL strings and maps.
Your state management layer has one clean seam to call into.
The data logic becomes testable in isolation — sqflite even ships an FFI variant that runs on your desktop for fast tests, no emulator required.
If you ever change storage engines, you rewrite one layer instead of performing archaeology on every screen.

Practical tips

Index columns you filter or sort by. An index turns a full-table scan into a lookup as data grows. Don't index everything — each one taxes writes — just what your queries actually use.
Keep database work async and off the UI thread. sqflite's API is async already; just never await a big query inside a build-critical path without a loading state.
Use transactions for bulk work. Wrapping a hundred inserts in one transaction is dramatically faster than a hundred individual commits — often ten times or more — and it's atomic: all or nothing, no half-imported states.
Consider a typed wrapper as complexity grows. drift builds on SQLite with compile-time-checked queries, generated Dart types, and reactive streams that update your UI when a table changes. For an app with many tables and relations, the boilerplate it removes is worth the code-generation setup.
Decide what happens at uninstall and reinstall. SQLite lives and dies with the app. If users would expect their data to survive a new phone, you need backup or sync on top — that's a local-first architecture question worth answering deliberately rather than by accident.

Summary

SQLite is the right tool the moment a Flutter app needs structured, queryable, offline data — and the wrong tool for a handful of settings, where key-value storage is simpler. Map between models and rows in one place, hide the SQL behind a repository, index what you query, batch writes in transactions, and — above all — treat schema migrations as a first-class discipline from version 1. Do that and you get a local store that's fast, dependable, and boring in the best possible way, quietly doing its job for years while you build features on top of it.