Location to place Sqlite DB in Flutter

Question

A Sqlite DB file has pre existing data, and this needs to be added to a Flutter application, ideally in a way which will work for both Android and iOS.

Have placed the file in the following location:

/assets/db/myDB.db

/assets is a folder in the root, like lib.

pubspec.yaml has:

 assets:
    - assets/db/

The code to open and query the database:

 final db = await openDatabase('myDb.db');

 final List<Map<String, dynamic>> maps = await db.rawQuery('SELECT * FROM mytable WHERE module_id = ?', [moduleId]);

An error is being thrown when trying to query the database, although none on opening the database. Even if I change the name of the db to one which does not exist, an error does not appear to be thrown. Tested this by using

await openDatabase('name.db')
          .catchError((e) => print('DB BERROR:' + e));

Nothing is printed.

What is the correct location to place the db file, so that it will be included when deployed on both Android and iOS?

Thanks

Answer 1

The assets is not really a folder in your filesystem. In order to use it with sqlite, you need to copy the asset to a folder, typically the cache folder of your app.

Here is an example (add path_provider to your pubspec.yaml):

import 'dart:io';
import 'package:flutter/services.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

Future<Database> openDatabaseFromAssets() async {
  // Get the temporary directory (cache)
  final directory = await getTemporaryDirectory();
  final path = join(directory.path, 'myDB.db');
  
  // Check if the database file exists
  final exists = await File(path).exists();
  
  if (!exists) {
    // Copy from assets
    ByteData data = await rootBundle.load('assets/db/myDB.db');
    List<int> bytes = data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
    
    // Write and flush the bytes written
    await File(path).writeAsBytes(bytes, flush: true);
  }
  
  // Open the database
  return await openDatabase(path);
}