Swift + SQLite: Insert Data (INSERT)

In this article, we'll go over how to insert data into a SQLite table using Swift.

We'll build on the code from How to Create a SQLite Table in Swift by adding the logic for inserting data into SQLite.

Defining a Function to Insert Data into the Students Table

Previously, we created a students table designed to store Student struct data:

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, let's define an insertStudent function that takes a Student object and inserts it into the students table.

Open DBService.swift and add the following code:

func insertStudent(student: Student) -> Bool {
    let insertSql = """
                    INSERT INTO students
                        (student_id, student_number, first_name, last_name, age)
                        VALUES
                        (?, ?, ?, ?, ?);
                    """;
    var insertStmt: OpaquePointer? = nil
    
    if sqlite3_prepare_v2(db, (insertSql as NSString).utf8String, -1, &insertStmt, nil) != SQLITE_OK {
        print("db error: \(getDBErrorMessage(db))")
        return false
    }
    
    sqlite3_bind_int(insertStmt, 1, Int32(student.StudentID))
    sqlite3_bind_text(insertStmt, 2, (student.StudentNumber as NSString).utf8String, -1, nil)
    sqlite3_bind_text(insertStmt, 3, (student.FirstName as NSString).utf8String, -1, nil)
    sqlite3_bind_text(insertStmt, 4, (student.LastName as NSString).utf8String, -1, nil)
    
    if student.Age == nil {
        sqlite3_bind_null(insertStmt, 5)
    } else {
        sqlite3_bind_int(insertStmt, 5, Int32(student.Age!))
    }
    
    if sqlite3_step(insertStmt) != SQLITE_DONE {
        print("db error: \(getDBErrorMessage(db))")
        sqlite3_finalize(insertStmt)
        return false
    }
    sqlite3_finalize(insertStmt)
    return true
}

Let's go through the code of the insertStudent function step by step.

First, in lines 2 to 7, we define the SQLite INSERT statement script in insertSql.

To execute an SQL statement in SQLite, it must first be compiled into a bytecode program using a preparation function.

Here, we use the sqlite3_prepare_v2() function to compile the SQL statement.

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 */
);

In line 8, we define the variable insertStmt of type OpaquePointer?, which will be used as the statement handle to pass to the sqlite3_prepare_v2() function.

In line 10, we call the sqlite3_prepare_v2() function, passing the db handle obtained when we opened the database, the insertSql string converted to UTF-8, and &insertStmt defined above, among other parameters.

The sqlite3_prepare_v2() function returns SQLITE_OK if successful, and an error code if it fails.

If it fails, we use the getDBErrorMessage() function to print the error code and return false.

In lines 15 to 24, we bind values to the prepared statement.

To bind a string, use sqlite3_bind_text(), and to bind an integer, use sqlite3_bind_int(). Different functions are used depending on the data type.

In line 15, we use sqlite3_bind_int() to bind student.StudentID, converted to Int32, to the first ? placeholder in the insertSql statement.

In line 16, we use sqlite3_bind_text() to bind student.StudentNumber, converted to UTF-8, to the second ? placeholder in the insertSql statement.

Similarly, in lines 17 and 18, we use sqlite3_bind_text() to bind student.FirstName and student.LastName to the third and fourth ? placeholders.

In lines 20 to 24, if student.Age is null, we use sqlite3_bind_null() to bind null to the fifth ? placeholder; otherwise, we use sqlite3_bind_int() to bind student.Age.

In lines 26 to 30, we use the sqlite3_step() function to evaluate and execute the compiled SQL statement.

The sqlite3_step() function returns SQLITE_DONE if successful.

If it fails, we use the getDBErrorMessage() function to print the error code.

Before returning, we use the sqlite3_finalize() function to destroy the prepared statement object.

In lines 31 to 32, we finalize the prepared statement object using sqlite3_finalize(), and then return true.

Inserting Data into the Students Table

Now, let's use the insertStudent() function we created to insert data.

Add the following code to viewDidLoad() in ViewController.swift.

let 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")
}

In line 1, we create a Student object named student1.

In lines 3 to 7, we call the function DBService.shared.insertStudent() with student1 to insert the data.

If the insertion succeeds, the output console of the debugger will show the following:

Opened connection to database
Insert success

Run the program in the simulator and then open the created SQLite file with DB Browser for SQLite to check the result.

You can check the location of the SQLite file in “How to Find the SQLite File Location”.

We will introduce DB Browser for SQLite in a separate article later.

You will see that the students table has been created, and one row of data has been inserted.

That's it for how to insert data into a SQLite table in Swift.

Next, we will update data in SQLite using Swift.

Next Article: “Swift - Update Data in SQLite (UPDATE)”