How to use a pre-populated sqlite database my App in Flutter

Question

As the question in the title says.

My database file could be pretty large so I don't want to make copies of it unnecessarily and I certainly don't want to build it in situ.

If the db file is a flutter asset, is there a way for sqlite to access it directly?

I've seen suggestions that I should copy the raw data of the asset into a file and then access it but that is a waste of storage. Or can I then delete the asset?

Is there a simple way of deploying the database as something other than an asset, ie as a raw file?

Answer 1

iOS (and, I think, Android?) will require you to copy the file into the app's working directory first. This is standard practice, and it's part of working within a protected file system. If somehow it's a deal killer to have copies in both your app bundle (consider this the pristine "master") and app documents folder (the "working copy"), I suppose you could also download it from a server on initial app launch, but... time is money.

It's truly not that big a deal, though. To do so, ensure the file in included in the app bundle via your pubspec.yaml file:

flutter:
  assets:
    - assets/stored_data.db

Then, before opening the database, copy it from the app bundle to your documents directory:

Edit: The following apparently fails to copy large files; see iKK's comment below if you experience such issues, as it looks like he's found a native solution. For smaller files, however, this should work fine.

// Create a new file within your document directory (Probably want to check whether it already exists first...)

Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, "working_data.db");

ByteData data = await rootBundle.load(join("assets", "stored_data.db"));
List<int> bytes = data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
await new File(path).writeAsBytes(bytes);

Now that you have the database within your app's document directory, you should be able to open it from the path. If you're using the tekartik/sqflite package (which is where I originally got these instructions), for instance, you can simply:

Database db = await openDatabase(path); // Opens SQL database, working_data.db

There's no reason (or ability) to delete the pristine copy from the app bundle (which would alter the original binary -- a huge nono). Eventually, the OS itself may end up offloading such dreck to a cloud server or other memory-management service, but that's for the platform to decide.