Flutter how to backup and restore sqflite database?

Question

I'm using sqflite for my flutter project and now, i want to backup and then restore it. I searched for this issue but cant find a result. Are there any way to do it? Thanks.

Answer 1

Dependencies

dependencies:
  encrypt: ^4.1.0
  path: ^1.7.0
  sqflite: ^1.3.1

Class

import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'dart:convert' as convert;
import 'package:encrypt/encrypt.dart' as encrypt ;

class DatabaseRepository {
 
  Database _db;

  static const SECRET_KEY = "2021_PRIVATE_KEY_ENCRYPT_2021";
  static const DATABASE_VERSION = 1;

  List<String> tables =[
   
  ];

  Future<Database> get db async 
  {
    if(_db != null)
    {
      return _db;
    } 
    else
    {
      _db = await initDb(DATABASE_VERSION);
      return _db;
    }
  }

  Future<String> _databasePath() async 
  {
    String databasesPath = await getDatabasesPath();
    return join(databasesPath, "database.db");
  }

  Future<Database> initDb(int version) async 
  {
    String path = await _databasePath();
    return await openDatabase(path, version:version, onCreate: onCreate,onUpgrade: onUpgrade);
  }

  Future deleteDB() async 
  {
    String path = await _databasePath();
    await deleteDatabase(path);
  }

  FutureOr onCreate(Database db, int newerVersion) => this._onCreates[newerVersion](db);

  Map<int,Function> _onCreates = {
    1:(Database db) async {

      print("DATABASE CREATE v1");
    },
    2:(Database db) async{

      print("DATABASE CREATE v2");
    },
    3:(Database db) async{

   
      print("DATABASE CREATE v3");
    },
  };

  FutureOr<void> onUpgrade(Database db , int oldVersion, int newVersion ) async 
  {
    for (var migration = oldVersion; migration < newVersion; migration++) 
    {
      this._onUpgrades["from_version_${migration}_to_version_${migration+1}"](db);
    }
  }

  Map<String,Function> _onUpgrades = {
    'from_version_1_to_version_2':(Database db) async {
      
      print('from_version_1_to_version_2');
    },
    'from_version_2_to_version_3':(Database db) async {
      
      print('from_version_2_to_version_3');
    },
  };

  Future clearAllTables() async 
  {
    try
    {
      var dbs = await this.db;
      for (String table  in [
     
      ])
      {
        await dbs.delete(table);
        await dbs.rawQuery("DELETE FROM sqlite_sequence where name='$table'");
      }
      
      print('------ CLEAR ALL TABLE');
    }
    catch(e){}
  }

  Future<String>generateBackup({bool isEncrypted = true}) async {

    print('GENERATE BACKUP');
   
    var dbs = await this.db;

    List data =[];

    List<Map<String,dynamic>> listMaps=[];

    for (var i = 0; i < tables.length; i++)
    {

      listMaps = await dbs.query(tables[i]); 

      data.add(listMaps);

    }

    List backups=[tables,data];

    String json = convert.jsonEncode(backups);

    if(isEncrypted)
    {

      var key = encrypt.Key.fromUtf8(SECRET_KEY);
      var iv = encrypt.IV.fromLength(16);
      var encrypter = encrypt.Encrypter(encrypt.AES(key));
      var encrypted = encrypter.encrypt(json, iv: iv);
        
      return encrypted.base64;  
    }
    else
    {
      return json;
    }
  }

  Future<void>restoreBackup(String backup,{ bool isEncrypted = true}) async {

    var dbs = await this.db;
    
    Batch batch = dbs.batch();
    
    var key = encrypt.Key.fromUtf8(SECRET_KEY);
    var iv = encrypt.IV.fromLength(16);
    var encrypter = encrypt.Encrypter(encrypt.AES(key));

    List json = convert.jsonDecode(isEncrypted ? encrypter.decrypt64(backup,iv:iv):backup);

    for (var i = 0; i < json[0].length; i++)
    {
      for (var k = 0; k < json[1][i].length; k++)
      {
        batch.insert(json[0][i],json[1][i][k]);
      }
    }

    await batch.commit(continueOnError:false,noResult:true);

    print('RESTORE BACKUP');
  }

}
       

Use:

    final DatabaseRepository databaseRepository = new DatabaseRepository();

    String backup =  await databaseRepository.generateBackup(isEncrypted: true);

    await databaseRepository.clearAllTables();

    await databaseRepository.restoreBackup(backup,isEncrypted: true);

Answer 2

For complete details, go to link

ElevatedButton(
      onPressed: () async {
        final dbFolder = await getDatabasesPath();
        File source1 = File('$dbFolder/doggie_database.db');

        Directory copyTo =
            Directory("storage/emulated/0/Sqlite Backup");
        if ((await copyTo.exists())) {
          // print("Path exist");
          var status = await Permission.storage.status;
          if (!status.isGranted) {
            await Permission.storage.request();
          }
        } else {
          print("not exist");
          if (await Permission.storage.request().isGranted) {
            // Either the permission was already granted before or the user just granted it.
            await copyTo.create();
          } else {
            print('Please give permission');
          }
        }

        String newPath = "${copyTo.path}/doggie_database.db";
        await source1.copy(newPath);

        setState(() {
          message = 'Successfully Copied DB';
        });
      },
      child: const Text('Copy DB'),
    ),
    
    ElevatedButton(
      onPressed: () async {
        var databasesPath = await getDatabasesPath();
        var dbPath = join(databasesPath, 'doggie_database.db');

        FilePickerResult? result =
            await FilePicker.platform.pickFiles();

        if (result != null) {
          File source = File(result.files.single.path!);
          await source.copy(dbPath);
          setState(() {
          message = 'Successfully Restored DB';
        });
        } else {
          // User canceled the picker

        }
      },
      child: const Text('Restore DB'),
    ),