🗄️ SQLite in iOS: Writing Tables, Inserting Records & Bundling a Database with Your App (Xcode)

---

✅ 1. Definition

🔹 Writing Tables

Creating a table in SQLite means defining a structure to store data in rows and columns.

---

🔹 Inserting Records

Adding actual data (rows) into a database table using SQL INSERT.

---

🔹 Bundling Database with App

It means including a pre-created SQLite database inside the app bundle, so the app can use it immediately when installed.

---

🧠 2. Key Concepts

🔹 Table Structure

A table contains:

• Columns (fields) → name, age, id
• Rows (records) → actual data

---

🔹 SQL Commands Used

Operation	SQL Command
Create Table	CREATE
Insert Data	INSERT
Read Data	SELECT

---

🔹 App Bundle

• Read-only location inside the app
• Cannot modify files directly inside bundle
• Database must be copied to Documents folder for editing

---

🏗️ 3. Writing a Table in SQLite

---

🔹 SQL Query to Create Table

CREATE TABLE IF NOT EXISTS Students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER
);

---

🔹 Swift Code

let createTableQuery = """
CREATE TABLE IF NOT EXISTS Students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER
);
"""

if sqlite3_exec(db, createTableQuery, nil, nil, nil) == SQLITE_OK {
    print("Table Created Successfully")
} else {
    print("Table Creation Failed")
}

---

➕ 4. Inserting Records into Table

---

🔹 SQL Insert Query

INSERT INTO Students (name, age)
VALUES ('Ali', 20);

---

🔹 Swift Code for Insert

let insertQuery = "INSERT INTO Students (name, age) VALUES ('Ali', 20);"

if sqlite3_exec(db, insertQuery, nil, nil, nil) == SQLITE_OK {
    print("Record Inserted Successfully")
} else {
    print("Insert Failed")
}

---

🔹 Dynamic Insert (Recommended)

var stmt: OpaquePointer?

let query = "INSERT INTO Students (name, age) VALUES (?, ?)"

if sqlite3_prepare_v2(db, query, -1, &stmt, nil) == SQLITE_OK {
    
    sqlite3_bind_text(stmt, 1, "Sara", -1, nil)
    sqlite3_bind_int(stmt, 2, 22)
    
    if sqlite3_step(stmt) == SQLITE_DONE {
        print("Data inserted")
    }
}

sqlite3_finalize(stmt)

---

📊 5. Database Table Diagram (for Exams)

Table: Students
-------------------------
| id | name | age      |
-------------------------
| 1  | Ali  | 20       |
| 2  | Sara | 22       |
-------------------------

---

📦 6. Bundling SQLite Database with App

---

🔹 Step 1: Add Database File

• Drag .sqlite file into Xcode project
• Select Copy items if needed

---

🔹 Step 2: Access Database from Bundle

let bundlePath = Bundle.main.path(forResource: "AppDatabase", ofType: "sqlite")

---

🔹 Step 3: Copy to Documents Directory (Important)

👉 Bundle is read-only, so we copy database:

let fileManager = FileManager.default

let documentsURL = fileManager.urls(for: .documentDirectory, in: .userDomainMask).first!
let destPath = documentsURL.appendingPathComponent("AppDatabase.sqlite")

if !fileManager.fileExists(atPath: destPath.path) {
    try fileManager.copyItem(atPath: bundlePath!, toPath: destPath.path)
    print("Database copied to Documents folder")
}

---

🔹 Step 4: Use Copied Database

sqlite3_open(destPath.path, &db)

---

💡 7. Example App

🎯 Student Management App

• Preloaded database contains student list

• App copies database on first launch

• Users can:

  • View students
  • Add new students
  • Store data permanently

---

📌 8. Important Rules / Tips

• Always copy bundled DB to Documents directory
• Use IF NOT EXISTS when creating tables
• Use prepared statements for safe insertion
• Close database after operations
• Keep schema simple and structured

---

⚠️ 9. Common Mistakes

• ❌ Trying to modify database inside app bundle
• ❌ Forgetting to copy database to Documents folder
• ❌ Not checking SQL execution success
• ❌ Using unsafe direct string queries
• ❌ Incorrect table structure

---

🧠 10. Best Practices

• Always use prepared statements
• Validate user input before inserting
• Store database in Documents directory
• Keep backup of initial database in bundle
• Use meaningful table/column names

---

📝 11. Likely Exam Questions

1. What is SQLite used for in iOS?
2. Write SQL query to create a table.
3. How do you insert records in SQLite?
4. What is a bundled database?
5. Why do we copy database from bundle to Documents folder?
6. Write Swift code for inserting data.
7. Explain structure of SQLite table.
8. What is the use of sqlite3_prepare_v2()?

---

📚 12. Quick Revision Summary

• Tables store structured data (rows & columns)
• Use CREATE TABLE to define structure
• Use INSERT to add records
• Bundled DB = preloaded database inside app
• Must copy DB to Documents for writing
• SQLite used for offline data storage
• Essential for student, notes, inventory apps

---