An older sqflite repository and migration design

A local database layer needs a schema version, an upgrade path, and a clear owner for the open connection. This earlier Flutter design separated migrations, record conversion, repositories, and application-facing storage.

sqflite: ^1.3.2+1

Versioned migrations

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;');
  }
}

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
  }
}

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);
  }
}

The second migration references application repositories outside the small excerpt. New installation and upgrade-from-each-supported-version are different cases; neither is proved by opening one existing database.

Models and repositories

Read the historical model/repository layer

abstract class BaseModel<T> {

  Map<String, dynamic> toJson();

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

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];
  }
}

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();
  }
}

Validation and SQL parameter handling belong at the boundary where values reach queries. Generic repository methods do not establish a valid domain record by themselves.

Database lifetime and store access

Read the provider and store implementations

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,
    );
  }
}

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

  Future<void> clearAll();

}

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

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

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

}

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;
  }
  /// ----

}

The mock is part of the historical source, not a new test implementation added to this repository. Initialization readiness, upgrade atomicity, failure handling, and connection cleanup need review in the complete application. No database was opened or migrated for this revision.

← Back to the garden