How to insert data in SQLite in swift iOS?

Question

I'm very new to SQLite and Swift. I have followed this tutorial https://medium.com/@imbilalhassan/saving-data-in-sqlite-db-in-ios-using-swift-4-76b743d3ce0e

but I want ID to auto-increment by SQLite.

// Model

struct PersonModel {
    
    let firstName: String?
    let lastName: String?
    let phone: String?
    let address: String?
    
}

// DBManager

import Foundation
import UIKit
import SQLite3

class DBManager
{
    init()
    {
        db = openDatabase()
        createTable()
    }

    let dbPath: String = "myDb.sqlite"
    var db:OpaquePointer?
    
    // MARK: - Open DataBase

    func openDatabase() -> OpaquePointer?
    {
        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent(dbPath)
        var db: OpaquePointer? = nil
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK
        {
            print("error opening database")
            return nil
        }
        else
        {
            print("Successfully opened connection to database at \(dbPath)")
            return db
        }
    }
    
    // MARK: - Create Table
    
    func createTable() {
        let createTableString = "CREATE TABLE IF NOT EXISTS person(id INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT, phone TEXT, address TEXT);"
        var createTableStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK
        {
            if sqlite3_step(createTableStatement) == SQLITE_DONE
            {
                print("person table created.")
            } else {
                print("person table could not be created.")
            }
        } else {
            print("CREATE TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createTableStatement)
    }
    
    // MARK: - Insert
    
    func insert(firstName: String, lastName: String, phone: String, address: String)
    {
        
        let insertStatementString = "INSERT INTO person (id, firstName, lastName, phone, address) VALUES (?, ?, ?, ?, ?);"
        var insertStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(insertStatement, 1, (firstName as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, (lastName as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, (phone as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 4, (address as NSString).utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
    }
    
    // MARK: - Read
    
    func read() -> [PersonModel] {
        let queryStatementString = "SELECT * FROM person;"
        var queryStatement: OpaquePointer? = nil
        var psns : [PersonModel] = []
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let id = sqlite3_column_int(queryStatement, 0)
                let firstName = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
                let lastName = String(describing: String(cString: sqlite3_column_text(queryStatement, 2)))
                let phone = String(describing: String(cString: sqlite3_column_text(queryStatement, 3)))
                let address = String(describing: String(cString: sqlite3_column_text(queryStatement, 4))) // this is where i'm getting error: Fatal error: Unexpectedly found nil while implicitly unwrapping an Optional value
                
                psns.append(PersonModel(firstName: firstName, lastName: lastName, phone: phone, address: address))
                print("Query Result:")
                print("\(id) | \(firstName) | \(lastName) | \(phone) | \(address)")
            }
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return psns
    }
    
    // MARK: - Delete
    
    func deleteByID(id:Int) {
        let deleteStatementStirng = "DELETE FROM person WHERE Id = ?;"
        var deleteStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, deleteStatementStirng, -1, &deleteStatement, nil) == SQLITE_OK {
            sqlite3_bind_int(deleteStatement, 1, Int32(id))
            if sqlite3_step(deleteStatement) == SQLITE_DONE {
                print("Successfully deleted row.")
            } else {
                print("Could not delete row.")
            }
        } else {
            print("DELETE statement could not be prepared")
        }
        sqlite3_finalize(deleteStatement)
    }
    
}

// ViewController

class ViewController: UIViewController {
    
    @IBOutlet weak var txtFirstName: UITextField!
    @IBOutlet weak var txtLastName: UITextField!
    @IBOutlet weak var txtPhoneNumber: UITextField!
    @IBOutlet weak var txtAddress: UITextField!
    @IBOutlet weak var btnSave: UIButton!
    
    var db: DBManager = DBManager()
    var persons: [PersonModel] = []

    override func viewDidLoad() {
        super.viewDidLoad()
        
    }
    
    func setUpDataBase() {
        
        db.insert(firstName: txtFirstName.text ?? "", lastName: txtLastName.text ?? "", phone: txtPhoneNumber.text ?? "", address: txtAddress.text ?? "")
        
        persons = db.read()
        
    }
    
    // MARK: - Button Save Event

    @IBAction func btnSave_Event(_ sender: UIButton) {
        
        setUpDataBase()
    }
}

when I insert data in SQLite, in func read(), line:

let address = String(describing: String(cString: sqlite3_column_text(queryStatement, 4)))

is throwing error:

Fatal error: Unexpectedly found nil while implicitly unwrapping an Optional value.

I'm adding value in address textField and still getting this error, also I have no idea if this is right approach for auto-increment ID. could anyone help me out ?

UPDATE

as per @Joakim Danielson suggestion I solved the issue:

// MARK: - Insert
    
    func insert(firstName: String, lastName: String, phone: String, address: String)
    {
        
        let insertStatementString = "INSERT INTO person (firstName, lastName, phone, address) VALUES (?, ?, ?, ?);"
        var insertStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(insertStatement, 1, (firstName as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, (lastName as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, (phone as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 4, (address as NSString).utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
    }
    
    // MARK: - Read
    
    func read() -> [PersonModel] {
        let queryStatementString = "SELECT * FROM person;"
        var queryStatement: OpaquePointer? = nil
        var psns : [PersonModel] = []
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let id = sqlite3_column_int(queryStatement, 0)
                let firstName = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
                let lastName = String(describing: String(cString: sqlite3_column_text(queryStatement, 2)))
                let phone = String(describing: String(cString: sqlite3_column_text(queryStatement, 3)))
                let address = String(describing: String(cString: sqlite3_column_text(queryStatement, 4)))
                
                psns.append(PersonModel(id: Int(id), firstName: firstName, lastName: lastName, phone: phone, address: address))
                print("Query Result:")
                print("\(id) | \(firstName) | \(lastName) | \(phone) | \(address)")
            }
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return psns
    }

In the bind text I use : stringVar.cString(using: .utf8) to convert Swift String to null terminated String. — Ptit Xav
– Ptit Xav, Commented May 28, 2023 at 12:22

Joakim Danielson · Accepted Answer · 2023-01-06 16:24:52Z

3

You should not include the id attribute in your SQL insert statement since it will be automatically handled by the db engine. So change your insert statement to

let insertStatementString = "INSERT INTO person (firstName, lastName, phone, address) VALUES (?, ?, ?, ?);"

When I did this I could run your database related code in a playground and reading values successfully without any runtime error. If you are still getting Unexpectedly found nil while implicitly unwrapping an Optional value error then there is something else that is incorrect, perhaps related to your view controller.

answered Jan 6, 2023 at 16:24

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user11717481 · Accepted Answer · 2023-01-07 15:13:36Z

I would recoomend to use Swift package manager to install SQlite3 module by writing the following to your package file:

// Package.swift 
dependencies: [
    .package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.14.1")
]

Build your project: $ swift build

Using a third-party library to manage your database is a common way to access data stored in a Database, it allow you to perform any operation (insert, read, delete) without using string statements, this through by differents methods such as Table, prepare, insert, filter, etc... One of the key features is that you can set different types of fields depending on your data (string, numbers, datetime, arrays) and create any required model structure.

struct PersonModel {
    
    let firstName: String?
    let lastName: String?
    let phone: String?
    let address: String?
}

import Foundation
import UIKit
import SQLite3

class DBManager
{
    init()
    {
        db = openDatabase()
        createTable()
    }

    let dbPath: String = "myDb.sqlite"
    var db:OpaquePointer?

    func openDatabase() -> OpaquePointer?
    {
        let fileURL = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0]
        var db = try? Connection(dbPath)
    }
    func createTable() {
        let createTableString = Table("person")
        let id = Expression<Int64>("id")
        let firstName = Expression<String?>("firstname")
        let lastName = Expression<String>("lastname")
        let phone = Expression<String>("phone")
        let address = Expression<String>("address")

        try! db?.run(users.create(ifNotExists: true, block: { (table) in
            table.column(id, primaryKey: true)
            table.column(firstName)
            table.column(lastName)
            table.column(phone, unique: true)
            table.column(address)
        }))
    }

    func insert(firstName: String, lastName: String, phone: String, address: String, person: Table)
    {
        let insert = person.insert("firstname" <- firstName, "lastname" <- lastName, "phone" <- phone, "address" <- address)
        let rowid = (try! db?.run(insert))!
    }
    
    func read(person: Table) -> [PersonModel] {
        var psns : [PersonModel] = []
        for user in (try! db?.prepare(person))! {
            print("Query :id: \(user[id]), name: \(user[firstname]), second name: \(user[lastname]), phone: \(user[phone]), address: \(user[address])")
        }
        return psns
    }
    
    func deleteByID(id:Int, person: Table) {
        try! db?.run(person.filter(id).delete())
    }
    
}

note: method used to create a database table createTable is equivalent to this sentence

CREATE TABLE IF NOT EXISTS "users" (
    "id" INTEGER PRIMARY KEY NOT NULL,
    "firstname" TEXT,
    "lastname" TEXT
    "phone" TEXT NOT NULL UNIQUE
    "address" TEXT
)

thank u for your time! I have to use minimum third party integration in my app. but I appreciate your suggestion

Collectives™ on Stack Overflow

How to insert data in SQLite in swift iOS?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related