iOS SQLite: How to Manage Data Efficiently

SQLite empowers iOS developers with remarkable local database capabilities – combining powerful querying, transaction support, and excellent performance without server dependencies. This compact yet mighty solution handles everything from simple data persistence to complex relational operations while maintaining a tiny footprint.

I was building a fitness tracking app last year when I discovered how dramatically SQLite improved my app’s performance. Let’s skip the basics and jump straight into making SQLite work brilliantly in your iOS projects.

Swift-Friendly Setup with SQLite.swift

Setting up SQLite directly with C APIs feels like trying to assemble IKEA furniture without instructions. Let’s use SQLite.swift instead:

// In Package.swift
dependencies: [
    .package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.15.3")
]

Import it and create your database:

import SQLite

// Create database connection
let dbPath = try FileManager.default.url(
  for: .documentDirectory,
  in: .userDomainMask,
  appropriateFor: nil,
  create: false
)
.appendingPathComponent("fitness.db").path

let db = try Connection(dbPath)

The code above creates your database file in the app’s documents directory – perfect for persisting data between app launches.

Table Creation – Your Data’s Home

We’ll now create the most important part of the application which is the database and I’ll be using SQLite for this.

// Define workout table
let workouts = Table("workouts")
let id = Expression<Int64>("id")
let type = Expression<String>("type")
let duration = Expression<Double>("duration")
let calories = Expression<Int>("calories")
let completed = Expression<Bool>("completed")

// Create the table
try db.run(workouts.create { t in
    t.column(id, primaryKey: .autoincrement)
    t.column(type)
    t.column(duration)
    t.column(calories)
    t.column(completed, defaultValue: false)
})

This creates a table tracking workout activities with columns for type, duration, calories burned, and completion status. The primary key automatically increments, saving you from managing unique IDs manually.

CRUD Operations – The Data Lifecycle

Creating Records

Adding workouts becomes surprisingly readable with SQLite.swift:

// Add a new workout
try db.run(workouts.insert(
    type <- "Running",
    duration <- 35.5,
    calories <- 320,
    completed <- true
))

// Output:
// Inserted workout with ID: 1

SQLite.swift transforms your insert operations into clean, expressive code using the <- operator for assignments.

Reading Records

Querying data feels like writing plain English:

// Get all completed workouts
let completedWorkouts = workouts.filter(completed == true)

for workout in try db.prepare(completedWorkouts) {
    print("Workout: \(workout[type]), Duration: \(workout[duration]) minutes")
}

// Output:
// Workout: Running, Duration: 35.5 minutes

The .filter() method works similar to Swift arrays but operates directly on the database, keeping memory usage low even with thousands of records.

Updating Records

Need to correct a workout duration? Simple:

// Update the running workout duration
let runningWorkout = workouts.filter(type == "Running" && id == 1)
try db.run(runningWorkout.update(duration <- 42.0))

// Output:
// Updated workout duration

The update syntax mirrors inserts, maintaining a consistent mental model across operations.

Deleting Records

Removing data follows the same pattern:

// Delete short workouts
let shortWorkouts = workouts.filter(duration < 10)
try db.run(shortWorkouts.delete())

// Output:
// Deleted 3 short workouts

Advanced Query Techniques

Aggregation – Getting Valuable Insights

Transform raw workout data into actionable insights:

// Calculate total calories burned this week
let mondayDate = Calendar.current.date(from: DateComponents(year: 2025, month: 4, day: 22))!
let weeklyWorkouts = workouts.filter(timestamp >= mondayDate)

let totalCalories = try db.scalar(weeklyWorkouts.select(calories.sum))
print("Weekly calories burned: \(totalCalories ?? 0)")

// Output:
// Weekly calories burned: 2150

The .sum function is just one of many aggregations including .count, .avg, .min, and .max – perfect for dashboard metrics.

Transactions – Ensuring Data Integrity

Transactions guarantee all-or-nothing operations:

try db.transaction {
    // Record completion of a workout program
    try db.run(workouts.insert(type <- "Warmup", duration <- 5.0, calories <- 30))
    try db.run(workouts.insert(type <- "Strength", duration <- 25.0, calories <- 210))
    try db.run(workouts.insert(type <- "Cardio", duration <- 20.0, calories <- 180))
    try db.run(workouts.insert(type <- "Cooldown", duration <- 5.0, calories <- 20))
    
    // Update user statistics
    try db.run(userStats.update(lastWorkoutDate <- Date(), 
                               totalWorkouts <- totalWorkouts + 1))
                               
    return .commit
}

If any part fails, the entire transaction rolls back – your data stays consistent even if the app crashes mid-update.

Real-World Application: Offline-First Sync System

Let’s build a workout tracking system with server synchronization:

// Create pending changes table
let pendingSync = Table("pending_sync")
let changeId = Expression<Int64>("id")
// "insert", "update", "delete"
let changeType = Expression<String>("change_type")
let recordId = Expression<Int64?>("record_id")
let recordData = Expression<String>("data_json")
let syncAttempts = Expression<Int>("attempts")

try db.run(
  pendingSync.create { t in
    t.column(changeId, primaryKey: .autoincrement)
    t.column(changeType)
    t.column(recordId)
    t.column(recordData)
    t.column(syncAttempts, defaultValue: 0)
  })

// Track a new workout and queue for sync
func saveWorkout(type: String, duration: Double, calories: Int) {
  try db.transaction {
    // 1. Save to workouts table
    let id = try db.run(
      workouts.insert(
        type <- type,
        duration <- duration,
        calories <- calories,
        completed <- true
      ))

    // 2. Add to sync queue
    let workoutData = """
      {"type":"\(type)","duration":\(duration),"calories":\(calories)}
      """

    try db.run(
      pendingSync.insert(
        changeType <- "insert",
        recordId <- id,
        recordData <- workoutData
      ))

    return .commit
  }
}

// Process pending syncs
func syncWithServer() {
  // Get unsynced items (limit 20 per batch)
  let pendingItems = pendingSync.filter(syncAttempts < 3).limit(20)

  for item in try db.prepare(pendingItems) {
    apiService.sync(
      type: item[changeType],
      recordId: item[recordId],
      data: item[recordData]
    ) { success in
      if success {
        try db.run(pendingSync.filter(changeId == item[changeId]).delete())
      } else {
        try db.run(
          pendingSync.filter(changeId == item[changeId])
            .update(syncAttempts <- item[syncAttempts] + 1))
      }
    }
  }
}

This system ensures data persists locally first, then syncs when connectivity allows – creating a smooth experience regardless of network conditions.

Performance Optimization

Keep your database lightning fast:

// Create strategic indexes
try db.run(workouts.createIndex(type))
try db.run(workouts.createIndex([timestamp, type])) // Compound index

// Use explain query plan to diagnose performance
for row in try db.prepare("EXPLAIN QUERY PLAN SELECT * FROM workouts WHERE type = 'Running'") {
    print(row)
}

Indexes dramatically speed up filtering operations, much like a book’s index helps you find content without reading every page.

Database Migrations

Handle schema evolution gracefully:

// Version tracking table
let versions = Table("schema_version")
let versionNumber = Expression<Int>("version")

// Current app schema version
let currentVersion = 2

// Run migrations
func migrateIfNeeded() {
    try db.transaction {
        // Create version table if needed
        try db.run(versions.create(ifNotExists: true) { t in
            t.column(versionNumber)
        })
        
        // Get current database version
        let dbVersion = try db.scalar(versions.select(versionNumber).limit(1)) ?? 0
        
        // Run needed migrations
        if dbVersion < 1 {
            try db.run("ALTER TABLE workouts ADD COLUMN difficulty TEXT")
        }
        
        if dbVersion < 2 {
            try db.run("ALTER TABLE workouts ADD COLUMN notes TEXT")
            try db.run(workouts.createIndex(difficulty))
        }
        
        // Update version tracking
        if dbVersion == 0 {
            try db.run(versions.insert(versionNumber <- currentVersion))
        } else {
            try db.run(versions.update(versionNumber <- currentVersion))
        }
        
        return .commit
    }
}

This migration system runs only the necessary updates based on the current database version, ensuring smooth upgrades without data loss.

Security Considerations

For sensitive information, consider encryption:

// Using SQLCipher (requires additional dependencies)
let encryptedDB = try Connection(dbPath)
try encryptedDB.key("user_specific_strong_key")

// Alternative: Sensitive data encryption
func encryptSensitiveData(_ data: String) -> String {
    // Implement AES encryption using CryptoKit
    // Store encrypted values rather than plaintext
    return encryptedValue
}

SQLCipher provides full database encryption, while field-level encryption offers targeted protection for specific sensitive values.

Conclusion

SQLite delivers remarkable performance for iOS apps with zero configuration servers. The combination of a tiny footprint, SQL querying power, and transaction support makes it perfect for practically any data persistence need.

The built-in support in iOS means you’re not adding external dependencies to your app, and SQLite.swift provides an elegant Swift-native interface that feels natural alongside your other code.

Whether tracking workouts, caching network data, or building a complete offline-first experience, SQLite handles the complexity while letting you focus on creating a fantastic user experience.