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: FetchableRecord { var trackTitle: String var albumTitle: String }

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

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

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

import GRDB import GRDBQuery // https://github.com/groue/GRDBQuery

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

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

func publisher(in dbQueue: DatabaseQueue) -> AnyPublisher<[Track], Error> {
    ValueObservation
        .tracking { db in try Track.fetchAll(db) }
        .publisher(in: dbQueue)
        .eraseToAnyPublisher()
}

}

See GRDBQuery documentation for SwiftUI reactive bindings.

Filtered Observation

func observeGenre(_ genre: String) -> ValueObservation<[Track]> { ValueObservation.tracking { db in try Track .filter(Column("genre") == genre) .fetchAll(db) } }

let cancellable = observeGenre("Rock") .publisher(in: dbQueue) .sink { tracks in print("Rock tracks: (tracks.count)") }

Migrations

DatabaseMigrator

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 axiom-database-migration skill.

Migration with Data Transform

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

try dbQueue.write { db in for batch in tracks.chunked(into: 500) { for track in batch { try track.insert(db) } } }

Prepared Statements

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

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

// 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:

import SQLiteData import GRDB

@Dependency(.database) var database // SQLiteData Database

// Access underlying GRDB DatabaseQueue try await database.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

// Read single value let count = try db.fetchOne(Int.self, 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])

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

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

Resources

GitHub: groue/GRDB.swift, groue/GRDBQuery

Docs: sqlite.org/docs.html

Skills: axiom-database-migration, axiom-sqlitedata, axiom-swiftdata

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

• Blindly add indexes (don't know which columns help)

• Move logic to Swift (premature escape from database)

• Over-engineer migrations (distrust the system)

DO

• Profile with database.trace

• Use EXPLAIN QUERY PLAN to understand execution

• Trust GRDB's migration versioning system

Profiling Complex Queries

When query is slow (10+ seconds)

var database = try DatabaseQueue(path: dbPath)

// Enable tracing to see SQL execution database.trace { print($0) }

// 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(fetching: db, sql: "EXPLAIN QUERY PLAN SELECT ...") print(plan) // Look for SCAN (slow, full table) vs SEARCH (fast, indexed) }

Add indexes strategically

// 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

// Re-evaluates query on ANY write to database ValueObservation.tracking { db in try Track.fetchAll(db) }.start(in: database, onError: { }, onChange: { tracks in // Called for every change — CPU spike! })

Optimization patterns

// Coalesce rapid updates (recommended) ValueObservation.tracking { db in try Track.fetchAll(db) }.removeDuplicates() // Skip duplicate results .debounce(for: 0.5, scheduler: DispatchQueue.main) // Batch updates .start(in: database, ...)

Decision framework

• Small datasets (<1000 records): Use plain .tracking

• Medium datasets (1-10k records): Add .removeDuplicates()

• .debounce()

• Large datasets (10k+ records): Use explicit table dependencies or predicates

Migration Versioning Guarantees

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

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

for track in 50000Tracks { try dbQueue.write { db in try track.insert(db) } // 50k transactions! }

Fix Single transaction with batches

❌ Synchronous database access on main thread

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

// Slow query without index try Track.filter(Column("genre") == "Rock").fetchAll(db)

Fix Create indexes on frequently queried columns

❌ N+1 queries

for track in tracks { let album = try Album.fetchOne(db, key: track.albumId) // N queries! }

Fix Use JOIN or batch fetch

Targets: iOS 13+, Swift 5.7+ Framework: GRDB.swift 6.0+ History: See git log for changes