Swift SQLite Tutorial for Beginners

Many times we need a local database for our application. And if we talk about iOS Application Development then we have an inbuilt database called SQLite. So in this Swift SQLite Tutorial for Beginners, we are going to learn how to use SQLite Database in our iOS Application. Here I will be using the Swift Language and Xcode 9. So let’s start our Swift SQLite Tutorial.

Note: Here I am not going to use any 3rd party libraries. We will only be using the inbuilt tools.

Contents

1 What is SQLite?
2 The Database Design
3 Swift SQLite Tutorial for Beginners
4 Swift SQLite Tutorial Source Code Download
- 4.1 Share this:
- 4.2 Related

What is SQLite?

I hope you already got some idea about an SQL database. And SQLite is also an SQL database where we can create tables to store data. We can create primary keys, foreign keys to relate multiple tables. All these things are fundamental SQL stuff, and I hope you already know about it.

The Database Design

For this example, I will be using a simple table. And then we will see the basic operations, like Storing Values to the database and Reading the stored values back from the database.

So, in this post, I will be creating the above-given table.

As always we will start with a new Xcode Project.

Creating a new Xcode Project

This step is the easiest. So just create a new Xcode Project. I have created a project named SwiftExample.
When your project is open, the first thing we need to do is, we need to open our SQLite database. To do this, we need to create a .sqlite file.

Creating Database File

Creating a swift file is very simple, you just need to write the following code in your viewDidLoad().

        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent("HeroesDatabase.sqlite")

Opening Database

Now we can use the function sqlite3_open() function to open the database. But before opening the database we need an OpaquePointer object. So first, inside your class define this.

    var db: OpaquePointer?

Now we can use the method to open the database.

        if sqlite3_open(fileURL.path, &amp;db) != SQLITE_OK {
            print("error opening database")
        }

Once we have the database open, we need to create the table. So lets create the table that we need.

Creating Table

To create the table we can use the function sqlite3_exec(). So add the following lines after opening the database.

        if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS Heroes (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, powerrank INTEGER)", nil, nil, nil) != SQLITE_OK {
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("error creating table: \(errmsg)")
        }

Now lets see how do we insert values. To the table we created.

Inserting Values

To insert values first, we will create an Interface, from where user can enter the values and we can save it to the database table on a button click.

Creating Interface

So come inside Main.storyboard and create the following interface. Here I have two Text Fields and a Button.

Now you need to connect these Views to your ViewController.swift file. If you don’t know how to do this go to the previous Xcode Button Tutorial.
Now once you have connected everything to your swift file your code will be like shown below.

//
//  ViewController.swift
//  SwiftExample
//
//  Created by Belal Khan on 18/11/17.
//  Copyright © 2017 Belal Khan. All rights reserved.
//
 
import UIKit
import SQLite3
 
class ViewController: UIViewController{
 
    var db: OpaquePointer?
 
    @IBOutlet weak var tableViewHeroes: UITableView!
    @IBOutlet weak var textFieldName: UITextField!
    @IBOutlet weak var textFieldPowerRanking: UITextField!
 
    @IBAction func buttonSave(_ sender: UIButton) {
 
    }
 
 
    override func viewDidLoad() {
        super.viewDidLoad()
 
        //the database file
        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent("HeroesDatabase.sqlite")
 
        //opening the database
        if sqlite3_open(fileURL.path, &amp;db) != SQLITE_OK {
            print("error opening database")
        }
 
        //creating table 
        if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS Heroes (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, powerrank INTEGER)", nil, nil, nil) != SQLITE_OK {
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("error creating table: \(errmsg)")
        }
 
    }
}

Now inside the function buttonSave (It will be called on button click) we will save the values by taking the inputs from TextFields.

Saving Values

Write the following code inside buttonSave method to save values.

    @IBAction func buttonSave(_ sender: UIButton) {
        //getting values from textfields
        let name = textFieldName.text?.trimmingCharacters(in: .whitespacesAndNewlines)
        let powerRanking = textFieldPowerRanking.text?.trimmingCharacters(in: .whitespacesAndNewlines)
 
 
        //validating that values are not empty
        if(name?.isEmpty)!{
            textFieldName.layer.borderColor = UIColor.red.cgColor
            return
        }
 
        if(powerRanking?.isEmpty)!{
            textFieldName.layer.borderColor = UIColor.red.cgColor
            return
        }
 
        //creating a statement
        var stmt: OpaquePointer?
 
        //the insert query
        let queryString = "INSERT INTO Heroes (name, powerrank) VALUES (?,?)"
 
        //preparing the query
        if sqlite3_prepare(db, queryString, -1, &amp;stmt, nil) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("error preparing insert: \(errmsg)")
            return
        }
 
        //binding the parameters
        if sqlite3_bind_text(stmt, 1, name, -1, nil) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("failure binding name: \(errmsg)")
            return
        }
 
        if sqlite3_bind_int(stmt, 2, (powerRanking! as NSString).intValue) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("failure binding name: \(errmsg)")
            return
        }
 
        //executing the query to insert values
        if sqlite3_step(stmt) != SQLITE_DONE {
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("failure inserting hero: \(errmsg)")
            return
        }
 
        //emptying the textfields
        textFieldName.text=""
        textFieldPowerRanking.text=""
 
 
        readValues()
 
        //displaying a success message
        print("Herro saved successfully")
    }

Now you can test the application and it should save the values to database.

Reading Values

Now lets see how we read the values back from SQLite Database.

Creating Model Class

First create a class inside your project named Hero. We will use this class to store all the heroes as a List.

class Hero {
 
    var id: Int
    var name: String?
    var powerRanking: Int
 
    init(id: Int, name: String?, powerRanking: Int){
        self.id = id
        self.name = name
        self.powerRanking = powerRanking
    }
}

Reading Heroes from Table

First we will create a Hero List to save heroes into this list. So inside the class define a List of type Hero.

    var heroList = [Hero]()

Now inside your ViewController.swift create a new function named readValues() and write the following code.

    func readValues(){
 
        //first empty the list of heroes
        heroList.removeAll()
 
        //this is our select query
        let queryString = "SELECT * FROM Heroes"
 
        //statement pointer
        var stmt:OpaquePointer?
 
        //preparing the query
        if sqlite3_prepare(db, queryString, -1, &amp;stmt, nil) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("error preparing insert: \(errmsg)")
            return
        }
 
        //traversing through all the records
        while(sqlite3_step(stmt) == SQLITE_ROW){
            let id = sqlite3_column_int(stmt, 0)
            let name = String(cString: sqlite3_column_text(stmt, 1))
            let powerrank = sqlite3_column_int(stmt, 2)
 
            //adding values to list
            heroList.append(Hero(id: Int(id), name: String(describing: name), powerRanking: Int(powerrank)))
        }
 
    }

We have read the values, but where to display it? So I will create a TableView here to display the List of Heroes.

Creating TableView

Again add a TableView to your ViewController.

You need to create here a prototype cell, and also you need to give the cell an identifier as well.
To make the prototype cell click on the TableView and from the right make the Prototype Cell value to 1.

The select the Prototype Cell and give an Identifier to this cell. Here I have given the identifier as “cell“.

You also need to select data source and delegate. Fir this come inside Main.storyboard, select your TableView and press control and then drag it to the yellow circle on the top and select dataSource, the do the same thing again and select delegate. If you are confused what to do then you can check this Detailed TableView Tutorial.
Now inside your class you need to add UITableViewDataSource and UITableViewDelegate to your ViewController class as well.

class ViewController: UIViewController, UITableViewDataSource, UITableViewDelegate {

Now you need to add the below functions to your class.

    func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -&gt; Int {
        //this method is giving the row count of table view which is 
        //total number of heroes in the list
        return heroList.count
    }
 
 
    //this method is binding the hero name with the tableview cell
    func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -&gt; UITableViewCell {
        let cell = UITableViewCell(style: UITableViewCellStyle.default, reuseIdentifier: "cell")
        let hero: Hero
        hero = heroList[indexPath.row]
        cell.textLabel?.text = hero.name
        return cell
    }

Now inside readValues() function at the end reload the TableView.

       self.tableViewHeroes.reloadData()

Now you just need to call readValues() everytime you are inserting a new hero. And it will show up in the list automatically.

Swift SQLite Tutorial

You see it is working fine as well.

The Complete ViewController.swift Code

So here, is the complete code of the ViewController.swift file.

//
//  ViewController.swift
//  SwiftExample
//
//  Created by Belal Khan on 18/11/17.
//  Copyright © 2017 Belal Khan. All rights reserved.
//
 
import UIKit
import SQLite3
 
class ViewController: UIViewController, UITableViewDataSource, UITableViewDelegate {
 
 
    var db: OpaquePointer?
    var heroList = [Hero]()
 
    @IBOutlet weak var tableViewHeroes: UITableView!
    @IBOutlet weak var textFieldName: UITextField!
    @IBOutlet weak var textFieldPowerRanking: UITextField!
 
    @IBAction func buttonSave(_ sender: UIButton) {
        let name = textFieldName.text?.trimmingCharacters(in: .whitespacesAndNewlines)
        let powerRanking = textFieldPowerRanking.text?.trimmingCharacters(in: .whitespacesAndNewlines)
 
        if(name?.isEmpty)!{
            textFieldName.layer.borderColor = UIColor.red.cgColor
            return
        }
 
        if(powerRanking?.isEmpty)!{
            textFieldName.layer.borderColor = UIColor.red.cgColor
            return
        }
 
        var stmt: OpaquePointer?
 
        let queryString = "INSERT INTO Heroes (name, powerrank) VALUES (?,?)"
 
        if sqlite3_prepare(db, queryString, -1, &amp;stmt, nil) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("error preparing insert: \(errmsg)")
            return
        }
 
        if sqlite3_bind_text(stmt, 1, name, -1, nil) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("failure binding name: \(errmsg)")
            return
        }
 
        if sqlite3_bind_int(stmt, 2, (powerRanking! as NSString).intValue) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("failure binding name: \(errmsg)")
            return
        }
 
        if sqlite3_step(stmt) != SQLITE_DONE {
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("failure inserting hero: \(errmsg)")
            return
        }
 
        textFieldName.text=""
        textFieldPowerRanking.text=""
 
        readValues()
 
        print("Herro saved successfully")
    }
 
 
    func tableView(_ tableView: UITableView, numberOfRowsInSection section: Int) -&gt; Int {
        return heroList.count
    }
 
    func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -&gt; UITableViewCell {
        let cell = UITableViewCell(style: UITableViewCellStyle.default, reuseIdentifier: "cell")
        let hero: Hero
        hero = heroList[indexPath.row]
        cell.textLabel?.text = hero.name
        return cell
    }
 
 
    func readValues(){
        heroList.removeAll()
 
        let queryString = "SELECT * FROM Heroes"
 
        var stmt:OpaquePointer?
 
        if sqlite3_prepare(db, queryString, -1, &amp;stmt, nil) != SQLITE_OK{
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("error preparing insert: \(errmsg)")
            return
        }
 
        while(sqlite3_step(stmt) == SQLITE_ROW){
            let id = sqlite3_column_int(stmt, 0)
            let name = String(cString: sqlite3_column_text(stmt, 1))
            let powerrank = sqlite3_column_int(stmt, 2)
 
            heroList.append(Hero(id: Int(id), name: String(describing: name), powerRanking: Int(powerrank)))
        }
 
        self.tableViewHeroes.reloadData()
    }
 
    override func viewDidLoad() {
        super.viewDidLoad()
 
 
        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent("HeroesDatabase.sqlite")
 
 
        if sqlite3_open(fileURL.path, &amp;db) != SQLITE_OK {
            print("error opening database")
        }
 
        if sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS Heroes (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, powerrank INTEGER)", nil, nil, nil) != SQLITE_OK {
            let errmsg = String(cString: sqlite3_errmsg(db)!)
            print("error creating table: \(errmsg)")
        }
 
        readValues()
    }
}

Swift SQLite Tutorial Source Code Download

If you are still confused at some point then you can directly download the source code of my project. Just unlock the link given below to download the source code.

Swift SQLite Tutorial Source Code

So that’s, all of this Swift SQLite Tutorial friends. Hope you found it helpful. If you are having any question regarding this Swift SQLite tutorial, then let me know in comments.

Also if you found this Swift SQLite Tutorial helpful then please SHARE it with friends. Thank You 🙂

Comments

Fatehlal Prajapat says

December 18, 2017 at 11:40 am

Hello , I have request to you sir, I’m a fresher iOS developer, currently I’m trainee in ahmedabad based company, now my request is hopw to connect server data to sqlite database , mean app to server and server to app.. without any thirdparty pod file or api. if sir it is possible , please make tutorial on it.

- Ram Odedra says
  
  August 17, 2018 at 5:00 am
  
  No, i think it’s not possible to send data to server without using api,pod file or else.
  or get data from server also not possible.
  
  i am not sure but i think.
  
Rafael Roa says

January 1, 2018 at 12:23 pm

Hi, i am following your code, but it is weird: I created this table:

CREATE TABLE IF NOT EXISTS Clientes (id INTEGER PRIMARY KEY AUTOINCREMENT, nombre TEXT, dni TEXT, calle TEXT, cp INT, ciudad TEXT, telefono INT);

When I save data from uitextfields that are long they are saved properly. But if they are short they store wrong data.

Input Data 1
=========
nombre: lem3a arquitectura avanzada de Andalucía
DNI: 12345678B
Calle: Alameda Principal 20
CP: 29005
Ciudad: Malaga
Telefono: 123456789

This is a almost correct output
id=12 nombre=lem3a arquitectura avanzada de Andalucía DNI=Malaga Calle=Alameda Principal 20 CP=29005 Ciudad=Malaga Telefono=123456789

if I input this

Input Data 2
=========
nombre: lem3a
DNI: 12345678B
Calle: Alameda Principal 20
CP: 29005
Ciudad: Malaga
Telefono: 123456789

This is the output
id=13 nombre=Malag DNI=Malaga Calle=Alameda Principal 20 CP=29005 Ciudad=Malaga Telefono=606562744

I don’t know what is wrong, and I have been 3 days researching in google.

- Orlando says
  
  March 21, 2018 at 5:43 pm
  
  Hello Rafael, I have the same problem too, did you managed to solve it?
  
  - Diego Ramos says
    
    May 15, 2018 at 7:18 pm
    
    Hello guys, I have the same problem too, did you managed to solve it?
    
- Shivaji Tanpure says
  
  May 22, 2018 at 12:58 pm
  
  Hi Rafael Roa ,
  
  You can find the below solution I have given which will solve your problem.
  
  Thanks.
  
savita dayma says

January 2, 2018 at 10:21 am

how to update database when update query fired??

Ali Raza says

January 27, 2018 at 1:36 pm

There are some issues like if we want to display name then powerRanking is displaying and vice versa. Another problem is it is showing less words than the text we write in textfield for name or powerRanking, Why is this so? AnyWays tutorial is nice but with some problems.

Rachel says

February 5, 2018 at 9:38 am

Thanks so much for this! Most helpful thing I’ve found anywhere on the topic, including books, paid tutorials, etc. Clean, up-to-date for iOS 11/Swift 4, to the point and it works. 🙂

awadh says

April 11, 2018 at 8:50 am

nice

Steve says

May 6, 2018 at 3:12 am

Where is the data stored? I can’t find the database “HeroesDatabase.sqlite”. I’d like to be able to generate a large db and access it within an app.
Would I need to write a very long SQL Insert statement?

Shivaji Tanpure says

May 22, 2018 at 12:52 pm

Hi All,

I am also facing the same above issue, but I have found 2 solutions for that. Please find the below solutions:

solution 1: You have to encode your values into utf8String before it will pass to the sqlite3_bind statement like
sqlite3_bind_text(insertStatement, 1, name.utf8String, -1, nil)
Note: for the above solution your name should be type of NSString otherwise you can with solution number 2

solution 2: sqlite3_bind_text(stmt, 1, String(utf8String: name.cString(using: .utf8)!), -1, nil)
you have to encode it within sqlite3_bind statement.

Swift SQLite Tutorial for Beginners – Using SQLite in iOS Application

What is SQLite?

The Database Design