Database represents a raw SQLite connection. You don’t create instances directly; instead, access them through DatabaseQueue or DatabasePool access methods.
Overview
try dbQueue.write { db in
// db is a Database instance
try db.execute(sql: "CREATE TABLE player (id INTEGER PRIMARY KEY, name TEXT)")
}
Properties
The database configuration
The rowID of the most recently inserted row
The number of rows modified by the most recent INSERT, UPDATE, or DELETE
The total number of rows modified since the connection was opened
Whether the database is currently inside a transaction
The date of the current transaction (constant during a transaction)
Executing SQL
execute(sql:arguments:)
Executes an SQL statement.
The SQL statement to execute
arguments
StatementArguments
default:"StatementArguments()"
Optional statement arguments
try db.execute(sql: """
CREATE TABLE player (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
score INTEGER
)
""")
try db.execute(
sql: "INSERT INTO player (name, score) VALUES (?, ?)",
arguments: ["Arthur", 100]
)
execute(literal:)
Executes an SQL literal with interpolated values.
let name = "Arthur"
let score = 100
try db.execute(literal: """
INSERT INTO player (name, score) VALUES (\(name), \(score))
""")
SQL literals provide compile-time SQL syntax validation and automatic argument binding.
Creating Statements
makeStatement(sql:)
Creates a reusable prepared statement.
let statement = try db.makeStatement(sql: """
SELECT * FROM player WHERE score > ?
""")
let players = try Player.fetchAll(statement, arguments: [50])
cachedStatement(sql:)
Returns a cached prepared statement for better performance.
let statement = try db.cachedStatement(sql: """
UPDATE player SET score = ? WHERE id = ?
""")
try statement.execute(arguments: [150, 1])
Cached statements are automatically cleared when the database schema changes.
Transactions
beginTransaction(_:)
Begins a transaction.
kind
Database.TransactionKind
default:".deferred"
The transaction kind: .deferred, .immediate, or .exclusive
try db.beginTransaction(.immediate)
try Player(name: "Arthur").insert(db)
try db.commit()
commit()
Commits the current transaction.
rollback()
Rolls back the current transaction.
inTransaction(::)
Executes a closure in a transaction.
try db.inTransaction { db in
try Player(name: "Arthur").insert(db)
try Player(name: "Barbara").insert(db)
return .commit // or .rollback
}
inSavepoint(_:)
Executes a closure in a savepoint.
try db.inSavepoint {
try Player(name: "Arthur").insert(db)
try Player(name: "Barbara").insert(db)
return .commit
}
Savepoints can be nested and allow partial rollbacks within a transaction.
Database Functions
add(function:)
Adds a custom SQL function.
let uppercase = DatabaseFunction("uppercase", argumentCount: 1) { dbValues in
guard let string = String.fromDatabaseValue(dbValues[0]) else {
return .null
}
return string.uppercased().databaseValue
}
db.add(function: uppercase)
let name = try String.fetchOne(db, sql: "SELECT uppercase('arthur')")!
// name is "ARTHUR"
remove(function:)
Removes a custom SQL function.
db.remove(function: uppercase)
Collations
add(collation:)
Adds a custom collation for text comparison.
let collation = DatabaseCollation("reverse") { (lhs, rhs) in
// Compare strings in reverse
return lhs.reversed().caseInsensitiveCompare(rhs.reversed())
}
db.add(collation: collation)
try db.execute(sql: """
CREATE TABLE item (name TEXT COLLATE reverse)
""")
Database Observation
add(transactionObserver:extent:)
Adds a transaction observer to monitor database changes.
let observer = MyTransactionObserver()
db.add(transactionObserver: observer)
registerAccess(to:)
Registers that a specific database region is accessed during a ValueObservation.
let observation = ValueObservation.tracking { db in
try db.registerAccess(to: Player.all())
try db.registerAccess(to: Team.all())
// Fetch value
}
notifyChanges(in:)
Notifies observers that changes occurred in a database region.
try db.notifyChanges(in: Player.all())
lastErrorCode
The last SQLite error code.
let errorCode: ResultCode = db.lastErrorCode
lastErrorMessage
The last SQLite error message.
if let message = db.lastErrorMessage {
print("Error: \(message)")
}
maximumStatementArgumentCount
The maximum number of arguments an SQLite statement can accept.
let maxArgs = db.maximumStatementArgumentCount // Typically 999
Memory Management
releaseMemory()
Frees as much memory as possible.
clearSchemaCache()
Clears the cached database schema information.
Checkpoints
checkpoint(_:on:)
Performs a WAL checkpoint.
mode
Database.CheckpointMode
default:".passive"
The checkpoint mode: .passive, .full, .restart, or .truncate
let (walFrameCount, checkpointedFrameCount) = try db.checkpoint(.full)
print("Checkpointed \(checkpointedFrameCount) of \(walFrameCount) frames")
Tracing
trace(options:_:)
Traces SQL statements and other database events.
var config = Configuration()
config.prepareDatabase { db in
db.trace(options: .statement) { event in
if case let .statement(statement) = event {
print("SQL: \(statement.sql)")
}
}
}
Read-Only Mode
readOnly(_:)
Executes operations in read-only mode.
try db.readOnly {
let players = try Player.fetchAll(db) // OK
try Player(name: "Arthur").insert(db) // Throws SQLITE_READONLY
}
Static Properties
Global error logging function
SQLite library version number
// Set up global error logging
Database.logError = { resultCode, message in
NSLog("SQLite error %@: %@", "\(resultCode)", message)
}
print(Database.sqliteLibVersionNumber) // e.g., 3048000
Notifications
Notification posted to suspend database operations
Notification posted to resume database operations
See Also
