7 min read

Guide for SQFlite in Flutter

Guide for SQFlite in Flutter

Why SQFlite?

Sqflite is a popular package for implementing SQLite databases in Flutter. It provides a simple and efficient way to store and retrieve data from local storage, making it ideal for mobile applications that require offline data access.

Some benefits of using Sqflite in Flutter include:

Efficiency: Sqflite is built on top of SQLite, which is a fast and reliable database engine. It uses minimal resources and can handle large datasets efficiently.

Scalability: Sqflite is scalable and can handle complex data structures with ease. It can be used to implement tables with relationships, indices, and constraints.

Ease of Use: Sqflite provides a simple and intuitive interface for database operations. It allows developers to write clean and maintainable code by abstracting away the complexities of SQL queries.

Cross-platform Support: Sqflite is compatible with both iOS and Android platforms. It provides a unified API that works seamlessly on both platforms, making it easy to develop cross-platform applications.

By using Sqflite in Flutter, developers can create high-performance mobile applications with robust data storage capabilities.

Get started

Step 1: Add the dependency to your pubspec.yaml file. Open your project's pubspec.yaml file and add the following line under the dependencies section:

dependencies:
  sqflite: ^2.3.0

Step 2: Run flutter pub get. Save the pubspec.yaml file and run the following command in your terminal to fetch the auto_route dependency:

flutter pub get

How to create a database in SQFlite

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

Future<Database> openMyDatabase() async {
  final dbPath = await getDatabasesPath();
  final path = join(dbPath, 'my_database.db');
  final database = await openDatabase(
    path,
    version: 1,
    onCreate: (db, version) async {
      // Create tables here
    },
  );
  return database;
}

In this example, we’re creating a new function called openMyDatabase that returns a Future object. Inside this function, we're first getting the default databases directory using getDatabasesPath. Then we're joining this directory with our desired database name (my_database.db) to get its full path.

Finally, we’re calling openDatabase with our full path and specifying that our current version is 1. We're also providing an onCreate callback function that gets called if our specified version does not match any existing versions. In this callback function, we can create tables using SQL statements like CREATE TABLE.

How to create a table in SQFlite

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

class DatabaseHelper {
  static final _databaseName = "myDatabase.db";
  static final _databaseVersion = 1;

  static final table = 'myTable';
  static final columnId = '_id';
  static final columnName = 'name';
  static final columnAge = 'age';

  // 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;
    _database = await _initDatabase();
    return _database;
  }

  // this opens the database (and creates it if it doesn't exist)
  _initDatabase() async {
    String path = join(await getDatabasesPath(), _databaseName);
    return await openDatabase(path, version: _databaseVersion,
        onCreate: (db, version) {
      db.execute('''
          CREATE TABLE $table (
            $columnId INTEGER PRIMARY KEY,
            $columnName TEXT NOT NULL,
            $columnAge INTEGER NOT NULL
          )
          ''');
    });
  }
}

This code defines an SQLite database using the SQLite package. The DatabaseHelper class is a singleton that manages the creation and access of the database. The table variable specifies the name of the table, and columnId, columnName, and columnAge specify the names of the columns in the table.

The _initDatabase() method opens the database, creating it if it doesn’t exist, and executes an SQL statement to create the table with the specified columns.

To use this code to create a table, simply call await DatabaseHelper.instance.database; in your code. It will open the database and create the table if it doesn’t already exist.

How to insert data into a table in SQFlite

Now that you have created your database and tables, it’s time to start inserting data into them. To do this, you can use the insert method provided by the Database object.

Here’s an example:

final db = await openMyDatabase();
await db.insert(
  'my_table',
  {
    'column1': 'value1',
    'column2': 42,
  },
);

In this example, we’re first opening our database using the openMyDatabase function we defined earlier. Then we're calling the insert method on our database object.

The first argument of the insert method is the name of the table we want to insert data into (my_table in this case). The second argument is a map that contains key-value pairs representing the columns and their values. In this example, we're inserting a string value ('value1') into 'column1', and an integer value (42) into 'column2'.

You can also insert multiple rows at once using the batch method:

final db = await openMyDatabase();
await db.batch().insert(
  'my_table',
  [
    {'column1': 'value1', 'column2': 42},
    {'column1': 'value2', 'column2': 43},
    {'column1': 'value3', 'column2': 44},
  ],
);

In this example, we’re calling the batch method on our database object to create a new batch operation. Then we're calling the insert method on this batch object with an array of maps representing each row to be inserted.

How to retrieve data from a table in SQFlite

Retrieving data from a table in SQFlite is similar to inserting data. You can use SQL queries or ORMs like moor_flutter to retrieve data.

To retrieve all rows from a table, you can use the query method provided by the Database object:

final db = await openMyDatabase();
final rows = await db.query('my_table');

In this example, we’re first opening our database using the openMyDatabase function we defined earlier. Then we're calling the query method on our database object with the name of the table we want to retrieve data from (my_table in this case).

The query method returns a list of maps representing each row retrieved from the table. Each map contains key-value pairs representing the columns and their values.

You can also filter your results using SQL queries:

final db = await openMyDatabase();
final rows = await db.rawQuery(
  'SELECT * FROM my_table WHERE column1 = ?',
  ['value1'],
);

In this example, we’re using the rawQuery method instead of the query method to execute a raw SQL query. The first argument of this method is our SQL statement, which selects all columns from my_table where 'column1' equals 'value1'. The second argument is an array containing any placeholders used in our SQL statement (? in this case).

Once you’ve retrieved your data successfully, you can display it in your UI or perform further computations on it as needed.

How to update data in a table in SQFlite

Updating data in a table is an essential operation in most applications. To update data, you can use the update the method provided by the Database object.

Here’s an example:

final db = await openMyDatabase();
await db.update(
  'my_table',
  {
    'column1': 'new_value1',
    'column2': 43,
  },
  where: 'id = ?',
  whereArgs: [1],
);

In this example, we’re first opening our database using the openMyDatabase function we defined earlier. Then we're calling the update method on our database object.

The first argument of the update method is the name of the table we want to update data in (my_table in this case). The second argument is a map that contains key-value pairs representing the columns and their new values. In this example, we're updating 'column1' with a new string value ('new_value1') and 'column2' with a new integer value (43).

The third argument is an optional SQL WHERE clause that specifies which rows to update. In this example, we’re updating only the row with an ID of 1. The fourth argument is an array containing any placeholders used in our SQL WHERE clause (? in this case).

You can also use raw SQL queries to update your data:

final db = await openMyDatabase();
await db.rawUpdate(
  'UPDATE my_table SET column1 = ?, column2 = ? WHERE id = ?',
  ['new_value1', 43, 1],
);

In this example, we’re using the rawUpdate method instead of the update method to execute a raw SQL UPDATE statement. The first argument of this method is our SQL statement, which updates 'column1' and 'column2' with new values and filters by ID. The second argument is an array containing any placeholders used in our SQL statement (? in this case).

How to delete data from a table in SQFlite

Deleting data from a table is as important as inserting or updating it. To delete data, you can use the delete method provided by the Database object.

Here’s an example:

final db = await openMyDatabase();
await db.delete(
  'my_table',
  where: 'id = ?',
  whereArgs: [1],
);

In this example, we’re first opening our database using the openMyDatabase function we defined earlier. Then we're calling the delete method on our database object.

The first argument of the delete method is the name of the table we want to delete data from (my_table in this case). The second argument is an optional SQL WHERE clause that specifies which rows to delete. In this example, we're deleting only the row with an ID of 1. The third argument is an array containing any placeholders used in our SQL WHERE clause (? in this case).

You can also use raw SQL queries to delete your data:

final db = await openMyDatabase();
await db.rawDelete(
  'DELETE FROM my_table WHERE id = ?',
  [1],
);

In this example, we’re using the rawDelete method instead of the delete method to execute a raw SQL DELETE statement. The first argument of this method is our SQL statement, which deletes rows filtered by ID. The second argument is an array containing any placeholders used in our SQL statement (? in this case).

Done! You’ve successfully incorporated and set up the SQFlite dependency in your Flutter app. You are now equipped to employ the generated database interface to interact with your SQLite database, facilitating the storage and retrieval of data within your application.

Published by:

Deep Rupareliya