QLite in Flutter: When to Use It and How to Do It Right

Flutter

SQLite in Flutter is the go-to relational database for apps that need structured queries, constraints, and transactions. This guide explains when SQLite is a better fit than key–value stores, how to set it up with sqflite, and how to structure DAOs, migrations, and performance tuning so your database stays healthy in production.

Audience: IntermediateTested on: Flutter 3.x, Dart 3.x, Android 14 / iOS 17, macOS 14

When to choose SQLite over Hive or shared_preferences

  • You need joins or complex queries. Reports, search, filters, and cross-table relationships.
  • You want integrity constraints. Primary keys, unique constraints, foreign keys, cascading deletes.
  • You care about transactions. All-or-nothing multi-step writes.

For simple settings use shared_preferences; for document-like caches use Hive; for relational data use SQLite. (See your broader storage plan in Choosing Hive, shared_preferences, or flutter_secure_storage in Flutter.)

Two common choices: sqflite vs drift

  • sqflite: thin, imperative API. You write SQL strings, manage migrations yourself. Maximum control, minimal abstraction.
  • drift (formerly moor): type-safe SQL with code generation, DAOs, migrations helpers, reactive streams. Great developer ergonomics.

This article shows sqflite (lowest common denominator). If you want compile-time safety and streams, consider drift.

Install sqflite and path_provider

# pubspec.yaml
dependencies:
  sqflite: ^latest
  path_provider: ^latest

Open the database and create tables

// lib/data/app_database.dart
import 'dart:io';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

class AppDatabase {
  static const _dbName = 'app.db';
  static const _dbVersion = 1;

  Database? _db;

  Future<Database> get database async {
    if (_db != null) return _db!;
    _db = await _open();
    return _db!;
  }

  Future<Database> _open() async {
    final dir = await getApplicationDocumentsDirectory();
    final path = p.join(dir.path, _dbName);
    return openDatabase(
      path,
      version: _dbVersion,
      onConfigure: (db) async {
        // Enforce foreign keys
        await db.execute('PRAGMA foreign_keys = ON');
      },
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE users(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL UNIQUE,
            name TEXT NOT NULL
          );
        ''');
        await db.execute('''
          CREATE TABLE todos(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            done INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
          );
        ''');
        await db.execute('CREATE INDEX idx_todos_user ON todos(user_id);');
      },
    );
  }
}

Create a DAO layer (queries in one place)

// lib/data/todo_dao.dart
import 'package:sqflite/sqflite.dart';
import 'app_database.dart';

class TodoDao {
  TodoDao(this._db);
  final AppDatabase _db;

  Future<int> insertTodo({
    required int userId,
    required String title,
  }) async {
    final db = await _db.database;
    return db.insert('todos', {
      'user_id': userId,
      'title': title,
      'done': 0,
      'created_at': DateTime.now().millisecondsSinceEpoch,
    });
  }

  Future<List<Map<String, Object?>>> getOpenTodos(int userId) async {
    final db = await _db.database;
    return db.query(
      'todos',
      where: 'user_id = ? AND done = 0',
      whereArgs: [userId],
      orderBy: 'created_at DESC',
    );
  }

  Future<int> toggleDone(int id, {required bool done}) async {
    final db = await _db.database;
    return db.update('todos', {'done': done ? 1 : 0}, where: 'id = ?', whereArgs: [id]);
  }

  Future<void> deleteForUser(int userId) async {
    final db = await _db.database;
    await db.delete('todos', where: 'user_id = ?', whereArgs: [userId]);
  }
}

Transactions and batches

Group multi-step operations to keep data consistent and faster.

// Insert a user and initial todos atomically
Future<void> createUserWithSeedData(Database db, String email, String name) async {
  await db.transaction((txn) async {
    final userId = await txn.insert('users', {'email': email, 'name': name});
    final batch = txn.batch();
    for (final t in ['Welcome', 'Read docs', 'Build feature']) {
      batch.insert('todos', {
        'user_id': userId,
        'title': t,
        'done': 0,
        'created_at': DateTime.now().millisecondsSinceEpoch,
      });
    }
    await batch.commit(noResult: true);
  });
}

Schema migrations

When you change tables, bump the version and implement onUpgrade. Keep migrations idempotent and incremental.

// in openDatabase(...)
version: 3,
onUpgrade: (db, oldV, newV) async {
  if (oldV <= 1) {
    await db.execute('ALTER TABLE todos ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;');
    await db.execute('CREATE INDEX IF NOT EXISTS idx_todos_priority ON todos(priority);');
  }
  if (oldV <= 2) {
    await db.execute('CREATE TABLE tags(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE);');
    await db.execute('CREATE TABLE todo_tags(todo_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, PRIMARY KEY(todo_id, tag_id));');
  }
},

Performance tips

  • Indexes: Add indexes on foreign keys and frequently filtered columns.
  • SELECT columns explicitly: Avoid SELECT * in hot paths.
  • Use batches for many similar writes.
  • Move heavy parsing off the UI thread. For huge result sets, consider compute() or isolates.
  • Keep connections singleton-like. Open the DB once and reuse.

Testing with a temporary database

import 'dart:io';
import 'package:sqflite/sqflite.dart';
import 'package:test/test.dart';

void main() {
  test('insert & query todo', () async {
    final dir = await Directory.systemTemp.createTemp();
    final path = '${dir.path}/test.db';
    final db = await openDatabase(path, version: 1, onCreate: (db, _) async {
      await db.execute('CREATE TABLE todos(id INTEGER PRIMARY KEY, title TEXT);');
    });

    await db.insert('todos', {'title': 'Write tests'});
    final rows = await db.query('todos');
    expect(rows.single['title'], 'Write tests');

    await db.close();
  });
}

Security & pitfalls

  • Secrets don’t belong here. Do not store tokens or passwords in plaintext tables; use flutter_secure_storage and only keep non-sensitive relational data in SQLite.
  • Foreign keys off by default in SQLite. Always PRAGMA foreign_keys = ON in onConfigure.
  • Blocking the UI thread. Very large queries can jank; paginate and consider isolates if you parse heavy data.
  • Migrations are code. Review them like production code; add tests that open an old DB and upgrade to the new version.

FAQ

Q: Should I use an ORM? A: If you prefer type safety and reactive streams, try drift. If you’re comfortable with SQL and want minimal tooling, sqflite is fine.

Q: Can I sync SQLite with a server? A: Yes—treat SQLite as an offline cache or source of truth, and implement HTTP-based sync with conflict resolution (timestamps, versions, or server reconciliation).

Q: How big is too big? A: SQLite handles large datasets, but mobile UX suffers if you load everything at once. Use paging, indices, and background work.

Conclusion

SQLite in Flutter gives you relationships, constraints, and powerful queries. Use it when your data is naturally relational and you need transactions and joins. Pair it with Hive for document-like caches and shared_preferences for simple flags, and keep secrets out of SQLite by using secure storage.

Updated: 2025-11-12

Comment

Copied title and URL