Working with SQLite Databases using sqflite in Flutter

In Flutter development, managing local data efficiently is a common requirement. SQLite is a popular choice for local data storage due to its lightweight nature and reliability. The sqflite plugin in Flutter allows you to easily interact with SQLite databases, enabling you to store, query, and manipulate data directly on the user’s device.

What is SQLite?

SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. It is the most used database engine in the world, found in smartphones, computers, and various other applications. SQLite doesn’t require a separate server process and reads/writes directly to ordinary disk files.

Why Use SQLite in Flutter?

• Lightweight: Minimal overhead compared to other database systems.
• Local Data Storage: Store data directly on the device, ensuring availability even offline.
• SQL Compatibility: Supports standard SQL queries for data manipulation.
• Easy Integration: sqflite plugin simplifies the integration with Flutter apps.

How to Implement SQLite using sqflite in Flutter

To work with SQLite databases in Flutter, you’ll need to follow these steps:

Step 1: Add the sqflite Plugin Dependency

Include the sqflite package in your pubspec.yaml file:

dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.3.0

Run flutter pub get to fetch the dependency.

Step 2: Initialize the Database

Create a method to open the database and create the necessary tables:

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

class DatabaseHelper {
  static Database? _database;

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

  Future _initDatabase() async {
    String path = join(await getDatabasesPath(), 'my_database.db');
    return await openDatabase(path, version: 1, onCreate: _onCreate);
  }

  Future _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT,
        content TEXT,
        createdAt TEXT
      )
    ''');
  }
}

Step 3: CRUD Operations

Implement CRUD (Create, Read, Update, Delete) operations using SQL queries:

Create (Insert)

Future insertNote(Note note) async {
  final db = await database;
  return await db.insert('notes', note.toMap());
}

Read (Query)

Future<List> getNotes() async {
  final db = await database;
  final List<Map> maps = await db.query('notes');
  return List.generate(maps.length, (i) {
    return Note(
      id: maps[i]['id'],
      title: maps[i]['title'],
      content: maps[i]['content'],
      createdAt: maps[i]['createdAt'],
    );
  });
}

Update

Future updateNote(Note note) async {
  final db = await database;
  return await db.update(
    'notes',
    note.toMap(),
    where: 'id = ?',
    whereArgs: [note.id],
  );
}

Delete

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

Step 4: Model Class

Create a Dart class to represent the data:

class Note {
  final int? id;
  final String title;
  final String content;
  final String createdAt;

  Note({
    this.id,
    required this.title,
    required this.content,
    required this.createdAt,
  });

  Map toMap() {
    return {
      'id': id,
      'title': title,
      'content': content,
      'createdAt': createdAt,
    };
  }
}

Step 5: Usage in Flutter UI

Use the database helper class to perform CRUD operations from your Flutter widgets:

import 'package:flutter/material.dart';

void main() {
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  final dbHelper = DatabaseHelper();

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      home: Scaffold(
        appBar: AppBar(
          title: Text('SQLite Example'),
        ),
        body: FutureBuilder<List>(
          future: dbHelper.getNotes(),
          builder: (context, snapshot) {
            if (snapshot.hasData) {
              return ListView.builder(
                itemCount: snapshot.data!.length,
                itemBuilder: (context, index) {
                  Note note = snapshot.data![index];
                  return ListTile(
                    title: Text(note.title),
                    subtitle: Text(note.content),
                  );
                },
              );
            } else if (snapshot.hasError) {
              return Center(child: Text('Error: ${snapshot.error}'));
            } else {
              return Center(child: CircularProgressIndicator());
            }
          },
        ),
        floatingActionButton: FloatingActionButton(
          onPressed: () async {
            final note = Note(
              title: 'New Note',
              content: 'This is a new note',
              createdAt: DateTime.now().toString(),
            );
            await dbHelper.insertNote(note);
            // Force refresh the UI
            (context as Element).markNeedsBuild();
          },
          child: Icon(Icons.add),
        ),
      ),
    );
  }
}

Conclusion

Integrating SQLite databases in Flutter apps using the sqflite plugin is straightforward and powerful. By following these steps, you can easily store and manage local data, making your applications more robust and capable of handling offline scenarios. From setting up the database to performing CRUD operations, Flutter and sqflite provide all the necessary tools to build efficient data management solutions.