sqflite: opening and migrating a database

package:sqflite is the Flutter plugin for SQLite on Android, iOS and macOS. A database is a file identified by a path; a relative path is resolved against getDatabasesPath(). openDatabase runs a small version-based migration mechanism (onCreate / onUpgrade / onDowngrade) inside a transaction and returns a Database that you keep open for the life of the app.

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

Future<Database> openAppDatabase() async {
  final path = join(await getDatabasesPath(), 'app.db');
  return openDatabase(
    path,
    version: 1,
    onConfigure: (db) => db.execute('PRAGMA foreign_keys = ON'),
    onCreate: (db, version) async {
      await db.execute(
        'CREATE TABLE Todo (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, done INTEGER NOT NULL)',
      );
    },
  );
}

Guidelines

Imports and platforms

• import 'package:sqflite/sqflite.dart'; gives the global functions (openDatabase, openReadOnlyDatabase, getDatabasesPath, deleteDatabase, databaseExists), the global databaseFactory, the Sqflite helper class and every type of package:sqflite/sqlite_api.dart (Database, Transaction, Batch, DatabaseFactory, OpenDatabaseOptions, ConflictAlgorithm, DatabaseException, inMemoryDatabasePath, ...).
• sqflite only works on Android, iOS and macOS. For Linux, Windows, the Dart VM and unit tests use package:sqflite_common_ffi; for the web use package:sqflite_common_ffi_web; both plug in through databaseFactory = ... and the code below stays unchanged. Code that must not depend on Flutter should import package:sqflite_common/sqlite_api.dart and receive a DatabaseFactory.
• Build paths with join from package:path, never with string concatenation. getDatabasesPath() is data/data/<package>/databases on Android and the Documents directory on iOS/macOS; on iOS the Library directory from path_provider (getLibraryDirectory()) is the recommended location instead.
• The plugin creates the parent directory of a read-write database on open. When you write the file yourself (asset copy) create the directory first with Directory(dirname(path)).create(recursive: true).

Versioning callbacks

• Pass version (an int > 0) to enable migrations. Callbacks run in this order: onConfigure, then exactly one of onCreate / onUpgrade / onDowngrade, then onOpen. Without version only onConfigure and onOpen run.
• onCreate(db, version) runs when the file does not exist. onUpgrade(db, oldVersion, newVersion) runs when the stored version is lower than version (and also instead of onCreate, with oldVersion == 0, when no onCreate is given). onDowngrade runs when the stored version is higher; pass onDatabaseDowngradeDelete to delete and recreate the database in that case, or onDatabaseVersionChangeError to fail.
• onCreate, onUpgrade and onDowngrade already run inside a transaction: use the db they receive directly (or db.batch() + commit()), never call db.transaction() inside them. The version is stored (PRAGMA user_version) when the callback completes without throwing.
• Write migrations as a chain: if (oldVersion < 2) {...} if (oldVersion < 3) {...} so any old version reaches the newest schema. Put schema statements in a Batch, one statement per execute (multi-statement strings separated by ; are not supported).
• onConfigure is the place for PRAGMA foreign_keys = ON, db.setJournalMode('WAL') (extension SqfliteDatabaseExt, handles the Android quirk where execute fails), PRAGMA auto_vacuum (only when await db.getVersion() == 0, i.e. a new file) and, on Android, db.androidSetLocale('fr-FR') (extension SqfliteDatabaseAndroidExt). They must be re-applied at every open, which is why they belong there.
• db.getVersion() / db.setVersion() exist (extension SqfliteDatabaseExecutorExt) but do not drive migrations with them; use version and the callbacks.
• openDatabase(path, options: OpenDatabaseOptions(...)) is equivalent to the named parameters; when options is given all other parameters are ignored. DatabaseFactory.openDatabase only takes options.

Instances, closing, deleting

• Open the database once and keep the Database; store the Future<Database> (not the Database) in a field so concurrent callers share the same open call. Many apps never close it.
• singleInstance: true (default) returns the same Database for the same path; a second openDatabase on that path returns the existing instance and ignores its callbacks. It is forced to false for inMemoryDatabasePath (':memory:'). Opening the same file twice with singleInstance: false causes "database is locked" errors on Android.
• rollbackActiveTransactionOnOpen (OpenDatabaseOptions, default: true in debug, false in release) rolls back a transaction left open by a previous isolate/hot restart when singleInstance is true. Keep the default unless you deliberately use several isolates.
• readOnly: true (or openReadOnlyDatabase(path)) ignores every callback, never starts a transaction and fails on the first write. Use it for shipped asset databases.
• Delete with deleteDatabase(path), never with File(path).delete(): it closes the open instance, handles the hot-restart state and removes the -wal, -shm and -journal side files.
• databaseExists(path) checks the file; db.isOpen tells if close() was called; db.path is the resolved absolute path.
• databaseFactory.readDatabaseBytes(path) / writeDatabaseBytes(path, bytes) copy a whole database file (backup, restore, asset import) in a way that also works with the ffi and web factories.
• factory.sandbox(path: root) (extension SqfliteDatabaseFactorySandboxExtension) returns a DatabaseFactory whose relative paths live under root and whose absolute paths must stay inside it. Use it to isolate tests or per-user data.

Isolates and hot restart

• Use the database from the main isolate: native calls already run on a background thread and the transaction lock is not cross-isolate. If a background isolate (push notification, work manager) must read the database, open it there with singleInstance: false and do not close it.
• After changing the schema during development restart the app; a hot reload keeps the native connection open with the old schema.

Examples

Migration chain with batches and a downgrade policy

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

const _version = 2;

void _createV1(Batch batch) {
  batch.execute('DROP TABLE IF EXISTS Company');
  batch.execute(
    'CREATE TABLE Company (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)',
  );
}

void _upgradeV1ToV2(Batch batch) {
  batch.execute('ALTER TABLE Company ADD description TEXT');
  batch.execute('''CREATE TABLE Employee (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    companyId INTEGER,
    FOREIGN KEY (companyId) REFERENCES Company(id) ON DELETE CASCADE)''');
}

Future<Database> openCompanyDb() async {
  final path = join(await getDatabasesPath(), 'company.db');
  return openDatabase(
    path,
    version: _version,
    onConfigure: (db) async {
      await db.execute('PRAGMA foreign_keys = ON');
    },
    onCreate: (db, version) async {
      // Fresh install: build the latest schema through the same steps.
      final batch = db.batch();
      _createV1(batch);
      _upgradeV1ToV2(batch);
      await batch.commit();
    },
    onUpgrade: (db, oldVersion, newVersion) async {
      final batch = db.batch();
      if (oldVersion < 2) {
        _upgradeV1ToV2(batch);
      }
      await batch.commit();
    },
    onDowngrade: onDatabaseDowngradeDelete,
  );
}

One shared instance for the whole app

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class AppDatabase {
  Future<Database>? _db;

  /// Safe to call concurrently: the first call starts the open, others await it.
  Future<Database> get database => _db ??= _open();

  Future<Database> _open() async {
    final path = join(await getDatabasesPath(), 'app.db');
    return openDatabase(
      path,
      version: 1,
      onConfigure: (db) => db.setJournalMode('WAL'),
      onCreate: (db, _) => db.execute(
        'CREATE TABLE Note (id INTEGER PRIMARY KEY, content TEXT)',
      ),
    );
  }

  Future<void> close() async {
    final db = await _db;
    _db = null;
    await db?.close();
  }
}

Copy a bundled asset database on first launch

import 'dart:io';

import 'package:flutter/services.dart' show rootBundle;
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

Future<Database> openAssetDatabase() async {
  final path = join(await getDatabasesPath(), 'catalog.db');
  if (!await databaseExists(path)) {
    await Directory(dirname(path)).create(recursive: true);
    final data = await rootBundle.load(url.join('assets', 'catalog.db'));
    await databaseFactory.writeDatabaseBytes(
      path,
      data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes),
    );
  }
  // Shipped data: open read-only, no callbacks run.
  return openReadOnlyDatabase(path);
}

Delete and recreate, in-memory database

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

Future<void> resetDatabase() async {
  final path = join(await getDatabasesPath(), 'app.db');
  await deleteDatabase(path); // also closes the open instance
}

Future<Database> openScratchDb() =>
    openDatabase(inMemoryDatabasePath); // singleInstance is forced to false

Opening through an explicit factory

import 'package:sqflite/sqflite.dart';

/// Works with databaseFactory (sqflite), databaseFactoryFfi, databaseFactoryFfiWeb...
Future<Database> openWith(DatabaseFactory factory, String path) {
  return factory.openDatabase(
    path,
    options: OpenDatabaseOptions(
      version: 1,
      onCreate: (db, version) =>
          db.execute('CREATE TABLE Test (id INTEGER PRIMARY KEY, value TEXT)'),
    ),
  );
}

Common mistakes

• Calling db.transaction() inside onCreate / onUpgrade: they are already in a transaction, use db directly or a batch.
• Creating two tables in one execute string. One statement per call.
• Forgetting to bump version after changing onCreate; existing installs never see the new schema. Handle it in onUpgrade too.
• Deleting the file with dart:io instead of deleteDatabase, then wondering why onCreate does not run after a hot restart.
• Opening the database in every widget/repository call with singleInstance: false, leading to database is locked (code 5).
• Putting PRAGMA foreign_keys / setJournalMode in onCreate: they are per connection and must go in onConfigure.
• Calling openDatabase on Linux/Windows/web without first setting databaseFactory from sqflite_common_ffi / sqflite_common_ffi_web (StateError: databaseFactory not initialized).

More

Queries, transactions and batches: see the sqflite-crud-and-transactions skill. Unit tests, desktop/web factories and logging: see the sqflite-testing-and-platforms skill.