Create, Read, Update and Delete from SQLite - Swift & Swift UI

Create, Read, Update and Delete from SQLite – Swift & Swift UI

SQLite is used to store the data locally inside the app in SQL structure. It is a relational local database. You can use this database to store data inside the app using Swift and Swift UI.

We will be creating a simple iOS app in Swift UI to create, read, update, and delete users from the SQLite database.

We will be using a library called SQLite by Stephen Celis. To install this library, you must have Cocoapods installed in your system.

Installation

You can install Cocoapods in your system by simply running the following command in your terminal:

sudo gem install cocoapods

First, you need to open a command prompt (Terminal) at the root of your XCode project and run the following command in it:

pod init

Now you will see a new file created at the root of your project named Podfile. Open that file in your text editor and add the line to install the library. Following will be the content of your Podfile:

# Uncomment the next line to define a global platform for your project
# platform :ios, '9.0'

target 'SQLite_Database' do
  # Comment the next line if you don't want to use dynamic frameworks
  use_frameworks!

  # Pods for SQLite_Database
  pod 'SQLite.swift', '~> 0.12.0'

end

After that, run the following command in your Terminal to install this library:

pod update

Once installed, close your XCode and re-open but this time double-click on the file which has an extension “.xcworkspace“. This file will be created only after the pod has been installed/updated.

1. Add user

First, we will create a model class that will hold the data structure for each user. Create a file named “UserModel.swift” and paste the following code in it:

//
//  UserModel.swift
//  SQLite_Database
//
//  Created by Adnan Afzal on 24/11/2020.
//  Copyright © 2020 Adnan Afzal. All rights reserved.
//

import Foundation

class UserModel: Identifiable {
    public var id: Int64 = 0
    public var name: String = ""
    public var email: String = ""
    public var age: Int64 = 0
}

Your model class must conform to Identifiable protocol in order to show the users in List view.

To handle the database functions, we will create a separate file named “DB_Manager.swift“. In this class, we will create the database, tables and it’s columns in the constructor.

//
//  DB_Manager.swift
//  SQLite_Database
//
//  Created by Adnan Afzal on 24/11/2020.
//  Copyright © 2020 Adnan Afzal. All rights reserved.
//

import Foundation

// import library
import SQLite

class DB_Manager {
    
    // sqlite instance
    private var db: Connection!
    
    // table instance
    private var users: Table!

    // columns instances of table
    private var id: Expression<Int64>!
    private var name: Expression<String>!
    private var email: Expression<String>!
    private var age: Expression<Int64>!
    
    // constructor of this class
    init () {
        
        // exception handling
        do {
            
            // path of document directory
            let path: String = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first ?? ""

            // creating database connection
            db = try Connection("\(path)/my_users.sqlite3")
            
            // creating table object
            users = Table("users")
            
            // create instances of each column
            id = Expression<Int64>("id")
            name = Expression<String>("name")
            email = Expression<String>("email")
            age = Expression<Int64>("age")
            
            // check if the user's table is already created
            if (!UserDefaults.standard.bool(forKey: "is_db_created")) {

                // if not, then create the table
                try db.run(users.create { (t) in
                    t.column(id, primaryKey: true)
                    t.column(name)
                    t.column(email, unique: true)
                    t.column(age)
                })
                
                // set the value to true, so it will not attempt to create the table again
                UserDefaults.standard.set(true, forKey: "is_db_created")
            }
            
        } catch {
            // show error message if any
            print(error.localizedDescription)
        }
        
    }
}

A comment has been added with each line for clarification. Now, in your content view, we will create a navigation link which will move to the AddUserView file.

Open your content view file file and place this code inside the body:

// create navigation view
NavigationView {

    VStack {

        // create link to add user
        HStack {
            Spacer()
            NavigationLink (destination: AddUserView(), label: {
                Text("Add user")
            })
        }

        // list view goes here

    }.padding()
    .navigationBarTitle("SQLite")
}

Now, create a new view file named “AddUserView.swift” and paste the following code in it:

//
//  AddUserView.swift
//  SQLite_Database
//
//  Created by Adnan Afzal on 24/11/2020.
//  Copyright © 2020 Adnan Afzal. All rights reserved.
//

import SwiftUI

struct AddUserView: View {
    
    // create variables to store user input values
    @State var name: String = ""
    @State var email: String = ""
    @State var age: String = ""
    
    // to go back on the home screen when the user is added
    @Environment(\.presentationMode) var mode: Binding<PresentationMode>
    
    var body: some View {
        
        VStack {
            // create name field
            TextField("Enter name", text: $name)
                .padding(10)
                .background(Color(.systemGray6))
                .cornerRadius(5)
                .disableAutocorrection(true)
            
            // create email field
            TextField("Enter email", text: $email)
                .padding(10)
                .background(Color(.systemGray6))
                .cornerRadius(5)
                .keyboardType(.emailAddress)
                .autocapitalization(.none)
                .disableAutocorrection(true)
            
            // create age field, number pad
            TextField("Enter age", text: $age)
                .padding(10)
                .background(Color(.systemGray6))
                .cornerRadius(5)
                .keyboardType(.numberPad)
                .disableAutocorrection(true)
            
            // button to add a user
            Button(action: {
                // call function to add row in sqlite database
                DB_Manager().addUser(nameValue: self.name, emailValue: self.email, ageValue: Int64(self.age) ?? 0)
                
                // go back to home page
                self.mode.wrappedValue.dismiss()
            }, label: {
                Text("Add User")
            })
                .frame(maxWidth: .infinity, alignment: .trailing)
                .padding(.top, 10)
                .padding(.bottom, 10)
        }.padding()
        
    }
}

This is a bit lengthy but comments have been added with each line for an explanation.

At this point, you will see an error in the highlighted line. Because we have to create that function in our DB manager class. So, create the following function in your DB_Manager.swift file:

public func addUser(nameValue: String, emailValue: String, ageValue: Int64) {
    do {
        try db.run(users.insert(name <- nameValue, email <- emailValue, age <- ageValue))
    } catch {
        print(error.localizedDescription)
    }
}

If you run the app now, you will a link to add user view, on that view you will see 3 fields. After filling the fields and hitting the submit button, the data will be saved in SQLite database and you will be moved to home view.

However, you will not see the data that has been saved in database. So now we will show the data in List view.

2. View all users

In your content view, where you want to show all the data from database, create a state property wrapper:

// array of user models
@State var userModels: [UserModel] = []

Now, create a list view in the content view body after the navigation link for add user view:

// create list view to show all users
List (self.userModels) { (model) in

    // show name, email and age horizontally
    HStack {
        Text(model.name)
        Spacer()
        Text(model.email)
        Spacer()
        Text("\(model.age)")
        Spacer()

        // edit and delete button goes here
    }
}

When the content view is loaded, we need to add the data from the database in that userModels array. Attach an onAppear function at the end of your VStack like below:

VStack {
    ...
}
// load data in user models array
.onAppear(perform: {
    self.userModels = DB_Manager().getUsers()
})

Now, we need to create a function named getUsers() in our DB manager class:

// return array of user models
public func getUsers() -> [UserModel] {
    
    // create empty array
    var userModels: [UserModel] = []

    // get all users in descending order
    users = users.order(id.desc)

    // exception handling
    do {

        // loop through all users
        for user in try db.prepare(users) {

            // create new model in each loop iteration
            let userModel: UserModel = UserModel()

            // set values in model from database
            userModel.id = user[id]
            userModel.name = user[name]
            userModel.email = user[email]
            userModel.age = user[age]

            // append in new array
            userModels.append(userModel)
        }
    } catch {
        print(error.localizedDescription)
    }

    // return array
    return userModels
}

This will fetch the users from the SQLite database in descending order (latest users first) and return them as an array of user model classes.

Run the app and now you will see the users that has been added in a tabular format. You can try by removing the app from background and open again, the data will still be there. Data will only be removed when the app is un-installed.

3. Edit user

Editing the user requires multiple steps:

  1. Get user by ID.
  2. Show user details in input fields.
  3. Update the user in database using his ID.

First, we need to create 2 state variables at the top of the content view which will tell WHEN the user is selected for editing and WHICH user is selected.

// check if user is selected for edit
@State var userSelected: Bool = false

// id of selected user to edit or delete
@State var selectedUserId: Int64 = 0

Now, in your HStack inside the list view, create a button to edit the user:

// button to edit user
Button(action: {
    self.selectedUserId = model.id
    self.userSelected = true
}, label: {
    Text("Edit")
        .foregroundColor(Color.blue)
    })
    // by default, buttons are full width.
    // to prevent this, use the following
    .buttonStyle(PlainButtonStyle())

This will set the userSelected variable to true, and selectedUserId to the ID of the user you have selected. Now we need to move to the new view to edit the user.

Create a navigation link with an empty view, inside the content view body and before the list view:

// navigation link to go to edit user view
NavigationLink (destination: EditUserView(id: self.$selectedUserId), isActive: self.$userSelected) {
    EmptyView()
}

Create a new view file named EditUserView.swift and paste the following code in it. Pay close attention to the highlighted lines:

//
//  EditUserView.swift
//  SQLite_Database
//
//  Created by Adnan Afzal on 24/11/2020.
//  Copyright © 2020 Adnan Afzal. All rights reserved.
//

import SwiftUI

struct EditUserView: View {
    
    // id receiving of user from previous view
    @Binding var id: Int64
    
    // variables to store value from input fields
    @State var name: String = ""
    @State var email: String = ""
    @State var age: String = ""
    
    // to go back to previous view
    @Environment(\.presentationMode) var mode: Binding<PresentationMode>
    
    var body: some View {
        VStack {
            // create name field
            TextField("Enter name", text: $name)
                .padding(10)
                .background(Color(.systemGray6))
                .cornerRadius(5)
                .disableAutocorrection(true)
            
            // create email field
            TextField("Enter email", text: $email)
                .padding(10)
                .background(Color(.systemGray6))
                .cornerRadius(5)
                .keyboardType(.emailAddress)
                .autocapitalization(.none)
                .disableAutocorrection(true)
            
            // create age field, number pad
            TextField("Enter age", text: $age)
                .padding(10)
                .background(Color(.systemGray6))
                .cornerRadius(5)
                .keyboardType(.numberPad)
                .disableAutocorrection(true)
            
            // button to update user
            Button(action: {
                // call function to update row in sqlite database
                DB_Manager().updateUser(idValue: self.id, nameValue: self.name, emailValue: self.email, ageValue: Int64(self.age) ?? 0)

                // go back to home page
                self.mode.wrappedValue.dismiss()
            }, label: {
                Text("Edit User")
            })
                .frame(maxWidth: .infinity, alignment: .trailing)
                .padding(.top, 10)
                .padding(.bottom, 10)
        }.padding()

        // populate user's data in fields when view loaded
        .onAppear(perform: {
            
            // get data from database
            let userModel: UserModel = DB_Manager().getUser(idValue: self.id)
            
            // populate in text fields
            self.name = userModel.name
            self.email = userModel.email
            self.age = String(userModel.age)
        })
    }
}

struct EditUserView_Previews: PreviewProvider {
    
    // when using @Binding, do this in preview provider
    @State static var id: Int64 = 0
    
    static var previews: some View {
        EditUserView(id: $id)
    }
}

@Binding is used when you want to pass the value from one view to another. In that case, if you are using preview provider, then you have to create the @State variable in it.

At this time, you will be getting error at DB_Manager().updateUser and DB_Manager().getUser because these functions are not yet created in DB manager class.

First, we will create the function to get the single user from SQLite database using his ID. In your DB_Manager.swift:

// get single user data
public func getUser(idValue: Int64) -> UserModel {

    // create an empty object
    let userModel: UserModel = UserModel()
    
    // exception handling
    do {

        // get user using ID
        let user: AnySequence<Row> = try db.prepare(users.filter(id == idValue))

        // get row
        try user.forEach({ (rowValue) in

            // set values in model
            userModel.id = try rowValue.get(id)
            userModel.name = try rowValue.get(name)
            userModel.email = try rowValue.get(email)
            userModel.age = try rowValue.get(age)
        })
    } catch {
        print(error.localizedDescription)
    }

    // return model
    return userModel
}

The filter function at the highlighted line helps to perform the WHERE clause (to filter the data).

Now we need to create a function in the DB manager class to update the user in the SQLite database.

// function to update user
public func updateUser(idValue: Int64, nameValue: String, emailValue: String, ageValue: Int64) {
    do {
        // get user using ID
        let user: Table = users.filter(id == idValue)
        
        // run the update query
        try db.run(user.update(name <- nameValue, email <- emailValue, age <- ageValue))
    } catch {
        print(error.localizedDescription)
    }
}

Here, we are first getting the user object using the filter() function. Then we are running the update query on that user’s object.

Run the app and now you will see an “Edit” button at the end of each user’s record. Clicking on that will take you to another view, from where you will see the user’s data in input fields. Clicking the “Update” button will update the data and move back to the home view.

4. Delete user

To delete a user from the SQLite database, first, we will create a delete button in the list view like we created the edit button.

// create list view to show all users
List (self.userModels) { (model) in

    // show name, email, and age horizontally
    HStack {
        ... edit button
        
        // button to delete user
        Button(action: {

            // create db manager instance
            let dbManager: DB_Manager = DB_Manager()

            // call delete function
            dbManager.deleteUser(idValue: model.id)

            // refresh the user models array
            self.userModels = dbManager.getUsers()
        }, label: {
            Text("Delete")
                .foregroundColor(Color.red)
        })// by default, buttons are full width.
        // to prevent this, use the following
        .buttonStyle(PlainButtonStyle())
    }
}

The above code should be placed after the edit button in the content view. You can see that we created an instance of DB_Manager class and save it in another variable. Because we need to call 2 functions from DB_Manager class.

The first function will delete the user from the SQLite database, and the second function will update the list view. So the delete user will be removed from view as well.

Create the following function in your DB_Manager class:

// function to delete user
public func deleteUser(idValue: Int64) {
    do {
        // get user using ID
        let user: Table = users.filter(id == idValue)
        
        // run the delete query
        try db.run(user.delete())
    } catch {
        print(error.localizedDescription)
    }
}

If you run the app now, you will see a delete button at the end of each user’s record. Clicking that button will remove the user from the database and from the list view as well. You can try removing the app from the background process and re-open again, the delete data will not appear again.

Where you can use SQLite in the iOS app?

You can create a feature to save the search history of user locally in the app. So user can re-search the same thing from search history.

You can cache the frequent data in SQLite, so instead of requesting the data from the server, it will be accessed from inside the app. It will increase the performance of your app.

Or you can simply create a server-less app using SQLite like “Personal account book-keeping app”. These type of apps does not require an internet connection to work and the user can use them even without the access to the internet.

Leave a Reply

Please disable your adblocker or whitelist this site!