Improving sqlite3 code in swift using c api 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(&quot;mydb.db&quot;)

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

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

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

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

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.

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.

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.

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(&quot;AppData&quot;)

try? FileManager.default.createDirectory(at: folder, withIntermediateDirectories: true)

let fileURL = folder.appendingPathComponent(&quot;mydb.db&quot;)

var database: OpaquePointer?
var result = sqlite3_open(fileURL.path, &amp;database)

defer { sqlite3_close(database) }

guard result == SQLITE_OK else {
    printError(in: database, message: &quot;couldn't open database&quot;)
    return
}

result = sqlite3_exec(database, &quot;CREATE TABLE IF NOT EXISTS department(id INTEGER PRIMARY KEY, name TEXT NOT NULL)&quot;, nil, nil, nil)
guard result == SQLITE_OK else {
    printError(in: database, message: &quot;couldn't insert into department&quot;)
    return
}

result = sqlite3_exec(database, &quot;INSERT OR REPLACE INTO department(id, name) VALUES(0, 'HR'), (1, 'Sales'), (2, 'Accounts'), (3, 'Shipping')&quot;, nil, nil, nil)
guard result == SQLITE_OK else {
    printError(in: database, message: &quot;error inserting&quot;)
    return
}

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


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.

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(&quot;CREATE TABLE IF NOT EXISTS department(id INTEGER PRIMARY KEY, name TEXT NOT NULL)&quot;)
    try database.exec(&quot;INSERT OR REPLACE INTO department(id, name) VALUES(0, 'HR'), (1, 'Sales'), (2, 'Accounts'), (3, 'Shipping')&quot;)
} catch {
    print(error)
}

Where:
func openDatabase() throws -&gt; Database {
    let folder = try! FileManager.default
        .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
        .appendingPathComponent(&quot;AppData&quot;)

    try? FileManager.default.createDirectory(at: folder, withIntermediateDirectories: true)

    let fileURL = folder.appendingPathComponent(&quot;mydb.db&quot;)

    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 = &quot;yyyy-MM-dd'T'HH:mm:ss.SSSX&quot;
        formatter.timeZone = TimeZone(secondsFromGMT: 0)
        formatter.locale = Locale(identifier: &quot;en_US_POSIX&quot;)
        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) -&gt; 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, &amp;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 -&gt; Statement {
        guard database != nil else { throw DatabaseError.notOpened }

        var stmt: OpaquePointer?

        try call { sqlite3_prepare_v2(database, sql, -1, &amp;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() -&gt; 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() -&gt; 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() -&gt; 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() -&gt; 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: () -&gt; (Int32)) throws -&gt; 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 -&gt; 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) -&gt; 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&lt;T: DatabaseBindable&gt;(_ type: T.Type, index: Int32) -&gt; 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) -&gt; 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) -&gt; 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) -&gt; 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) -&gt; 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) -&gt; 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) -&gt; 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: &quot;en_US_POSIX&quot;)) else { return nil }
        self = value
    }

    public func bind(to statement: Statement, offset: Int32) -&gt; Int32 {
        var value = self
        let string = NSDecimalString(&amp;value, Locale(identifier: &quot;en_US_POSIX&quot;))
        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) -&gt; 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) -&gt; 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) -&gt; 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) -&gt; 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.

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(&quot;CREATE TABLE IF NOT EXISTS department(id INTEGER PRIMARY KEY, name TEXT NOT NULL)&quot;)
    let statement = try database.prepare(&quot;INSERT OR REPLACE INTO department(id, name) VALUES (?, ?)&quot;)
    for (id, string) in [(5, &quot;Bob's Department&quot;), (6, &quot;Susan's Department&quot;)] {
        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.

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.