How do you interact with SQLite in Dart, and what packages do you use?

When I work with local data storage in Dart or Flutter, especially for offline-first apps, I typically interact with SQLite using the sqflite package — it’s the most popular and reliable plugin for database management in Flutter. It gives direct access to SQLite operations, and I can perform CRUD (Create, Read, Update, Delete) operations efficiently within the app.

In one of my projects, I built an expense tracking app where all transactions had to be stored locally, and I used sqflite for managing the data. I usually start by defining a helper class to handle database initialization and queries. Here’s a simplified version:

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

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;
  DatabaseHelper._init();

  Future get database async {
    if (_database != null) return _database!;
    _database = await _initDB('expense.db');
    return _database!;
  }

  Future _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);
    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDB,
    );
  }

  Future _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE expenses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT,
        amount REAL,
        date TEXT
      )
    ''');
  }

  Future insertExpense(Map data) async {
    final db = await instance.database;
    return await db.insert('expenses', data);
  }

  Future<List<Map>> fetchExpenses() async {
    final db = await instance.database;
    return await db.query('expenses', orderBy: 'date DESC');
  }

  Future close() async {
    final db = await instance.database;
    db.close();
  }
}

Here, I manage the database as a singleton to ensure only one connection is maintained throughout the app lifecycle. When inserting or querying data, I can easily call DatabaseHelper.instance.insertExpense() or fetchExpenses() from anywhere in the app.

One of the challenges I faced while working with sqflite was handling migrations when updating the database schema. If I add new columns or tables in a later version, I need to define an onUpgrade callback to manage those changes without losing existing user data. Another challenge was thread management — since database operations can be expensive, I had to make sure all queries run asynchronously using await, so the main isolate (UI) doesn’t get blocked.

In Flutter, I once faced a scenario where I needed reactive data updates — whenever data changed, the UI should automatically refresh. Since sqflite doesn’t offer reactive streams, I combined it with the provider package for state management or used streamController manually to broadcast changes to the UI.

In terms of limitations, sqflite is great for structured data but not ideal for storing large blobs like images or files — those I prefer saving to local storage and only storing file paths in the database.