Transactions

Drift has support for transactions and allows multiple statements to run atomically, so that none of their changes is visible to the main database until the transaction is finished. To begin a transaction, call the transaction method on your database or a DAO. It takes a function as an argument that will be run transactionally. In the following example, which deals with deleting a category, we move all todo entries in that category back to the default category:

Future<void> deleteCategory(Category category) {
  return transaction(() async {
    // first, move the affected todo entries back to the default category
    await (update(todoItems)
          ..where((row) => row.category.equals(category.id)))
        .write(const TodoItemsCompanion(category: Value(null)));

    // then, delete the category
    await delete(categories).delete(category);
  });
}

⚠️ Important things to know about transactions

There are a couple of things that should be kept in mind when working with transactions:

1. Await all calls: All queries inside the transaction must be await-ed. The transaction will complete when the inner method completes. Without await, some queries might be operating on the transaction after it has been closed! This can cause data loss or runtime crashes. Drift contains some runtime checks against this misuse and will throw an exception when a transaction is used after being closed.
2. Different behavior of stream queries: Inside a transaction callback, stream queries behave differently. If you're creating streams inside a transaction, check the next section to learn how they behave.

Transactions and query streams

Query streams that have been created outside a transaction work nicely together with updates made in a transaction: All changes to tables will only be reported after the transaction completes. Updates inside a transaction don't have an immediate effect on streams, so your data will always be consistent and there aren't any unnecessary updates.

Streams created inside a transaction block (or in a function that was called inside a transaction) block reflect changes made in a transaction immediately. However, such streams close when the transaction completes.

This behavior is useful if you're collapsing streams inside a transaction, for instance by calling first or fold. However, we recommend that streams created inside a transaction are not listened to outside of a transaction. While it's possible, it defeats the isolation principle of transactions as its state is exposed through the stream.

Nested transactions

Starting from drift version 2.0, it is possible to nest transactions on most implementations. When calling transaction again inside a transaction block (directly or indirectly through method invocations), a nested transaction is created. Nested transactions behave as follows:

• When they start, queries issued in a nested transaction see the state of the database from the outer transaction immediately before the nested transaction was started.
• Writes made by a nested transaction are only visible inside the nested transaction at first. The outer transaction and the top-level database don't see them right away, and their stream queries are not updated.
• When a nested transaction completes successfully, the outer transaction sees the changes made by the nested transaction as an atomic write (stream queries created in the outer transaction are updated once).
• When a nested transaction throws an exception, it is reverted (so in that sense, it behaves just like other transactions). The outer transaction can catch this exception, after it will be in the same state before the nested transaction was started. If it does not catch that exception, it will bubble up and revert that transaction as well.

The following snippet illustrates the behavior of nested transactions:

Future<void> nestedTransactions() async {
  await transaction(() async {
    await into(categories).insert(CategoriesCompanion.insert(name: 'first'));

    // this is a nested transaction:
    await transaction(() async {
      // At this point, the first category is visible
      await into(categories)
          .insert(CategoriesCompanion.insert(name: 'second'));
      // Here, the second category is only visible inside this nested
      // transaction.
    });

    // At this point, the second category is visible here as well.

    try {
      await transaction(() async {
        // At this point, both categories are visible
        await into(categories)
            .insert(CategoriesCompanion.insert(name: 'third'));
        // The third category is only visible here.
        throw Exception('Abort in the second nested transaction');
      });
    } on Exception {
      // We're catching the exception so that this transaction isn't reverted
      // as well.
    }

    // At this point, the third category is NOT visible, but the other two
    // are. The transaction is in the same state as before the second nested
    // `transaction()` call.
  });

  // After the transaction, two categories are visible.
}

Supported implementations

Nested transactions require support by the database implementation you're using with drift. All popular implementations support this feature, including:

• A NativeDatabase from package:drift/native.dart
• A WasmDatabase from package:drift/wasm.dart
• The sql.js-based WebDatabase from package:drift/web.dart
• A SqfliteDatabase from package:drift_sqflite.

Further, nested transactions are supported through remote database connections (e.g. isolates or web workers) if the server uses a database implementation that supports them.