Improving sqlite3 code in swift using c api

Question

Trying to come up with a pattern where the code to open db, writing queries is elegant and clean while handling all possible error conditions.

Following code

let file = try! FileManager.default.url(for: .cachesDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("mydb.db")

var result = sqlite3_open(file.path, &database)

if result == SQLITE_OK {
    defer { sqlite3_close(database) }
} else {
    print("couldn't open database")
    return
}

result = sqlite3_exec(database, "CREATE TABLE IF NOT EXISTS department(id INTEGER PRIMARY KEY, name TEXT NOT NULL)", nil, nil, nil)
if result != SQLITE_OK {
    print("couldn't insert into department")
    return
}

result = sqlite3_exec(database, "INSERT OR REPLACE INTO department(id, name) VALUES(0, 'HR'), (1, 'Sales'), (2, 'Accounts'), (3, 'Shipping')", nil, nil, nil)
if result != SQLITE_OK {
    print("error inserting")
    return
}

Answer 1

A few observations:

1. The .cachesDirectory is deleted when your device runs low in storage. Are you sure you want to delete your app database in this scenario? Usually we would use .applicationSupportDirectory, which is not deleted when storage runs low and is backed up when you back up your device. But we avoid the use of the .documents folder, because that is reserved for user-facing files.

   Also, we would also generally use use the create option of true.

2. If opening a database failed for any reason, you still need to call sqlite3_close. As the documentation says:

   Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

   So I would move the defer out of that if statement. You want to move it out of there, anyway, because defer will run at the end of the current scope (at the end of your if statement in your current implementation), not at the end of the function. But if you move the defer to the right place, that observation is moot.

3. If a SQL statement fails for any reason, if you are going to print an error, you really should include the sqlite3_errmsg. Without that, it will be exceedingly difficult to debug problems.

4. I would generally advise using guard in situations where you want to exit upon failure. The compiler will make sure that you properly return when an error condition occurs.

   So, pulling that together with the above points, you get something like:

   let folder = try! FileManager.default
       .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
       .appendingPathComponent("AppData")
   
   try? FileManager.default.createDirectory(at: folder, withIntermediateDirectories: true)
   
   let fileURL = folder.appendingPathComponent("mydb.db")
   
   var database: OpaquePointer?
   var result = sqlite3_open(fileURL.path, &database)
   
   defer { sqlite3_close(database) }
   
   guard result == SQLITE_OK else {
       printError(in: database, message: "couldn't open database")
       return
   }
   
   result = sqlite3_exec(database, "CREATE TABLE IF NOT EXISTS department(id INTEGER PRIMARY KEY, name TEXT NOT NULL)", nil, nil, nil)
   guard result == SQLITE_OK else {
       printError(in: database, message: "couldn't insert into department")
       return
   }
   
   result = sqlite3_exec(database, "INSERT OR REPLACE INTO department(id, name) VALUES(0, 'HR'), (1, 'Sales'), (2, 'Accounts'), (3, 'Shipping')", nil, nil, nil)
   guard result == SQLITE_OK else {
       printError(in: database, message: "error inserting")
       return
   }
   

   Where

   func printError(in database: OpaquePointer?, message: String) {
       let error = sqlite3_errmsg(database).flatMap { String(cString: $0) } ?? "Unknown error"
       print(message, error)
   }
   

5. Usually we do not open and close the database with every method call. We generally open the database once, and leave it open. SQLite is very good about committing changes to the file as you go along, so opening and closing the database for every call is a tad inefficient.

6. As a broader observation, I would suggest abstracting the SQLite API from this department related SQL. E.g., perhaps

   do {
       let database = try openDatabase()
       try database.exec("CREATE TABLE IF NOT EXISTS department(id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
       try database.exec("INSERT OR REPLACE INTO department(id, name) VALUES(0, 'HR'), (1, 'Sales'), (2, 'Accounts'), (3, 'Shipping')")
   } catch {
       print(error)
   }
   

   Where:

   func openDatabase() throws -> Database {
       let folder = try! FileManager.default
           .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
           .appendingPathComponent("AppData")
   
       try? FileManager.default.createDirectory(at: folder, withIntermediateDirectories: true)
   
       let fileURL = folder.appendingPathComponent("mydb.db")
   
       let database = Database(fileURL: fileURL)
   
       try database.open()
   
       return database
   }
   

   Now, the above is using my own personal SQLite database wrapper:

   //  Database.swift
   //
   //  Created by Robert Ryan on 3/8/19.
   
   import Foundation
   import SQLite3
   
   private let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
   private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
   
   // MARK: - Database
   
   /// Thin wrapper for SQLite C interface
   
   public class Database {
       // MARK: - Properties
   
       /// The URL for the database
       let fileURL: URL
   
       /// The `sqlite3_open` options
       public var options: OpenOptions
   
       /// A `DateFormatter` for writing dates to the database
       public static var dateFormatter: DateFormatter = {
           let formatter = DateFormatter()
           formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSSX"
           formatter.timeZone = TimeZone(secondsFromGMT: 0)
           formatter.locale = Locale(identifier: "en_US_POSIX")
           return formatter
       }()
   
       /// The SQLite database pointer
       private var database: OpaquePointer?
   
       /// Array of prepared statements that have not yet been finalized
       private var openStatements: [Statement] = []
   
       // MARK: - Initialization
   
       /// Database initializer
       ///
       /// Note: You must still `open` this database before using it.
       public init(fileURL: URL, options: OpenOptions = .default) {
           self.fileURL = fileURL
           self.options = options
       }
   
       /// Deinitializer that will finalize any open statements and then close the database if not already closed.
       deinit {
           finalizeStatements()
           try? close()
       }
   }
   
   // MARK: - Types
   
   public extension Database {
       enum DatabaseError: Error {
           case failed(ReturnCode, String)
           case unknownType(Any)
           case notOpened
           case noStatementPrepared
           case closeFailed
       }
   
       struct OpenOptions: OptionSet {
           public let rawValue: Int32
   
           public static let readOnly     = OpenOptions(rawValue: SQLITE_OPEN_READONLY)
           public static let readWrite    = OpenOptions(rawValue: SQLITE_OPEN_READWRITE)
           public static let create       = OpenOptions(rawValue: SQLITE_OPEN_CREATE)
           public static let noMutex      = OpenOptions(rawValue: SQLITE_OPEN_NOMUTEX)
           public static let fullMutex    = OpenOptions(rawValue: SQLITE_OPEN_FULLMUTEX)
           public static let sharedCache  = OpenOptions(rawValue: SQLITE_OPEN_SHAREDCACHE)
           public static let privateCache = OpenOptions(rawValue: SQLITE_OPEN_PRIVATECACHE)
   
           public static let `default`:  OpenOptions = [.readWrite, .create]
   
           public init(rawValue: Int32) {
               self.rawValue = rawValue
           }
       }
   
       enum ReturnCode: Equatable {
           // non error codes
   
           case ok
           case done
           case row
   
           // error codes
   
           case auth
           case busy
           case cantOpen
           case constraint
           case corrupt
           case empty
           case error
           case fail
           case format
           case full
           case `internal`
           case interrupt
           case ioerr
           case locked
           case mismatch
           case misuse
           case nolfs
           case nomem
           case notadb
           case notfound
           case notice
           case perm
           case `protocol`
           case range
           case readonly
           case schema
           case toobig
           case warning
   
           case unknown(Int32)
   
           static func code(for code: Int32) -> ReturnCode {
               switch code {
               case SQLITE_OK:         return .ok
               case SQLITE_DONE:       return .done
               case SQLITE_ROW:        return .row
   
               case SQLITE_AUTH:       return .auth
               case SQLITE_BUSY:       return .busy
               case SQLITE_CANTOPEN:   return .cantOpen
               case SQLITE_CONSTRAINT: return .constraint
               case SQLITE_CORRUPT:    return .corrupt
               case SQLITE_EMPTY:      return .empty
               case SQLITE_ERROR:      return .error
               case SQLITE_FAIL:       return .fail
               case SQLITE_FORMAT:     return .format
               case SQLITE_FULL:       return .full
               case SQLITE_INTERNAL:   return .internal
               case SQLITE_INTERRUPT:  return .interrupt
               case SQLITE_IOERR:      return .ioerr
               case SQLITE_LOCKED:     return .locked
               case SQLITE_MISMATCH:   return .mismatch
               case SQLITE_MISUSE:     return .misuse
               case SQLITE_NOLFS:      return .nolfs
               case SQLITE_NOMEM:      return .nomem
               case SQLITE_NOTADB:     return .notadb
               case SQLITE_NOTFOUND:   return .notfound
               case SQLITE_NOTICE:     return .notice
               case SQLITE_PERM:       return .perm
               case SQLITE_PROTOCOL:   return .protocol
               case SQLITE_RANGE:      return .range
               case SQLITE_READONLY:   return .readonly
               case SQLITE_SCHEMA:     return .schema
               case SQLITE_TOOBIG:     return .toobig
               case SQLITE_WARNING:    return .warning
   
               default:                return .unknown(code)
               }
           }
       }
   }
   
   // MARK: - Public methods
   
   public extension Database {
       /// Open database
       func open() throws {
           do {
               try call { sqlite3_open_v2(fileURL.path, &database, options.rawValue, nil) }
           } catch {
               try? close()
               throw error
           }
       }
   
       /// Close database
       func close() throws {
           if database == nil { return }
           finalizeStatements()
           try call {
               defer { database = nil }
               return sqlite3_close(database)
           }
       }
   
       /// Execute statement
       ///
       /// - Parameter sql: SQL to be performed.
       /// - Throws: SQLite errors.
       func exec(_ sql: String) throws {
           guard database != nil else { throw DatabaseError.notOpened }
   
           try call { sqlite3_exec(database, sql, nil, nil, nil) }
       }
   
   
       /// Prepare SQL
       ///
       /// - Parameters:
       ///   - sql:        SQL to be prepared
       ///   - parameters: Any parameters to be bound to any `?` in the SQL.
       /// - Returns: The prepared statement.
       /// - Throws: SQLite errors.
       func prepare(_ sql: String, parameters: [DatabaseBindable?]? = nil) throws -> Statement {
           guard database != nil else { throw DatabaseError.notOpened }
   
           var stmt: OpaquePointer?
   
           try call { sqlite3_prepare_v2(database, sql, -1, &stmt, nil) }
   
           let statement = Statement(database: self, statement: stmt!)
   
           openStatements.append(statement)
   
           try statement.bind(parameters)
   
           return statement
       }
   
       /// The `rowid` of the last row inserted
       ///
       /// - Returns: The `rowid`.
       func lastRowId() -> Int64 {
           sqlite3_last_insert_rowid(database)
       }
   
       /// Returns number of rows changed by last `INSERT`, `UPDATE`, or `DELETE` statement.
       ///
       /// - Returns: Number of rows changed.
       func changes() -> Int32 {
           sqlite3_changes(database)
       }
   
       /// Returns number of rows changed `INSERT`, `UPDATE`, or `DELETE` statements since the database was opened.
       ///
       /// - Returns: Number of rows changed.
       func totalChanges() -> Int32 {
           sqlite3_total_changes(database)
       }
   
       /// Finalize a previously prepared statement
       ///
       /// - Parameter statement: The previously prepared statement.
       /// - Throws: SQLite error.
       func finalize(_ statement: Statement) throws {
           guard let index = openStatements.firstIndex(where: { $0.sqlite3_stmt == statement.sqlite3_stmt }) else {
               return
           }
   
           openStatements.remove(at: index)
   
           try call {
               defer { statement.sqlite3_stmt = nil }
               return sqlite3_finalize(statement.sqlite3_stmt)
           }
       }
   
       /// The version of SQLite being used.
       ///
       /// - Returns: Version string.
       func version() -> String? {
           sqlite3_libversion()
               .flatMap { String(cString: $0) }
       }
   }
   
   // MARK: Private methods
   
   fileprivate extension Database {
       /// Call block containing SQLite C function
       ///
       /// - Parameter block: Block that returns value from SQLite C function.
       /// - Returns: Returns return value from that C function if it returned `.ok`, `.done`, or `.row`.
       /// - Throws: SQLite error.
       @discardableResult
       func call(block: () -> (Int32)) throws -> Database.ReturnCode {
           let result = Database.ReturnCode.code(for: block())
           switch result {
           case .ok, .done, .row:
               return result
   
           default:
               let message = String(cString: sqlite3_errmsg(database))
               throw DatabaseError.failed(result, message)
           }
       }
   
       /// Finalize all open statements (those prepared but not yet finalized).
       func finalizeStatements() {
           for statement in openStatements {
               try? finalize(statement)
           }
       }
   }
   
   // MARK: - Statement
   
   /// SQLite statement.
   public class Statement {
       public fileprivate(set) var sqlite3_stmt: OpaquePointer?
       private weak var database: Database?
   
       init(database: Database, statement: OpaquePointer) {
           self.database = database
           self.sqlite3_stmt = statement
       }
   
       deinit {
           try? database?.finalize(self)
       }
   }
   
   // MARK: Public methods
   
   public extension Statement {
       /// Bind array of parameters to `?` placeholders in SQL
       ///
       /// - Parameter parameters: The array of parameters.
       /// - Throws: SQLite error.
       func bind(_ parameters: [DatabaseBindable?]?) throws {
           try parameters?.enumerated().forEach { index, value in
               let offset = Int32(index + 1)
               if let value = value {
                   try database?.call { value.bind(to: self, offset: offset) }
               } else {
                   try database?.call { sqlite3_bind_null(sqlite3_stmt, offset) }
               }
           }
       }
   
       @discardableResult
       /// Perform the prepared statement.
       ///
       /// - Returns: The return code if `.done`, `.row` (or `.ok`, which it never can be).
       /// - Throws: The SQLite error if return code is not one of the aforementioned values.
       func step() throws -> Database.ReturnCode {
           guard
               let database = database,
               let statement = sqlite3_stmt
           else {
               throw Database.DatabaseError.notOpened
           }
   
           return try database.call { sqlite3_step(statement) }
       }
   
       /// Reset the values bound to this prepared statement.
       ///
       /// Used if you want to bind new values and perform the statement again without re-preparing it.
       ///
       /// - Throws: SQLite error.
       func reset() throws {
           guard let database = database,
               let statement = sqlite3_stmt else { throw Database.DatabaseError.notOpened }
   
           try database.call { sqlite3_reset(statement) }
       }
   
       /// Determines if the particular column value is `NULL` or not.
       ///
       /// - Parameter index: The column index number.
       func isNull(index: Int32) -> Bool {
           sqlite3_column_type(sqlite3_stmt, index) == SQLITE_NULL
       }
   
       /// Retrieve the value returned for a column of the particular `index`.
       ///
       /// - Parameters:
       ///    - type: The type to be returned for the column (e.g. `Int.self`).
       ///    - index: The zero-based column index number.
       /// - Returns: Returns the value found at the specified column index. If the value cannot be converted to that type, it will return `nil`.
       /// - Throws: The SQLite error if return code is not one of the aforementioned values.
       func column<T: DatabaseBindable>(_ type: T.Type, index: Int32) -> T? {
           T(from: self, index: index)
       }
   
       /// Retrieve the name of the column of the particular `index`.
       ///
       /// - Parameter index: The zero-based column index number.
       /// - Returns: The name of the column or `nil` if it couldn't determine the name.
       func columnName(index: Int32) -> String? {
           sqlite3_column_name(sqlite3_stmt, index)
               .flatMap { String(cString: $0) }
       }
   
       /// Retrieve the origin name of the column of the particular `index`.
       ///
       /// - Parameter index: The zero-based column index number.
       /// - Returns: The name of the column or `nil` if it couldn't determine the name.
       func columnOriginName(index: Int32) -> String? {
           sqlite3_column_origin_name(sqlite3_stmt, index)
               .flatMap { String(cString: $0) }
       }
   
       /// Retrieve the name of the table associated with the column of the particular `index`.
       ///
       /// - Parameter index: The zero-based column index number.
       /// - Returns: The name of the column or `nil` if it couldn't determine the name.
       func columnTableName(index: Int32) -> String? {
           sqlite3_column_table_name(sqlite3_stmt, index)
               .flatMap { String(cString: $0) }
       }
   
       /// Retrieve the name of the table associated with the column of the particular `index`.
       ///
       /// - Parameter index: The zero-based column index number.
       /// - Returns: The name of the column or `nil` if it couldn't determine the name.
       func columnDatabaseName(index: Int32) -> String? {
           sqlite3_column_database_name(sqlite3_stmt, index)
               .flatMap { String(cString: $0) }
       }
   }
   
   // MARK: - Data binding protocol
   
   public protocol DatabaseBindable {
       /// Initializer used when returning value from result set of performed SQL `SELECT` statement.
       ///
       /// - Parameters:
       ///   - statement: The prepared and performed SQLite statement.
       ///   - index: The 0-based index for the column being returned.
       init?(from statement: Statement, index: Int32)
   
       /// When binding a value to a prepared (but not yet performed) SQL statement.
       ///
       /// - Parameters:
       ///   - statement: The prepared SQLite statement to be performed.
       ///   - offset: the 1-based index for the column being bound.
       /// - Returns: The SQLite return code.
       func bind(to statement: Statement, offset: Int32) -> Int32
   }
   
   // MARK: Specific type conformances
   
   extension String: DatabaseBindable {
       public init?(from statement: Statement, index: Int32) {
           guard !statement.isNull(index: index), let pointer = sqlite3_column_text(statement.sqlite3_stmt, index) else { return nil }
           self = String(cString: pointer)
       }
   
       public func bind(to statement: Statement, offset: Int32) -> Int32 {
           sqlite3_bind_text(statement.sqlite3_stmt, offset, cString(using: .utf8), -1, SQLITE_TRANSIENT)
       }
   }
   
   extension Decimal: DatabaseBindable {
       public init?(from statement: Statement, index: Int32) {
           guard
               !statement.isNull(index: index),
               let string = String(from: statement, index: index),
               let value = Decimal(string: string, locale: Locale(identifier: "en_US_POSIX")) else { return nil }
           self = value
       }
   
       public func bind(to statement: Statement, offset: Int32) -> Int32 {
           var value = self
           let string = NSDecimalString(&value, Locale(identifier: "en_US_POSIX"))
           return sqlite3_bind_text(statement.sqlite3_stmt, offset, string.cString(using: .utf8), -1, SQLITE_TRANSIENT)
       }
   }
   
   extension IntegerLiteralType: DatabaseBindable {
       public init?(from statement: Statement, index: Int32) {
           guard !statement.isNull(index: index) else { return nil }
           let value = sqlite3_column_int64(statement.sqlite3_stmt, index)
           self = .init(value)
       }
   
       public func bind(to statement: Statement, offset: Int32) -> Int32 {
           sqlite3_bind_int64(statement.sqlite3_stmt, offset, Int64(self))
       }
   }
   
   extension BinaryFloatingPoint {
       public init?(from statement: Statement, index: Int32) {
           guard !statement.isNull(index: index) else { return nil }
           self = Self(sqlite3_column_double(statement.sqlite3_stmt, index))
       }
   
       public func bind(to statement: Statement, offset: Int32) -> Int32 {
           sqlite3_bind_double(statement.sqlite3_stmt, offset, Double(self))
       }
   }
   
   extension Data: DatabaseBindable {
       public init?(from statement: Statement, index: Int32) {
           guard !statement.isNull(index: index) else { return nil }
   
           let count = sqlite3_column_bytes(statement.sqlite3_stmt, index)
           if count == 0 { return nil }
   
           guard let bytes = sqlite3_column_blob(statement.sqlite3_stmt, index) else { return nil }
           self = Data(bytes: bytes, count: Int(count))
       }
   
       public func bind(to statement: Statement, offset: Int32) -> Int32 {
           withUnsafeBytes { pointer in
               let bytes = pointer.baseAddress
               return sqlite3_bind_blob(statement.sqlite3_stmt, offset, bytes, Int32(count), SQLITE_TRANSIENT)
           }
       }
   }
   
   extension Date: DatabaseBindable {
       public init?(from statement: Statement, index: Int32) {
           guard
               !statement.isNull(index: index),
               let pointer = sqlite3_column_text(statement.sqlite3_stmt, index) else { return nil }
   
           let string = String(cString: pointer)
           guard let date = Database.dateFormatter.date(from: string) else { return nil }
   
           self = date
       }
   
       public func bind(to statement: Statement, offset: Int32) -> Int32 {
           let string = Database.dateFormatter.string(from: self)
           return sqlite3_bind_text(statement.sqlite3_stmt, offset, string.cString(using: .utf8), -1, SQLITE_TRANSIENT)
       }
   }
   

   Now, that is my personal thin wrapper around the SQLite API, but there are lots of them out there and you probably want to find one that is publicly supported. Or roll your own. But I would advise abstracting the SQLite code away from your app-specific SQL.

   Even if you do this (using my wrapper, your own, or some established third-party wrapper), I'd also abstract the “department” repository code out in its own controller. You do not want to entangle your model object and general controller code with any particular storage mechanism. You should be able to change your database API at a future date without impacting your app-specific logic.

7. When inserting into a database, we often would avoid using string interpolation, but rather bind values to ? placeholders in our SQL using sqlite3_bind_xxx(). Or, using the above SQLite wrapper:

   do {
       let database = try openDatabase()
       try database.exec("CREATE TABLE IF NOT EXISTS department(id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
       let statement = try database.prepare("INSERT OR REPLACE INTO department(id, name) VALUES (?, ?)")
       for (id, string) in [(5, "Bob's Department"), (6, "Susan's Department")] {
           try statement.bind([id, string])
           try statement.step()
           try statement.reset()
       }
   } catch {
       print(error)
   }
   

   This gets us out of worrying about the presence of apostrophes or other SQLite reserved string literals in our SQL. In the case of this fixed list of departments, it is not much of an issue, but if you start inserting values supplied by the end user, making sure that values are inserted correctly with sqlite3_bind_xxx() functions and ? placeholders in your SQL.

8. Personally, I would not just open a database with sqlite3_open.

   • I would use sqlite3_open_v2 with the SQLITE_OPEN_READWRITE option (or the .readWrite option in my wrapper), but not the SQLITE_OPEN_CREATE option (or my .create option).
   • If open failed, then I would go through the creation process (creating tables, populating them, etc.; or frequently I would put an initialized database in my bundle, and if opening in app support directory failed, then I'd copy from the bundle to app support directory and then open again).

   In your simple example, it is not terribly relevant, but as the app/db grows in complexity, you may want to avoid having to write a ton of “create and populate the database” code. It is convenient to have a saved “initial state” database ready and waiting in the bundle. It is your call, though, but just a recommendation.

   Either way, it is often preferable to avoiding sqlite3_open, which silently creates a database if it is not there, but use sqlite3_open_v2 without the “create” option, and use the success or failure of that to determine whether you need to initialize the database or not.