Using SQLite in iOS app

In Software development, we need to persist the data in the application very often. However, we use data structures to manage and navigate data in the applications, but we may also need to store these structures for future use. In other words, data structures don’t reside in the memory once the app gets killed. We have to save the data structures somewhere to access them whenever required.

There are various solutions to store the structured data in the database like SQLite, Realm, CoreData, etc. In this tutorial, we will discuss how we can store and manage the data in the SQLite database. However, if we have used CoreData before, we have already used SQLite since CoreData is just a layer on the top of SQLite. In this tutorial, we will perform the following database operations in the iOS application.

Creating and connecting to a database.
Creating a table.
Insert, update, and delete rows.
Query the database to fetch results.

Getting Started

Let’s create a single-view iOS application as SQLiteDemo to start with SQLite operations.

Once we are done creating the project, open the Main.storyboard file and add a table view to the View Controller as shown in the below image.

To use the table view, we also need to create its outlet in the ViewController.swift class. Once we implement the delegate and datasource methods, the ViewController.swift contains the following code.

  import UIKit    class ViewController: UIViewController {          @IBOutlet weak var tableView: UITableView!            override func viewDidLoad() {          super.viewDidLoad()          // Do any additional setup after loading the view.          tableView.delegate = self          tableView.dataSource = self      }      }      extension ViewController : UITableViewDelegate{      func tableView(_ tableView: UITableView, heightForRowAt indexPath: IndexPath) -> CGFloat {          return UITableView.automaticDimension      }  }      extension  ViewController : UITableViewDataSource{      func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {          return 5      }      func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {          let cell = tableView.dequeueReusableCell(withIdentifier: “tableViewCell”) ?? UITableViewCell()          return cell      }  }  

Now, we will create a model class Employee. In our project, the Employee has the name, age, and id. For this purpose, create a new swift file and add the following code.

  import Foundation    class Employee  {            var name: String = “”      var age: Int = 0      var id: Int = 0            init(id:Int, name:String, age:Int)      {          self.id = id          self.name = name          self.age = age      }  }  

Now, we will create a class that contains the DB operations. We have named the class as DBManager. It includes the methods to create the database, create the table, insert in the table, read from the table, and performing the deletion. The DBManager contains the following code.

  import Foundation  import SQLite3      class DBManager  {      init()      {          db = openDatabase()          createTable()      }          let dbPath: String = “myDb.sqlite”      var db:OpaquePointer?          func openDatabase() -> OpaquePointer?      {          let filePath = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)              .appendingPathComponent(dbPath)          var db: OpaquePointer? = nil          if sqlite3_open(filePath.path, &db) != SQLITE_OK          {              debugPrint(“can’t open database”)              return nil          }          else          {              print(“Successfully created connection to database at (dbPath)”)              return db          }      }            func createTable() {          let createTableString = “CREATE TABLE IF NOT EXISTS person(Id INTEGER PRIMARY KEY,name TEXT,age INTEGER);”          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)      }                  func insert(id:Int, name:String, age:Int)      {          let persons = read()          for p in persons          {              if p.id == id              {                  return              }          }          let insertStatementString = “INSERT INTO person (Id, name, age) VALUES (?, ?, ?);”          var insertStatement: OpaquePointer? = nil          if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {              sqlite3_bind_int(insertStatement, 1, Int32(id))              sqlite3_bind_text(insertStatement, 2, (name as NSString).utf8String, -1, nil)              sqlite3_bind_int(insertStatement, 3, Int32(age))                            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)      }            func read() -> [Employee] {          let queryStatementString = “SELECT * FROM person;”          var queryStatement: OpaquePointer? = nil          var emps : [Employee] = []          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 name = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))                  let year = sqlite3_column_int(queryStatement, 2)                  emps.append(Employee(id: Int(id), name: name, age: Int(year)))                  print(“Query Result:”)                  print(“(id) | (name) | (year)”)              }          } else {              print(“SELECT statement could not be prepared”)          }          sqlite3_finalize(queryStatement)          return emps      }            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)      }        }  

Finally, we need to save the data in db in the ViewController. For this purpose, we will use DBManager class to save and retrieve data. The ViewController.swift contains the following code.

  import UIKit    class ViewController: UIViewController {          @IBOutlet weak var tableView: UITableView!      var db = DBManager()      var emps = Array()            override func viewDidLoad() {          super.viewDidLoad()          // Do any additional setup after loading the view.          tableView.delegate = self          tableView.dataSource = self                    db.insert(id: 1, name: “John”, age: 24)          db.insert(id: 2, name: “Mike”, age: 25)          db.insert(id: 3, name: “Harsh”, age: 23)          db.insert(id: 4, name: “Sachin”, age: 44)          db.insert(id: 5, name: “Rohit”, age: 32)          emps = db.read()                }      }      extension ViewController : UITableViewDelegate{      func tableView(_ tableView: UITableView, heightForRowAt indexPath: IndexPath) -> CGFloat {          return UITableView.automaticDimension      }  }      extension  ViewController : UITableViewDataSource{      func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -> Int {          return emps.count      }      func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> UITableViewCell {          let cell = tableView.dequeueReusableCell(withIdentifier: “tableViewCell”) ?? UITableViewCell()          cell.textLabel?.text = “Id: ” + emps[indexPath.row].id.description + “, Name: ” + emps[indexPath.row].name + “, age: ” + emps[indexPath.row].age.description          return cell      }  }  

The output is shown in the image below.

Next TopicCreating a real-time chat application using Firebase

Using SQLite in iOS app

Using SQLite in iOS app

Getting Started

Access Control in Swift iOS

Java Enum

You may also like