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!

Answer 1

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