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).
