GRDB

Overview

Direct SQLite access using 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 iOS 13+, Swift 5.7+ 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 axiom-sqlitedata-ref 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 axiom-database-migration skill for safe schema evolution patterns.

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, database.trace for 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

Database Setup

DatabaseQueue (Single Connection)

import GRDB

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

// In-memory database (tests)
let dbQueue = try DatabaseQueue()

DatabasePool (Connection Pool)

// For apps with heavy concurrent access
let dbPool = try DatabasePool(path: dbPath)

Use Queue for Most apps (simpler, sufficient) Use Pool for Heavy concurrent writes from multiple threads

Record Types

Using Codable

struct Track: Codable {
    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)  // Codable conformance provides insert
}

FetchableRecord (Read-Only)

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)

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)
}

Raw SQL Queries

Reading Data

// 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")
}

Writing Data

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

Transactions

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

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

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

Sorting

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

Joins

struct TrackWithAlbum: FetchableRecor