axiom-grdb

---
name: axiom-grdb
description: "Use when writing raw SQL queries with GRDB, complex joins, ValueObservation for reactive queries, DatabaseMigrator patterns, query profiling under performance pressure, or dropping down from SQLiteData for performance - direct SQLite access for iOS/macOS"
---


# GRDB

## Overview

Direct SQLite access using [GRDB.swift](https://github.com/groue/GRDB.swift) — a toolkit for SQLite databases with type-safe queries, migrations, and reactive observation.

**Core principle** Type-safe Swift wrapper around raw SQL with full SQLite power when you need it.

**Requires** GRDB 7.9+, Swift 6.1+, Xcode 16.3+ — GRDB 7.9 raised the toolchain floor. Features added later in the 7.x line are marked inline.
**License** MIT (free and open source)

## When to Use GRDB

#### Use raw GRDB when you need
- ✅ Complex SQL joins across 4+ tables
- ✅ Window functions (ROW_NUMBER, RANK, LAG/LEAD)
- ✅ Reactive queries with ValueObservation
- ✅ Full control over SQL for performance
- ✅ Advanced migration logic beyond schema changes

**Note:** SQLiteData now supports GROUP BY (`.group(by:)`) and HAVING (`.having()`) via the query builder — see the `skills/sqlitedata-ref.md` skill.

#### Use SQLiteData instead when
- Type-safe `@Table` models are sufficient
- CloudKit sync needed
- Prefer declarative queries over SQL

#### Use SwiftData when
- Simple CRUD with native Apple integration
- Don't need raw SQL control

**For migrations** See the `skills/database-migration.md` skill for safe schema evolution patterns.

**For performance, FTS, or app-group sharing**

- Query slow, schema design, `PRAGMA optimize`, `EXPLAIN QUERY PLAN`, index design, cursors → `skills/grdb-performance.md`
- Full-text search (FTS5), tokenizers, Unicode normalization, external-content sync → `skills/sqlite-fts-ref.md`
- Database shared with widget, extension, or Live Activity → `skills/grdb-app-groups.md`

## Example Prompts

These are real questions developers ask that this skill is designed to answer:

#### 1. "I need to query messages with their authors and count of reactions in one query. How do I write the JOIN?"
→ The skill shows complex JOIN queries with multiple tables and aggregations

#### 2. "I want to observe a filtered list and update the UI whenever notes with a specific tag change."
→ The skill covers ValueObservation patterns for reactive query updates

#### 3. "I'm importing thousands of chat records and need custom migration logic. How do I use DatabaseMigrator?"
→ The skill explains migration registration, data transforms, and safe rollback patterns

#### 4. "My query is slow (takes 10+ seconds). How do I profile and optimize it?"
→ The skill covers EXPLAIN QUERY PLAN, `db.trace` for SQL profiling, and index creation

#### 5. "I need to fetch tasks grouped by due date with completion counts, ordered by priority. Raw SQL seems easier than type-safe queries."
→ The skill demonstrates when GRDB's raw SQL is clearer than type-safe wrappers

#### 6. "My sync writes server rows over the top of edits the user just made locally. How do I insert new rows without touching existing ones?"
→ The skill covers upsert conflict strategies and why `INSERT OR REPLACE` makes this worse

---

## Database Setup

### DatabaseQueue (Single Connection)

```swift
import GRDB

// File-based database
let dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
let dbQueue = try DatabaseQueue(path: "\(dbPath)/db.sqlite")

// In-memory database, for tests
let testQueue = try DatabaseQueue()
```

### DatabasePool (Connection Pool)

```swift
// Parallel reads alongside one serialized writer (WAL)
let dbPool = try DatabasePool(path: dbPath)
```

**Use Queue for** Most apps (simpler, sufficient)
**Use Pool for** Concurrent reads during writes (UI reads keep flowing while a slow background write runs). Writes are still serialized one at a time, so a pool does not speed up write-heavy workloads.

## Record Types

> A nested `Codable` property (array, dictionary, or struct) is stored as a JSON string automatically. For querying inside those columns (`JSONColumn`, `->>`, the `Database.json*` functions), JSONB, and indexing JSON fields, see `sql-json-ref.md`.

### Using Codable

**`Codable` alone is not a record.** The conformance supplies the column mapping; the `fetchAll`/`insert` methods come from declaring `FetchableRecord` and `PersistableRecord`.

```swift
struct Track: Codable, FetchableRecord, PersistableRecord {
    var id: String
    var title: String
    var artist: String
    var duration: TimeInterval
}

// Fetch
let tracks = try dbQueue.read { db in
    try Track.fetchAll(db, sql: "SELECT * FROM tracks")
}

// Insert
try dbQueue.write { db in
    try track.insert(db)  // PersistableRecord provides insert
}
```

### FetchableRecord (Read-Only)

```swift
struct TrackInfo: FetchableRecord {
    var title: String
    var artist: String
    var albumTitle: String

    init(row: Row) {
        title = row["title"]
        artist = row["artist"]
        albumTitle = row["album_title"]
    }
}

let results = try dbQueue.read { db in
    try TrackInfo.fetchAll(db, sql: """
        SELECT tracks.title, tracks.artist, albums.title as album_title
        FROM tracks
        JOIN albums ON tracks.albumId = albums.id
        """)
}
```

### PersistableRecord (Write)

```swift
struct Track: Codable, PersistableRecord {
    var id: String
    var title: String

    // Customize table name
    static let databaseTableName = "tracks"
}

try dbQueue.write { db in
    var track = Track(id: "1", title: "Song")
    try track.insert(db)

    track.title = "Updated"
    try track.update(db)

    try track.delete(db)
}
```

## Upsert (Insert or Update)

`upsert` writes a row that may or may not already exist, in a single statement. It is the right primitive for sync, import, and cache-fill code — no fetch-then-branch race window, and none of the data loss `INSERT OR REPLACE` causes.

**Requires SQLite 3.35+** (iOS 15+/macOS 12+) — satisfied on Axiom's floor.

```swift
// On conflict, overwrite every column with the inserted values
try player.upsert(db)

// Same, and return the stored row (requires FetchableRecord)
let stored = try player.upsertAndFetch(db)
```

### Controlling which columns a conflict updates

The `updating:` parameter takes `UpsertUpdateStrategy` — `.allColumns` (default) or `.noColumnUnlessSpecified`. The strategy parameter is **GRDB 7.10+**; the closure form works on any GRDB 7.

| Goal | Call |
|---|---|
| Overwrite everything (default) | `upsertAndFetch(db)` |
| Overwrite everything except one column | `upsertAndFetch(db) { _ in [Column("score").noOverwrite] }` |
| Never overwrite an existing row's values | `upsertAndFetch(db, updating: .noColumnUnlessSpecified)` |
| Update only the columns you name | `upsertAndFetch(db, updating: .noColumnUnlessSpecified) { … }` |

```swift
// Import that must not clobber locally-edited rows
let existing = try player.upsertAndFetch(db, updating: .noColumnUnlessSpecified)

// Refresh only `name` from the server; leave every other column untouched
let refreshed = try player.upsertAndFetch(db, updating: .noColumnUnlessSpecified) { excluded in
    [Column("name").set(to: excluded[Column("name")])]
}
```

`excluded` is SQLite's `excluded` pseudo-table — the values that *would* have been inserted.

**`.noColumnUnlessSpecified` still rewrites the row.** No column value changes, and `upsertAndFetch` correctly returns the stored row — but because the statement needs a `RETURNING` clause to produce it, GRDB emits a self-assignment of the first primary-key column (`SET "id" = "id"`). Any `AFTER UPDATE` trigger fires, and observers see a change event. The data is safe; the side effects are not zero.

**`.allColumns` covers the columns the *record* encodes**, minus the primary key and conflict target — not every column in the table. A column the record doesn't encode is never touched. The hazard is therefore in the record type, not the schema: add a property to the record and it silently joins the overwrite set. For a table the user also edits locally, prefer `.noColumnUnlessSpecified` with explicit assignments over a `.noOverwrite` list that each new property can quietly defeat.

### ❌ `INSERT OR REPLACE` is not an upsert

`INSERT OR REPLACE` — GRDB's `.replace` conflict policy — **deletes the existing row, then inserts a new one**. Consequences:

- Columns absent from the insert reset to their defaults — silent data loss
- `ON DELETE CASCADE` fires, taking child rows with it
- The rowid changes, invalidating anything holding it
- Transaction observers never see the duplicate-row deletion, so observations go stale

Use `upsert`. Choose `.replace` only when delete-then-insert is genuinely the semantics you want.

### `WITHOUT ROWID` tables

Two constraints, both covered in `skills/grdb-performance.md` §7. Upsert against a `WITHOUT ROWID` table needs **GRDB 7.11+**, and still fails on 7.11.1 when that table's primary key is `INTEGER`. Such tables are also **never observed** — a `ValueObservation` on one goes silent after its initial value. Settings and key-value stores are the common case for all three of "`WITHOUT ROWID`", "upsert target", and "observed", so check before combining them.

The `INTEGER` failure is GRDB misclassifying the primary key, not a SQLite limit: `Database+Schema.swift` returns a rowid primary key for *any* single-column `INTEGER` primary key, so the generated `RETURNING rowid` names a column the table does not have. [PR #1879](https://github.com/groue/GRDB.swift/pull/1879) is open to fix it. The observation silence is SQLite's own: the update hook is not invoked for `WITHOUT ROWID` tables. Until those land, a write that calls `try db.notifyChanges(in: Track.all())` revives the observation.

## Raw SQL Queries

### Reading Data

```swift
// Fetch all rows
let rows = try dbQueue.read { db in
    try Row.fetchAll(db, sql: "SELECT * FROM tracks WHERE genre = ?", arguments: ["Rock"])
}

// Fetch single value
let count = try dbQueue.read { db in
    try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM tracks")
}

// Fetch into Codable
let tracks = try dbQueue.read { db in
    try Track.fetchAll(db, sql: "SELECT * FROM tracks ORDER BY title")
}
```

### Cursors for Large Result Sets

`fetchCursor` streams rows lazily — use it instead of `fetchAll` when the result set is large or memory-sensitive.

```swift
try dbQueue.read { db in
    let cursor = try Track.fetchCursor(db, sql: "SELECT * FROM tracks")
    while let track = try cursor.next() {
        process(track)
    }
}
```

**Critical:** cursors MUST be consumed inside the `read { ... }` closure — pulling a cursor out is undefined behavior. Cursors iterate once. With `Cursor<Row>`, `Row` is reused per iteration (use `row.copy()` to snapshot).

For design guidance on cursors vs `fetchAll`, see `skills/grdb-performance.md` §9.

### Writing Data

```swift
try dbQueue.write { db in
    try db.execute(sql: """
        INSERT INTO tracks (id, title, artist, duration)
        VALUES (?, ?, ?, ?)
        """, arguments: ["1", "Song", "Artist", 240])
}
```

### Transactions

```swift
try dbQueue.write { db in
    // Automatic transaction - all or nothing
    for track in tracks {
        try track.insert(db)
    }
    // Commits automatically on success, rolls back on error
}
```

## Type-Safe Query Interface

### Filtering

```swift
let request = Track
    .filter(Column("genre") == "Rock")
    .filter(Column("duration") > 180)

let tracks = try dbQueue.read { db in
    try request.fetchAll(db)
}
```

### Sorting

```swift
let request = Track
    .order(Column("title").asc)
    .limit(10)
```

### Joins

```swift
struct TrackWithAlbum: FetchableRecord {
    var trackTitle: String
    var albumTitle: String

    init(row: Row) {
        trackTitle = row["trackTitle"]
        albumTitle = row["albumTitle"]
    }
}

let request = Track
    .joining(required: Track.belongsTo(Album.self))
    .select(Column("title").forKey("trackTitle"), Column("album_title").forKey("albumTitle"))

let results = try dbQueue.read { db in
    try TrackWithAlbum.fetchAll(db, request)
}
```

## Complex Joins

```swift
let sql = """
    SELECT
        tracks.title as track_title,
        albums.title as album_title,
        artists.name as artist_name,
        COUNT(plays.id) as play_count
    FROM tracks
    JOIN albums ON tracks.albumId = albums.id
    JOIN artists ON albums.artistId = artists.id
    LEFT JOIN plays ON plays.trackId = tracks.id
    WHERE artists.genre = ?
    GROUP BY tracks.id
    HAVING play_count > 10
    ORDER BY play_count DESC
    LIMIT 50
    """

struct TrackStats: FetchableRecord {
    var trackTitle: String
    var albumTitle: String
    var artistName: String
    var playCount: Int

    init(row: Row) {
        trackTitle = row["track_title"]
        albumTitle = row["album_title"]
        artistName = row["artist_name"]
        playCount = row["play_count"]
    }
}

let stats = try dbQueue.read { db in
    try TrackStats.fetchAll(db, sql: sql, arguments: ["Rock"])
}
```

## ValueObservation (Reactive Queries)

### Basic Observation

```swift
import GRDB
import Combine

let observation = ValueObservation.tracking { db in
    try Track.fetchAll(db)
}

// Start observing with Combine
let cancellable = observation.publisher(in: dbQueue)
    .sink(
        receiveCompletion: { _ in },
        receiveValue: { tracks in
            print("Tracks updated: \(tracks.count)")
        }
    )
```

### SwiftUI Integration

```swift
import Combine
import GRDB
import GRDBQuery  // https://github.com/groue/GRDBQuery

@Query(Tracks())
var tracks: [Track]

struct Tracks: Queryable {
    static var defaultValue: [Track] { [] }

    @MainActor func publisher(in context: DatabaseContext) throws -> AnyPublisher<[Track], Error> {
        ValueObservation
            .tracking { db in try Track.fetchAll(db) }
            .publisher(in: try context.reader)
            .eraseToAnyPublisher()
    }
}
```

**Requires GRDBQuery 0.9+** — `Queryable` is generic over its `Context` (default `DatabaseContext`), and `publisher(in:)` is `@MainActor` and throws. See the [GRDBQuery documentation](https://github.com/groue/GRDBQuery) for SwiftUI reactive bindings.

### DatabaseRegionObservation

Use `DatabaseRegionObservation` when you need *transaction notifications* rather than fresh values — the callback receives a `Database` for inspection, not fetched results.

```swift
let observation = DatabaseRegionObservation(tracking: Track.all())
let cancellable = observation.start(in: dbQueue) { error in
    // handle error
} onChange: { db in
    // a transaction touched the tracked region
}
```

**Use this instead of `ValueObservation` when:**
- You need every individual transaction (no coalescing)
- You're broadcasting cross-process change notifications (see `skills/grdb-app-groups.md` §7)
- Synchronous-after-commit semantics matter

See `skills/grdb-performance.md` §10 for design tradeoffs.

### Filtered Observation

```swift
// ValueObservation is generic over its reducer, not its value
func observeGenre(_ genre: String) -> ValueObservation<ValueReducers.Fetch<[Track]>> {
    ValueObservation.tracking { db in
        try Track
            .filter(Column("genre") == genre)
            .fetchAll(db)
    }
}

let cancellable = observeGenre("Rock")
    .publisher(in: dbQueue)
    .sink(receiveCompletion: { _ in }, receiveValue: { tracks in
        print("Rock tracks: \(tracks.count)")
    })
```

## Migrations

### DatabaseMigrator

```swift
var migrator = DatabaseMigrator()

// Migration 1: Create tables
migrator.registerMigration("v1") { db in
    try db.create(table: "tracks") { t in
        t.column("id", .text).primaryKey()
        t.column("title", .text).notNull()
        t.column("artist", .text).notNull()
        t.column("duration", .real).notNull()
    }
}

// Migration 2: Add column
migrator.registerMigration("v2_add_genre") { db in
    try db.alter(table: "tracks") { t in
        t.add(column: "genre", .text)
    }
}

// Migration 3: Add index
migrator.registerMigration("v3_add_indexes") { db in
    try db.create(index: "idx_genre", on: "tracks", columns: ["genre"])
}

// Run migrations
try migrator.migrate(dbQueue)
```

**For migration safety patterns** See the `skills/database-migration.md` skill.

### Migration with Data Transform

```swift
migrator.registerMigration("v4_normalize_artists") { db in
    // 1. Create new table
    try db.create(table: "artists") { t in
        t.column("id", .text).primaryKey()
        t.column("name", .text).notNull()
    }

    // 2. Extract unique artists
    try db.execute(sql: """
        INSERT INTO artists (id, name)
        SELECT DISTINCT
            lower(replace(artist, ' ', '_')) as id,
            artist as name
        FROM tracks
        """)

    // 3. Add foreign key to tracks
    try db.alter(table: "tracks") { t in
        t.add(column: "artistId", .text)
            .references("artists", onDelete: .cascade)
    }

    // 4. Populate foreign keys
    try db.execute(sql: """
        UPDATE tracks
        SET artistId = (
            SELECT id FROM artists
            WHERE artists.name = tracks.artist
        )
        """)
}
```

## Performance Patterns

### Batch Writes

```swift
try dbQueue.write { db in
    for start in stride(from: 0, to: tracks.count, by: 500) {
        let batch = tracks[start..<min(start + 500, tracks.count)]
        for track in batch {
            try track.insert(db)
        }
    }
}
```

### Prepared Statements

```swift
try dbQueue.write { db in
    let statement = try db.makeStatement(sql: """
        INSERT INTO tracks (id, title, artist, duration)
        VALUES (?, ?, ?, ?)
        """)

    for track in tracks {
        try statement.execute(arguments: [track.id, track.title, track.artist, track.duration])
    }
}
```

### Indexes

```swift
try db.create(index: "idx_tracks_artist", on: "tracks", columns: ["artist"])
try db.create(index: "idx_tracks_genre_duration", on: "tracks", columns: ["genre", "duration"])

// Unique index
try db.create(index: "idx_tracks_unique_title", on: "tracks", columns: ["title"], unique: true)
```

### Query Planning

```swift
// Analyze query performance
let explanation = try dbQueue.read { db in
    try String.fetchOne(db, sql: "EXPLAIN QUERY PLAN SELECT * FROM tracks WHERE artist = ?", arguments: ["Artist"])
}
print(explanation)
```

## Dropping Down from SQLiteData

When using SQLiteData but need GRDB for specific operations:

```swift
import SQLiteData
import GRDB

@Dependency(\.defaultDatabase) var database  // SQLiteData's DatabaseWriter

// Access the underlying GRDB writer
try await database.write { db in
    // Full GRDB power here
    try db.execute(sql: "CREATE INDEX idx_genre ON tracks(genre)")
}
```

#### Common scenarios
- Complex JOIN queries
- Custom migrations
- Bulk SQL operations
- ValueObservation setup

## Quick Reference

### Common Operations

```swift
// Read single value
let count = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM tracks")

// Read all rows
let rows = try Row.fetchAll(db, sql: "SELECT * FROM tracks WHERE genre = ?", arguments: ["Rock"])

// Write
try db.execute(sql: "INSERT INTO tracks VALUES (?, ?, ?)", arguments: [id, title, artist])

// Insert or update in one statement
try track.upsert(db)
_ = try track.upsertAndFetch(db, updating: .noColumnUnlessSpecified)  // don't overwrite values

// Transaction
try dbQueue.write { db in
    // All or nothing
}

// Observe changes
ValueObservation.tracking { db in
    try Track.fetchAll(db)
}.publisher(in: dbQueue)
```

## Encryption at Rest (SQLCipher)

GRDB encrypts with SQLCipher v3.4+. This is a *different* protection than the Data Protection classes in `skills/grdb-app-groups.md` §4 — Data Protection is enforced by the file system and keyed to the device passcode; SQLCipher encrypts the database file itself with a passphrase you manage.

**The setup cost is real.** CocoaPods is the smooth path (`pod 'GRDB.swift/SQLCipher'` plus `pod 'SQLCipher'`, and it must be the *only* active GRDB pod in the project or you get linker/runtime conflicts with the system SQLite). For **SPM you must fork GRDB and edit `Package.swift`** — 7.10.0 shrank that diff to an auditable minimum but did not remove the fork. Instructions are in `Package.swift` itself, in comments marked `GRDB+SQLCipher`.

**This changes the SQLite version floor everywhere else.** A SQLCipher build vendors its own SQLite, so the system-SQLite version table in `skills/sql-json-ref.md` §1 no longer describes your build — check the version SQLCipher actually ships, not the OS's.

Keep the passphrase in the Keychain, never in source or `UserDefaults`. See axiom-security.

## Resources

**GitHub**: groue/GRDB.swift, groue/GRDBQuery

**Docs**: sqlite.org/docs.html, sqlite.org/lang_upsert.html, sqlcipher.net

**Skills**: axiom-data (skills/database-migration.md), axiom-data (skills/sqlitedata.md), axiom-data (skills/swiftdata.md)

## Production Performance: Query Optimization Under Pressure

### Red Flags — When GRDB Queries Slow Down

If you see ANY of these symptoms:
- ❌ Complex JOIN query takes 10+ seconds
- ❌ ValueObservation runs on every single change (battery drain)
- ❌ Can't explain why migration ran twice on old version

#### DO NOT
1. Blindly add indexes (don't know which columns help)
2. Move logic to Swift (premature escape from database)
3. Over-engineer migrations (distrust the system)

#### DO
1. Profile with SQL tracing (`db.trace`, configured on the queue)
2. Use `EXPLAIN QUERY PLAN` to understand execution
3. Trust GRDB's migration versioning system
4. **Apply `PRAGMA optimize` on connection setup and periodically** — single biggest cheap perf win. See `skills/grdb-performance.md` §4 for the exact pattern.

### Profiling Complex Queries

#### When query is slow (10+ seconds)

```swift
var config = Configuration()

// Enable tracing to see SQL execution
config.prepareDatabase { db in
    db.trace { print($0) }
}
let database = try DatabaseQueue(path: dbPath, configuration: config)

// Run the slow query
try database.read { db in
    let results = try Track.fetchAll(db)  // Watch output for execution time
}

// Use EXPLAIN QUERY PLAN to understand execution:
try database.read { db in
    let plan = try String.fetchOne(db, sql: "EXPLAIN QUERY PLAN SELECT ...")
    print(plan as Any)
    // Look for SCAN (slow, full table) vs SEARCH (fast, indexed)
}
```

#### Add indexes strategically

```swift
// Add index on frequently queried column
try database.write { db in
    try db.execute(sql: "CREATE INDEX idx_plays_track_id ON plays(track_id)")
}
```

#### Time cost
- Profile: 10 min (enable trace, run query, read output)
- Understand: 5 min (interpret EXPLAIN QUERY PLAN)
- Fix: 5 min (add index)
- **Total: 20 minutes** (vs 30+ min blindly trying solutions)

### ValueObservation Performance

#### When using reactive queries, know the costs

```swift
// Re-evaluates the query on any write that touches the observed table
ValueObservation.tracking { db in
    try Track.fetchAll(db)
}.start(in: database, onError: { _ in }, onChange: { tracks in
    // Called for every change — CPU spike!
})
```

#### Optimization patterns

```swift
import Combine

// Coalesce rapid updates (recommended)
ValueObservation.tracking { db in
    try Track.fetchAll(db)
}
.publisher(in: database)  // Combine operators live on the publisher, not the observation
.removeDuplicates()  // Skip duplicate results (Track: Equatable)
.debounce(for: 0.5, scheduler: DispatchQueue.main)  // Batch updates
.sink(receiveCompletion: { _ in }, receiveValue: { tracks in
    print(tracks.count)
})
```

#### Decision framework
- Small datasets (<1000 records): Use plain `.tracking`
- Medium datasets (1-10k records): add `.removeDuplicates()` and `.debounce()` on the publisher
- Large datasets (10k+ records): Use explicit table dependencies or predicates

### Migration Versioning Guarantees

#### Trust GRDB's DatabaseMigrator - it prevents re-running migrations

```swift
var migrator = DatabaseMigrator()

migrator.registerMigration("v1_initial") { db in
    try db.execute(sql: "CREATE TABLE tracks (...)")
}

migrator.registerMigration("v2_add_plays") { db in
    try db.execute(sql: "CREATE TABLE plays (...)")
}

// GRDB guarantees:
// - Each migration runs exactly ONCE
// - In order (v1, then v2)
// - Safe to call migrate() multiple times
try migrator.migrate(dbQueue)
```

#### You don't need defensive SQL (IF NOT EXISTS)
- GRDB tracks which migrations have run
- Running `migrate()` twice only executes new ones
- Over-engineering adds complexity without benefit

#### Trust it.

---

## Common Mistakes

### ❌ Not using transactions for batch writes
```swift
// manyTracks holds 50,000 records
for track in manyTracks {
    try dbQueue.write { db in try track.insert(db) }  // 50k transactions!
}
```
**Fix** Single transaction with batches

### ❌ Synchronous database access on main thread
```swift
let tracks = try dbQueue.read { db in try Track.fetchAll(db) }  // Blocks UI
```
**Fix** Use async/await or dispatch to background queue

### ❌ Forgetting to add indexes
```swift
// Slow query without index
try Track.filter(Column("genre") == "Rock").fetchAll(db)
```
**Fix** Create indexes on frequently queried columns

### ✅ N+1 *reads* are not a mistake here

```swift
for track in tracks {
    let album = try Album.fetchOne(db, key: track.albumId)  // fine in SQLite
}
```

The N+1 rule is a client/server-database rule. SQLite runs in-process, so each query is a function call, not a network round trip — sqlite.org documents rendering a 50-entry timeline in under 25 ms with 200+ statements. Don't contort code into a mega-JOIN to avoid this shape. See `skills/grdb-performance.md` §1.

The write case *is* a mistake, and it's the first entry above: N inserts outside one transaction means N commits.

## tvOS

**No local directory on tvOS is guaranteed to survive between launches.** `Documents`, `Caches`, and `Application Support` are separate directories, and the system may purge them under storage pressure — so treat a local-only GRDB database as a cache, not the source of truth.

**If targeting tvOS**, pair GRDB with CloudKit sync (via CKSyncEngine or SQLiteData's SyncEngine) so iCloud is the persistent store and the local database rebuilds automatically. See axiom-swift (skills/tvos.md) for full tvOS storage constraints.

---

**Targets:** Axiom floor — iOS 18+/macOS 15+
**Framework:** GRDB.swift 7.9+ (current 7.11.1), Swift 6.1+, Xcode 16.3+