1
\$\begingroup\$

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
}
\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$
1
  • 1
    \$\begingroup\$ Excellent answer (as always)! \$\endgroup\$
    – Martin R
    Dec 30 '20 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.