macOS中Swift调用SQLite3查询结果不一致问题排查

Problem Description #

I'm hitting a weird inconsistent issue when using SQLite3 with Swift on macOS. Most of the time (over 99% of attempts), my query only pulls the first row—after the first sqlite3_step returns SQLITE_ROW, the next call immediately gives SQLITE_DONE, killing the loop early. Very rarely (<1% of cases), it works as expected, returning all matching records with multiple SQLITE_ROW responses from sqlite3_step.

Relevant Code #

func sure(_ c: UnsafePointer<UInt8>) -> String{ return String(cString:c) }
func loadUsageOf(_ startDateId: String, _ endDateId: String) -> [Usage]{
    let sql = "SELECT host, date_id, loaded, switched from Usage where date_id >= ? and date_id <= ?"
    var selectStatement: OpaquePointer?
    var result: [Usage] = []
    if sqlite3_prepare_v2(db, sql, -1, &selectStatement, nil) == SQLITE_OK {
        sqlite3_bind_text(selectStatement, 1, startDateId, -1, nil)
        sqlite3_bind_text(selectStatement, 2, endDateId, -1, nil)
        var steps = sqlite3_step(selectStatement)
        print("Loading usage step \(steps)")
        while(steps == SQLITE_ROW ){
            let host = sure(sqlite3_column_text(selectStatement, 0))
            let date_id = sure(sqlite3_column_text(selectStatement, 1))
            let loaded = sqlite3_column_int64(selectStatement, 2)
            let switched = sqlite3_column_int64(selectStatement, 3)
            let usage = Usage(h: host, loaded: loaded, switched: switched == 0 ? nil: switched)
            print("Loaded \(usage) on \(date_id)")
            result.append(usage)
            steps = sqlite3_step(selectStatement)
            print("Loading more usages step \(steps)")
        }
    }
    sqlite3_finalize(selectStatement)
    return result
}

Observed Behavior #

• Failure case: First sqlite3_step returns SQLITE_ROW(100), second call returns SQLITE_DONE(101) right away.
• Success case: Multiple sqlite3_step calls return SQLITE_ROW until all rows are fetched.

Let’s walk through the most likely culprits here—this kind of flaky behavior almost always ties to memory management or thread safety when mixing SQLite’s C API with Swift’s ARC memory model.

1. Incorrect Parameter Binding Destructor #

Your sqlite3_bind_text calls use nil as the final destructor parameter. When you pass nil, SQLite assumes the string pointer will stay valid for the entire lifetime of the prepared statement. But Swift’s String is managed by ARC—if startDateId or endDateId get deallocated (or their underlying memory is reused) before the statement runs, SQLite could be reading garbage data, which might cause the query to terminate unexpectedly early.

Fix this by using SQLITE_TRANSIENT instead of nil. This tells SQLite to make an immediate copy of the string, so you don’t have to worry about Swift’s memory management interfering:

sqlite3_bind_text(selectStatement, 1, startDateId, -1, SQLITE_TRANSIENT)
sqlite3_bind_text(selectStatement, 2, endDateId, -1, SQLITE_TRANSIENT)

If SQLITE_TRANSIENT isn’t recognized (sometimes an issue with import setups), you can use unsafeBitCast(-1, to: sqlite3_destructor_type.self) as a fallback.

2. Thread Safety Violations #

SQLite database connections (db in your code) are not thread-safe by default. If you’re accessing the same db pointer from multiple threads without proper synchronization (like a serial queue or mutex), you’ll get all sorts of inconsistent behavior—partial results, crashes, even corrupted data.

Fix: Route all database operations (prepare, bind, step, finalize) through a single serial queue. For example:

private let dbQueue = DispatchQueue(label: "com.yourapp.sqlite.queue")

func loadUsageOf(_ startDateId: String, _ endDateId: String, completion: @escaping ([Usage]) -> Void) {
    dbQueue.async {
        // Your existing query code lives here
        completion(result)
    }
}

Never share the db pointer across threads without this kind of guardrail.

3. Defensive Handling for sqlite3_column_text #

Your sure function converts UnsafePointer<UInt8> to a String, but sqlite3_column_text returns a pointer that’s only valid until the next call to sqlite3_step or sqlite3_finalize. While this usually works, adding an optional check makes your code more robust against edge cases (like NULL columns) and eliminates any chance of dangling pointer issues:

func sure(_ c: UnsafePointer<UInt8>?) -> String {
    guard let c = c else { return "" }
    return String(cString: c)
}

4. Add Error Checking for Binding #

It’s possible that binding is failing silently in some cases, leading to malformed queries. Add checks for the sqlite3_bind_text results to rule this out:

let bind1Result = sqlite3_bind_text(selectStatement, 1, startDateId, -1, SQLITE_TRANSIENT)
let bind2Result = sqlite3_bind_text(selectStatement, 2, endDateId, -1, SQLITE_TRANSIENT)
if bind1Result != SQLITE_OK || bind2Result != SQLITE_OK {
    print("Binding failed: \(String(cString: sqlite3_errmsg(db)))")
    sqlite3_finalize(selectStatement)
    return result
}

This will print a human-readable error if something goes wrong during binding.

内容的提问来源于stack exchange，提问作者user5616998