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:

1. use the sqlite3 package to open the backup database file.
2. run the VACUUM INTO ? statement on the backup database, targetting the path of your application's database (the one you pass to NativeDatabase).