Executing Raw SQL

While SQLite.swift's type-safe expression builder is recommended for most operations, there are times when you may need to execute raw SQL statements. The library provides several simple and safe methods for this on the Connection object.

execute() for Batch Operations

Use execute() to run a string containing one or more SQL statements separated by semicolons. This is useful for initial database setup or complex multi-step migrations.

try db.execute("""
    BEGIN TRANSACTION;
    CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
    PRAGMA user_version = 1;
    COMMIT TRANSACTION;
""")

prepare() for Reusable Statements

prepare() compiles a SQL statement and returns a Statement object. This is efficient for statements that you need to run multiple times with different parameters.

let stmt = try db.prepare("INSERT INTO users (email) VALUES (?)")

// Run the statement with different bindings
try stmt.run("alice@example.com")
try stmt.run("betty@example.com")

To retrieve results, you can iterate over the prepared statement:

let query = try db.prepare("SELECT id, email FROM users")
for row in query {
    let id = row[0] as? Int64
    let email = row[1] as? String
    print("id: \(id), email: \(email)")
}

"Resetting Statements": Long-lived prepared statements should be manually reset using statement.reset() after each use to release locks and conclude implicit transactions. Statements are automatically reset when they are deinitialized.

run() for Single Statements

run() is a convenience method that prepares and executes a single SQL statement. It's ideal for one-off operations.

// Without bindings
try db.run("UPDATE users SET admin = 1 WHERE id = 1")

// With bindings
try db.run("INSERT INTO users (email) VALUES (?)", "carol@example.com")

scalar() for Single Values

scalar() executes a query and returns the first value from the first row of the result set. It's perfect for aggregate queries.

let count = try db.scalar("SELECT count(*) FROM users") as! Int64
print("There are \(count) users.")

Statement objects also have a scalar() method:

let stmt = try db.prepare("SELECT count (*) FROM users")
let count = try stmt.scalar() as! Int64