Best way to keep SQFlite data after app is uninstalled/reinstalled on Android?

Question

I have a problem with SQFlite that the database is deleted after the app is uninstalled/reinstalled. My client will have thousands of logs stored in the database and I can't risk the data being lost.

Some questions and ideas I have:

Can I create a SQFLite database file in the external file system and query directly from a db file there?

Or is my only option to constantly write a copy of the db file as a backup to the external file system? Will this be a problem if the file reaches a size of 2-5mb? Will that write be slow or could cause crashes?

Should I just skip using SQFlite altogether and use a remote SQL Server database instead for my purpose?

I find it very annoying that there doesn't seem to be a good option to backup SQFLite. Many apps need to keep the data I'm sure.

Thanks!

Solution

You can keep the data between installations of the app by storing the SQFLite database in external storage, since this is not deleted when uninstalling the app. I have tested it myself and it works.

Here is an example of how you can set up that database in external memory:

import 'dart:io';
import 'package:SQFLite_test/helpers/ClientMocker.dart';
import 'package:SQFLite_test/models/ClientModel.dart';
import 'package:ext_storage/ext_storage.dart';
import 'package:flutter/cupertino.dart';
import 'package:permission_handler/permission_handler.dart';
import 'package:sqflite/sqflite.dart';

class LogServiceTwo {
  LogServiceTwo._();

  static final LogServiceTwo logRepo = LogServiceTwo._();

  static Database _database;

  Future<Database> get database async {
    if (_database != null) {
      return _database;
    }

    await askForWritePermission();
    var db = await openDb();
    if (db == null) {
      _database = await initDB();
      return _database;
    }

    var hasClientTableB = await hasClientTable(db);
    if (hasClientTableB) {
      _database = db;
      return _database;
    }

    await createClientTable(db);
    _database = db;
    return _database;
  }

  Future createClientTable(Database db) async {
    await db.execute("CREATE TABLE Client ("
        "id INTEGER PRIMARY KEY,"
        "first_name TEXT,"
        "last_name TEXT,"
        "blocked BIT"
        ")");
  }

  Future<bool> hasClientTable(Database db) async {
    try {
      var table = await db.query("Client");
      return table != null;
    } catch (e) {
      return false;
    }
  }

  Future<Database> openDb() async {
    try {
      var path = await getPersistentDbPath();
      var db = await openDatabase(path, version: 1);
      return db;
    } catch (e) {
      return null;
    }
  }

  Future initDB() async {
    var path = await getPersistentDbPath();
    return await openDatabase(path, version: 1, onOpen: (db) {}, onCreate: (Database db, int version) async {
      await db.execute("CREATE TABLE Client ("
          "id INTEGER PRIMARY KEY,"
          "first_name TEXT,"
          "last_name TEXT,"
          "blocked BIT"
          ")");
    });
  }

  Future newClient(Client newClient) async {
    final db = await database;
    var res = await db.insert("Client", newClient.toMap());
    return res;
  }

  Future newClients(List<Client> clients) async {
    var clientMaps = clients.map((client) => client.toMap()).toList();
    final db = await database;
    var batch = db.batch();
    clientMaps.forEach((clientMap) async {
      batch.insert("Client", clientMap);
    });
    await batch.commit(noResult: true);
  }

  Future<Client> getClient(int id) async {
    final db = await database;
    var res = await db.query("Client", where: "id = ?", whereArgs: [id]);
    return res.isNotEmpty ? Client.fromMap(res.first) : Null;
  }

  Future<List<Client>> getAllClients() async {
    final db = await database;
    var res = await db.query("Client");
    List<Client> list = res.isNotEmpty ? res.map((c) => Client.fromMap(c)).toList() : [];
    return list;
  }

  Future<List<Client>> getBlockedClients() async {
    final db = await logRepo.database;
    var res = await db.rawQuery("SELECT * FROM Client WHERE blocked=1");
    List<Client> list = res.isNotEmpty ? res.toList().map((c) => Client.fromMap(c)) : null;
    return list;
  }

  Future<List<String>> getTables() async {
    var db = await logRepo.database;
    var tableNames = (await db.query('sqlite_master', where: 'type = ?', whereArgs: ['table'])).map((row) => row['name'] as String).toList(growable: false);
    return tableNames;
  }

  Future<String> getPersistentDbPath() async {
    return await createPersistentDbDirecotry();
  }

  Future<String> createPersistentDbDirecotry() async {
    var externalDirectoryPath = await ExtStorage.getExternalStorageDirectory();
    var persistentDirectory = "$externalDirectoryPath/db_persistent";
    var directory = await createDirectory(persistentDirectory);
    listFiles(directory);
    return "$persistentDirectory/persistent.db";
  }

  listFiles(Directory directory) {
    print("${directory.path} files:");
    directory.list().forEach((file) {
      print(file.path);
      print(file.statSync().size);
    });
  }

  Future<Directory> createDirectory(String path) async {
    return await (new Directory(path).create());
  }

  Future<bool> askForWritePermission() async {
    var status = await Permission.storage.status;
    if (!status.isGranted) {
      status = await Permission.storage.request();
      return status.isGranted;
    }
    return status.isGranted;
  }

  Future mockData() async {
    var clients = ClientMocker.createClients();
    await newClients(clients);
  }

  Future deleteAll() async {
    var db = await database;
    await db.rawDelete("DELETE FROM Client");
  }

  Future getClients() async {
    try {
      var db = await database;
      return await db.rawQuery("SELECT * FROM Client");
    } catch (e) {
      print(e);
    }
  }
}