Flutter sqflite: Persist data with SQLite

pubspec.yaml

sqflite: ^1.3.2+1

lib/services/local/migrate/migrate.dart

import 'package:sqflite/sqflite.dart';

abstract class Migrate {
  /// Handle the creation of a fresh database in onCreate
  Future<void> create(Batch batch);

  /// Handle the schema migration in onUpgrade
  Future<void> upgrade(Batch batch);

  /// Clean table
  void deleteRepo(Batch batch, String repoName) {
    batch.execute('DELETE TABLE IF EXISTS $repoName');
  }

  /// Add column
  /// Ex: columnAndType = 'updated INTEGER'
  void addColumn(Batch batch, String repoName, String columnAndType) {
    batch.execute('ALTER TABLE IF EXISTS $repoName ADD COLUMN $columnAndType;');
  }

  /// Drop repo
  void dropRepo(Batch batch, String repoName) {
    batch.execute('DROP TABLE IF EXISTS $repoName;');
  }
}

lib/services/local/migrate/migrate_v1.dart

import 'package:sqflite/sqlite_api.dart';

import '../repo/config_repo.dart';
import 'migrate.dart';

class MigrateV1 extends Migrate {
  @override
  Future<void> create(Batch batch) async {
    await ConfigRepo().create(batch);
  }

  @override
  Future<void> upgrade(Batch batch) async {
    /// With the first version (v1) no need to upgrade anything
    /// do nothing here
  }
}

lib/services/local/migrate/migrate_v2.dart

import '../repo/config_repo.dart';
import '../repo/new_repo.dart';
import 'migrate.dart';

class MigrateV2 extends Migrate {
  @override
  Future<void> create(Batch batch) async {
    await ConfigRepo().create(batch);
    await NewRepo().create(batch);
  }

  @override
  Future<void> upgrade(Batch batch) async {
    // Create new repo
    await NewRepo().create(batch);

    // Example other upgrade
    // Add updated INTEGER to NewRepo
    //addColumn(batch, NewRepo.repoName, 'updated INTEGER');

    // Or drop all and create again
    // Drop all table definition and all associated data
    //dropRepo(batch, 'config_repo');
    //dropRepo(batch, 'new_repo');
    // Recreate
    //await create(batch);
  }
}

lib/services/local/repo/base_model.dart

abstract class BaseModel<T> {

  Map<String, dynamic> toJson();

  T fromJson(Map<String, dynamic> json);
}

lib/services/local/repo/base_repo.dart

import 'package:flutter/cupertino.dart';
import 'package:sqflite/sqlite_api.dart';

import '../local_provider.dart';

abstract class IWrite<T> {
  Future<bool> create(Batch batch);

  Future<int> insert(T item);

  Future<List<dynamic>> insertAll(List<T> items);

  Future<int> update(T item);

  Future<int> delete(String id);
}

abstract class IRead<T> {
  Future<List<T>> find({String where, List<String> whereArgs});

  Future<T> findOne(String id);
}

abstract class BaseRepo<T> implements IWrite<T>, IRead<T> {
  Database database = LocalProvider.instance.database;

  String getRepoName();

  Map<String, dynamic> toJson(T item);

  T fromJson(Map<String, dynamic> json);

  @override
  Future<int> insert(T item) async {
    return await database.insert(getRepoName(), toJson(item),
        conflictAlgorithm: ConflictAlgorithm.replace);
  }

  @override
  Future<List<dynamic>> insertAll(List<T> items) async {
    final Batch batch = database.batch();

    for (final T item in items) {
      batch.insert(getRepoName(), toJson(item),
          conflictAlgorithm: ConflictAlgorithm.replace);
    }

    final List<dynamic> res =
        (await batch.commit(continueOnError: true)).map<dynamic>((dynamic d) {
      if (d is DatabaseException) {
        debugPrint(d.toString());
        return -1;
      }
      return d;
    }).toList();
    return res;
  }

  @override
  Future<int> update(T item) async {
    return await database.update(getRepoName(), toJson(item));
  }

  @override
  Future<int> delete(String id) async {
    return await database
        .delete(getRepoName(), where: 'id = ?', whereArgs: <String>[id]);
  }

  @override
  Future<List<T>> find({String where, List<String> whereArgs}) async {
    final List<Map<String, dynamic>> results =
        await database.query(getRepoName(), where: where, whereArgs: whereArgs);
    /// Convert the List<Map<String, dynamic> into a List<T>.
    return List<T>.generate(results.length, (int i) => fromJson(results[i]));
  }

  @override
  Future<T> findOne(String id) async {
    final List<Map<String, dynamic>> results = await database
        .query(getRepoName(), where: 'id = ?', whereArgs: <String>[id]);
    if (results.isEmpty) {
      return null;
    }
    final List<T> data =
        List<T>.generate(results.length, (int i) => fromJson(results[i]));
    return data[0];
  }
}

lib/services/local/repo/config_repo.dart

import 'package:sqflite/sqlite_api.dart';

import 'base_model.dart';
import 'base_repo.dart';

class ConfigModel implements BaseModel<ConfigModel> {
  ConfigModel({this.id, this.data});

  String id;
  String data;

  @override
  Map<String, dynamic> toJson() {
    return <String, dynamic>{'id': id, 'data': data};
  }

  @override
  ConfigModel fromJson(Map<String, dynamic> json) {
    id = json['id'] as String;
    data = json['data'] as String;
    return this;
  }

  @override
  String toString() {
    return 'ConfigModel{id: $id, data: $data}';
  }
}

class ConfigRepo extends BaseRepo<ConfigModel> {
  static const String repoName = 'config_repo';

  @override
  Future<bool> create(Batch batch) async {
    batch.execute('''
    CREATE TABLE IF NOT EXISTS $repoName (
                              id TEXT PRIMARY KEY, 
                              data TEXT
                           )
                 ''');
    return true;
  }

  @override
  String getRepoName() {
    return repoName;
  }

  @override
  ConfigModel fromJson(Map<String, dynamic> json) {
    return ConfigModel().fromJson(json);
  }

  @override
  Map<String, dynamic> toJson(ConfigModel item) {
    return item.toJson();
  }
}

lib/services/local/local_provider.dart

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

import 'migrate/migrate.dart';
import 'migrate/migrate_v1.dart';

/// https://pub.dev/packages/sqflite
class LocalProvider {
  /// Create api instance
  LocalProvider._private();

  static final LocalProvider instance = LocalProvider._private();

  /// Database instance
  Database database;

  /// Init database connection
  Future<Database> init({String databaseName}) async {
    if (database != null && database.isOpen) {
      database.close();
    }
    return database = await openDatabase(
      join(await getDatabasesPath(), databaseName),
      onCreate: (Database db, int version) async {
        /// Data types: https://www.sqlite.org/datatype3.html
        Migrate migrate;
        switch (version) {
          case 1:
            migrate = MigrateV1();
            break;
        }

        /// Create as new installation
        if (migrate != null) {
          final Batch batch = db.batch();
          await migrate.create(batch);
          await batch.commit();
        }
      },
      onUpgrade: (Database db, int oldVersion, int newVersion) async {

        final List<Migrate> migrates = <Migrate>[
          MigrateV1(),
        ];
        for (int i = oldVersion + 1; i <= newVersion; i++) {
          final int migrateIndex = i - 1;
          if (migrateIndex >= 0 || migrateIndex < migrates.length) {
            final Migrate migrate = migrates[migrateIndex];
            final Batch batch = db.batch();
            await migrate.upgrade(batch);
            await batch.commit();
          }
        }
      },
      onDowngrade: onDatabaseDowngradeDelete,
      version: 1,
    );
  }
}

lib/services/store/store.dart

abstract class Store {
  Future<void> init();

  Future<void> clearAll();

}

lib/services/store/store_mock.dart

import 'package:nft/services/store/store.dart';

class StoreMock implements Store {
  @override
  Future<void> init() async {}

  @override
  Future<void> clearAll() async {}

}

lib/services/store/store_local.dart

import 'dart:async';

import 'package:flutter/foundation.dart';
import 'package:nft/services/local/local_provider.dart';
import 'package:nft/services/local/repo/config_repo.dart';
import 'package:nft/services/store/store.dart';
import 'package:sqflite/sqlite_api.dart';

///
/// Usage: init database in main
/// await StoreLocal.I.init();
///
class StoreLocal implements Store {
  StoreLocal._private();

  static final StoreLocal I = StoreLocal._private();

  Database database;

  @override
  Future<void> init({String databaseName = 'app_database.db'}) async {
    database = await LocalProvider.instance.init(databaseName: databaseName);
    debugPrint('Database version ${await database.getVersion()}');
  }

  @override
  Future<void> clearAll() async {
    await database.delete(ConfigRepo.repoName);
  }

  /// For test only
  Future<void> runTest() async {
    debugPrint('RUN TEST');

    /// Clear database
    await clearAll();

    /// test preference
    const String fakeId = 'fake_id';
    const String fakeVal = 'fake_val';
    await savePref(fakeId, fakeVal);
    assert(await getPref(fakeId) == fakeVal);

    debugPrint('TEST DONE');
  }

  /// Table Preference
  Future<String> _getPref(String key) async {
    final ConfigModel pref = await ConfigRepo().findOne(key);
    if (pref != null) {
      return pref.data;
    }
    return null;
  }

  Future<bool> _savePref(String key, String value) async {
    final ConfigModel pref =
        ConfigRepo().fromJson(<String, dynamic>{'id': key, 'data': value});
    return await ConfigRepo().insert(pref) > 0;
  }

  Future<bool> _removePref(String key) async {
    return await ConfigRepo().delete(key) > 0;
  }
  /// ----

}