Transactions

Run multiple queries atomically with automatic commit and rollback

Transactions#

Knex Dart supports database transactions through the trx() method on every driver. All queries inside the callback are wrapped in a single atomic unit — automatically committed on success and rolled back on any error.

Basic Usage#

await db.trx((trx) async {
  await trx.insert(
    trx('accounts').insert({'owner': 'Alice', 'balance': 1000}),
  );
  await trx.update(
    trx('ledger').insert({'action': 'deposit', 'amount': 1000}),
  );
  // Both succeed → automatic COMMIT
});

If any statement inside throws, both are rolled back automatically — nothing is partially written.

Error Handling and Rollback#

try {
  await db.trx((trx) async {
    await trx.update(
      trx('accounts')
        .where('id', '=', fromId)
        .update({'balance': db.raw('balance - ?', [amount])}),
    );

    // This throws → triggers automatic ROLLBACK
    await trx.update(
      trx('accounts')
        .where('id', '=', toId)
        .update({'balance': db.raw('balance + ?', [amount])}),
    );
  });
} catch (e) {
  print('Transfer failed, changes rolled back: $e');
}

Reading Inside a Transaction#

All queries — reads and writes — must go through trx, not the outer db, to execute inside the transaction:

await db.trx((trx) async {
  // READ inside transaction (sees uncommitted writes above)
  final balance = await trx.select(
    trx('accounts').select(['balance']).where('id', '=', accountId).limit(1),
  );

  if (balance[0]['balance'] < amount) {
    throw Exception('Insufficient funds');  // triggers ROLLBACK
  }

  await trx.update(
    trx('accounts')
      .where('id', '=', accountId)
      .update({'balance': db.raw('balance - ?', [amount])}),
  );
});

Returning Values from a Transaction#

trx() returns whatever your callback returns:

final newId = await db.trx((trx) async {
  final rows = await trx.select(
    trx('users')
      .insert({'name': 'Alice', 'email': 'alice@example.com'})
      .returning(['id']),
  );
  await trx.insert(
    trx('audit_log').insert({'user_id': rows[0]['id'], 'action': 'signup'}),
  );
  return rows[0]['id'];
});

print('Created user $newId');

Driver-Specific Details#

PostgreSQL#

Uses the postgres package's native runTx internally — full PostgreSQL transaction semantics including isolation levels.

import 'package:knex_dart_postgres/knex_dart_postgres.dart';

final db = await KnexPostgres.connect(...);
await db.trx((trx) async { ... });

MySQL#

Uses native MySQL transaction with START TRANSACTION / COMMIT / ROLLBACK.

import 'package:knex_dart_mysql/knex_dart_mysql.dart';

final db = await KnexMySQL.connect(...);
await db.trx((trx) async { ... });

SQLite#

Uses BEGIN / COMMIT / ROLLBACK statements directly on the synchronous SQLite connection.

import 'package:knex_dart_sqlite/knex_dart_sqlite.dart';

final db = await KnexSQLite.connect(filename: ':memory:');
await db.trx((trx) async { ... });

Real-World Example: Bank Transfer#

Future<void> transfer({
  required int fromAccountId,
  required int toAccountId,
  required double amount,
}) async {
  await db.trx((trx) async {
    // Lock both rows and read balances
    final accounts = await trx.select(
      trx('accounts')
        .whereIn('id', [fromAccountId, toAccountId])
        .select(['id', 'balance']),
    );

    final from = accounts.firstWhere((r) => r['id'] == fromAccountId);
    final to = accounts.firstWhere((r) => r['id'] == toAccountId);

    if ((from['balance'] as num) < amount) {
      throw Exception('Insufficient balance');
    }

    // Debit
    await trx.update(
      trx('accounts')
        .where('id', '=', fromAccountId)
        .update({'balance': db.raw('balance - ?', [amount])}),
    );

    // Credit
    await trx.update(
      trx('accounts')
        .where('id', '=', toAccountId)
        .update({'balance': db.raw('balance + ?', [amount])}),
    );

    // Audit
    await trx.insert(
      trx('transfers').insert({
        'from_account_id': fromAccountId,
        'to_account_id': toAccountId,
        'amount': amount,
        'created_at': DateTime.now().toIso8601String(),
      }),
    );
  });
}

Nested Transactions (Savepoints)#

Calling trx() inside an already-open transaction creates a savepoint instead of a new BEGIN. This allows partial rollback without rolling back the entire outer transaction.

await db.trx((outer) async {
  // Outer insert
  await outer.insert(
    outer('accounts').insert({'owner': 'Alice', 'balance': 1000}),
  );

  try {
    await outer.trx((inner) async {
      await inner.insert(
        inner('accounts').insert({'owner': 'Bob', 'balance': 500}),
      );
      throw Exception('something went wrong');  // inner rolls back to savepoint
    });
  } catch (_) {
    // inner rolled back — outer is still open
  }

  // Alice's row is still there; Bob's row was rolled back
  await outer.insert(
    outer('audit_log').insert({'action': 'partial_rollback_demo'}),
  );
  // COMMIT — only Alice's row and the audit log entry are written
});

If the inner exception is not caught, it propagates to the outer callback and rolls back everything:

// Everything rolled back — both Alice and Bob rows are gone
await db.trx((outer) async {
  await outer.insert(outer('accounts').insert({'owner': 'Alice', 'balance': 1000}));
  await outer.trx((inner) async {
    await inner.insert(inner('accounts').insert({'owner': 'Bob', 'balance': 500}));
    throw Exception('bubble up');  // not caught → outer also rolls back
  });
});

Savepoints are supported on PostgreSQL, MySQL, SQLite, and DuckDB. The savepoint name is generated automatically (sp_ + microseconds in base-36).

Notes#

Transactions are connection-pinned: each outer trx() acquires one pooled connection for its full scope.
Nesting depth is unlimited — each nested call adds one more savepoint level.

Next Steps#

Write Operations — INSERT, UPDATE, DELETE
Schema Builder — Creating tables inside transactions
Examples — More real-world patterns