Using a pre-populated/ external SQLite database in Flutter


Sometimes in our app, instead of creating a new SQLite database from scratch in a new device and initializing database in onCreate method, we want to load a prefilled database in that device. This is very important since it saves time required to load data inside the database in every new device by just loading it once in the database and then just loading this filled database in every new device. So let's learn how this can be achieved in Flutter.

 

1. Prepare the SQLite database file(.db) which you want to load. If you want to export SQLite database from same or some other app see this tutorial.

 

2. Add your sqlite(.db) file in the assets folder of the project directory.

Note : assets directory should be in the same level as lib directory. 


3. If assets folder is not added in pubspec.yaml then first add it in pubspec.yaml


  flutter:
      assets:
        - assets/

 

 Note : Don't forget to do pub get

 

4. Set up Database client(database_helper) class to handle all the sqlite operations as shown below. 

Generally this code is used when creating new SQLite database in app and performing the initialization operations in onCreate method.

Note : If you don't know the proper way to manage sqlite SQLite in app, see this tutorial. 


class DatabaseHelper {
  static final _databaseName = "codingwithart.db";
  static final _databaseVersion = 1;

  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    _database = await _initDatabase(); // only initialize if not created already
    return _database;
  }

  _initDatabase() async {
    String path = join(await getDatabasesPath(), _databaseName);
    return await openDatabase(path, version: _databaseVersion, onCreate : _onCreate);
  }

  Future _onCreate(Database db, int version) async {

  // code for initializing database when created

  }
}

 

5. Now we want to change it and instead of creating new database, we want to use database present in the assets folder.

👉 The code for using pre-populated database from assets folder is given below :

class DatabaseHelper {
  static final _databaseName = "codingwithart.db";
  static final _databaseVersion = 1;

  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  static Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    _database = await _initDatabase(); // only initialize if not created already
    return _database;
  }

  _initDatabase() async {
    String path = join(await getDatabasesPath(), _databaseName);

    if (!(await databaseExists(path))) {
      ByteData data = await rootBundle.load(join("assets", _databaseName));
      List<int> bytes =
          data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
      await new File(path).writeAsBytes(bytes);
    }
    return await openDatabase(path, version: _databaseVersion);
  }
}

 

Let's understand this. We removed the onCreate method as we are not creating a new database now. We modify the _initDatabase method. We check if the database exists or not. If database does not exist, we copy the database from our assets folder into the app database location. And if database already exists we simply just open the database. That's all we need to do. This way you can simply use a pre-populated external database in your flutter app.

I hope this article helped you and if you face any problem do comment below and I would be glad to help.

Thank you for your patience reading. If you enjoyed this post, I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Whatsapp or Facebook. 

😇Happy Learning!!