Extensive use of exception for database wrapper

Question

My favourite programming language is Java and I usually use JDBC for database operations.

I'm using Swift for one of my projects and I should use a database. The problem is SQLite is only available in C. But I'm not comfortable with C due to its naming conventions and lack of documentation (when using autocomplete in Xcode). So I decided to write a wrapper class for SQLite C in Swift for my future self.

Problem:

The SQLite has some 30 Primary Result Code List.

Other than SQLITE_OK, SQLITE_DONE, SQLITE_ROW other codes represent errors.

I used exceptions to throw with the error code. Is it right practice to use exceptions to indicate error (although it doesn't crash the program)?

import Foundation
public class Database
{
 /** callback closure type */
 public typealias SQLiteExecCallBack = @convention(c) (_ void: UnsafeMutableRawPointer?, _ columnCount: Int32, _ values: UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>?, _ columns:UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>?) -> Int32
 public enum SQLError: Error
 {
 case ErrorMessage(msg: String)
 }
 /**
 Specifies what type of DataBase to use
 There are 2 possible values.
 - DBType.inMemory: Creates Database in RAM
 - DBType.file(String): Creates or opens Database file specified by name as an argument
 */
 public enum DBType
 {
 case inMemory
 case file(String)
 }
 /**
 Required to cast to appropriate data type while using "select" query.
 Taken from -> https://sqlite.org/c3ref/c_blob.html
 #define SQLITE_INTEGER 1
 #define SQLITE_FLOAT 2
 #define SQLITE_BLOB 4
 #define SQLITE_NULL 5
 #ifdef SQLITE_TEXT
 # undef SQLITE_TEXT
 #else
 # define SQLITE_TEXT 3
 #endif
 #define SQLITE3_TEXT 3
 */
 private enum dataType: Int32
 {
 case SQLITE_INTEGER = 1, SQLITE_FLOAT, SQLITE3_TEXT, SQLITE_BLOB, SQLITE_NULL
 }
 private var db: OpaquePointer? = nil
 private var dbName: String = ""
 /** 
 Returns Database name.
 - ":memory:" : If the database is from memory
 - file_name : If the database is from file
 */
 public var databaseName: String {
 get {
 return dbName
 }
 }
 /** Returns SQLite version */
 public var SQLiteVersion: String {
 get {
 return String(cString: sqlite3_libversion())
 }
 }
 public var dbObject: OpaquePointer? {
 get {
 return db
 }
 }
 /**
 The database connection object
 - parameter type: There are 2 possible values.
 - DBType.inMemory: Creates Database in RAM
 - DBType.file(String): Creates or opens Database file specified by name as an argument
 - returns: Database object which can be used to open, execute, close.
 */
 init(type: DBType)
 {
 switch type
 {
 case .inMemory:
 self.dbName = ":memory:"
 case .file(let databaseName):
 self.dbName = databaseName
 }
 }
 /**
 Open a connection to a new or existing SQLite database.
 */
 func open() throws
 {
 let rc = sqlite3_open(dbName, &self.db)
 if rc != SQLITE_OK
 {
 let msg = "Can't open database. Something went wrong!\n" + " Error: " + String(cString:sqlite3_errmsg(self.db))
 throw SQLError.ErrorMessage(msg: msg)
 }
 #if DEBUG
 print("open() : Successfully created/opened database!")
 #endif
 }
 /**
 Compile SQL text into byte-code that will do the work of querying or updating the database.
 - parameter sql: SQL query as String
 - returns: Prepared Statement for querying or updating the database.
 */
 func prepareStatement(sql: String) throws -> OpaquePointer
 {
 var statement: OpaquePointer? = nil
 /* Third parameter can be -1 but giving length of string can increase performance slightly */
 let rc = sqlite3_prepare_v2(db, sql, Int32(sql.lengthOfBytes(using: .utf8)), &statement, nil)
 if rc != SQLITE_OK
 {
 let msg = "Can't prepare statements!" + "\nError: " + String(cString: sqlite3_errmsg(self.db))
 throw SQLError.ErrorMessage(msg: msg)
 }
 if statement == nil
 {
 let msg = "Something went wrong! " + " The prepare statement returned is null " + " Error: " + String(cString: sqlite3_errmsg(self.db))
 throw SQLError.ErrorMessage(msg: msg)
 }
 #if DEBUG
 print("prepareStatement(sql:) : Successfully prepared!")
 #endif
 return statement!
 }
 /**
 Used to execute queries using SELECT command
 - parameter pstmt: It is returned from prepareStatement(sql:)
 - returns: Multi-dimensional array of type Any. Rows and columns of array represents rows and columns of table
 */
 func execute(pstmt: OpaquePointer) throws -> [[Any]]
 {
 var data = [[Any]]()
 var data1 = [Any]()
 while true
 {
 let r = sqlite3_step(pstmt)
 if r == SQLITE_ROW // SQLITE_ROW is returned for select query. Other queries returns SQLITE_DONE
 {
 for i in 0..<sqlite3_column_count(pstmt)
 {
 let type = sqlite3_column_type(pstmt, i)
 switch type
 {
 case dataType.SQLITE_INTEGER.rawValue:
 data1.append(sqlite3_column_int(pstmt, i))
 case dataType.SQLITE_FLOAT.rawValue:
 data1.append(sqlite3_column_double(pstmt, i))
 case dataType.SQLITE3_TEXT.rawValue:
 data1.append(String(cString: sqlite3_column_text(pstmt, i)))
 case dataType.SQLITE_BLOB.rawValue:
 print("It is BLOB!") // should do something
 case dataType.SQLITE_NULL.rawValue:
 print("It is NULL!")
 default:
 print("Just to stop crying of swift.")
 }
 }
 data.append(data1)
 data1.removeAll()
 }
 else if r == SQLITE_DONE
 {
 break;
 }
 else
 {
 let error = String(cString: sqlite3_errmsg(self.db))
 var msg = "Error code: \(r) "
 if error != "not an error"
 {
 msg += error
 }
 throw SQLError.ErrorMessage(msg: msg)
 }
 }
 /*
 The sqlite3_reset() function is called to reset a prepared statement object back to its initial state, ready to be re-executed.
 It does not change the values of any bindings on the prepared statement
 Use sqlite3_clear_bindings() to reset the bindings.
 */
 sqlite3_reset(pstmt)
 return data
 }
 func executeOnly(pstmt: OpaquePointer)
 {
 // still need to implement.
 }
 /**
 Executes SQL statements to insert, update, delete. If using SELECT, use execute(pstmt:) -> [[Any]]
 - parameter pstmt: Prepared Statement returned from prepareStatement(sql:)
 - returns: Number of rows effected. 
 -1 if any error occurred
 */
 func execute(pstmt: OpaquePointer) throws -> Int
 {
 let r = sqlite3_step(pstmt)
 if r == SQLITE_DONE // SQLITE_DONE is returned for sql queries other than select query(it returns SQLITE_ROW)
 {
 #if DEBUG
 print("execute(pstmt:) -> Int : Successfully Executed")
 #endif
 /*
 The sqlite3_reset() function is called to reset a prepared statement object back to its initial state, ready to be re-executed.
 It does not change the values of any bindings on the prepared statement
 Use sqlite3_clear_bindings() to reset the bindings.
 */
 sqlite3_reset(pstmt)
 return Int(sqlite3_changes(db))
 }
 else
 {
 let msg = "Didn't execute! Think of using another execute(pstmt:) ERROR CODE: \(r)" + " Error: " + String(cString: sqlite3_errmsg(self.db))
 throw SQLError.ErrorMessage(msg: msg)
 }
 }
 /**
 It is a convenience wrapper around prepareStatement(sql:), execute(), and destroyPS(pstmt:), that allows an application to run multiple statements of SQL without having to use a lot of code.
 - parameter sql: SQL query string
 - parameter callback: It is a closure to execute while using "SELECT" queries. Otherwise it is not executed.
 */
 func execute(sql: String, callback: SQLiteExecCallBack?) throws
 {
 var zErrMsg:UnsafeMutablePointer<Int8>? = nil
 var rc: Int32 = 0
 rc = sqlite3_exec(db, sql, callback ?? nil , nil, &zErrMsg)
 #if DEBUG
 print("Successfully executed!")
 #endif
 if rc != SQLITE_OK
 {
 let msg = "ERROR: execute(sql:callback:) ERROR CODE: \(rc) " + String(cString: zErrMsg!)
 sqlite3_free(zErrMsg)
 throw SQLError.ErrorMessage(msg: msg)
 }
 }
 /**
 Closes prepared statement in order to avoid resource leaks.
 - parameter pstmt: Prepared Statement returned from prepareStatement(sql:)
 */
 func destroyPS(pstmt: OpaquePointer)
 {
 let r = sqlite3_finalize(pstmt)
 /* Should I just throw an exception if, if r != SQLITE_OK */
 if r == 21
 {
 print(String(cString: sqlite3_errmsg(self.db)))
 }
 }
 /**
 Closes a connection to Database
 */
 func close()
 {
 sqlite3_close(self.db)
 }
}

I know that Exceptions usually slow the program but I read somewhere in the internet that it is not the case in Swift (I can't find the link to post it here).

Is my approach correct? Are there any overall improvements to be made in the code?

• Martin R: Are you aware that there are established and well-tested Swift wrappers for SQLite, such as github.com/stephencelis/SQLite.swift ?
• SkrewEverything: @MartinR yes im aware of that. Some of the reasons is that I dont usually trust open-source projects unless I cant do it on my own (in the past i have some bad experiences due to some open-source projects stopped development and I have to learn and implement my-self) and also I get to design a wrapper class to suit my requirements. And the last reason, I get to know about the programming challenges involved in designing an API (as Im a UG student).
• yuri: You don't like C because it does not autocomplete in Xcode?
• SkrewEverything: @yuri main reason is because of its naming conventions. I dont find it to be self-explanatory like in Java and Swift( I started my programming in Java. So I find using underscores and short names(like strcmp etc) very difficult and C doesnt have a good documentation while using an IDE ).

Answer 1

Swift errors are not exceptions. The Error Handling chapter in "The Swift Programming Language" explains that

NOTE

Error handling in Swift resembles exception handling in other languages, with the use of the try, catch and throw keywords. Unlike exception handling in many languages — including Objective-C — error handling in Swift does not involve unwinding the call stack, a process that can be computationally expensive. As such, the performance characteristics of a throw statement are comparable to those of a return statement.

So it is good practice, and you need not worry about the performance. Note also that all Objective-C APIs from the Foundation and other frameworks which return an NSError, are imported into Swift as throwing functions.

By throwing an error (not an exception!) you can inform the caller that the method failed and provide additional information about the problem. The caller has the options to

• do a sequence of related method calls in a single do-catch-block, or
• wrap each method call into its own do-catch-block, or
• call the method with try? to convert the error into an optional value (or even to ignore the error).

So throwing errors is the most flexible method to report errors to the caller.

How you throw errors and what information these errors contain can be improved in your code. Let's take the following example:

do {
 let db = Database(type: .file("/no/such/dir/test.sqlite"))
 try db.open()
 db.close()
} catch let error {
 print(error)
 // ErrorMessage("Can\'t open database. Something went wrong!\n Error: unable to open database file")
}

We see that:

• "ErrorMessage", "Something went wrong!", and "Error:" are unnecessary. If an error is thrown then we know that something went wrong. The caller might prefix the error message with some text, but that is not responsibility of the throwing function.
• The single-quote and the newline character are escaped, which does not look nice, e.g. when the message is presented to a user.
• Information is duplicated: "Can\'t open database" and "unable to open database file".

Let's compare this with a call to the Foundation framework:

do {
 let data = try Data(contentsOf: URL(fileURLWithPath: "/no/such/file"))
} catch let error {
 print(error)
 // Error Domain=NSCocoaErrorDomain Code=260 "The file "file" couldn’t be opened because there is no such file." UserInfo={NSFilePath=/no/such/file, NSUnderlyingError=0x100c07060 {Error Domain=NSPOSIXErrorDomain Code=2 "No such file or directory"}}
 print(error.localizedDescription)
 // The file "file" couldn’t be opened because there is no such file.
}

Here

• error contains the full information, which is useful for debug output.
• error.localizedDescription is a string which is suited to be presented to a user.

This does not yet work in our case:

do {
 let db = Database(type: .file("/no/such/dir/test.sqlite"))
 try db.open()
 db.close()
} catch let error {
 print(error.localizedDescription)
 // The operation couldn’t be completed. (MyApp.Database.SQLError error 0.)
}

prints no useful information at all. The solution is to

• throw instances of NSError, or
• make the error conform to the LocalizedError protocol.

I'll describe the second method (see also How to provide a localized description with an Error type in Swift? on Stack Overflow).

public struct DBError: Error, LocalizedError {
 let sqliteMsg: String
 let sqliteError: Int32
 init(db: Database, sqliteError: Int32) {
 self.sqliteError = sqliteError
 self.sqliteMsg = String(cString: sqlite3_errmsg(db.db))
 }
 public var errorDescription: String? {
 return sqliteMsg
 }
}

Note that the call to sqlite3_errmsg() is now in the constructor of the error, and not spread across the entire class anymore. This is how you would use it in your class

func open() throws
{
 let rc = sqlite3_open(dbName, &self.db)
 if rc != SQLITE_OK {
 throw DBError(db: self, sqliteError: rc)
 }
}

and produces both a full error and a short description:

do {
 let db = Database(type: .file("/no/such/dir/test.sqlite"))
 try db.open()
 db.close()
} catch let error {
 print(error)
 // DBError(sqliteMsg: "unable to open database file", errorCode: 14)
 print(error.localizedDescription)
 // unable to open database file
}

The caller has also the option to cast the error to the concrete error type and print detailed information:

do {
 let db = Database(type: .file("/no/such/dir/test.sqlite"))
 try db.open()
 db.close()
} catch let error as Database.DBError {
 print("Error \(error.sqliteError): \(error.sqliteMsg)")
 // Error 14: unable to open database file
} catch let error {
 print(error.localizedDescription)
}

but he does not have to. The localizedDescription is available without knowing the concrete error type at all.

You can also make the error type conform to CustomNSError to provide additional information to the caller, e.g. the SQLite error code.

• SkrewEverything: As the question is primarily about exceptions, I thought this should be marked as the answer. But both the answers really helped me. Thanks.

Answer 2

Here some remarks and suggestions which are not related to the error handling mechanism:

Swift uses upper camel case names for types, and lower camel case names for enumeration cases (compare API Design Guidelines. So

private enum dataType: Int32
{
 case SQLITE_INTEGER = 1, SQLITE_FLOAT, SQLITE3_TEXT, SQLITE_BLOB, SQLITE_NULL
}

should be

private enum SQLiteDataType: Int32
{
 case integer = 1, float, text, blob, null
}

The computed property

public var databaseName: String {
 get {
 return dbName
 }
}

is not needed if you make dbName public read-only, but internally read-write:

public private (set) var dbName: String = ""

But actually you can make it a constant property

public let dbName: String

because it is assigned only in the init method.

If you open the database in the init method (making it a throwing initializer):

init(type: DBType) throws {
 switch type {
 case .inMemory:
 self.dbName = ":memory:"
 case .file(let databaseName):
 self.dbName = databaseName
 }
 var db: OpaquePointer? = nil
 let rc = sqlite3_open(dbName, &db)
 if rc != SQLITE_OK {
 throw ...
 }
 self.db = db!
}

then you can make db a non-optional (private or public) constant property as well:

public let db: OpaquePointer

Closing the database handle can be done "automatic" in a deinit method:

deinit {
 sqlite3_close(self.db)
}

Now it is no longer possible to call any database method without having opened it.

For better type safety I would define a custom type PreparedStatement with a private OpaquePointer property. execute() could then be a method of this type.

You have two methods

func execute(pstmt: OpaquePointer) throws -> [[Any]]
func execute(pstmt: OpaquePointer) throws -> Int

which differ only in the return type. That is perfectly valid in Swift, but forces the caller to provide the return type, e.g.

let rows: [[Any]] = try db.execute(pstmt: stmt)

I would probably name the methods differently.

What is generally missing is a way to bind values to prepared statements, which is important to avoid SQL injection attacks.

• SkrewEverything: I think it is a good thing to separate the implementation which might help in the future if editing is required in a specific part. Is it a good practice to write private func open() and call the open() in init()?
• Martin R: @SkrewEverything: Surely you can do that. My suggestion was just to make it impossible to create a Database object without opening it and to make the db property non-optional and a non-constant.
• Martin R: @SkrewEverything: Sorry, I do not have a good idea for that. Perhaps execute() vs fetch()?