Local Data Storage

SQLite with sqflite: Database Setup and Schema Design

16 min Lesson 6 of 12

SQLite with sqflite: Database Setup and Schema Design

When an app needs to persist structured, relational data locally — such as user-created notes, cached API responses, or offline-capable records — a full SQL database is the right tool. sqflite is the most widely used Flutter plugin for SQLite, the self-contained, serverless relational database engine that runs directly on the device. This lesson covers how to open a sqflite database, define a versioned schema with onCreate, write proper DDL statements, and wrap everything in a thread-safe singleton helper.

Adding sqflite to Your Project

Add the two required packages to pubspec.yaml:

dependencies:
  sqflite: ^2.3.3          # SQLite bindings for Flutter
  path: ^1.9.0             # Cross-platform path utilities

Run flutter pub get to install them. The path package is needed to build the absolute file path where SQLite stores the .db file on the device.

Opening a Database with openDatabase

The core API call is openDatabase(path, version: n, onCreate: callback). sqflite locates (or creates) the file, runs any pending migration callbacks, and returns a Database handle wrapped in a Future. The database file is stored in the app’s documents directory, which is private to the app and persists across app restarts.

Note: Always use getDatabasesPath() from sqflite combined with join() from the path package to build the file path. Hard-coding an absolute path or using a relative path will fail on different devices and operating systems.

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

Future<Database> openMyDatabase() async {
  // Resolve the platform-specific databases directory
  final dbPath = await getDatabasesPath();

  // Join produces e.g. /data/user/0/com.example.app/databases/app.db
  final path = join(dbPath, 'app.db');

  return openDatabase(
    path,
    version: 1,
    onCreate: (Database db, int version) async {
      // Called ONLY when the database file does not exist yet
      await db.execute('''
        CREATE TABLE notes (
          id      INTEGER PRIMARY KEY AUTOINCREMENT,
          title   TEXT    NOT NULL,
          body    TEXT    NOT NULL DEFAULT '',
          color   INTEGER NOT NULL DEFAULT 0,
          created_at INTEGER NOT NULL
        )
      ''');
    },
  );
}

Defining a Versioned Schema with onCreate

The onCreate callback receives the fresh Database and the target version integer. Inside it, you execute one or more CREATE TABLE statements using db.execute(). Key DDL considerations:

INTEGER PRIMARY KEY AUTOINCREMENT — SQLite auto-assigns a unique, ever-increasing rowid to each inserted row.
NOT NULL constraints — enforce data integrity at the database level, not just in Dart code.
DEFAULT values — guarantee a sensible fallback when optional columns are omitted during insertion.
INTEGER for booleans and timestamps — SQLite has no native bool or datetime type; store booleans as 0/1 and timestamps as Unix epoch milliseconds.
FOREIGN KEY constraints — link related tables; must be enabled per-connection with PRAGMA foreign_keys = ON.

Tip: Use multi-line Dart string literals ('''...''') for SQL statements. They are easier to read, format, and diff than concatenated single-line strings.

Building a Database Helper Singleton

Opening a database is an expensive async operation. The recommended pattern is a singleton: a single class instance holds the Database handle and exposes it through a lazy-initialised getter. All parts of the app share this one connection, avoiding duplicate file handles and race conditions during startup.

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

class DatabaseHelper {
  // Private constructor prevents external instantiation
  DatabaseHelper._internal();

  // The single instance, stored as a static field
  static final DatabaseHelper instance = DatabaseHelper._internal();

  // Nullable backing field; null means "not yet opened"
  Database? _database;

  // Public getter — opens the DB on first call, returns cached handle after
  Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, 'notes_app.db');

    return openDatabase(
      path,
      version: 2,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
      // Enable foreign-key enforcement for every connection
      onConfigure: (db) async => await db.execute('PRAGMA foreign_keys = ON'),
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    // Use a Batch for multiple DDL statements — runs them atomically
    final batch = db.batch();

    batch.execute('''
      CREATE TABLE categories (
        id    INTEGER PRIMARY KEY AUTOINCREMENT,
        name  TEXT NOT NULL UNIQUE
      )
    ''');

    batch.execute('''
      CREATE TABLE notes (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        title       TEXT    NOT NULL,
        body        TEXT    NOT NULL DEFAULT '',
        is_pinned   INTEGER NOT NULL DEFAULT 0,
        category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
        created_at  INTEGER NOT NULL,
        updated_at  INTEGER NOT NULL
      )
    ''');

    // Speeds up lookups that filter or sort by category
    batch.execute(
      'CREATE INDEX idx_notes_category ON notes(category_id)',
    );

    await batch.commit(noResult: true);
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      // Schema migration: add a column introduced in version 2
      await db.execute(
        'ALTER TABLE notes ADD COLUMN updated_at INTEGER NOT NULL DEFAULT 0',
      );
    }
  }
}

Using the Singleton in Practice

Any part of the app accesses the database through the shared instance:

// Inserting a row
Future<int> insertNote(Map<String, dynamic> note) async {
  final db = await DatabaseHelper.instance.database;
  return db.insert('notes', note, conflictAlgorithm: ConflictAlgorithm.replace);
}

// Querying all rows
Future<List<Map<String, dynamic>>> getAllNotes() async {
  final db = await DatabaseHelper.instance.database;
  return db.query('notes', orderBy: 'created_at DESC');
}

Schema Design Best Practices

Use plural snake_case table names (notes, categories) and singular snake_case column names.
Store all timestamps as Unix epoch milliseconds (DateTime.now().millisecondsSinceEpoch) for reliable sorting and cross-platform consistency.
Avoid storing JSON blobs in TEXT columns for data you need to filter or join — normalise into separate tables instead.
Always increment the version number and implement onUpgrade before shipping schema changes; never alter the schema inside onCreate directly for an existing version.

Warning: Never call openDatabase directly inside a widget’s build() method or inside a FutureBuilder without caching the result. Every call to build() would re-open the database, producing multiple file handles and eventually causing DatabaseException: database is locked errors.

Summary

The sqflite workflow for structured local data starts with openDatabase(path, version, onCreate). The onCreate callback runs DDL statements that define tables, constraints, and indexes. Wrapping this in a singleton DatabaseHelper with a lazy-initialised database getter ensures the expensive open operation happens only once and that all queries share a single connection. Version numbers and the onUpgrade callback give you a safe, incremental path for evolving the schema over time without losing user data.