Creating in-memory databases using SQLite in Dart and Flutter

October 29, 2020

I have been playing around with the Alpha version of Flutter Desktop lately, and needed to use a SQLite database which I may need to update at runtime. This was causing an an issue, as once I loaded the database file into SQLite it was holding a lock on the file (and thus preventing me from overwriting it).

Since I was only reading from the database, and never writing, I wanted to just completely load the database into memory and disconnect it from the file. SQLite does allow you to create an new in-memory database using inMemoryDatapasePath, but I could not find a simple way to load an existing database directly into memory (i’m using the sqflite_common_ffi library).

The workaround that I came up with was to load the database from the file, and then copy the tables I needed into a new database created in memory:

First, create a new in-memory SQLite database:

_db = await databaseFactoryFfi.openDatabase(inMemoryDatabasePath,
      options: OpenDatabaseOptions(singleInstance: false));

Then, we can attach the file based database to the in-memory database, and copy over the tables we need.

await _db.rawQuery("ATTACH DATABASE ? as tmpDb", [dbFile.path]);

String table = "TABLE_NAME";
//copy the table from the database file to in memory data (can copy multiple)
await _db.rawQuery("CREATE TABLE $table AS SELECT * FROM tmpDb.$table");

//we need to detach it or else it will maintain a connection to the db file
//on the file system
await _db.rawQuery("DETACH DATABASE tmpDb");

Note that when we are done, we need to detach the file based database from the in-memory database, or else it may maintain a lock on the file.

Here is the complete example:

Database _db;

Future<void> initialize({@required File dbFile}) async {
  sqfliteFfiInit();

  //close the existing db if it exists
  _closeDb(_db);

  //create a new database in memory using the inMemoryDatabasePath constant
  _db = await databaseFactoryFfi.openDatabase(inMemoryDatabasePath,
      options: OpenDatabaseOptions(singleInstance: false));

  try {
    //attach the databse file to our in memory database
    await _db.rawQuery("ATTACH DATABASE ? as tmpDb", [dbFile.path]);

    String table = "TABLE_NAME";
    //copy the table from the database file to in memory data (can copy multiple)
    await _db.rawQuery("CREATE TABLE $table AS SELECT * FROM tmpDb.$table");

    //we need to detach it or else it will maintain a connection to the db file
    //on the file system
    await _db.rawQuery("DETACH DATABASE tmpDb");

  } catch (e) {
    print("Error creating database");

    //if anything goes wrong, make sure we clean up
    _closeDb(_db);
    _db = null;
    throw (e);
  }
}

Future<void> _closeDb(Database db) async {
  if (db != null) {
    try {
      if (db.isOpen) {
        await db.close();
      }
    } catch (e) {
      print("Could not close database");
    }
  }
}

This works well, and ensures that the database file wont be locked. It also allows me to just copy over the data that I need. If need need to update the database, I can just overwrite the file, and then reinitialize the new database from the updated file.

mike chambers | about

Creating in-memory databases using SQLite in Dart and Flutter