Managing Offline Data with SQLite in Flutter

In modern mobile app development, providing a seamless user experience regardless of network connectivity is crucial. Managing offline data effectively is essential to achieving this. SQLite is a popular choice for local data storage in mobile applications, offering a lightweight, reliable, and efficient solution. This article will explore how to use SQLite in Flutter to manage offline data effectively.

What is SQLite?

SQLite is a self-contained, high-reliability, embedded, full-featured, SQL database engine. It is the most used database engine in the world. SQLite doesn’t require a separate server process to operate (serverless). SQLite reads and writes directly to ordinary disk files.

Why Use SQLite in Flutter?

• Lightweight: SQLite is small and has minimal overhead, making it perfect for mobile applications.
• Reliable: Proven and widely used in numerous applications.
• SQL Support: Supports standard SQL queries, making data management straightforward.
• Offline Access: Enables your Flutter app to function without a network connection by storing data locally.

Setting Up SQLite in a Flutter Project

To start using SQLite in a Flutter project, you need to add the sqflite package, along with a platform-specific implementation for database access.

Step 1: Add Dependencies

Add the sqflite and path_provider packages to your pubspec.yaml file:

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

Run flutter pub get to install these packages.

Step 2: Configure Platform-Specific Settings

No specific configuration is needed for Android as sqflite uses native Android SQLite libraries.

For iOS, add the following to your ios/Runner/Info.plist to avoid backup issues with the database:

<key>NSAppTransportSecurity</key>
<dict>
  <key>NSAllowsArbitraryLoads</key>
  <true/>
</dict>

Step 3: Implement Database Helper Class

Create a DatabaseHelper class to manage database operations. This class will handle creating, opening, and querying the database.

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

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

  DatabaseHelper._privateConstructor();

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

  Future<Database> _initDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, 'my_database.db');
    return await openDatabase(
      path,
      version: 1,
      onCreate: _onCreate,
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE my_table (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        value INTEGER
      )
    ''');
  }

  // Helper methods for database operations (insert, query, update, delete) will go here
}

Explanation:

• The DatabaseHelper class uses a singleton pattern to ensure only one instance exists.
• _initDatabase() initializes the database and sets the path using the path_provider package to get a suitable location for storing the database.
• _onCreate() creates the database table with specified columns.

Performing CRUD Operations

Implement methods within the DatabaseHelper class to perform CRUD (Create, Read, Update, Delete) operations.

Insert Data

  Future<int> insertData(Map<String, dynamic> row) async {
    final db = await instance.database;
    return await db.insert('my_table', row);
  }

Query Data

  Future<List<Map<String, dynamic>>> queryAll() async {
    final db = await instance.database;
    return await db.query('my_table');
  }

Update Data

  Future<int> updateData(Map<String, dynamic> row) async {
    final db = await instance.database;
    int id = row['id'];
    return await db.update(
      'my_table',
      row,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

Delete Data

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

Example Usage in Flutter UI

Here’s how to use the DatabaseHelper class in a Flutter UI:

import 'package:flutter/material.dart';
import 'database_helper.dart';

class MyHomePage extends StatefulWidget {
  const MyHomePage({Key? key}) : super(key: key);

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  final dbHelper = DatabaseHelper.instance;

  List<Map<String, dynamic>> _dataList = [];

  @override
  void initState() {
    super.initState();
    _queryAll();
  }

  void _insert() async {
    // row to insert
    Map<String, dynamic> row = {
      'name': 'Item Name',
      'value': 123
    };
    final id = await dbHelper.insertData(row);
    print('Inserted row id: \$id');
    _queryAll();
  }

  void _queryAll() async {
    final allRows = await dbHelper.queryAll();
    print('All rows:');
    allRows.forEach(print);
    setState(() {
      _dataList = allRows;
    });
  }

  void _delete(int id) async {
    final rowsDeleted = await dbHelper.deleteData(id);
    print('Deleted \$rowsDeleted row(s): row \$id');
    _queryAll();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('SQLite Demo'),
      ),
      body: ListView.builder(
        itemCount: _dataList.length,
        itemBuilder: (context, index) {
          return ListTile(
            title: Text('Name: \${_dataList[index]['name']}, Value: \${_dataList[index]['value']}'),
            trailing: IconButton(
              icon: const Icon(Icons.delete),
              onPressed: () => _delete(_dataList[index]['id']),
            ),
          );
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _insert,
        child: const Icon(Icons.add),
      ),
    );
  }
}

Advanced Techniques

For more complex scenarios, consider these techniques:

• Data Migration: Implement database migration strategies to handle schema changes between app versions.
• Asynchronous Operations: Ensure database operations are performed asynchronously to avoid blocking the UI thread.
• Data Encryption: If storing sensitive data, encrypt the SQLite database for enhanced security.

Conclusion

Managing offline data with SQLite in Flutter allows you to build robust, user-friendly applications that provide value even without an active network connection. By implementing the sqflite package and following best practices for database operations, you can ensure data persistence and a smooth user experience.