Simplified iOS

  • Home
  • About
  • Contact
  • Advertise
  • Write for Us

Swift SQLite Tutorial for Beginners – Using SQLite in iOS Application

November 19, 2017 by Belal Khan 12 Comments

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
    • 3.1 Creating a new Xcode Project
    • 3.2 Creating Database File
    • 3.3 Opening Database
    • 3.4 Creating Table
    • 3.5 Inserting Values
      • 3.5.1 Creating Interface
      • 3.5.2 Saving Values
    • 3.6 Reading Values
      • 3.6.1 Creating Model Class
      • 3.6.2 Reading Heroes from Table
      • 3.6.3 Creating TableView
    • 3.7 The Complete ViewController.swift Code
  • 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.

database table

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

Swift SQLite Tutorial for Beginners

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, &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.

View Controller

  • 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, &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, &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.

save in sqlite database swift

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, &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.

tableview

  • 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.

prototype cell

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

cell identifier

  • 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) -> 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) -> 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

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, &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) -> Int {
        return heroList.count
    }
 
    func tableView(_ tableView: UITableView, cellForRowAt indexPath: IndexPath) -> 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, &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, &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 🙂

 

Share this:

  • Tweet
  • Share on Tumblr
  • WhatsApp

Related

Filed Under: iOS Development Tutorial Tagged With: swift sqlite

About Belal Khan

I am Belal Khan, I am currently pursuing my MCA. In this blog I write tutorials and articles related to coding, app development, iphone etc.

Comments

  1. 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.

    Reply
    • 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.

      Reply
  2. 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.

    Reply
    • Orlando says

      March 21, 2018 at 5:43 pm

      Hello Rafael, I have the same problem too, did you managed to solve it?

      Reply
      • Diego Ramos says

        May 15, 2018 at 7:18 pm

        Hello guys, I have the same problem too, did you managed to solve it?

        Reply
    • 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.

      Reply
  3. savita dayma says

    January 2, 2018 at 10:21 am

    how to update database when update query fired??

    Reply
  4. 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.

    Reply
  5. 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. 🙂

    Reply
  6. awadh says

    April 11, 2018 at 8:50 am

    nice

    Reply
  7. 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?

    Reply
  8. 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.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *




About Me

belal khan simplified ios

Hello I am Belal Khan, founder and owner of Simplified iOS. I am currently pursuing MCA from St. Xavier's College, Ranchi. Apart from my academic I am a blogger, I run various websites and majority of them are about coding and development.

Connect with Me

Follow Simplified iOS

Simplified iOS

Popular Posts

  • Swift PHP MySQL Tutorial – Connecting iOS App… (94,532)
  • Swift SQLite Tutorial for Beginners – Using… (94,175)
  • UIWebView Example to Load URL in iOS using Swift in Xcode (78,244)
  • Xcode Login Screen Example using Swift 3, PHP and MySQL (65,225)
  • Download Full High Sierra Installer to Create Bootable USB (61,246)
  • How to Format USB on Mac? Formatting External Hard… (60,779)
  • Swift JSON Tutorial – Fetching and Parsing… (58,674)
  • Firebase Realtime Database Tutorial for Swift using Xcode (52,001)
  • iOS Registration Form Example using PHP and MySQL (46,976)
  • Xcode Text Field Tutorial for iOS Application using Swift (39,039)




About

Simplified iOS is a blog where you can find latest tutorials related to coding and app development for iphone and MAC OS. Here you can get Simplified iPhone, iPad and Mac OS development tutorial to know the things about Apple Development from basics to advanced level.

Quick Links

  • Advertise
  • Contact
  • Disclaimer
  • Privacy Policy
  • Write for Us

Copyright © 2017 · Simplified iOS· All rights Reserved. And Our Sitemap.All Logos & Trademark Belongs To Their Respective Owners·