Getting to know Flutter: SQFLite

So you've created a beautiful Flutter app and now you're wondering how to persist data between app launches. Well, you have a couple of options:

• If you're storing a small amount of data you can use the shared_preference plugin and save it using key-value pairs.
• If your data structure is not based on few elements but not even large objects you can write and read files to disk using the path_provider package and the dart:io library.
• If your app needs to read and write a large volume of data the best option is to save it in a database using the sqflite package.

In this article we will be focusing on sqflite, which is the SQLite plugin for Flutter (yes the "f" of the name stands for Flutter 😄). SQLite is the most used database engine for mobile application, because it's reliable, small and fast.

On this example we will create a little database to store TV Series you can't wait to binge watch (yes, I know you have all the major streaming services subscriptions but 24 hours per day is too little time for using all of them, let's try not to forget any of them).

The first thing to do is to add the following packages to your pubspec.yaml file, we will use path_provider to find the correct path to save the database.

dependencies:
  sqflite: ^1.3.2
  path_provider: ^1.6.27

Once you've added those up install the packages running a good old flutter pub get.

Now let's create our helper class that will manage all the database functions. it is not "essential" to do that in a single class, you can also have initialization and data creation on Widgets or other places, but I'm sure you'll need a bit of order in your code.
Create the database_manager.dart class with this content.

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

class DatabaseManager {
  static final _dbName = "Database.db";

  // Use this class as a singleton
  DatabaseManager._privateConstructor();

  static final DatabaseManager instance = DatabaseManager._privateConstructor();

  static Database _database;

  Future<Database> get database async {
    if (_database != null) return _database;
    // Instantiate the database only when it's not been initialized yet.
    _database = await _initDatabase();
    return _database;
  }

  // Creates and opens the database.
  _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _dbName);
    return await openDatabase(
      path,
      version: 1,
      onCreate: _onCreate,
    );
  }

  // Creates the database structure
  Future _onCreate(
    Database db,
    int version,
  ) async {
    await db.execute('''
          CREATE TABLE TVSeries (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            image TEXT,
            episodes INTEGER,
            description TEXT
          )
          ''');
  }
}

This is the singleton class that we will use to interact with the database, using DatabaseManager.instance. We've created the singleton and added the code to create the TVSeries table.

Now let's create the model class that will represent a TV series in our app (tv_series.dart). Each TV series will have an id, a title, an image, the number of episodes and a description. The functions fromDbMap and toDbMap will be used to save and retrieve TVSeries objects from the database. See that's good practice to have these methods on the class.

class TVSeries {
  int id;
  String title;
  int episodes;
  String image;
  String description;

  TVSeries.fromDbMap(Map<String, dynamic> map)
      : id = map['id'],
        title = map['title'],
        image = map['image'],
        episodes = map['episodes'],
        description = map['description'];

  Map<String, dynamic> toDbMap() {
    var map = Map<String, dynamic>();
    map['id'] = id;
    map['title'] = title;
    map['image'] = image;
    map['episodes'] = episodes;
    map['description'] = description;
    return map;
  }
}

The last thing that we have to do is to add functions to fetch, add, edit or delete TVSeries in the DatabaseManager class.

Below you'll see some useful methods you can integrate on your helper class:

Fetch all TV Series

  Future<List<TVSeries>> fetchAllTvSeries() async {
    Database database = _database;
    List<Map<String, dynamic>> maps = await database.query('TVSeries');
    if (maps.isNotEmpty) {
      return maps.map((map) => TVSeries.fromDbMap(map)).toList();
    }
    return null;
  }

Add a TV Series

  Future<int> addTVSeries(TVSeries tvSeries) async {
    Database database = _database;
    return database.insert(
      'TVSeries',
      tvSeries.toDbMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

Update a TV Series

  Future<int> updateTvSeries(TVSeries newTvSeries) async {
    Database database = _database;
    return database.update(
      'TVSeries',
      newTvSeries.toDbMap(),
      where: 'id = ?',
      whereArgs: [newTvSeries.id],
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

Delete a TV Series

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

Well, you got to the bottom, was it hard? It wasn't, right?
Be aware that there's not really a "wrong" method to store data, you can absolutely have thousands of files saved, but SQFLite is not only the most used data persistence method but also the one recommended by Flutter team, so it will mean something... Ain't it?