SQFlite to manage a database with SQLite in Flutter

SQLite is an embedded, self-contained relational database management system. Its main feature lies in its portability, efficiency, and lightweight nature, making it the standard choice for local storage in mobile applications.

Databases are one of the core elements shared by applications today, and we can use them jointly with SharedPreferences in Flutter to save simple data; in practically all applications nowadays, there is always data that is persistently recorded; when you want to store structured data persistently in an application, the first thing that comes to mind is using a database.

In Android, the database used by default is SQLite; which is nothing more than a file where all our structured records are saved.

SQLite is a small, fast, self-contained, high-reliability, full-featured SQL database engine. SQLite is the most widely used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

Persistent data is very important for users, as it would be inconvenient for them to type their information every time or wait for the network to load the same data again. In situations like this, it would be better to save their data locally. In this article, I will demonstrate this using SQLite in Flutter.

Unlike traditional server environments, where independent engines like MySQL or PostgreSQL are used, on mobile devices it is not feasible to connect the application directly to these external services due to network and security reasons. Therefore, mobile platforms integrate local solutions or require disk persistence through structured files. In Flutter, the sqflite package provides the necessary abstractions to interact with this engine natively and asynchronously, since any read or write operation on physical storage requires delegating execution threads so as not to block the user interface.

Why SQLite?

SQLite is one of the most popular ways to store data locally. For this article, we will use the sqflite package to connect with SQLite. Sqflite is one of the most widely used and updated packages for connecting to SQLite databases in Flutter.

In Flutter, we have several options for storing data persistently, and we can even use SQLite for this purpose; although, unlike native Android, Flutter does not natively support SQLite, so we have to use a plugin with which we can take advantage of this feature; it is known as SQFlite, which enables the use of an SQLite database in Flutter:

https://pub.dev/packages/sqflite

1. Add dependencies to your project

To install it, we do it via pub:

pubspec.yaml

dependencies:
 flutter:
   sdk: flutter
 sqflite:

Basic operation

SQFlite is very simple to use. As you can imagine, when using an SQLite database, which is nothing more than a file—a file stored in a folder within our application—all the operations you want to perform on it, such as creating or reading a database, creating, modifying, retrieving, or deleting records, are asynchronous. Therefore, we will have to use Futures for each of these operations.

Creating and opening a database

To create or open a database, we use the following function:

openDatabase()

Which receives a path where we must specify the route to the database; therefore, we have two factors here:

1. The path where the database is saved.
2. The name of the database.

To get the database path, we also have a helper function that returns the default path used to register the database:

getDatabasesPath()

2. Create a database client

Finally, the code to create the database is:

lib\helpers\db_helper.dart

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

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

class DBHelper {
 static Future<Database> _openDB() async {
   return openDatabase(path.join(await getDatabasesPath(), 'sites.db'),
       onCreate: (db, version) {
     return db.execute(
         "CREATE TABLE places (id INTEGER PRIMARY KEY, name TEXT, image TEXT)");
   }, version: 1);
 }
}

The initialization and opening of a file-based database constitutes an expensive operation in terms of computation and input/output (I/O) performance. To optimize resource usage, the recommended architecture consists of centralizing access using the Singleton design pattern.

This pattern ensures that the database is opened a single time during the application's lifecycle—generally during startup within the main() function. From that point forward, a single active instance is maintained in RAM, allowing any module of the application to perform queries cleanly without the penalty of reopening the file on disk for every transaction.

Lifecycle and Database Initialization

The environment preparation process is managed through an initialization method (commonly named init()). The operational flow executes the following tasks:

1. Path Definition: The secure storage directory assigned by the operating system for the application is located and concatenated with the physical name of the database file (for example, app_database.db).
2. Atomic Opening or Creation: The open method provided by the package is invoked. If the file does not exist in the device's storage (such as in a clean installation from Google Play), the engine automatically creates it and triggers the onCreate lifecycle callback. If the file already exists, it simply returns the direct reference.
3. Execution of the Initial Schema (onCreate): Inside this callback, the initial tables are configured using raw SQL statements (DDL). It is a common preventive practice to structure statements under the CREATE TABLE IF NOT EXISTS clause to avoid name collisions or schema inconsistencies.

lib/services/database_helper.dart

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

  late WordDbService words;
  late SentenceDbService sentences;
  late TagDbService tags;
  late WordPackDbService wordPacks;
  late SentencePackDbService sentencePacks;
  late LatestPracticeDbService latestPractices;
  late StreakDbService streaks;
  late ApiKeyDbService apiKeys;
  late PromptHistoryDbService promptHistory;

  Future<void> init() async {
    if (_database != null) return;
    _database = await _initDB();
    _initServices(_database!);
  }

And in the main:

lib/main.dart

Future<void> main() async {
 ***
 await DatabaseHelper.instance.init();
}

Implementation of CRUD Operations and Service Layer

To maintain a clean and legible architecture, data access logic should be segmented into well-defined service classes and data models. Let's take a practical case focused on storing practiced words in a learning application as an example.

2. Abstracted Operations vs. Native SQL

The persistence package offers two methodologies for data manipulation and querying:

• Convenience Methods: They allow performing insertions or updates programmatically by passing a structured map, delegating the internal construction of the query to the framework. An example is the insert() method, which abstracts the structured syntax.
• Raw Queries: When complex filters with WHERE clauses, sorting via ORDER BY, specific limits, or uniqueness validations (such as checking if a record already exists before inserting it) are required, direct SQL statements are used via rawQuery() or rawInsert(). These queries return collections of maps that must then be cast to the corresponding data type for use in the interface.

import 'package:sqflite/sqflite.dart';
import '../../models/latest_practice.dart';

/// Provides database operations for the user's latest practices.
class LatestPracticeDbService {
  final Database db;

  LatestPracticeDbService(this.db);

  /// Inserts a [LatestPractice] into the database.
  Future<void> insertLatestPractice(
    LatestPractice practice, {
    required int maxWords,
  }) async {
    // 1. VERIFICATION: Check if the combination of notTraslated and traslated already exists
    final List<Map<String, dynamic>> existingRecords = await db.query(
      'latest_practices',
      where: 'notTraslated = ?',
      whereArgs: [practice.notTraslated],
    );

    if (existingRecords.isNotEmpty) {
      // If it already exists, we update the existing record with the new data
      // final idExistente = existingRecords.first['id'];
      // await db.update(
      //    'latest_practices',
      //    practice.toMap(),
      //    where: 'id = ?',
      //    whereArgs: [idExistente],
      // );

      // Since it was an update, the total number of rows did not change.
      // We return here to prevent insertion and the excess deletion logic.
      return;
    }

    await db.insert('latest_practices', practice.toMap());

    final count = Sqflite.firstIntValue(
      await db.rawQuery('SELECT COUNT(*) FROM latest_practices'),
    );
    if (count != null && count > maxWords) {
      final excess = count - maxWords;
      await db.rawDelete(
        'DELETE FROM latest_practices WHERE id IN (SELECT id FROM latest_practices ORDER BY date ASC LIMIT ?)',
        [excess],
      );
    }
  }

  /// Retrieves all [LatestPractice] from the database, ordered by date descending.
  Future<List<LatestPractice>> getAllLatestPractices() async {
    final List<Map<String, dynamic>> maps = await db.query(
      'latest_practices',
      orderBy: 'date DESC',
    );

    if (maps.isEmpty) {
      return [];
    }

    return List.generate(maps.length, (i) {
      return LatestPractice.fromMap(maps[i]);
    });
  }

  /// Deletes all [LatestPractice] from the database.
  Future<void> deleteAllLatestPractices() async {
    await db.delete('latest_practices');
  }
}

Inserting records

To insert records into the database, the insert() function is used, which receives two parameters:

1. The table where you want to insert the records.
2. The map containing the data to be inserted.

Regarding the map, as you might assume, the function named toMap() defined in the Place model will be used.

Remember that to perform any operation on the database, it is necessary to open it, and for that, the _openDB() function created previously is used.

Finally, the code for the insert function:

lib\helpers\db_helper.dart

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

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

class DBHelper {
 static Future<Database> _openDB() async {
  // ***
 }

 static Future<int> insert(Place place) async {
   Database database = await _openDB();
   return database.insert("places", place.toMap());
 }
}

Retrieving the list of all records

To get a list of all records, the function named query() is used, indicating the name of the table; this function returns a list of maps, which is converted to a list of objects using the List.generate() function, iterating through each of the records returned in a map and converting them to an object:

lib\helpers\db_helper.dart

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

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

class DBHelper {
 static Future<Database> _openDB() async {
  // ***
 }

 static Future<int> insert(Place place) async {
  // ***
 }

 static Future<List<Place>> places() async {
   Database database = await _openDB();

   final List<Map<String, dynamic>> placesMap = await database.query("places");

   for (var p in placesMap) {
     print("${p['id']}  ${p['name']} ");
   }

   return List.generate(
       placesMap.length,
       (i) => Place(
           id: placesMap[i]['id'],
           name: placesMap[i]['name'],
           image: placesMap[i]['image']));
 }
}

Updating records

The update function is similar to the insert function; however, a where clause is defined to filter the records that you want to update; in this particular case, it would be a single record searched by the site's id:

• where specifies the conditions or condition by which you want to filter, indicating the column, operator, and value.
• whereArgs specifies the values or value to be passed into the where parameter; these are typical schemes to separate the condition from the data to help prevent "SQL injection attacks".

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

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

class DBHelper {
 static Future<Database> _openDB() async {
  // ***
 }

 static Future<int> insert(Place place) async {
  // ***
 }

 static Future<List<Place>> places() async {
   // ***
 }

 static Future<int> update(Place place) async {
   Database database = await _openDB();
   return database.update("places", place.toMap(),
       where: 'id = ?', whereArgs: [place.id]);
 } 
}

Deleting records

To delete records, the same logic as updating is applied, indicating the where query, but in this case, data for creating or updating is not specified:

import 'package:place/models/place.dart';
import 'package:sqflite/sqflite.dart';

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

class DBHelper {
 static Future<Database> _openDB() async {
  // ***
 }

 static Future<int> insert(Place place) async {
  // ***
 }

 static Future<List<Place>> places() async {
   // ***
 }

 static Future<int> update(Place place) async {
   // ***
 }

 static Future<int> delete(Place place) async {
   Database database = await _openDB();
   return database.delete("places", where: 'id = ?', whereArgs: [place.id]);
 }
}

The use of all functions with which operations are going to be performed on the database are asynchronous, meaning there is no need to create instances of this class.

In Flutter, we have several options to persist data, and we also have the HiveDB database.