SQLite With Swift Tutorial: Getting Started

Updating Contacts

Your next step is to update an existing row. Get started by creating the UPDATE statement:

let updateStatementString = "UPDATE Contact SET Name = 'Adam' WHERE Id = 1;"

Here, you’re using real values instead of ? placeholders. You’d usually use the placeholders and bind the statements properly, but this tutorial will skip those steps for brevity.

Next, add the following function to the playground:

func update() {
  var updateStatement: OpaquePointer?
  if sqlite3_prepare_v2(db, updateStatementString, -1, &updateStatement, nil) == 
      SQLITE_OK {
    if sqlite3_step(updateStatement) == SQLITE_DONE {
      print("\nSuccessfully updated row.")
    } else {
      print("\nCould not update row.")
    }
  } else {
    print("\nUPDATE statement is not prepared")
  }
  sqlite3_finalize(updateStatement)
}

This is a similar flow to what you’ve seen before: prepare, step and finalize.

Next, add the following to your playground:

update()
query()

This executes your new function, then calls your previously-defined query() function so that you can see the results:

Successfully updated row.

Query Result:
1 | Adam

Congratulations on updating your first row! How easy was that? :]

You’re doing great! You can now create a table, add contacts to it and update those contacts. In the next step, you’ll learn how to delete those contacts.

Deleting Contacts

The final step on the path to becoming an SQLite ninja is to delete the row you created. Again, you’ll use the familiar pattern of prepare, step and finalize.

Add the following to the playground:

let deleteStatementString = "DELETE FROM Contact WHERE Id = 1;"

Now, add the following function to execute the statement:

func delete() {
  var deleteStatement: OpaquePointer?
  if sqlite3_prepare_v2(db, deleteStatementString, -1, &deleteStatement, nil) == 
      SQLITE_OK {
    if sqlite3_step(deleteStatement) == SQLITE_DONE {
      print("\nSuccessfully deleted row.")
    } else {
      print("\nCould not delete row.")
    }
  } else {
    print("\nDELETE statement could not be prepared")
  }
  
  sqlite3_finalize(deleteStatement)
}

Are you feeling it now? Prepare, step and finalize. :]

Execute this new function as below:

delete()
query()

Run your playground and you should see the following output in your console:

Successfully deleted row.
Query returned no results.

So you’ve now mastered working with your table, but what about when things inevitably go wrong? In the next section, you’ll learn how to deal with errors.

Handling Errors

Hopefully, you’ve managed to avoid SQLite errors up to this point. But the time will come when you make a call that doesn’t make sense or simply won’t compile.

Handling error message when these things happen saves a lot of development time. It also gives you the opportunity to present meaningful error messages to your users.

To start, you need an error to handle. Add the following statement, which is intentionally malformed:

let malformedQueryString = "SELECT Stuff from Things WHERE Whatever;"

Now, add a function to execute this malformed statement:

func prepareMalformedQuery() {
  var malformedStatement: OpaquePointer?
  // 1
  if sqlite3_prepare_v2(db, malformedQueryString, -1, &malformedStatement, nil) 
      == SQLITE_OK {
    print("\nThis should not have happened.")
  } else {
    // 2
    let errorMessage = String(cString: sqlite3_errmsg(db))
    print("\nQuery is not prepared! \(errorMessage)")
  }
  
  // 3
  sqlite3_finalize(malformedStatement)
}

Here’s how you’re going to force an error:

1. Prepare the statement, which will fail and will NOT return SQLITE_OK.
2. Get the error message from the database using sqlite3_errmsg(). This returns a textual description of the most recent error. You then print the error to the console.
3. Finalize the statement.

Call the function to see the error message:

prepareMalformedQuery()

Run your playground. You’ll see the following output in your console:

Query is not prepared! no such table: Things

Well, that’s actually helpful — you obviously cannot run a SELECT statement on a table that doesn’t exist! Despite the intentional error that you created, you should use this example as a pattern for handling other errors from SQLite. Nice job!

Closing the Database Connection

When you’re done with a database connection, you’re responsible for closing it. But beware — there are a number of things you must do before you can successfully close your database, as described in the SQLite documentation.

Call the close function:

sqlite3_close(db)

Run your playground. You’ll see a status code of 0 on the results sidebar, which represents SQLITE_OK.

Great, your close call succeeded!

You’ve successfully created a database, added a table, added rows to the table, queried rows, updated rows and even deleted a row, all using the SQLite C APIs from Swift. Great job!

In the next section, you’ll see how to wrap some of these calls in Swift.

SQLite With Swift

As a Swift developer, you might feel a little uneasy about the first part of this tutorial. The good news is you can take the power of Swift and wrap those C routines to make things easier for yourself.

For this part of the SQLite with Swift tutorial, click the Making It Swift link at the bottom of the playground:

Wrapping Errors

Getting errors from the C API is a bit awkward as a Swift developer. Checking a result code and then calling another function doesn’t make sense in this new world. It would make more sense if functions that fail threw an error. Your next step will be to make that happen in your code.

Add the following:

enum SQLiteError: Error {
  case OpenDatabase(message: String)
  case Prepare(message: String)
  case Step(message: String)
  case Bind(message: String)
}

This is a custom Error enum that covers four of the main operations you’re using that can fail. Note how each case has an associated value to hold the error message.

Wrapping the Database Connection

Another not-so-Swifty aspect of the work you’ve done so far is those blasted OpaquePointer types. Your next step will be to take care of those.

Wrap up the database connection pointer in its own class, as shown below:

class SQLiteDatabase {
  private let dbPointer: OpaquePointer?
  private init(dbPointer: OpaquePointer?) {
    self.dbPointer = dbPointer
  }
  deinit {
    sqlite3_close(dbPointer)
  }
}

This looks much better. When you need a database connection, you create a reference to a more meaningful type of SQLiteDatabase rather than using an OpaquePointer.

You’ll notice the initializer is private. That’s because you don’t want your Swift developers passing in that OpaquePointer. Instead, you let them instantiate this class with a path to the database file.

Add the following static method to SQLiteDatabase:

static func open(path: String) throws -> SQLiteDatabase {
  var db: OpaquePointer?
  // 1
  if sqlite3_open(path, &db) == SQLITE_OK {
    // 2
    return SQLiteDatabase(dbPointer: db)
  } else {
    // 3
    defer {
      if db != nil {
        sqlite3_close(db)
      }
    }
    if let errorPointer = sqlite3_errmsg(db) {
      let message = String(cString: errorPointer)
      throw SQLiteError.OpenDatabase(message: message)
    } else {
      throw SQLiteError
        .OpenDatabase(message: "No error message provided from sqlite.")
    }
  }
}

Here’s what’s happening:

1. You attempt to open the database at the provided path.
2. If successful, you return a new instance of SQLiteDatabase.
3. Otherwise, you defer closing the database if the status code is anything but SQLITE_OK and throw an error.

Now you can create and open a database connection using much cleaner syntax.

Add the following outside the SQLiteDatabase class:

let db: SQLiteDatabase
do {
    db = try SQLiteDatabase.open(path: part2DbPath ?? "")
    print("Successfully opened connection to database.")
} catch SQLiteError.OpenDatabase(_) {
    print("Unable to open database.")
    PlaygroundPage.current.finishExecution()
}

Ah, much more Swift-like. Here, you wrap the attempt to open the database in a do-try-catch block and pass the error message from SQLite to the catch block, thanks to that custom enum you added earlier.

Run your playground and watch the console output. You’ll see the following:

Successfully opened connection to database.

Now, you can use and inspect the db instance as a proper and meaningful type.

Before moving on to writing methods that execute statements, it would be nice if SQLiteDatabase let you easily access SQLite error messages.

Add the following computed property to the SQLiteDatabase class:

fileprivate var errorMessage: String {
  if let errorPointer = sqlite3_errmsg(dbPointer) {
    let errorMessage = String(cString: errorPointer)
    return errorMessage
  } else {
    return "No error message provided from sqlite."
  }
}

Here, you’ve added a computed property, which simply returns the most recent error SQLite knows about. If there is no error, it returns a generic message stating as much.