Transactions & Savepoints

Transactions are a fundamental feature of any relational database, allowing you to group a series of SQL statements into a single atomic operation. This ensures data integrity by guaranteeing that either all statements in the group complete successfully, or none of them do.

Using Transactions

SQLite.swift provides a simple transaction method on the Connection object that takes a closure. All statements executed within the closure are part of the transaction.

• If the closure completes without throwing an error, the transaction is committed.
• If the closure throws an error, the transaction is rolled back, and all changes made within it are undone.

let alice = users.filter(id == 1)
let betty = users.filter(id == 2)
let balance = Expression<Double>("balance")
let amount = 100.0

try db.transaction {
    // Withdraw from Alice's account
    try db.run(alice.update(balance -= amount))

    // Deposit into Betty's account
    try db.run(betty.update(balance += amount))
}
// SQL Executed:
// BEGIN DEFERRED TRANSACTION
// UPDATE "users" SET "balance" = "balance" - 100.0 WHERE ("id" = 1)
// UPDATE "users" SET "balance" = "balance" + 100.0 WHERE ("id" = 2)
// COMMIT TRANSACTION

If either of the update statements were to fail (e.g., if a CHECK constraint was violated), an error would be thrown, and a ROLLBACK TRANSACTION would be executed automatically.

Transaction Modes

You can specify a TransactionMode to control the locking behavior of the transaction:

• .deferred (Default): Locks are not acquired until the first read or write operation.
• .immediate: A reserved lock is acquired on the database immediately.
• .exclusive: An exclusive lock is acquired on all databases immediately.

try db.transaction(.immediate) {
    // ...
}

Using Savepoints for Nested Transactions

SQLite does not support nesting BEGIN...COMMIT transactions. To achieve a similar effect, you can use savepoints.

Savepoints allow you to roll back to a specific point within a larger transaction without rolling back the entire transaction.

try db.transaction {
    // Perform some initial updates
    try db.run(users.insert(email <- "betty@icloud.com"))

    do {
        try db.savepoint {
            // This nested block can fail independently
            try db.run(users.insert(email <- "cathy@icloud.com")) // valid
            try db.run(users.insert(email <- "cathy@icloud.com")) // constraint violation, will throw
        }
    } catch {
        // The savepoint is rolled back, but the outer transaction continues.
        print("Inner operation failed, but we can continue.")
    }

    // The insert of "betty@icloud.com" will still be committed.
}

In this example, Cathy's insert is rolled back, but Betty's insert, which is part of the outer transaction, is still committed.