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.

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);
  });
}

• Using drift_flutter
• Using package:drift/native.dart

import 'dart:io';

import 'package:drift/drift.dart';

import 'package:drift_flutter/drift_flutter.dart';
import 'package:flutter/services.dart' show rootBundle;
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';

QueryExecutor openConnection() {
  return driftDatabase(
    // By default, drift creates a file named app.sqlite based on the name here.
    // Because we need to interact with the underlying file, we add the
    // `databasePath` option to specify the exact path here.
    name: 'app',
    native: DriftNativeOptions(
      databasePath: () 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),
          );
        }

        return file.path;
      },
    ),
  );
}

import 'dart:io';

import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:flutter/services.dart' show rootBundle;
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite3_flutter_libs/sqlite3_flutter_libs.dart';

QueryExecutor 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, targeting the path of your application's database (the one you pass to NativeDatabase).

The example app in the drift repository also has an example for a working backup/restore functionality.