Swift + SQLite: Delete Data (DELETE)

In this article, we'll explain how to delete data from a SQLite table in Swift.

We'll add code for deleting data to the example project from Swift - Fetch Data from SQLite (SELECT).

Table of Contents

Defining a Function to Delete Data from the Students Table
Deleting Data from the Students Table

Defining a Function to Delete Data from the Students Table

So far, we've inserted, updated, and fetched data in the students table, which stores Student struct data like this:

struct Student {
    var StudentID: Int
    var StudentNumber: String
    var FirstName: String
    var LastName: String
    var Age: Int?
    
    init(studentID: Int, studentNumber: String, firstName: String, lastName: String, age: Int?) {
        self.StudentID = studentID
        self.StudentNumber = studentNumber
        self.FirstName = firstName
        self.LastName = lastName
        self.Age = age
    }
}

CREATE TABLE IF NOT EXISTS students (
    student_id INTEGER NOT NULL PRIMARY KEY,
    student_number TEXT NOT NULL,
    first_name TEXT NULL,
    last_name TEXT NULL,
    age INTEGER NULL
);

Now we'll create a deleteStudent function that takes a studentID and deletes the matching record from the students table.

We assume the specified studentID exists in the table.

Open DBService.swift and add the following code:

func deleteStudent(studentID: Int) -> Bool {
    let deleteSql = "DELETE FROM students WHERE student_id = ?;";
    var deleteStmt: OpaquePointer? = nil
    
    if sqlite3_prepare_v2(db, (deleteSql as NSString).utf8String, -1, &deleteStmt, nil) != SQLITE_OK {
        print("db error: \(getDBErrorMessage(db))")
        return false
    }
    
    sqlite3_bind_int(deleteStmt, 1, Int32(studentID))
    
    if sqlite3_step(deleteStmt) != SQLITE_DONE {
        print("db error: \(getDBErrorMessage(db))")
        sqlite3_finalize(deleteStmt)
        return false
    }

    sqlite3_finalize(deleteStmt)
    return true
}

Swift - SQLite Delete Data (DELETE) 1

Let's go through the deleteStudent function step by step:

Line 2 defines the DELETE SQL statement in deleteSql.

As with other SQLite operations, SQL statements must be compiled into bytecode before execution. We use the sqlite3_prepare_v2() function here:

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

Line 3 defines deleteStmt, the statement handle passed to sqlite3_prepare_v2().

Line 5 executes sqlite3_prepare_v2(), passing the database handle, the UTF-8 converted deleteSql, and &deleteStmt.

If sqlite3_prepare_v2() succeeds, it returns SQLITE_OK. On failure, we print the error with getDBErrorMessage() and return false.

Line 10 binds the function argument studentID to the ? placeholder using sqlite3_bind_int().

Lines 12–16 execute the compiled SQL statement with sqlite3_step(). If successful, it returns SQLITE_DONE. On failure, we print the error, finalize the statement, and return false.

Note: If the record with the given student_id does not exist, it won't throw an error — the DELETE simply affects zero rows.

Lines 18–19 finalize the statement with sqlite3_finalize() and return true.

Deleting Data from the Students Table

Now let's use the deleteStudent() function to remove data.

Update ViewController.swift like this:

//var student1 = Student(studentID: 1, studentNumber: "S000001", firstName: "Yuta", lastName: "Tanaka", age: 16)
//
//if DBService.shared.insertStudent(student: student1) {
//    print("Insert success")
//} else {
//    print("Insert Failed")
//}

//student1.LastName = "Yamada"
//student1.Age = 17
//
//if DBService.shared.updateStudent(student: student1) {
//    print("Update success")
//} else {
//    print("Update Failed")
//}

//let (success, errorMessage, student) = DBService.shared.getStudent(studentId: 2)
//if(success){
//    if let student = student {
//        print(student)
//    } else {
//        print("Student not found")
//    }
//} else {
//    print(errorMessage ?? "Error")
//}

if DBService.shared.deleteStudent(studentID: 1) {
    print("Delete success")
} else {
    print("Delete Failed")
}

Line 29 calls our new deleteStudent() function, passing studentID: 1 to delete the record.

If the deletion succeeds, the debugger console shows:

Opened connection to database
Delete success

Swift - SQLite Delete Data (DELETE) 2

You can verify the result by opening the SQLite file in DB Browser for SQLite. For details on locating the file, see How to Find the SQLite File Location.

In the database, the record with student_id = 1 has been deleted:

Swift - SQLite Delete Data (DELETE) 3

Swift - SQLite Delete Data (DELETE) 4

That's it — we've explained how to delete data from a SQLite table in Swift.