Working with SQLite Databases Using the sqflite Package in Flutter

When building mobile applications with Flutter, data persistence is a crucial aspect. SQLite is a popular choice for local data storage due to its lightweight nature, reliability, and ease of integration. In Flutter, the sqflite package provides a convenient way to interact with SQLite databases. This blog post will guide you through working with SQLite databases in Flutter using the sqflite package, complete with code samples and best practices.

Introduction to SQLite in Flutter

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It is widely used as an embedded database in mobile apps. The sqflite package in Flutter offers a straightforward way to perform database operations such as creating, reading, updating, and deleting data.

Why Use SQLite with Flutter?

• Local Data Storage: Ideal for storing structured data locally on the device.
• Offline Support: Allows the application to function without a network connection.
• Lightweight: SQLite databases have a small footprint, making them suitable for mobile devices.
• SQL Compatibility: Provides a familiar SQL syntax for managing data.

Setting Up the sqflite Package

Before you can start working with SQLite, you need to add the sqflite package to your Flutter project.

Step 1: Add Dependency

Open your pubspec.yaml file and add the sqflite dependency:

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

Make sure to run flutter pub get to install the package.

Step 2: Import the Package

Import the sqflite package in your Dart file:

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

Creating and Opening a Database

The first step is to create and open an SQLite database.

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

class DatabaseHelper {
  static const _databaseName = "MyDatabase.db";
  static const _databaseVersion = 1;

  // Make this a singleton class.
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  // Only have a single app-wide reference to the database.
  static Database? _database;
  Future<Database> get database async {
    if (_database != null) return _database!;
    // Lazily instantiate the db the first time it is accessed.
    _database = await _initDatabase();
    return _database!;
  }

  // This opens the database (and creates it if it doesn't exist).
  Future<Database> _initDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
    );
  }

  // SQL code to create the database table.
  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE my_table (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        value INTEGER
      )
      ''');
  }
}

Explanation:

• We define the database name and version.
• The _initDatabase() method opens the database, creating it if it doesn’t exist.
• The _onCreate() method is called when the database is created. It contains the SQL code to create the database table.

Performing CRUD Operations

Now, let’s implement the basic CRUD (Create, Read, Update, Delete) operations.

Create (Insert)

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

Usage:

  Map<String, dynamic> row = {
    'name': 'John Doe',
    'value': 32
  };
  final id = await dbHelper.insert(row);
  print('inserted row id: $id');

Read (Query)

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

Usage:

  final allRows = await dbHelper.queryAllRows();
  print('all rows:');
  allRows.forEach((row) => print(row));

Update

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

Usage:

  Map<String, dynamic> row = {
    'id': 1,
    'name': 'Jane Doe',
    'value': 33
  };
  final rowsAffected = await dbHelper.update(row);
  print('updated $rowsAffected row(s)');

Delete

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

Usage:

  final rowsDeleted = await dbHelper.delete(2);
  print('deleted $rowsDeleted row(s): row $rowsDeleted');

Complete Example

Here’s a complete example demonstrating how to use the DatabaseHelper class in a Flutter app:

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

class DatabaseHelper {
  static const _databaseName = "MyDatabase.db";
  static const _databaseVersion = 1;

  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database? _database;
  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, _databaseName);
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
    );
  }

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

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

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

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

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

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

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'SQLite Demo',
      home: MyHomePage(),
    );
  }
}

class MyHomePage extends StatefulWidget {
  @override
  _MyHomePageState createState() => _MyHomePageState();
}

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

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('SQLite Demo'),
      ),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            ElevatedButton(
              child: Text('Insert'),
              onPressed: () async {
                Map<String, dynamic> row = {
                  'name': 'John Doe',
                  'value': 32
                };
                final id = await dbHelper.insert(row);
                print('inserted row id: $id');
              },
            ),
            ElevatedButton(
              child: Text('Query'),
              onPressed: () async {
                final allRows = await dbHelper.queryAllRows();
                print('all rows:');
                allRows.forEach((row) => print(row));
              },
            ),
            ElevatedButton(
              child: Text('Update'),
              onPressed: () async {
                Map<String, dynamic> row = {
                  'id': 1,
                  'name': 'Jane Doe',
                  'value': 33
                };
                final rowsAffected = await dbHelper.update(row);
                print('updated $rowsAffected row(s)');
              },
            ),
            ElevatedButton(
              child: Text('Delete'),
              onPressed: () async {
                final rowsDeleted = await dbHelper.delete(1);
                print('deleted $rowsDeleted row(s): row $rowsDeleted');
              },
            ),
          ],
        ),
      ),
    );
  }
}

Best Practices

• Use a Singleton Pattern: Ensures that there is only one instance of the DatabaseHelper class.
• Asynchronous Operations: Use async and await to perform database operations asynchronously to avoid blocking the UI thread.
• Error Handling: Implement proper error handling to manage database exceptions.
• Data Validation: Validate data before inserting or updating to ensure data integrity.
• Use Transactions: Use transactions for complex operations to ensure atomicity.
• Database Versioning: Increment the database version and use the onUpgrade method to handle schema changes.

Conclusion

Working with SQLite databases in Flutter using the sqflite package is straightforward and efficient. This guide provides a comprehensive overview of setting up the package, creating and opening databases, performing CRUD operations, and following best practices. By leveraging SQLite, you can create robust Flutter applications with reliable local data storage capabilities. Understanding and implementing these techniques will empower you to build high-quality, data-driven mobile applications.