🗄️ Working with Databases in iOS: Importing sqlite3 and Creating a Database (Xcode)

---

✅ 1. Definition

🔹 Database

A database is a structured way to store, manage, and retrieve data efficiently in an app.

🔹 SQLite (sqlite3)

SQLite is a lightweight, embedded relational database used in iOS apps. It stores data in a single file on the device.

👉 In simple words: SQLite allows iOS apps to save data permanently offline.

---

🧠 2. Key Concepts

🔹 SQLite Features

• Lightweight (small size)
• Fast performance
• No server required
• Stored locally on device

---

🔹 SQL (Structured Query Language)

Used to interact with SQLite:

• CREATE → create table
• INSERT → add data
• SELECT → read data
• UPDATE → modify data
• DELETE → remove data

---

🔹 sqlite3 Library

• Built-in C library for SQLite
• Used in iOS via bridging header

---

⚙️ 3. Importing sqlite3 in Xcode

---

🔹 Step 1: Import Library

import SQLite3

---

🔹 Step 2: Add Bridging Header (Important)

Since SQLite is in C language:

Create bridging header file:

ProjectName-Bridging-Header.h

Add:

#import <sqlite3.h>

---

🔹 Step 3: Enable Bridging Header

In Xcode:

• Build Settings → Objective-C Bridging Header
• Add path to header file

---

🏗️ 4. Creating a Database

---

🔹 Step 1: Declare Database Variable

var db: OpaquePointer?

---

🔹 Step 2: Create Database File

let fileURL = try! FileManager.default
    .urls(for: .documentDirectory, in: .userDomainMask)
    .first!
    .appendingPathComponent("AppDatabase.sqlite")

---

🔹 Step 3: Open Database

if sqlite3_open(fileURL.path, &db) == SQLITE_OK {
    print("Database Created Successfully")
} else {
    print("Error creating database")
}

---

🧱 5. Creating a Table

---

🔹 SQL Query Example

CREATE TABLE Users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER
);

---

🔹 Swift Code to Execute Query

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

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

---

📊 6. Database Structure Diagram (for Exams)

Database File (AppDatabase.sqlite)
        ↓
   Table: Users
        ↓
------------------------
| id | name | age     |
------------------------
| 1  | Ali  | 20      |
| 2  | Sara | 22      |

---

💡 7. Example App

🎯 Student Management App

• Store:

  • ID
  • Name
  • Age

👉 Data is saved permanently using SQLite

---

📌 8. Important Rules / Tips

• Always open database before using it
• Use IF NOT EXISTS to avoid errors
• Store database in Documents directory
• Close database after use (good practice)
• Use prepared statements for security

---

⚠️ 9. Common Mistakes

• ❌ Forgetting to import sqlite3
• ❌ Not setting bridging header
• ❌ Wrong file path for database
• ❌ Not checking execution success
• ❌ Using hardcoded SQL without validation

---

🧠 10. Best Practices

• Always check return status (SQLITE_OK)
• Use prepared statements for safety
• Keep database schema simple
• Store DB in Documents directory
• Close DB when not needed

---

📝 11. Likely Exam Questions

1. What is SQLite in iOS?
2. How do you import sqlite3 in Xcode?
3. What is a bridging header?
4. Write steps to create a database in iOS.
5. What is the use of sqlite3_open()?
6. Write SQL query to create a table.
7. Why is SQLite used in mobile apps?
8. Draw database structure diagram.

---

📚 12. Quick Revision Summary

• SQLite = lightweight local database

• Used for offline data storage

• Steps:

  1. Import sqlite3
  2. Create bridging header
  3. Open database
  4. Create tables

• Data stored in .sqlite file

• Used in apps like:

  • Students apps
  • Notes apps
  • Inventory systems

---