Building an Offline-First App with Flutter & SQLite — Data Persistence Best Practices

This article was translated specially for the channel FlutterPulseYou'll find lots of interesting things related to Flutter on this channel. Don't hesitate to subscribe!🚀

In today's mobile world, offline functionality is crucial for a seamless user experience. An offline-first app ensures users can continue…

In today's mobile world, offline functionality is crucial for a seamless user experience. An offline-first app ensures users can continue using the app even without an internet connection.

Flutter provides multiple solutions for data persistence, but SQLite remains the most powerful option for storing structured data locally. In this guide, we'll build an offline-first Flutter app with SQLite, covering:

✅ Why SQLite?
✅ Setting up SQLite in Flutter
✅ Creating a database & tables
✅ Performing CRUD operations
✅ Syncing offline data with an API

🔹 Why Choose SQLite for Offline Storage?

✔ Persistent Storage — Data is retained even after the app is closed.
✔ Structured Data — Uses tables and relationships like traditional databases.
✔ Fast & Lightweight — Perfect for mobile apps.
✔ Works Offline — No internet required to access data.
✔ Better Performance — Faster than shared preferences or local JSON files.

📌 Step 1: Adding Dependencies

To use SQLite in Flutter, install the sqflite package:

dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.3.0
  path_provider: ^2.1.2

Run:

flutter pub get

📌 Step 2: Setting Up SQLite Database

🛠 Create a Database Helper Class

We'll create a database_helper.dart file to initialize the database:

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

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  factory DatabaseHelper() => _instance;

  Database? _database;
  DatabaseHelper._internal();
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }
  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'app_database.db');
    return await openDatabase(
      path,
      version: 1,
      onCreate: _onCreate,
    );
  }
  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT,
        content TEXT,
        createdAt TEXT
      )
    ''');
  }
}

✔ Uses path_provider to store the database in the app's local directory.
✔ Creates a notes table with id, title, content, and createdAt.
✔ Handles database initialization only once (singleton pattern).

📌 Step 3: Performing CRUD Operations

🔹 Insert Data

Add a new note to the database:

Future<int> addNote(String title, String content) async {
  final db = await DatabaseHelper().database;
  return await db.insert('notes', {
    'title': title,
    'content': content,
    'createdAt': DateTime.now().toIso8601String(),
  });
}

🔹 Retrieve Data

Fetch all notes from the database:

Future<List<Map<String, dynamic>>> getNotes() async {
  final db = await DatabaseHelper().database;
  return await db.query('notes', orderBy: 'createdAt DESC');
}

🔹 Update Data

Edit an existing note:

Future<int> updateNote(int id, String title, String content) async {
  final db = await DatabaseHelper().database;
  return await db.update(
    'notes',
    {'title': title, 'content': content},
    where: 'id = ?',
    whereArgs: [id],
  );
}

🔹 Delete Data

Remove a note from the database:

Future<int> deleteNote(int id) async {
  final db = await DatabaseHelper().database;
  return await db.delete('notes', where: 'id = ?', whereArgs: [id]);
}

📌 Step 4: Displaying Data in UI

Create a Note Model

class Note {
  final int id;
  final String title;
  final String content;
  final String createdAt;
  Note({required this.id, required this.title, required this.content, required this.createdAt});
  factory Note.fromMap(Map<String, dynamic> map) {
    return Note(
      id: map['id'],
      title: map['title'],
      content: map['content'],
      createdAt: map['createdAt'],
    );
  }
}

Fetch & Display Notes in a ListView

import 'package:flutter/material.dart';

class NotesScreen extends StatefulWidget {
  @override
  _NotesScreenState createState() => _NotesScreenState();
}
class _NotesScreenState extends State<NotesScreen> {
  List<Note> _notes = [];
  @override
  void initState() {
    super.initState();
    _loadNotes();
  }
  Future<void> _loadNotes() async {
    final notesList = await DatabaseHelper().getNotes();
    setState(() {
      _notes = notesList.map((note) => Note.fromMap(note)).toList();
    });
  }
  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Offline Notes')),
      body: ListView.builder(
        itemCount: _notes.length,
        itemBuilder: (context, index) {
          final note = _notes[index];
          return ListTile(
            title: Text(note.title),
            subtitle: Text(note.content),
            trailing: Text(note.createdAt),
          );
        },
      ),
    );
  }
}

📌 Step 5: Syncing Offline Data with an API

To keep local data in sync with a remote server, implement:

✅ Background Syncing — Periodically sync offline data with an API.
✅ Conflict Resolution — Handle changes when the app is online.
✅ Detect Network Status — Use connectivity_plus to check connectivity.

Detect Network Changes

Install:

dependencies:
  connectivity_plus: ^5.0.2
import 'package:connectivity_plus/connectivity_plus.dart';

Future<bool> isConnected() async {
  var result = await Connectivity().checkConnectivity();
  return result != ConnectivityResult.none;
}

Sync Data When Online

Future<void> syncData() async {
  if (await isConnected()) {
    final localNotes = await DatabaseHelper().getNotes();

    for (var note in localNotes) {
      await uploadToServer(note); // Assume API call
    }

    print('Synced with server!');
  }
}

📌 Step 6: Handling Database Upgrades (Versioning)

If you need to modify database structure, update the onUpgrade function:

Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
  if (oldVersion < 2) {
    await db.execute("ALTER TABLE notes ADD COLUMN isSynced INTEGER DEFAULT 0");
  }
}

✔ Keeps existing user data intact.
✔ Prevents data loss when updating the app.

🚀 Conclusion

SQLite is a powerful solution for building offline-first apps in Flutter.
✔ Local database storage for persistence.
✔ Efficient CRUD operations for managing data.
✔ Offline data syncing with APIs when online.
✔ Optimized for performance & versioning.

🎯 What's Next? Stay tuned for more Flutter best practices, performance optimizations, and advanced state management techniques! 🚀🔥

If you found this story helpful, you can support me at Buy Me a Coffee!