Mastering Local Storage in Flutter: A Practical Guide to SQFlite for Offline Data

Mastering Local Storage in Flutter: A Practical Guide to SQFlite for Offline Data

In today’s world of spotty connections and mobile-first experiences, your Flutter app needs to work reliably offline. Whether you’re building a note-taking app, a fitness tracker, or an inventory management system, local data persistence isn’t just a nice-to-have feature—it’s often the backbone of your application’s reliability. That’s where SQFlite comes in.

Why SQFlite?

SQFlite brings the power of SQLite—a lightweight, file-based database engine—to Flutter. It provides a structured way to store, query, and manage your app’s data locally. Unlike simple key-value stores, SQLite gives you full relational database capabilities: complex queries, joins, transactions, and data integrity. The best part? It’s entirely local to the device, so your app remains functional regardless of network availability.

Let’s build a practical task manager app to demonstrate SQFlite in action.

Setting Up Your Project

First, add the necessary dependencies to your pubspec.yaml:

dependencies:
  sqflite: ^2.3.0
  path: ^1.8.0

Run flutter pub get, and you’re ready to start.

Database Initialization and Schema Design

The foundation of any database system is proper schema design. Let’s create a database helper class to manage our database lifecycle:

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

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

  TaskDatabase._init();

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

  Future<Database> _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 tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        is_completed INTEGER NOT NULL DEFAULT 0,
        created_at TEXT NOT NULL
      )
    ''');
  }
}

Key Points:

• We use a singleton pattern to ensure only one database instance exists
• The onCreate callback only runs when the database is first created
• SQLite doesn’t have a boolean type—we use INTEGER (0 for false, 1 for true)
• Always include a primary key for efficient data management

CRUD Operations Made Simple

Now let’s implement the core operations: Create, Read, Update, and Delete.

1. Creating a Model Class

First, define a Dart class to represent your data:

class Task {
  final int? id;
  final String title;
  final String? description;
  final bool isCompleted;
  final DateTime createdAt;

  Task({
    this.id,
    required this.title,
    this.description,
    this.isCompleted = false,
    required this.createdAt,
  });

  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'title': title,
      'description': description,
      'is_completed': isCompleted ? 1 : 0,
      'created_at': createdAt.toIso8601String(),
    };
  }

  static Task fromMap(Map<String, dynamic> map) {
    return Task(
      id: map['id'],
      title: map['title'],
      description: map['description'],
      isCompleted: map['is_completed'] == 1,
      createdAt: DateTime.parse(map['created_at']),
    );
  }
}

2. Inserting Data

Future<int> insertTask(Task task) async {
  final db = await TaskDatabase.instance.database;
  
  return await db.insert(
    'tasks',
    task.toMap(),
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
}

// Usage:
final newTask = Task(
  title: 'Buy groceries',
  description: 'Milk, eggs, bread',
  createdAt: DateTime.now(),
);
final taskId = await insertTask(newTask);

3. Reading Data

Future<List<Task>> getAllTasks() async {
  final db = await TaskDatabase.instance.database;
  final maps = await db.query('tasks', orderBy: 'created_at DESC');
  
  return maps.map((map) => Task.fromMap(map)).toList();
}

Future<List<Task>> getPendingTasks() async {
  final db = await TaskDatabase.instance.database;
  final maps = await db.query(
    'tasks',
    where: 'is_completed = ?',
    whereArgs: [0],
    orderBy: 'created_at DESC',
  );
  
  return maps.map((map) => Task.fromMap(map)).toList();
}

4. Updating Data

Future<int> updateTask(Task task) async {
  final db = await TaskDatabase.instance.database;
  
  return await db.update(
    'tasks',
    task.toMap(),
    where: 'id = ?',
    whereArgs: [task.id],
  );
}

// Mark a task as completed
Future<void> completeTask(int taskId) async {
  final db = await TaskDatabase.instance.database;
  await db.rawUpdate(
    'UPDATE tasks SET is_completed = 1 WHERE id = ?',
    [taskId],
  );
}

5. Deleting Data

Future<int> deleteTask(int taskId) async {
  final db = await TaskDatabase.instance.database;
  
  return await db.delete(
    'tasks',
    where: 'id = ?',
    whereArgs: [taskId],
  );
}

Common Pitfalls and Best Practices

1. Don’t forget to close your database: While SQFlite handles this reasonably well, explicitly closing your database when your app is disposed is good practice:

Future<void> close() async {
  final instance = TaskDatabase.instance;
  final db = await instance.database;
  db.close();
}

2. Handle database migrations: When you need to change your schema, implement the onUpgrade callback in openDatabase:

return await openDatabase(
  path,
  version: 2,
  onCreate: _createDB,
  onUpgrade: (Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      await db.execute('ALTER TABLE tasks ADD COLUMN priority INTEGER DEFAULT 0');
    }
  },
);

3. Use transactions for multiple operations: This ensures data consistency:

Future<void> archiveCompletedTasks() async {
  final db = await TaskDatabase.instance.database;
  
  await db.transaction((txn) async {
    await txn.delete(
      'tasks',
      where: 'is_completed = ?',
      whereArgs: [1],
    );
    // Add to archive table in same transaction
  });
}

4. Avoid synchronous database calls: SQFlite operations are asynchronous—trying to make them synchronous will block your UI thread.

Wrapping Up

SQFlite provides a robust, reliable solution for local data storage in Flutter applications. By following the patterns outlined above, you can implement a complete offline data layer that supports complex queries while maintaining data integrity. Remember to start with a solid schema design, use proper model classes, and always handle errors gracefully.

The true power of offline storage emerges when you combine it with synchronization strategies for when connectivity returns, but that’s a topic for another post. For now, your app can confidently function anywhere, anytime.

Happy coding!