Flutter sqflite Tutorial: Local SQLite Database Step by Step

Coding Liquids videos let you watch rows change through insert, update, transaction, and rollback.

Subscribe on YouTube@codingliquids

Pick SQLite when records need queries

sqflite is a strong fit for related rows, filters, sorting, indexes, and atomic multi-step changes. A database gives structured constraints that a pile of preference keys or JSON files cannot provide.

For one theme boolean, SQLite adds schema and lifecycle cost without a useful return.

Create a versioned database and table

Build the path with getDatabasesPath and path.join, then call openDatabase with version and onCreate. CREATE TABLE should declare primary keys, required columns, defaults, and useful indexes explicitly.

Changing the SQL string later does not update databases that were already created.

flutter pub add sqflite path

Small SQLite diagrams on Instagram connect table shape, indexes, transactions, and Dart mapping.

Follow me on Instagram@sagnikteaches

Insert rows and map query results

Database.insert accepts a Map and returns the generated integer identifier, while query returns List>. Convert every result through Todo.fromMap so parsing stays consistent and typed.

Passing database maps directly into widgets spreads nullable column knowledge throughout the UI.

These runtime dependencies provide SQLite access and platform-safe path joining for the database file.

dependencies:
  flutter:
    sdk: flutter
  sqflite: any
  path: any

Update and delete with whereArgs

Use an id placeholder with whereArgs containing the identifier for update and delete. Placeholders let SQLite bind values correctly and prevent user input from changing the SQL expression.

Interpolating an identifier or title into the where string invites quoting bugs and injection.

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

Keep toMap and fromMap symmetrical

Todo.toMap defines the stored column names, while Todo.fromMap handles integer booleans and nullable migration fields. Exclude an auto-generated id from inserts but include it when copying an existing row.

A model that writes done but reads is_done silently loses state.

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

Future<Database> openAppDatabase() async {
  final file = join(await getDatabasesPath(), 'notes.db');
  return openDatabase(
    file,
    version: 2,
    onCreate: (db, _) => db.execute(
      'CREATE TABLE notes(id INTEGER PRIMARY KEY, title TEXT NOT NULL)',
    ),
    onUpgrade: (db, oldVersion, newVersion) async {
      if (oldVersion < 2) {
        await db.execute('ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0');
      }
    },
  );
}

The database migration thread I posted on LinkedIn focuses on skipped releases and rollback-safe upgrades.

Connect on LinkedInSagnik Bhattacharya

Advance schema versions through onUpgrade

Increase version and apply each missing migration in order inside onUpgrade. An ALTER TABLE that adds a non-null column needs a default for rows already on disk.

Testing only a fresh install misses failures experienced by users upgrading from older schemas.

Future<int> updateTodo(Todo todo) => db.update(
  'todos',
  todo.toMap(),
  where: 'id = ?',
  whereArgs: [todo.id],
);

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

Future<void> completeAndLog(int id) => db.transaction((txn) async {
  await txn.update('todos', {'done': 1}, where: 'id = ?', whereArgs: [id]);
  await txn.insert('events', {'todo_id': id, 'type': 'completed'});
});

Group related writes in batches and transactions

Batch reduces platform round trips for independent statements, while transaction makes a sequence commit or roll back together. Use a transaction when moving a task between lists requires both an insert and delete to succeed.

Starting long network work inside a database transaction keeps locks open and blocks other operations.

class Todo {
  const Todo({this.id, required this.title, this.done = false});
  final int? id;
  final String title;
  final bool done;

  Map<String, Object?> toMap() => {
    if (id != null) 'id': id,
    'title': title,
    'done': done ? 1 : 0,
  };

  factory Todo.fromMap(Map<String, Object?> map) => Todo(
    id: map['id'] as int,
    title: map['title'] as String,
    done: (map['done'] as int) == 1,
  );
}

Future<int> insertTodo(Todo todo) => db.insert('todos', todo.toMap());
Future<List<Todo>> allTodos() async =>
    (await db.query('todos', orderBy: 'id DESC')).map(Todo.fromMap).toList();

Plan the database beyond version one

A useful schema starts with the questions the application must answer. Choose column types and indexes from real queries, then inspect those queries with realistic row counts rather than assuming a primary key is enough. An index on a filter or sort column can remove a full-table scan, but every additional index also slows writes and consumes space. Store timestamps in one documented representation, preferably UTC, and convert them for display at the edge.

Database upgrades need an ordered migration for every released version. A user may jump from version one straight to version four, so the upgrade callback must apply versions two, three, and four in sequence. Adding a nullable column is simple; changing constraints often requires a new table, copied rows, verified counts, and an atomic rename. Wrap multi-step migrations in a transaction and back up important data before attempting a destructive transformation.

Concurrency deserves deliberate ownership. Route database access through a repository rather than opening a connection inside each widget, and use transactions when several statements represent one action. A read followed by a write is not automatically atomic. Parameter placeholders protect values from SQL injection and quoting mistakes; table or column names cannot be bound the same way, so choose those identifiers from trusted constants instead of user input.

Migration tests can create an old schema fixture, insert representative records, open it with the current code, and assert both structure and content. Include nulls, Unicode, large collections, and a failed constraint. During development, log the database version and the name of each applied migration without printing private rows. Before release, verify export or deletion requirements, test low-storage behaviour, and confirm that a failed transaction leaves no half-written parent-child relationship. Those checks turn local persistence from a demo into data users can safely keep for years.

Foreign-key enforcement and cascading behaviour should be enabled and tested explicitly for the connection configuration in use. Insert a child with no parent, delete a parent that still has children, and verify the intended constraint or cascade rather than assuming SQLite chose it. Batch operations improve throughput, but a batch is not automatically the right transaction boundary for user-visible work. Measure bulk imports with realistic data, yield feedback outside the database lock, and surface the row that failed validation. A repair strategy should preserve the original database until the application has proved that its replacement opens and contains the expected record counts.

Close the database through the same owner that opened it during tests and application shutdown paths that truly need closure. Ordinary route disposal should not tear down a connection shared by other repositories.

Common mistakes

• Pick SQLite when records need queries: In sqflite persistence, for one theme boolean, SQLite adds schema and lifecycle cost without a useful return; inspect this sqflite persistence cause before changing another sqflite persistence widget.
• Create a versioned database and table: In sqflite persistence, changing the SQL string later does not update databases that were already created; inspect this sqflite persistence cause before changing another sqflite persistence widget.
• Insert rows and map query results: In sqflite persistence, passing database maps directly into widgets spreads nullable column knowledge throughout the UI; inspect this sqflite persistence cause before changing another sqflite persistence widget.
• Update and delete with whereArgs: In sqflite persistence, interpolating an identifier or title into the where string invites quoting bugs and injection; inspect this sqflite persistence cause before changing another sqflite persistence widget.
• Keep toMap and fromMap symmetrical: In sqflite persistence, a model that writes done but reads is_done silently loses state; inspect this sqflite persistence cause before changing another sqflite persistence widget.
• Advance schema versions through onUpgrade: In sqflite persistence, testing only a fresh install misses failures experienced by users upgrading from older schemas; inspect this sqflite persistence cause before changing another sqflite persistence widget.

Frequently asked questions

How does pick SQLite when records need queries work in sqflite persistence?

For sqflite persistence, the starting rule is that sqflite is a strong fit for related rows, filters, sorting, indexes, and atomic multi-step changes. Apply this sqflite persistence rule first because pick SQLite when records need queries determines whether the sqflite persistence pattern fits.

Why does insert rows and map query results matter for sqflite persistence?

In sqflite persistence, convert every result through Todo.fromMap so parsing stays consistent and typed. Keeping insert rows and map query results at the sqflite persistence call site exposes the sqflite persistence return value directly.

What failure should I test first in sqflite persistence?

First reproduce the sqflite persistence case where a model that writes done but reads is_done silently loses state. The repaired mapping must round-trip every field through toMap, insertion, selection, and fromMap without changing its meaning.

How can I verify sqflite persistence before release?

Exercise group related writes in batches and transactions with real sqflite persistence inputs on every shipped platform. Inspect the final sqflite persistence callback or output; a successful sqflite persistence button tap alone is not proof.

Further reads

Connect sqflite persistence to the surrounding Flutter stack through these published tutorials:

• Flutter Development Guide 2026
• Flutter Drift Tutorial: Type-Safe SQLite for Dart
• Flutter Hive Tutorial: Fast Local NoSQL Storage
• Flutter Read and Write Files With path_provider
• Flutter shared_preferences: Save Simple App Data

Sources: Flutter documentation and the package documentation on pub.dev; sqflite persistence examples verified against current stable Flutter.