Importing and exporting databases
You can use drift with a pre-propulated database that you ship with your app. This page also describes how to export the underlying sqlite3 database used by drift into a file.
Using an existing database
You can use a LazyDatabase
wrapper to run an asynchronous computation before drift opens a database. This is a good place to check if the target database file exists, and, if it doesnt, create one. This example shows how to do that from assets.
Including the database
First, create the sqlite3 database you want to ship with your app. You can create a database with the sqlite3 CLI tool on your development machine. Of course, you can also create the database programmatically by using a library like sqlite3 (or even drift itself).
To ship that database to users, you can include it as a flutter asset. Simply include it in your pubspec:
flutter:
assets:
- assets/my_database.db
Extracting the database
To initialize the database before using drift, you need to extract the asset from your app onto the device. In drift, you can use a LazyDatabase to perform that work just before your drift database is opened:
import 'package:drift/drift.dart';
import 'package:flutter/services.dart' show rootBundle;
import 'package:path/path.dart' as p;
import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite3_flutter_libs/sqlite3_flutter_libs.dart';
LazyDatabase _openConnection() {
return LazyDatabase(() async {
// put the database file, called db.sqlite here, into the documents folder
// for your app.
final dbFolder = await getApplicationDocumentsDirectory();
final file = File(p.join(dbFolder.path, 'app.db'));
if (!await file.exists()) {
// Extract the pre-populated database file from assets
final blob = await rootBundle.load('assets/my_database.db');
final buffer = blob.buffer;
await file.writeAsBytes(buffer.asUint8List(blob.offsetInBytes, blob.lengthInBytes));
}
// Also work around limitations on old Android versions
if (Platform.isAndroid) {
await applyWorkaroundToOpenSqlite3OnOldAndroidVersions();
}
// Make sqlite3 pick a more suitable location for temporary files - the
// one from the system may be inaccessible due to sandboxing.
final cachebase = (await getTemporaryDirectory()).path;
// We can't access /tmp on Android, which sqlite3 would try by default.
// Explicitly tell it about the correct temporary directory.
sqlite3.tempDirectory = cachebase;
return NativeDatabase.createInBackground(file);
});
}
Finally, use that method to open your database:
@DriftDatabase(tables: [Todos, Categories])
class MyDatabase extends _$MyDatabase {
MyDatabase() : super(_openConnection());
// ...
Additional considerations for WAL
When enabling write-ahead logging, sqlite3 won't immediately store all writes in the main database file. Instead, additional -wal
and -shm
files are created to append new writes that are then periodically synced into the main database file.
Restoring a WAL database requires copying the -wal
file as well. When overwriting an existing WAL database on the device with a database using a different journaling mode, the -wal
file should be deleted too.
Exporting a database
To export a sqlite3 database into a file, you can use the VACUUM INTO
statement. Inside your database class, this could look like the following:
Future<void> exportInto(File file) async {
// Make sure the directory of the target file exists
await file.parent.create(recursive: true);
// Override an existing backup, sqlite expects the target file to be empty
if (file.existsSync()) {
file.deleteSync();
}
await customStatement('VACUUM INTO ?', [file.path]);
}
You can now export this file containing the database of your app with another package like flutter_share
or other backup approaches.
To import a database file into your app's database at runtime, you can use the following approach:
- use the
sqlite3
package to open the backup database file. - run the
VACUUM INTO ?
statement on the backup database, targetting the path of your application's database (the one you pass toNativeDatabase
).