SQLite With Swift Tutorial: Getting Started

Inserting Data

Add the following SQL statement:

let insertStatementString = "INSERT INTO Contact (Id, Name) VALUES (?, ?);"

This might look strange if you haven’t had much SQL experience. Why are the values represented by question marks?

Earlier, you used sqlite3_prepare_v2() to compile your statement. The ? syntax tells the compiler that you’ll provide real values when you actually execute the statement.

This lets you compile statements ahead of time, which improves performance since compilation is a costly operation. You can then reuse the compiled statements with different values.

Next, create the following function:

func insert() {
  var insertStatement: OpaquePointer?
  // 1
  if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == 
      SQLITE_OK {
    let id: Int32 = 1
    let name: NSString = "Ray"
    // 2
    sqlite3_bind_int(insertStatement, 1, id)
    // 3
    sqlite3_bind_text(insertStatement, 2, name.utf8String, -1, nil)
    // 4
    if sqlite3_step(insertStatement) == SQLITE_DONE {
      print("\nSuccessfully inserted row.")
    } else {
      print("\nCould not insert row.")
    }
  } else {
    print("\nINSERT statement is not prepared.")
  }
  // 5
  sqlite3_finalize(insertStatement)
}

Here’s how the function above works:

The first parameter of the function is the statement to bind to, while the second is a non-zero-based index for the position of the ? you’re binding to. The third and final parameter is the value itself. This binding call returns a status code, but for now, you assume that it succeeds.

1. First, compile the statement and verify that all is well.
2. Here, you define a value for the ? placeholder. The function’s name — sqlite3_bind_int() — implies you’re binding an Int to the statement.

   The first parameter of the function is the statement to bind to, while the second is a non-zero-based index for the position of the ? you’re binding to. The third and final parameter is the value itself. This binding call returns a status code, but for now, you assume that it succeeds.

3. Perform the same binding process, but this time for a text value. There are two additional parameters on this call. For the purposes of this tutorial, simply pass -1 and nil for them.
4. Use sqlite3_step() to execute the statement and verify that it finished.
5. Finalize the statement. If you were going to insert multiple contacts, you’d retain the statement and reuse it with different values.

Next, call your new function:

insert()

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

Successfully inserted row.

Challenge: Multiple Inserts

Challenge time! Your task is to update insert() to insert an array of contacts.

As a hint, you’ll need to reset your compiled statement to its initial state by calling sqlite3_reset() before you execute it again.

[spoiler title=”Solution — Insert multiple rows”]

func insert() {
  var insertStatement: OpaquePointer?
  // 1
  let names: [NSString] = ["Ray", "Chris", "Martha", "Danielle"]
  if sqlite3_prepare_v2(
    db, 
    insertStatementString, 
    -1, 
    &insertStatement, 
    nil
  ) == SQLITE_OK {
    print("\n")
    // 2
    for (index, name) in names.enumerated() {
      // 3
      let id = Int32(index + 1)
      sqlite3_bind_int(insertStatement, 1, id)
      sqlite3_bind_text(insertStatement, 2, name.utf8String, -1, nil)
      if sqlite3_step(insertStatement) == SQLITE_DONE {
        print("Successfully inserted row.")
      } else {
        print("Could not insert row.")
      }
      // 4
      sqlite3_reset(insertStatement)
    }
    sqlite3_finalize(insertStatement)
  } else {
    print("\nINSERT statement is not prepared.")
  }
}

As you can see, this is similar to the code you already had, with these notable differences:

1. You have an array of contacts now, rather than a single constant.
2. The array enumerates once for each contact.
3. You now generate the index from the index of the enumeration, which corresponds to the placement of the contact’s name in the array.
4. The SQL statement resets at the end of each enumeration so that next one can use it.

[/spoiler]

Great, you’ve now mastered adding data to your database. In the next section of this tutorial, you’ll learn how to access that data when you need it.

Querying Contacts

Now that you’ve inserted some rows, it’d be nice to verify they’re really there. :]

Add the following to the playground:

let queryStatementString = "SELECT * FROM Contact;"

This query retrieves all records from the contact table. Using a * returns all columns.

Now, add the following function to the query:

func query() {
  var queryStatement: OpaquePointer?
  // 1
  if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == 
      SQLITE_OK {
    // 2
    if sqlite3_step(queryStatement) == SQLITE_ROW {
      // 3
      let id = sqlite3_column_int(queryStatement, 0)
      // 4
      guard let queryResultCol1 = sqlite3_column_text(queryStatement, 1) else {
        print("Query result is nil")
        return
      }
      let name = String(cString: queryResultCol1)
      // 5
      print("\nQuery Result:")
      print("\(id) | \(name)")
  } else {
      print("\nQuery returned no results.")
  }
  } else {
      // 6
    let errorMessage = String(cString: sqlite3_errmsg(db))
    print("\nQuery is not prepared \(errorMessage)")
  }
  // 7
  sqlite3_finalize(queryStatement)
}

Taking each numbered comment in turn:

1. You prepare the statement.
2. Next, you execute the statement. Note that you’re now checking for the status code SQLITE_ROW, which means that you retrieved a row when you stepped through the result.
3. Now, you read the values from the returned row. Given what you know about the table’s structure and your query, you can access the row’s values column-by-column. The first column is an Int, so you use sqlite3_column_int() and pass in the statement and a zero-based column index. You assign the returned value to the locally-scoped id constant.
4. Here, you fetch the text value from the Name column. This is a bit messy due to the C API. First, you capture the value as queryResultCol1 so you can convert it to a proper Swift string on the next line.
5. Print the results.
6. Print an error, if any. Want to get adventurous? Change the table name in queryStatementString and see what happens.
7. You finalize the statement.

Now, call your new function:

query()

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

Query Result:
1 | Ray

WOOt! It looks like your data made it to the database after all, unless you got adventurous. :]

Challenge: Printing Every Row

Your task is to update query() to print out every contact in the table.

[spoiler title=”Solution — Print all contacts”]

func query() {
  var queryStatement: OpaquePointer?
  if sqlite3_prepare_v2(
    db, 
    queryStatementString, 
    -1, 
    &queryStatement, 
    nil
  ) == SQLITE_OK {
    print("\n")
    while (sqlite3_step(queryStatement) == SQLITE_ROW) {
      let id = sqlite3_column_int(queryStatement, 0)
      guard let queryResultCol1 = sqlite3_column_text(queryStatement, 1) else {
        print("Query result is nil.")
        return
      }
      let name = String(cString: queryResultCol1)
      print("Query Result:")
      print("\(id) | \(name)")
    }
  } else {
      let errorMessage = String(cString: sqlite3_errmsg(db))
      print("\nQuery is not prepared \(errorMessage)")
  }
  sqlite3_finalize(queryStatement)
}

Note that instead of using a single step to retrieve the first row as you did before, you use a while loop to execute the step, which will happen as long as the return code is SQLITE_ROW. When you reach the last row, the return code will be SQLITE_DONE, breaking the loop.
[/spoiler]

So now, you have created a database and can add contacts to it. But what if you need to make changes to any of those contacts? You’ll tackle that issue in the next section.