Sqflite in flutter, working with two databases (attach)

Question

I have a litte problem with the handling of two databases. I am using flutter with dart and the sqlite package sqflite.

Description of the problem: I have two databases in my asset (assets/masterdata.db and assets/usr.db). The masterdata.db has new data from time to time and in the usr.db I am storing the user data. I want to copy usr.db once and masterdata.db everytime at least with an update of the application.

Question: Is there a way to use "attach database" for the two databases and how can I do that?

Question 2: Or is there another way to save the data of the tables inside the usr.db while updating the masterdata? I want to avoid writing 100 of insert statements for new masterdata in the onUpdate event. And also using csv files for new data sounds not really smart.

Thank you in advance for any hint.

Answer 1

In your masterdata database helper you can do something like this to attach the database

    Future<Database> attachDb({Database db, String databaseName, String databaseAlias}) async {
      Directory documentDirectory = await getApplicationDocumentsDirectory();
      String absoluteEndPath = join(documentDirectory.path, databaseName);
      await db.rawQuery("ATTACH DATABASE '$absoluteEndPath' as '$databaseAlias'");
      return db;
    }

So when you initialize your database you can run

    attachDb(
        db:db,
        databaseName:"usr.db",
        databaseAlias:"USER_DATABASE",
    );

Then you can reference the database in your queries like this

    db.rawQuery("SELECT * FROM USER_DATABASE.table_name");