Database Backup and Attach Operations

SQLite.swift provides helpers for advanced database management tasks like online backups and attaching multiple databases to a single connection.

Online Database Backup

You can perform a live backup of a database, even while it's being accessed, using the SQLite Online Backup API. This is useful for creating snapshots without taking your database offline.

SQLite.swift wraps this API in a Backup object.

// The source database connection
let sourceDB = try Connection("path/to/source.sqlite")

// The destination database connection (can be in-memory or on disk)
let destinationDB = try Connection(.inMemory)

// Initialize the backup process
let backup = try sourceDB.backup(usingConnection: destinationDB)

// Execute the backup step-by-step or all at once
try backup.step() // Use Pages.all to copy all at once (default)

// To copy in chunks (e.g., to avoid blocking for too long):
try backup.step(pagesToCopy: .limited(number: 100))

The backup process can be performed in steps to avoid locking the database for extended periods. You can check the pageCount and remainingPages properties on the Backup object to monitor progress.

Attaching and Detaching Databases

SQLite allows you to attach another database file to the current database connection, making its tables available for querying. This is powerful for running cross-database queries.

Attaching a Database

Use the attach() method on a Connection. You must provide a schema name to refer to the attached database.

// Main database connection
let db = try Connection("main.sqlite")

// Attach an external database with the schema name "external"
try db.attach(.uri("external.sqlite"), as: "external")
// SQL: ATTACH DATABASE 'external.sqlite' AS 'external'

// Now you can query tables from the attached database
let externalUsers = Table("users", database: "external")
let count = try db.scalar(externalUsers.count)
// SQL: SELECT count(*) FROM "external"."users"

Attaching an Encrypted Database (SQLCipher)

If you are using the SQLCipher build of SQLite.swift, you can provide a key when attaching an encrypted database:

try db.attach(.uri("encrypted.sqlite"), as: "secrets", key: "a-secret-password")
// SQL: ATTACH DATABASE 'encrypted.sqlite' AS 'secrets' KEY 'a-secret-password'

Detaching a Database

To detach a database, use the detach() method with the schema name you used to attach it.

try db.detach("external")
// SQL: DETACH DATABASE 'external'