Managing Data in iOS Apps with SQLite

Share this article

Managing Data in iOS Apps with SQLite

Almost all apps will need to store data of some form. Maybe you need to save user preferences, progress in a game, or offline data so your app can work without a network connection. Developers have a lot of options for managing data in iOS apps, from Core Data to cloud based storage, but one elegant and reliable local storage option is SQLite.

In this tutorial I will show you how to add SQLite support to your app. You can find the final source code on GitHub.

This article was peer reviewed by Aleksander Koko . Thanks to all of SitePoint’s peer reviewers for making SitePoint content the best it can be!

Key Takeaways

  • SQLite is a reliable local storage option for managing data in iOS apps. It can be used to save user preferences, game progress, or offline data enabling the app to work without a network connection.
  • SQLite can be challenging to use due to its C language foundation and the necessity of understanding pointers and data types. However, developers can use Objective-C or Swift wrappers, like SQLite.swift, to serve as an adapter layer, making SQLite easier to manage.
  • SQLite.swift enables developers to create a database and perform CRUD (Create-Read-Update-Delete) operations without the need for hard-coded SQL commands. It also allows developers to manage the database, create tables, and handle data efficiently with a few lines of code.
  • Although SQLite itself doesn’t provide built-in encryption or security features, developers can use SQLCipher, an open-source extension for SQLite, to encrypt the database. Additionally, iOS provides data protection APIs that can be used to secure data.

Getting Started

The SQLite library is written in C, and all queries happen as calls to C functions. This makes it challenging to use, as you have to be aware of pointers and data types etc. To help, you can make use of Objective-C or Swift wrappers to serve as an adapter layer.

A popular choice is FMDB, an Objective-C wrapper around SQLite. Its easy to use, but personally I prefer to not use hard-coded SQL (Structured Query Language) commands. For this tutorial, I will use SQLite.swift to create a basic contact list.

First, create a new single view project in Xcode (SQLite.swift requires Swift 2 and Xcode 7 or greater). I created a ViewController in Main.storyboard that looks like the below. Create your own similar layout, or download the storyboard files here.

App Preview

At the bottom is a TableView which will hold the contacts.

Installation

You can install SQLite.swift with Carthage, CocoaPods, or manually.

The Model

Create a new Swift file / class named Contact.swift, it contains three properties and initializers to keep it simple.

import Foundation

class Contact {
    let id: Int64?
    var name: String
    var phone: String
    var address: String

    init(id: Int64) {
        self.id = id
        name = ""
        phone = ""
        address = ""
    }

    init(id: Int64, name: String, phone: String, address: String) {
        self.id = id
        self.name = name
        self.phone = phone
        self.address = address
    }
}

The id is required as a parameter when creating an object, so you can reference it in the database later.

Connecting the User Interface

In ViewController.swift make the class implement UITableViewDelegate and UITableViewSource protocols.

class ViewController: UIViewController, UITableViewDataSource, UITableViewDelegate {
   ...
 }

Connect the following IOutlets with their corresponding views by dragging or manually adding them in code.

@IBOutlet weak var nameTextField: UITextField!
@IBOutlet weak var phoneTextField: UITextField!
@IBOutlet weak var addressTextField: UITextField!

@IBOutlet weak var contactsTableView: UITableView!

Add outlets

Now you will need a list of contacts, and an index for the contact selected from the list.

private var contacts = [Contact]()
private var selectedContact: Int?

Link the DataSource and Delegate of the UITableView with the UIViewController in the storyboard.

Add datasource

Or by adding the following lines into the viewDidLoad() method of ViewController.swift.

contactsTableView.dataSource = self
contactsTableView.delegate = self

To insert, update and remove elements from the UITableView you need to implement three basic methods from the protocols mentioned above.

The first will fill the UITextFields with the corresponding contact information from a selected contact. Yt will then save the row that represents this contact in the table.

func tableView(tableView: UITableView, didSelectRowAtIndexPath indexPath: NSIndexPath) {
    nameTextField.text = contacts[indexPath.row].name
    phoneTextField.text = contacts[indexPath.row].phone
    addressTextField.text = contacts[indexPath.row].address

    selectedContact = indexPath.row
}

The next function tells the UITableViewDataSource how many cells of data it should load. For now, it will be zero since the array is empty.

func tableView(tableView: UITableView, numberOfRowsInSection section: Int) -> Int {
    return contacts.count
}

The last function returns a specific UITableViewCell for each row. First get the cell using the identifier, then its child views using their tag. Make sure that the identifiers match your element names.

func tableView(tableView: UITableView, cellForRowAtIndexPath indexPath: NSIndexPath) -> UITableViewCell {

    let cell = tableView.dequeueReusableCellWithIdentifier("ContactCell")!
    var label: UILabel
    label = cell.viewWithTag(1) as! UILabel // Name label
    label.text = contacts[indexPath.row].name

    label = cell.viewWithTag(2) as! UILabel // Phone label
    label.text = contacts[indexPath.row].phone

    return cell
}

The app can now run, but there is no ability to add or edit contacts yet. To do this link the following IBActions with the corresponding buttons.

@IBAction func addButtonClicked() {
    let name = nameTextField.text ?? ""
    let phone = phoneTextField.text ?? ""
    let address = addressTextField.text ?? ""

    let contact = Contact(id: 0, name: name, phone: phone, address: address)
    contacts.append(contact)
    contactsTableView.insertRowsAtIndexPaths([NSIndexPath(forRow: contacts.count-1, inSection: 0)], withRowAnimation: .Fade)
}

Here you take the values of the UITextFields, and create an object which is added to the contacts list. The id is set to 0, since you haven’t implemented the database yet. The function insertRowsAtIndexPaths() takes as arguments an array of indexes of the rows that will be affected, and the animation to perform with the change.

@IBAction func updateButtonClicked() {
    if selectedContact != nil {
        let id = contacts[selectedContact].id!
        let contact = Contact(
            id: id,
            name: nameTextField.text ?? "",
            phone: phoneTextField.text ?? "",
            address: addressTextField.text ?? "")

            contacts.removeAtIndex(selectedContact!)
                contacts.insert(contact, atIndex: selectedContact!)

        contactsTableView.reloadData()
    } else {
    print("No item selected")
    }
}

In this function you create a new Contact, and delete and re-insert in the same index of the list to make the replacement. The function doesn’t currently check to see if the data has changed.

@IBAction func deleteButtonClicked() {
    if selectedContact != nil {
        contacts.removeAtIndex(selectedContact)

        contactsTableView.deleteRowsAtIndexPaths([NSIndexPath(forRow: selectedContact, inSection: 0)], withRowAnimation: .Fade)
    } else {
    print("No item selected")
    }
}

The last function removes the contact selected and refreshes the table.

At this point the application works, but will lose all changes when relaunched.

Creating a Database

Now time to manage the database. Create a new swift file / class named StephencelisDB.swift and import the SQLite library.

import SQLite

class StephencelisDB {
}

First, initialize an instance of the class, using the ‘Singleton’ pattern. Then, declare an object of type Connection, which is the actual database object you will call.

static let instance = StephencelisDB()
private let db: Connection?

The other declarations are the table of contacts, and its column with a specific type.

private let contacts = Table("contacts")
private let id = Expression<Int64>("id")
private let name = Expression<String?>("name")
private let phone = Expression<String>("phone")
private let address = Expression<String>("address")

The constructor tries to open a connection with the database which has a specified name, and a path to the application data, and then creates the tables.

private init() {
    let path = NSSearchPathForDirectoriesInDomains(
        .DocumentDirectory, .UserDomainMask, true
        ).first!

    do {
        db = try Connection("\(path)/Stephencelis.sqlite3")
    } catch {
        db = nil
        print ("Unable to open database")
    }

    createTable()
}

func createTable() {
    do {
        try db!.run(contacts.create(ifNotExists: true) { table in
        table.column(id, primaryKey: true)
        table.column(name)
        table.column(phone, unique: true)
        table.column(address)
        })
    } catch {
        print("Unable to create table")
    }
}

Notice there is no SQL code to create the table and columns. This is the power of the wrapper used. With a few lines of code you have the database ready.

CRUD Operations

For those unfamiliar with the term, ‘CRUD’ is an acronym for Create-Read-Update-Delete. Next, add the four methods to the database class that perform these operations.

func addContact(cname: String, cphone: String, caddress: String) -> Int64? {
    do {
        let insert = contacts.insert(name <- cname, phone <- cphone, address <- caddress)
        let id = try db!.run(insert)

        return id
    } catch {
        print("Insert failed")
        return -1
    }
}

The <- operator assigns values to the corresponding columns as you would in a normal query. The run method will execute these queries and statements. The id of the row inserted is returned from the method.

Add print(insert.asSQL()) to see the executed query itself:

INSERT INTO "contacts" ("name", "phone", "address") VALUES ('Deivi Taka', '+355 6X XXX XXXX', 'Tirana, Albania')

If you want to undertake further debugging you can use a method instead. The prepare method returns a list of all the rows in the specified table. You loop through these rows and create an array of Contact objects with the column content as parameters. If this operation fails, an empty list is returned.

func getContacts() -> [Contact] {
    var contacts = [Contact]()

    do {
        for contact in try db!.prepare(self.contacts) {
            contacts.append(Contact(
            id: contact[id],
            name: contact[name]!,
            phone: contact[phone],
            address: contact[address]))
        }
    } catch {
        print("Select failed")
    }

    return contacts
}

For deleting items, find the item with a given id, and remove it from the table.

func deleteContact(cid: Int64) -> Bool {
    do {
        let contact = contacts.filter(id == cid)
        try db!.run(contact.delete())
        return true
    } catch {
        print("Delete failed")
    }
    return false
}

You can delete more than one item at once by filtering results to a certain column value.

Updating has similar logic.

func updateContact(cid:Int64, newContact: Contact) -> Bool {
    let contact = contacts.filter(id == cid)
    do {
        let update = contact.update([
            name <- newContact.name,
            phone <- newContact.phone,
            address <- newContact.address
            ])
        if try db!.run(update) > 0 {
            return true
        }
    } catch {
        print("Update failed: \(error)")
    }

    return false
}

Final Changes

After setting up the database managing class, there are some remaining changes needed to Viewcontroller.swift.

First, when the view is loaded get the previously saved contacts.

contacts = StephencelisDB.instance.getContacts()

The tableview methods you prepared earlier will display the saved contacts without adding anything else.

Inside addButtonClicked, call the method to add a contact to the database. Then update the tableview only if the method returned a valid id.

if let id = StephencelisDB.instance.addContact(name, cphone: phone, caddress: address) {
    // Add contact in the tableview
    ...
}

In a similar way, call these methods inside updateButtonClicked and deleteButtonClicked.

...
StephencelisDB.instance.updateContact(id, newContact: contact)
...
StephencelisDB.instance.deleteContact(contacts[selectedContact].id!)
...

Run the app and try to perform some actions. Below are two screenshots of how it should look. To update or delete a contact it must first be selected.

1

2

Any Queries?

SQLite is a good choice for working with local data, and is used by many apps and games. Wrappers like SQLite.swift make the implementation easier by avoiding the use of hardcoded SQL queries. If you need to store data in your app and don’t want to have to handle more complex options then SQLite i worth considering.

May the Code be with you!

Frequently Asked Questions (FAQs) about Managing Data in iOS Apps with SQLite

What are the advantages of using SQLite in iOS app development?

SQLite offers several advantages for iOS app development. Firstly, it is a serverless database and doesn’t require a separate server process to operate. This makes it a great choice for devices with limited resources like smartphones. Secondly, SQLite is self-contained, meaning it requires minimal setup or administration. Thirdly, it’s transactional, ensuring data integrity even in cases of system crashes or power failures. Lastly, SQLite is an open-source tool, which means it’s free to use and has a large community of developers contributing to its development and improvement.

How does SQLite compare to other databases used in iOS development?

SQLite is often compared to other databases like MySQL or Core Data. While MySQL is a powerful tool, it requires a server to operate, which can be a disadvantage for mobile applications. Core Data, on the other hand, is an object graph and persistence framework provided by Apple. It’s not a database, but it can use SQLite as its persistent store. Core Data is more complex and has a steeper learning curve than SQLite, but it offers more features like data modeling, versioning, and automatic migrations.

Can I use SQLite for complex data structures in my iOS app?

Yes, SQLite can handle complex data structures. However, it’s important to note that SQLite is a relational database, which means it works best with structured data. If your app requires storing complex, hierarchical, or semi-structured data, you might want to consider using a NoSQL database or a document store like MongoDB or CouchDB.

How secure is SQLite for storing sensitive data in my iOS app?

SQLite itself doesn’t provide any built-in encryption or security features. However, you can use SQLCipher, an open-source extension for SQLite, to encrypt your database. Additionally, iOS provides data protection APIs that you can use to secure data at rest.

How do I perform CRUD operations in SQLite?

CRUD operations in SQLite are performed using SQL commands. For example, to create a new table, you would use the CREATE TABLE command. To insert data, you would use the INSERT INTO command. To read data, you would use the SELECT command. To update data, you would use the UPDATE command. And to delete data, you would use the DELETE command.

How do I handle database versioning in SQLite?

SQLite doesn’t provide built-in support for database versioning. However, you can implement it manually by adding a version table to your database. This table would keep track of the current database version and any migrations that need to be applied.

Can I use SQLite with Swift, the programming language for iOS development?

Yes, you can use SQLite with Swift. There are several libraries available that provide a Swift interface to SQLite, such as SQLite.swift and GRDB.swift.

How do I handle errors in SQLite?

SQLite provides several error codes that you can check to handle errors. For example, if a SQL command fails, SQLite will return an error code that you can check to determine the cause of the failure.

Can I use SQLite for offline data storage in my iOS app?

Yes, SQLite is a great choice for offline data storage. Since it’s a serverless database, it can operate without an internet connection. This makes it ideal for apps that need to work offline and sync data when a connection is available.

How do I optimize performance in SQLite?

There are several ways to optimize performance in SQLite. For example, you can use indexes to speed up data retrieval, use transactions to group multiple operations into a single atomic operation, and normalize your database to reduce data redundancy.

Deivi TakaDeivi Taka
View Author

Deivi has graduated in Computer Engineering. He started writing code at High School, and now he can program in Java, C, C#, and Swift. Currently a Mobile Application Developer at a local company, he tries to share his passion writing articles for SitePoint.

datadatabasessql
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week