Using SQLite as an iOS Cache Layer with GRDB

Perdiem's data flow is straightforward: fetch from the API, cache locally, display from the cache. The cache needs to handle users, transactions, reactions, and financial accounts. Core Data is Apple's recommended solution, but I went with SQLite via GRDB instead. Here's why, and how the implementation works.

Why SQLite Over Core Data

Core Data is powerful, but it carries complexity that I didn't need.

Schema control. With SQLite, I write the schema in SQL. I can see every table, column, index, and constraint in a single file. With Core Data, the schema lives in a visual editor, and the generated model classes add a layer of abstraction between me and the data.

Migration simplicity. Core Data's lightweight migration works until it doesn't, and heavyweight migration is a significant undertaking. With raw SQL migrations, I write an ALTER TABLE or CREATE TABLE statement and increment a version number. It's manual, but it's predictable.

Debugging. I can open the SQLite database file in any SQL browser and inspect the data directly. No need to set up Core Data debugging flags or decode the binary store format.

GRDB as the Interface

GRDB.swift provides a Swift-native interface to SQLite without hiding the database. You write SQL when you want, use the query builder when it's convenient, and define record types that map to rows.

A typical model looks like:

struct Transaction: Codable, FetchableRecord, PersistableRecord {
    var id: String
    var userId: String
    var merchantName: String
    var amount: Double
    var date: Date
    var category: String?

    static let databaseTableName = "transactions"
}

Fetching is explicit:

let transactions = try dbQueue.read { db in
    try Transaction
        .filter(Column("userId") == userId)
        .order(Column("date").desc)
        .limit(50)
        .fetchAll(db)
}

No managed object context, no fetch request boilerplate, no predicate format strings. The SQL that GRDB generates is predictable, and you can always drop down to raw SQL if the query builder doesn't express what you need.

Schema Management

Perdiem's schema lives in numbered SQL files:

DataStore/schema/
├── 00-schema_full.sql     # Complete current schema
├── 18-add-categories.sql  # Migration from v17 to v18
├── 19-plaid-accounts.sql  # Migration from v18 to v19
└── 20-missing-user-reaction.sql  # Migration from v19 to v20

The full schema file (00-schema_full.sql) is used for fresh installs. Migration files are applied sequentially for existing users. Each migration is a small, focused SQL script:

-- 20-missing-user-reaction.sql
ALTER TABLE transaction_reactions ADD COLUMN user_id TEXT;
CREATE INDEX idx_reactions_user ON transaction_reactions(user_id);

The migration runner checks the current database version and applies any pending migrations in order:

func runMigrations(_ db: Database) throws {
    let currentVersion = try getCurrentVersion(db)

    for migration in self.migrations where migration.version > currentVersion {
        try db.execute(sql: migration.sql)
        try setCurrentVersion(db, version: migration.version)
    }
}

The Cache Pattern

The DataStore acts as a write-through cache. When the app fetches data from the API, it writes to SQLite first and reads from SQLite for display:

1. API response arrives → Parse JSON into model structs
2. Write to SQLite → Insert or update using GRDB's save() (upsert)
3. Read from SQLite → UI always queries the local database
4. Display → View controllers observe changes and reload

This means the UI is never waiting on the network for cached data. On app launch, the last-known state appears instantly. When fresh data arrives, it updates the cache and the UI refreshes.

Lessons Learned

Keep migrations small. Each migration should do one thing. A migration that adds a column and restructures an index and migrates data is three migrations. Small migrations are easier to test and less likely to fail on edge-case database states.

Test with real data shapes. Unit tests with empty databases catch schema errors but miss performance issues. I test with databases seeded with realistic data volumes to catch slow queries early.

SQLite is fast enough. I initially worried about query performance for complex feeds. In practice, SQLite handles thousands of rows with indexed queries in single-digit milliseconds on modern iPhones. The bottleneck is always the network, never the database.

Choosing SQLite over Core Data was an early decision that has paid dividends throughout development. The simplicity of raw SQL, combined with GRDB's ergonomic Swift API, gives me a persistence layer that's transparent, fast, and easy to debug.