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 can't find a result. Is there any way to do it?

Prithee

back it up to where? there is a physical sqlite db file created after you "open" it in sqflite you can upload to your server and then restore from that. the path is 0/data/[app_packagename]/[appname]/db.db – Nino

Answer 1

You can use this solution, it performs the backup of your bank with encryption or not, it's up to you, in addition to migrating from one version of the bank to another when, for example, the user is updating the application and the bank is a old version. I hope I helped, sorry for the writing because I don't speak English.

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

Algology

Please provide an explanation what your solution does and why it answers the question. Also strip down the solution to its essense (does the DB have to be encrypted?) – Inhalator

@Inhalator the code is self-explanatory. It does perform a backup with upload mechanism. Don't be picky, this is a good answer. – Antoninus

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'),
    ),

Advertence

Answer 3

dependencies:

 sqflite: ^2.0.0+3
 path_provider: ^2.0.11
 permission_handler: ^10.0.0

Export ( back up)

To export SQFLite database , I came across some errors , some of the error are

1. FileSystemException: Cannot open file, path
2. error: permission denied, errno = 13
3. etc........

I want to export my Database into Download folder that is ,

this is my Database path /data/user/0/com.example.reminder_app/databases/notes.db , it's a application directory path so my aim is to export notes.db file into this path /storage/emulated/0/Download/

1. Expanding dBToCopy functions , this function will give path of Database

  Future<File> dBToCopy() async {
    final db = await instance.database;
    final dbPath = await getDatabasesPath();
    var afile = File(dbPath);
    return afile;
  }

2. full code bellow

dbExportToDownloadFolder() async {

 File result = await NotesDatabase.instance.dBToCopy();
 print("lllllllllllllllllll ${result.absolute.path}"); 
        
 Directory documentsDirectory = 
           Directory("storage/emulated/0/Download/");

 String newPath = join(documentsDirectory.absolute.path + 'abcde.db');

 File b = 
      File("/data/user/0/com.example.reminder_app/databases/notes.db");
                         
 if ( await Permission.storage.request().isGranted &&
      await Permission.accessMediaLocation.request().isGranted &&
      await Permission.manageExternalStorage.request().isGranted )
       {
         File a = await b.copy(newPath);
        } else {
             print("No Permission Granted");
               }
}
                 

Note

File result = await NotesDatabase.instance.dBToCopy();

print("lllllllllllllllllll ${result.absolute.path}"); 

OutPut print

lllllllllllllllllll /data/user/0/com.example.reminder_app/databases

this result file not contain the notes.db file , only contain this path /data/user/0/com.example.reminder_app/databases

To get the DatabaseFile

File b = File("/data/user/0/com.example.reminder_app/databases/notes.db");

                or 

File b = File("${result.path}"+"/notes.db");

so using the file b we can copy the file to Download folder file that is abcde.db

To do that create a file in Download , that is abcde.db

 Directory documentsDirectory = Directory("storage/emulated/0/Download/");

 String newPath = join(documentsDirectory.absolute.path + 'abcde.db');

and using the copy method , to copy one file to another file

 File a = await b.copy(newPath);

Note

If you are getting permission denied errors and OS errors please add all permission in manifest , and using permission_handler allow all permissions

<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

<uses-permission android:name="android.permission.MANAGE_EXTERNAL_STORAGE" />

<uses-permission android:name="android.permission.ACCESS_MEDIA_LOCATION"/>

after the copying , A new file created in Download folder that is abcde.db

Import (restore)

To import abcde.db file from Download to databases folder file that is notes.db

importDataBaseFile () async {
  String recoveryPath =             
         "/data/user/0/com.example.noteapp/databases/notedb.db";
  Directory backupPath = Directory("storage/emulated/0/Download/");
  String newPath = join("${backupPath.absolute.path}" + 'abcde.db');
  File backupFile = File(newPath);
  backupFile.copy(recoveryPath);
  NotesDatabase.instance.readAllNotes();
      },

Expanding function readAllNotes ()

Future<List<Note>> readAllNotes() async {
    final db = await instance.database;

    final orderBy = '${NoteFields.time} ASC'; 

    final result = await db.query(tableNotes, orderBy: orderBy);

    return result.map((json) => Note.fromJson(json)).toList();
  }

so importing operation is performs reversing the export operation . use a FilePicker to pick Database file instead

Import from file picker ( restore from file picker )

dependencies:

 sqflite: ^2.0.0+3
 path_provider: ^2.0.11
 permission_handler: ^10.0.0
 file_picker: ^5.0.1

full code :

importDataBaseFile () async {
 bool? clear = await FilePicker.platform.clearTemporaryFiles();
 print(clear);
 FilePickerResult? result =
        await FilePicker.platform.pickFiles();
 String recoveryPath =
        "/data/user/0/com.example.reminder_app/databases/notes.db";
 String newPath = "${result!.files.single.path}";
 File backupFile = File(newPath);
 backupFile.copy(recoveryPath);
 refreshNotes();
  }

Note

this is the demo example so you can add encryptions and choose a different path to store the backup according to you

Pixilated

Answer 4

You can use this Guide to make your restore. https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_asset_db.md

With this, you can download from somewhere your .db and change with the phone version.

To backup, you can change some lines in exemple above.

Mellicent