Getting to know Flutter: SQFLite
Tags: dart, beginners, flutter, database
Published: 2021-02-10 16:04:09

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](https://pub.dev/packages/shared_preferences) 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](https://pub.dev/packages/path_provider) package and the [dart:io](https://api.flutter.dev/flutter/dart-io/dart-io-library.html) 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](https://pub.flutter-io.cn/packages/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.

```javascript
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.

```dart
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.

```dart
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

```dart
  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

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

 

# Update a TV Series

```dart
  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

```dart
  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?