SoFunction
Updated on 2025-04-11

Swift learning tutorial: Basic use of SQLite

Preface

In our daily development, we often encounter users' network outages or slow networks, so that users will display blank pages when they enter the page. So how can we avoid the embarrassment of displaying blank pages without the Internet? The answer is: first cache some data when the network is good, so that when the network is bad next time, at least the user can see the content cached before, which has achieved the improvement of the user experience of the APP.

SQLite is a solution for us to implement local data caching. SQLite has the following advantages: iOS embedded SQLite; time verification; open source; cross-platform.
OK, without further ado, now we will start our experience journey of SQLite. Of course, we have to do some preparation before we start, after all, we don’t fight unprepared wars.

Preparation

Create backup data

  • Import SQLite3:import SQLite3
  • Create a Goods class to represent the data type stored in the database
  • Create an array of Goods type
  • Declare a global variable of dbPath and db, declare a function that gets the libraryDirectory path (How to select the database storage path）

The code is as follows:

class Goods {
 let name: String!
 let weight: Int!
 var price: Double!
 
 init(name: String, weight: Int, price: Double) {
   = name
   = weight
   = price
 }
}

let goods = Goods(name: "computer", weight: 10, price: 2000.0)
var goodArr = [Goods]()
var dbPath = ""
var db: OpaquePointer?

func createData() {
 for index in 0...4 {
  let goods = Goods(name: "computer" + "\(index)", weight: index * 10, price: 20.0)
  (goods)
 }
}

func fetchLibraryPath() {
 if let libraryPathString = NSSearchPathForDirectoriesInDomains(.libraryDirectory, .userDomainMask, true).first {
  let pathURL = URL(fileURLWithPath: libraryPathString).appendingPathComponent("")
  dbPath = 
 }
}

Create and connect to the database

func openDatabase() -> OpaquePointer? {
 var db: OpaquePointer?
 if sqlite3_open(dbPath, &db) == SQLITE_OK {
   = "Successfully opened the database，path：\(dbPath)"
  return db
 } else {
   = "Domain opening failed"
  return nil
 }
}

Through the above code, we can see that first declares an optional value db of OpaquePointer type, and then call the sqlite3_open() method. The function of this method is: if the database was created before, it will be opened directly, and if it is not created, it will be created directly. If the method is called successfully, it will return an OpaquePointer value assigned to the db you passed in.

SQLITE_OK is a constant defined in the SQLite library, which represents an Int32 0. Most SQLite functions will return an Int32 value, such as SQLITE_ROW (100), SQLITE_DONE (101), etc. You can view the detailed list.here。

Now you can open or create a database by calling db = openDatabase(). Under normal circumstances, you will see the database successfully opened, the path: xxx/ output.

Now that we have successfully created a database named as, the next thing we have to do is create a table.

Create a table

Code

func createTable() {
 let createTableString = """
       CREATE TABLE Computer(
       Id INT PRIMARY KEY NOT NULL,
       Name CHAR(255),
       Weight Int,
       Price Float);
       """
 var createTableStatement: OpaquePointer?
 // Step 1 if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
  // Step 2  if sqlite3_step(createTableStatement) == SQLITE_DONE {
    = "Successfully created table"
  } else {
    = "Table not successfully created"
  }
 } else {
   
 }
 //Step 3 sqlite3_finalize(createTableStatement)
}

Code description

First, let’s explain createTableString: create a table with the name Computer, with Id as the primary key and not empty, the Name does not exceed 255 characters, Weight is type Int, and Price is type Float.
Then a variable of type OpaquePointer? is created for the following function: sqlite3_prepare_v2().

  • Step 1: This function will compile createTableString into byte code and return a status code. The successful execution of this function indicates that the database is ready to execute any SQL statement (the created SQL string). After the successful execution of this function, sqlite3_step() will be executed.
  • Step 2: sqlite3_step() is used to execute the compiled statement handle(createTableStatement) and return a status code.
  • Step 3: After each operation is completed, you must call sqlite3_finalize() to delete your statement to avoid resource leakage. Note: Once a statement is finalized, you should not use it again.

Insert a data

Code

func insertOneData() {
 let insertRowString = "INSERT INTO Computer (Id, Name, Weight, Price) VALUES (?, ?, ?, ?);"
 var insertStatement: OpaquePointer?
 //first step if sqlite3_prepare_v2(db, insertRowString, -1, &insertStatement, nil) == SQLITE_OK {
   let id: Int32 = 1
   //Step 2   sqlite3_bind_int(insertStatement, 1, id)
   
   sqlite3_bind_text(insertStatement, 2, , -1, nil)
   
   sqlite3_bind_int(insertStatement, 3, Int32())
   
   sqlite3_bind_double(insertStatement, 4, )
   //Step 3   if sqlite3_step(insertStatement) == SQLITE_DONE {
     = "Insert data successfully"
   } else {
     = "Insert data failed"
   }
 } else {
  
 }
 //Step 4 sqlite3_finalize(insertStatement)
}

Code description

  • The ? in insertRowString corresponds to the previous field. It just means a placeholder, telling the compiler to insert the corresponding value when the statement is actually executed.
  • Step 2: sqlite3_bind_int() identifies that you have bound an Int type value. The first parameter of the function is your statement (i.e. insertStatement), and the second parameter is the position of your statement (note that the value is non-zero), which is 1 here, and the third parameter is the value you want to bind. The sqlite3_bind_text() function indicates that you are bound to a text (usually used for longer strings). This function has two additional parameters than sqlite3_bind_int(). The fourth parameter means the number of bytes of text, which is generally passed -1. The fifth parameter is a closure callback, which is called after processing string.
  • Step 3, Step 4, Synchronize

Insert multiple pieces of data

Code

func insertMutipleData() {
  let insertRowString = "INSERT INTO Computer (Id, Name, Weight, Price) VALUES (?, ?, ?, ?);"
  var insertStatement: OpaquePointer?
  //first step  if sqlite3_prepare_v2(db, insertRowString, -1, &insertStatement, nil) == SQLITE_OK {
    for (index, good) in () {
      let id: Int32 = Int32(index + 1)
      //Step 2      sqlite3_bind_int(insertStatement, 1, id)
      
      sqlite3_bind_text(insertStatement, 2, , -1, nil)
      
      sqlite3_bind_int(insertStatement, 3, Int32())
      
      sqlite3_bind_double(insertStatement, 4, )
      //Step 3      if sqlite3_step(insertStatement) == SQLITE_DONE {
         = "Insert data successfully"
      } else {
         = "Insert data failed"
      }
      //Step 4      sqlite3_reset(insertStatement)
    }
  } else {
    
  }
  //Step 5  sqlite3_finalize(insertStatement)
}

Code description

  • insertRowString Same as above.
  • Step 4: Call the sqlite3_reset() function so that the insertStatement will be executed again in the next loop.
  • Step 1, Step 2, Step 3, and Step 5 are put together.

Update data

Code

func updateData() {
  let updateString = "UPDATE Computer SET Name = 'changeComputer' WHERE Id = 2;"
  var updateStatement: OpaquePointer?
  //first step  if sqlite3_prepare_v2(db, updateString, -1, &updateStatement, nil) == SQLITE_OK {
    //Step 2    if sqlite3_step(updateStatement) == SQLITE_DONE {
       = "Update Successfully"
    } else {
      
    }
  }
  //Step 3  sqlite3_finalize(updateStatement)
}

Code description

  • updateString: Change the Name field of the data with Id==2 to changeComputer.
  • sqlite3_prepare_v2(): prepare, sqlite3_step(): execute update statement, sqlite3_finalize(): end.

Delete data

Code

func deleteData() {
  let deleteString = "DELETE FROM Computer WHERE Id = 2;"
  var deleteStatement: OpaquePointer?
  //first step  if sqlite3_prepare_v2(db, deleteString, -1, &deleteStatement, nil) == SQLITE_OK {
    //Step 2    if sqlite3_step(deleteStatement) == SQLITE_DONE {
       = "Delete successfully"
    }
  } else {
    
  }
  //Step 3  sqlite3_finalize(deleteStatement)
}

Code description

  • deleteString: Delete the data with Id==2 in the table.
  • sqlite3_prepare_v2(): prepare, sqlite3_step(): execute the delete statement, sqlite3_finalize(): end.

Query a data

Code

func queryOneData() {
  let queryString = "SELECT * FROM Computer WHERE Id == 2;"
  var queryStatement: OpaquePointer?
  //first step  if sqlite3_prepare_v2(db, queryString, -1, &queryStatement, nil) == SQLITE_OK {
    //Step 2    if sqlite3_step(queryStatement) == SQLITE_ROW {
      //Step 3      let id = sqlite3_column_int(queryStatement, 0)
      
      let queryResultName = sqlite3_column_text(queryStatement, 1)
      let name = String(cString: queryResultName!)
      let weight = sqlite3_column_int(queryStatement, 2)
      let price = sqlite3_column_double(queryStatement, 3)
      
      
       = "id: \(id), name: \(name), weight: \(weight), price: \(price)"
    } else {
       = "error"
    }
  }
  //Step 4  sqlite3_finalize(queryStatement)
}

Code description

  • queryString: Find all data with Id == 2 in the Computer table.
  • Step 2: Note that the status code to be judged at this time is SQLITE_ROW. If the judgment is true, it means that the data you are querying exists in the table.
  • Step 3: The sqlite3_column_int() function takes data according to the number of columns. The first parameter is statement, and the second parameter is which column the field is (Id is the first column in the table, calculated from 0). sqlite3_column_text() is a little more complicated, it needs to convert the type through String(cString: queryResultName!).
  • Step 1 and Step 4 are put together

Query multiple data

Code

func queryAllData() {
  let queryString = "SELECT * FROM Computer;"
  var queryStatement: OpaquePointer?
  //first step  if sqlite3_prepare_v2(db, queryString, -1, &queryStatement, nil) == SQLITE_OK {
    //Step 2    while(sqlite3_step(queryStatement) == SQLITE_ROW) {
      //Step 3      let id = sqlite3_column_int(queryStatement, 0)
      
      let queryResultName = sqlite3_column_text(queryStatement, 1)
      let name = String(cString: queryResultName!)
      let weight = sqlite3_column_int(queryStatement, 2)
      let price = sqlite3_column_double(queryStatement, 3)
      
      
       = "id: \(id), name: \(name), weight: \(weight), price: \(price)"
    }
  }
  //Step 4  sqlite3_finalize(queryStatement)
}

Code description

  • Step 2: This is a while loop. When querying the last line, the SQLITE_DONE status code will be returned to end.
  • Step 1, Step 3, Step 4, and Step 2.

summary

Through the above we can summarize the general process of executing a statement: sqlite3_prepare_v2(): prepare, sqlite3_step(): execute statement, sqlite3_finalize(): end. Okay, the basic operations of SQLite3's addition, deletion, modification and search are all over here. In the next article, let’s learn about the advanced usage of SQLite. Bye~

OK, the above is the entire content of this article. I hope that the content of this article has certain reference value for everyone's study or work. Thank you for your support.