Local Data Storage in Flutter using SQFLite


Local Data Storage in Flutter using SQFLite

Use SQFLite - the SQLite plugin for Flutter to persist data locally in Flutter apps, enabling offline app usage.

Local data storage, Flutter app needs? Try SQFLite you must

Storing data to the local disk is a popular demand when it comes to offline apps like Google Keep, or apps that need to cache the latest data such that it’s still partially accessible in case the device is disconnected from the internet.
SQFLite is the SQLite plugin for Flutter that allows large and complex data to be stored to the local disk in a structured format, such that it makes it easy for the developers to perform CRUD operations on this data.

This tutorial will teach you how to integrate SQFLite in any existing application to perform CRUD operations on local data.

For the purpose of this tutorial, we will see SQFLite in action with a dream journal app that allows you to:

  1. record a dream as soon as you wake up,
  2. view all the dream entries you’ve made as yet,
  3. edit any particular dream entry later while you’re reviewing it,
  4. delete a specific dream entry, and
  5. search dream entries on the basis of keywords.

Dream Journal app using SQFLite

App Architecture

As seen in the gif above, our Dream Journal app has a total of 3 screens:
Screen 1: a list of all entries along with a floating action button to create a new journal entry, and each entry has a X button to delete that specific entry.
Screen 2: a form for the journal entry used to either record a new dream or to view an existing dream and edit it.
Screen 3: a search screen to look up dream entries containing that specific keyword in their titles.

I’m using BLoC for State Management here, but it’s etirely up to you to use whichever state management technique you prefer, depending on your use case.

This tutorial will only focus on the aspects of SQFLite - to keep things simple.

Getting Started

First off, add the following dependencies in your pubspec.yaml:

dependencies:

  sqflite: ^1.3.1+1
  path: ^1.6.4

sqflite is the Flutter plugin for SQLite and will be used to create/open our locally embedded database and perform CRUD operations on it,
and
path is the plugin responsible for physically embedding the database to the local disk.

If you run into any errors relating to version conflicts while running flutter pub get, upgrade or downgrade the version of the plugin throwing this error to make it consistent with the current version of your flutter sdk.

Creating the Dream class

Next, create a simple Dream class with the attributes you want to record and store in the database:

class Dream {
  int id;
  String title;
  String details;

  Dream({this.id, this.title, this.details});

  /// serialise object when writing to database
  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      DatabaseProvider.COLUMN_ID: id,
      DatabaseProvider.COLUMN_TITLE: title,
      DatabaseProvider.COLUMN_DETAILS: details,
    };

    return map;
  }

  /// deserialise object when reading from database
  Dream.fromMap(Map<String, dynamic> map) {
    id = map[DatabaseProvider.COLUMN_ID];
    title = map[DatabaseProvider.COLUMN_TITLE];
    details = map[DatabaseProvider.COLUMN_DETAILS];
  }
}

Each dream entry that we create for our journal will have a unique id, title and dream details that we want to record.

The toMap() method converts our Dream object to a Map object, so it can be stored in our database,
and
the fromMap() method converts the Map object that we retrieve from the database into the actual Dream object which will then be displayed in its respective UI.

Simply put, toMap() will handle the serialisation of our Dream object, and fromMap() will handle the deserialisation.

Also note the use of constants in place of each of the keys in our Map in the toMap() and fromMap() methods.
Let’s set these up next!

Creating the Database helper class

Create a new file db_provider.dart. This will contain our DatabaseProvider class - the class responsible for performing all operations related to our database.

import 'models.dart'; // the file that contains our Dream class
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite/sqlite_api.dart';

class DatabaseProvider {
  static const String TABLE_DREAM = 'dream';
  static const String COLUMN_ID = 'id';
  static const String COLUMN_TITLE = 'title';
  static const String COLUMN_DETAILS = 'details';
}

Our database will have one table called dream, and this table will have 3 fields; namely id, title and details because these are the 3 fields that we want to record for every dream entry in our journal.

Hence, we created the 4 constants TABLE_DREAM, COLUMN_ID, COLUMN_TITLE and COLUMN_DETAILS to avoid typing errors when we write our database queries or perform serialisation/deserialisation in our Dream class.
Instead of manually typing dream everytime we want to query on our dream table, we can simply use the TABLE_DREAM constant in its place.

Next, add the following lines to your DatabaseProvider class:

  // make this a singleton class
  DatabaseProvider._();
  static final DatabaseProvider db = DatabaseProvider._();

  // single reference to the database throughout the app
  Database _database;

  Future<Database> get database async {
    print('database getter called');

    if (_database != null) {
      return _database;
    }

    _database = await createDatabase();

    return _database;
  }

The DatabaseProvider class needs to be a singleton class, so we can create just one object of this class and use its state globally throughout the app whenever we want to use one of its methods.
The database getter will check if there’s any previously created instance of the Database object.
If it exists, that instance will be returned. If it doesn’t exist, it will create it using the createDatabase() method:

  Future<Database> createDatabase() async {
    String dbPath = await getDatabasesPath();

    return await openDatabase(
      join(dbPath, 'dreamDB.db'),
      version: 1,
      onCreate: (Database database, int version) async {
        print('Creating dream table');

        await database.execute(
          "CREATE TABLE $TABLE_DREAM ("
          "$COLUMN_ID INTEGER PRIMARY KEY,"
          "$COLUMN_TITLE TEXT,"
          "$COLUMN_DETAILS TEXT"
          ")",
        );
      },
    );
  }

The getDatabasesPath() is a method provided by the sqflite plugin which returns the default databases location.
On Android, it’s usually data/data//databases
and on iOS, it’s the Documents directory.

The openDatabase() is also a method provided by the sqflite plugin which then uses the dbPath to create our database if it doesn’t already exist and returns a singleton instance to it that we can use in our queries. If the said database already exists, this method simply returns a singleton instance to it.

Let’s look at this method step by step:

  1. We first pass the full path to our database location by using the join() method from the path plugin we initially added as a dependency.
    By providing dbPath and the name of our database - dreamDB.db to this method, the path plugin joins the 2 and returns the final location of our database.

NOTE: name of the database should always have a .db extension.

  1. The version parameter specifies the schema version of the database being opened. This is useful when we make schema changes to our database as this parameter indicates whether onCreate, onUpgrade or onDowngrade need to be called.

  2. The onCreate parameter creates the tables in our database when our database is being created. Note the syntax of creating our dream table. The id attribute of our Dream object is the primary key of this table, whereas the other 2 attributes which were of type String are denoted as type Text in our database.

Now that our setup is done, let’s create methods for each of the CRUD operations we need to perform in our app!

Read Dream Entries from the database

Add this code to your DatabaseProvider class:

  Future<List<Dream>> getDreams() async {
    final db = await database; // database getter called

    // from the dream table, get all rows (records) in the 1. descending order of their id, 2. columns returned should be id, title, and details
    // this returns a List<Map<String, dynamic>>
    var dreams = await db.query(
        TABLE_DREAM,
        columns: [COLUMN_ID, COLUMN_TITLE, COLUMN_DETAILS],
        orderBy: '$COLUMN_ID desc',
    );

    List<Dream> dreamsList = [];

    dreams.forEach((currentDream) {
      Dream dream = Dream.fromMap(currentDream); // deserialise

      dreamsList.add(dream);
    });

    return dreamsList;
  }

Note that the query() method is responsible for performing all read queries to a certain table.
You can specify different parameters to control the records you need to fetch. For instance, we have specified that our records need to be sorted in the descending order of their ids, using the orderBy parameter.

Create a new Dream Entry in the database

Add this code to your DatabaseProvider class:

  Future<Dream> insert(Dream dream) async {
    final db = await database; // database getter called
    dream.id = await db.insert(TABLE_DREAM, dream.toMap()); // serialise
    return dream;
  }

Note that the insert() method creates a new entry in the table specified. For this, we need to serialise our Dream object to a Map object using the toMap() method.

Delete a Dream Entry from the database

Add this code to your DatabaseProvider class:

  Future<int> delete(int id) async {
    final db = await database; // database getter called

    return await db.delete(
      TABLE_DREAM,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

Note that the delete() method deletes an entry/row from the table specified. For this, we need to define the criteria for our deletion.
In our case, we need to simply delete a Dream Entry by its id, so we specify that using the where and whereArgs parameters.
where takes the condition, and whereArgs takes the actual values that need to be used in the defined condition.

So the query above can be mentally read as:
Delete rows from the dream table WHERE the id column has the value id (the dream id that we provided).

Update a Dream Entry in the database

Add this code to your DatabaseProvider class:

  Future<int> update(Dream dream) async {
    final db = await database; // database getter called

    return await db.update(
      TABLE_DREAM,
      dream.toMap(),
      where: 'id = ?',
      whereArgs: [dream.id],
    );
  }

Note that the update() method updates an entry/row in the table specified. For this, we need to pass the new Dream object after serialising it and also define the criteria for updating.
In our case, we need to simply update a Dream Entry by its id, so we specify that using the where and whereArgs parameters.

So the query above can be mentally read as:
Update rows in the dream table with this new Dream object WHERE the id column has the value id (the dream id that we provided).

This query will look for all rows with the specified id and update its contents using the new Dream object that we provided.

Keyword based Search of Dream Entries from the database

Add this code to your DatabaseProvider class:

  Future<List<Dream>> getDreamsByKeywordInTitle(String keyword) async {
    final db = await database; // database getter called

    var dreams = await db.query(
      TABLE_DREAM,
      columns: [COLUMN_ID, COLUMN_TITLE, COLUMN_DETAILS],
      orderBy: '$COLUMN_ID desc',
      where: '$COLUMN_TITLE LIKE ?',
      whereArgs: ['%$keyword%']
    );

    List<Dream> dreamsList = [];

    dreams.forEach((currentDream) {
      Dream dream = Dream.fromMap(currentDream); // serialise

      dreamsList.add(dream);
    });

    return dreamsList;
  }

The LIKE operator searches for the specified pattern in the specified column - title, in our case
and
the % signs before and after our keyword variable indicate that the title can begin or end with a keyword, or even have it in any position in the middle.

So for instance, we have 5 entries in our table, and their titles are as follows:

  1. Cat playing DOTA
  2. I turned into a cat
  3. The pizza ate me
  4. Entering the blackhole
  5. Got a cat for my birthday

and we pass cat as our search keyword, then our query will return the 1st, 2nd and 5th records because their titles contain the word cat.

This query can be read as follows:
Get rows from the dream table in descending order of their id WHERE the title contains this keyword.

Calling the DatabaseProvider methods (UI + State Management)

You’re done with the most difficult part! Now all you need to do is call the methods you just created in your DatabaseProvider class to update your UI and handle state management accordingly.

Doing this is no different than making the usual async calls and handling Futures when working with Firestore, Rest APIs etc.

Since I’m using BLoC for state management, this is how I’m calling the getDreams() to fetch all my dream entries and display them in a ListView:

class _DreamEntriesState extends State<DreamEntries> {
  
  void initState() {
    super.initState();
    DatabaseProvider.db.getDreams().then(
      (dreamsList) {
        BlocProvider.of<DreamBloc>(context).add(SetDreams(dreamsList));
      },
    );
  }

  
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Tell Me Your Dreams 👀')),
      floatingActionButton: FloatingActionButton(
        child: Icon(Icons.add),
        onPressed: () => Navigator.push(
          context,
          MaterialPageRoute(builder: (BuildContext context) => DreamForm()),
        ),
      ),
      body: BlocConsumer<DreamBloc, List<Dream>>(
        builder: (context, dreamsList) {
          return ListView.separated(
            padding: const EdgeInsets.symmetric(vertical: 16),
            itemCount: dreamsList.length,
            shrinkWrap: true,
            separatorBuilder: (BuildContext context, int index) =>
                Divider(
              color: Colors.black,
              indent: 10,
              endIndent: 10,
            ),
            itemBuilder: (BuildContext context, int index) {
              Dream dream = dreamsList[index];
              return ListTile(
                title: Text(dream.title, style: TextStyle(fontSize: 22)),
                subtitle: Text(
                  dream.details,
                  maxLines: 3,
                  style: TextStyle(fontSize: 18),
                ),
                trailing: IconButton(
                  icon: Icon(Icons.clear),
                  onPressed: () =>
                      deleteConfirmationDialog(context, dream, index),
                ),
                onTap: () => Navigator.push(
                  context,
                  MaterialPageRoute(
                    builder: (context) =>
                        DreamForm(dream: dream, dreamIndex: index),
                  ),
                ),
              );
            },
          );
        },
      ),
    );
  }
}

If you’re not using any specific technique for state management, you can also simply wrap your ListView inside a FutureBuilder and make a call to the getDreams() method against the future parameter of your FutureBuilder.

You can make calls to the other methods in a similar fashion.

Check out the full source code to see the complete UI code and all the other method calls used with BLoC for state management.


Author: Sakina Abbas
Reprint policy: All articles in this blog are used except for special statements CC BY 4.0 reprint policy. If reproduced, please indicate source reactree.com !
  TOC