A beginners Guide to Integrating Sqflite in Flutter Projects

In the world of mobile app development, the need for robust and efficient data management solutions is paramount. When building complex Flutter applications that require local data storage and management, this can prove to be a game-changer.

What is Sqflite?

Sqflite is a Flutter plugin that provides a simple and efficient way to implement SQLite databases in your Flutter applications. With Sqflite, you can perform various database operations, such as creating, querying, updating, and deleting data, making it an essential tool for managing local data storage in your Flutter projects.

Its simplicity, coupled with its powerful capabilities, makes it a popular choice for developers looking to incorporate local database functionality into their applications.

How to Integrate Sqflite in Flutter Projects

Integrating Sqflite into your Flutter projects is a straightforward process. Follow these simple steps to get started:

Add the Dependency

Open your project's pubspec.yaml file and add the Sqflite dependency:

dependencies:
  sqflite: ^2.3.0

Install the Dependency

After adding the dependency, run the following command in your terminal:

flutter pub get

Import Sqflite

Import the Sqflite package into your Dart code:

import 'package:sqflite/sqflite.dart';

Use Sqflite API

Utilize the Sqflite API to create and manage your SQLite database operations. You can create tables, execute queries, and perform various data manipulation tasks.

Creating a Database and Table

Here we create a SQLite database and a table within that database using Sqflite in Flutter. It utilizes the openDatabase method to create a new database or open an existing one.

The onCreate callback is used to execute a SQL command that creates a table named "Users" with three columns: "id" as the primary key, "username" as a TEXT type, and "age" as an INTEGER type. Additionally, it retrieves the path for the database using getDatabasesPath() and joins it with the database name "example.db".

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

Future<void> createDatabase() async {
  var databasesPath = await getDatabasesPath();
  String path = join(databasesPath, 'my_database.db');

  // Delete any existing database:
  await deleteDatabase(path);

  // Create the database
  Database database = await openDatabase(path, version: 1,
      onCreate: (Database db, int version) async {
    await db.execute('
      CREATE TABLE Users (
        id INTEGER PRIMARY KEY,
        username TEXT,
        age INTEGER
      )
     ');
  });
}

Inserting a Row

To insert a row into the "Users" table in the SQLite database, it utilizes the insert method, which takes the table name, a map representing the data to be inserted, and an optional conflictAlgorithm parameter to handle conflicts that may arise during the insertion process.

In this case, if there is a conflict, the existing row is replaced with the new data.

Future<void> insertData(Database database) async {
  await database.insert(
    'Users',
    {'username': 'Alice', 'age': 30},
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
}

Selecting Data

To perform a simple query to retrieve data from the "Users" table in the SQLite database, it utilizes the query method, which takes the table name as a parameter and returns a list of maps representing the queried rows. The retrieved data can then be used for further processing or display purposes.

Future<List<Map<String, dynamic>>> queryData(Database database) async {
  return await database.query('Users');
}

Custom SQL query

To execute a custom SQL query that selects all rows from the "Users" table where the value of the "age" column is greater than 25. The rawQuery method allows you to execute custom SQL queries directly.

Make sure to handle the results appropriately based on the specific requirements of your application.

Future<List<Map<String, dynamic>>> customQuery(Database database) async {
  return await database.rawQuery('SELECT * FROM "Users" WHERE age > 25');
}

Deleting a Row

To delete a specific row from the "Users" table in the SQLite database based on a provided condition, it uses the delete method, which takes the table name, a where clause specifying the condition for deletion, and optional whereArgs to provide values for the placeholders in the where clause. The method returns the number of rows deleted as an integer.

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

Conclusion

In conclusion, integrating Sqflite in your Flutter projects can significantly enhance the performance and user experience of your applications.

Its simplicity, efficiency, and powerful data management capabilities make it an indispensable tool for managing local data storage and operations. By following the steps outlined in this guide and leveraging Sqflite's robust features, you can create powerful Flutter applications that deliver a seamless and efficient user experience.