When are records committed to disk with SQLite.swift?

atineoSE from

I am struggling to see some INSERTs reflected in a SQLite database when using SQLite.swift.

I debug my iOS app with breakpoints and after the insertion I export the app container (from device) to the host hard disk. Then I inspect the SQLite database to see the changes. INSERTs are not reflected thus.

If I add extra code such as SELECT on the table or restart the app, then the record is there, so it looks like the consolidation to the disk on the device is not happening straight away but at some other point. I am using a single Connection object and there are no concurrent accesses, so I don't expect threading issues. I also tried with the INSERT inside a transaction but still not reflected on the container.

When are the changes consolidated to disk so that it is really persistent? Is there a way to force a flush? What happens if the app is killed unexpectedly, could the data be lost because it wasn't really on the database?

ios sqlite sqlite.swift

Answers

answered 2 months ago David S. #1

Generally, at the C-API level, the results are only cached when using a transaction. At that level, sqlite3_reset() and/or sqlite3_finalize() will close out the statement and cause it to be written. My guess though is you aren't using transactions.

I found several posts online that suggest leaving open an active query to the database could prevent the cache from flushing to the DB. Without seeing your code though, it's not really possible to know if that's what's happening.

You could explicitly BEGIN a transaction and COMMIT it to force the flush if you wanted to.

Another thing to check is the PRAGMA SYNCHRONOUS value (docs). It is typically set to FULL (2) but you could try setting it to EXTRA (3).

Update:

I tried to replicate what you're describing using SQLite.swift, but the writes are always instant, and readable from a different connection immediately. Here's the code I wrote:

import UIKit
import SQLite

class ViewController: UIViewController {

    private static let REUSE_DB_CONN:Bool = true
    private static let DB_NAME = "SQLiteFlushTester.sqlite3"
    let pragma:String? = "PRAGMA foreign_keys = ON;"

    var connection:Connection?

    let fooTable = Table("FooTable")
    let id = Expression<Int64>("id")

    func dbDefaultPath(name: String = ViewController.DB_NAME) -> String {
        let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!

        let fmgr = FileManager.default
        if (!fmgr.fileExists(atPath: path)) {
            print("Directory does not exist at \(path)")
            do {
                try fmgr.createDirectory(atPath: path, withIntermediateDirectories: true, attributes: nil)
            } catch {
                print("Could not create directory \(path)")
            }
        }

        return "\(path)/\(name)"
    }

    func dbConn() -> Connection? {
        if let connection = connection {
            print("Recycling connection")
            return connection
        }

        let dbPath = dbDefaultPath()
        do {
            let dbConn = try Connection(dbPath)
            if let pragma = pragma {
                try dbConn.execute(pragma)
            }

            if ( ViewController.REUSE_DB_CONN ) {
                connection = dbConn
            }
            return dbConn
        } catch let error {
            print("Error opening DB \(error)?")
            return nil
        }
    }

    func createTestTable(_ db:Connection) throws {
        try db.run(fooTable.create { t in
            t.column(id, primaryKey: true)
        })
    }

    func insertIdIntoFoo(_ db:Connection) {
        let randval:Int64 = Int64(arc4random_uniform(10000))

        do {
            let insert = fooTable.insert(id <- randval)
            let rowid = try db.run(insert)
            print("Inserted to row id \(rowid)")
        } catch let error {
            print("Error inserting value: \(error)")
            return
        }
    }

    @IBAction func doAnInsert(_ sender: Any) {
        guard let db = dbConn() else {return}
        insertIdIntoFoo(db)
    }

    override func viewDidLoad() {
        super.viewDidLoad()

        guard let db = dbConn() else { return }

        do {
            try createTestTable(db)
        } catch let error {
            print("Error creating table: \(error)")
            return
        }
    }
}

I opened the DB from the sqlite3 command line tool and watched the updates as I pushed the button on my ViewController that triggered the IBAction and the row was reflected in the DB immediately. Reusing the connection (or not) made no difference (see REUSE_DB_CONN flag).

answered 1 month ago Jorge Cardenas #2

I had same issues with accessing the database and I found that it was particularly recurrent situation when I was inserting data making use of a loop like a for or a while statement.

  1. Make sure you are controlling the data flow by serializing access to the database.
  2. Make sure the database is opened the whole time the insert process is being performed, otherwise it's going to create a problem. If you open and close the database every loop cycle, that's an issue.

Have a nice day!