axiom-grdb

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

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)

使用GRDB.swift实现直接SQLite访问——这是一个为SQLite数据库提供类型安全查询、迁移和响应式观察的工具包。

核心原则 围绕原生SQL的类型安全Swift包装器，在需要时可完全发挥SQLite的全部能力。

要求 iOS 13+、Swift 5.7+ 许可证 MIT（免费开源）

When to Use GRDB

何时使用GRDB

Use raw GRDB when you need

以下场景适合使用原生GRDB

✅ 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.

✅ 涉及4张及以上表的复杂SQL联表查询
✅ 窗口函数（ROW_NUMBER、RANK、LAG/LEAD）
✅ 使用ValueObservation的响应式查询
✅ 为追求性能完全控制SQL语句
✅ 超出 schema 变更的高级迁移逻辑

注意： SQLiteData现在已通过查询构建器支持GROUP BY（

.group(by:)

)和HAVING（

.having()

)——请查看

axiom-sqlitedata-ref

技能文档。

Use SQLiteData instead when

以下场景适合使用SQLiteData替代

Type-safe
```
@Table
```
models are sufficient
CloudKit sync needed
Prefer declarative queries over SQL

类型安全的
```
@Table
```
模型已能满足需求
需要CloudKit同步功能
偏好声明式查询而非SQL语句

Use SwiftData when

以下场景适合使用SwiftData

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.

简单的CRUD操作，需要原生Apple集成
不需要原生SQL的控制权

关于迁移 请查看

axiom-database-migration

技能文档，了解安全的schema演进模式。

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?"

1. 「我需要在一个查询中获取消息及其作者和反应数量，该如何编写JOIN语句？」

→ The skill shows complex JOIN queries with multiple tables and aggregations

→ 本技能会展示涉及多表和聚合操作的复杂JOIN查询示例

2. "I want to observe a filtered list and update the UI whenever notes with a specific tag change."

2. 「我想观察一个过滤后的列表，当带有特定标签的笔记发生变化时更新UI。」

→ The skill covers ValueObservation patterns for reactive query updates

→ 本技能会覆盖实现响应式查询更新的ValueObservation模式

3. "I'm importing thousands of chat records and need custom migration logic. How do I use DatabaseMigrator?"

3. 「我正在导入数千条聊天记录，需要自定义迁移逻辑，该如何使用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?"

4. 「我的查询速度很慢（耗时10秒以上），该如何分析和优化？」

→ The skill covers EXPLAIN QUERY PLAN, database.trace for profiling, and index creation

→ 本技能会介绍EXPLAIN QUERY PLAN、database.trace分析工具以及索引创建方法

5. "I need to fetch tasks grouped by due date with completion counts, ordered by priority. Raw SQL seems easier than type-safe queries."

5. 「我需要按截止日期分组任务并统计完成数量，按优先级排序，原生SQL似乎比类型安全查询更简单。」

→ The skill demonstrates when GRDB's raw SQL is clearer than type-safe wrappers

→ 本技能会演示何时GRDB的原生SQL比类型安全包装器更清晰

Database Setup

数据库设置

DatabaseQueue (Single Connection)

DatabaseQueue（单连接）

swift

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

swift

import GRDB

// 基于文件的数据库
let dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
let dbQueue = try DatabaseQueue(path: "\(dbPath)/db.sqlite")

// 内存数据库（测试用）
let dbQueue = try DatabaseQueue()

DatabasePool (Connection Pool)

DatabasePool（连接池）

swift

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

swift

// 适用于高并发访问的应用
let dbPool = try DatabasePool(path: dbPath)

推荐使用Queue的场景 大多数应用（更简单，足以满足需求） 推荐使用Pool的场景 多线程下的高并发写入操作

Record Types

记录类型

Using Codable

使用Codable

swift

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
}

swift

struct Track: Codable {
    var id: String
    var title: String
    var artist: String
    var duration: TimeInterval
}

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

// 插入
try dbQueue.write { db in
    try track.insert(db)  // Codable协议自动提供insert方法
}

FetchableRecord (Read-Only)

FetchableRecord（只读）

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

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)

PersistableRecord（写入）

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

swift

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

    // 自定义表名
    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

原生SQL查询

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

swift

// 读取所有行
let rows = try dbQueue.read { db in
    try Row.fetchAll(db, sql: "SELECT * FROM tracks WHERE genre = ?", arguments: ["Rock"])
}

// 读取单个值
let count = try dbQueue.read { db in
    try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM tracks")
}

// 读取到Codable对象
let tracks = try dbQueue.read { db in
    try Track.fetchAll(db, sql: "SELECT * FROM tracks ORDER BY title")
}

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

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
}

swift

try dbQueue.write { db in
    // 自动事务——要么全部成功，要么全部回滚
    for track in tracks {
        try track.insert(db)
    }
    // 成功时自动提交，出错时自动回滚
}

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

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)

swift

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

Joins

联表

swift

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

swift

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

复杂联表

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

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)

ValueObservation（响应式查询）

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

swift

import GRDB
import Combine

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

// 使用Combine开始观察
let cancellable = observation.publisher(in: dbQueue)
    .sink(
        receiveCompletion: { _ in },
        receiveValue: { tracks in
            print("Tracks updated: \(tracks.count)")
        }
    )

SwiftUI Integration

SwiftUI集成

swift

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.

swift

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

参考 GRDBQuery文档了解SwiftUI响应式绑定。

Filtered Observation

过滤观察

swift

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

swift

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

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

axiom-database-migration

skill.

swift

var migrator = DatabaseMigrator()

// 迁移1：创建表
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()
    }
}

// 迁移2：添加列
migrator.registerMigration("v2_add_genre") { db in
    try db.alter(table: "tracks") { t in
        t.add(column: "genre", .text)
    }
}

// 迁移3：添加索引
migrator.registerMigration("v3_add_indexes") { db in
    try db.create(index: "idx_genre", on: "tracks", columns: ["genre"])
}

// 执行迁移
try migrator.migrate(dbQueue)

关于迁移安全模式 请查看

axiom-database-migration

技能文档。

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

swift

migrator.registerMigration("v4_normalize_artists") { db in
    // 1. 创建新表
    try db.create(table: "artists") { t in
        t.column("id", .text).primaryKey()
        t.column("name", .text).notNull()
    }

    // 2. 提取唯一艺术家
    try db.execute(sql: """
        INSERT INTO artists (id, name)
        SELECT DISTINCT
            lower(replace(artist, ' ', '_')) as id,
            artist as name
        FROM tracks
        """)

    // 3. 为tracks表添加外键
    try db.alter(table: "tracks") { t in
        t.add(column: "artistId", .text)
            .references("artists", onDelete: .cascade)
    }

    // 4. 填充外键值
    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 batch in tracks.chunked(into: 500) {
        for track in batch {
            try track.insert(db)
        }
    }
}

swift

try dbQueue.write { db in
    for batch in tracks.chunked(into: 500) {
        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])
    }
}

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)

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"])

// 唯一索引
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)

swift

// 分析查询性能
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

从SQLiteData切换到GRDB

When using SQLiteData but need GRDB for specific operations:

swift

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

当使用SQLiteData但需要GRDB处理特定操作时：

swift

import SQLiteData
import GRDB

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

// 访问底层的GRDB DatabaseQueue
try await database.database.write { db in
    // 在此处使用完整的GRDB功能
    try db.execute(sql: "CREATE INDEX idx_genre ON tracks(genre)")
}

Common scenarios

常见场景

Complex JOIN queries
Custom migrations
Bulk SQL operations
ValueObservation setup

复杂联表查询
自定义迁移
批量SQL操作
ValueObservation设置

Quick Reference

快速参考

Common Operations

常见操作

swift

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

swift

// 读取单个值
let count = try db.fetchOne(Int.self, sql: "SELECT COUNT(*) FROM tracks")

// 读取所有行
let rows = try Row.fetchAll(db, sql: "SELECT * FROM tracks WHERE genre = ?", arguments: ["Rock"])

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

// 事务
try dbQueue.write { db in
    // 要么全部成功，要么全部回滚
}

// 观察数据变化
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

GitHub：groue/GRDB.swift, groue/GRDBQuery

文档：sqlite.org/docs.html

相关技能：axiom-database-migration, axiom-sqlitedata, axiom-swiftdata

Production Performance: Query Optimization Under Pressure

生产环境性能：压力下的查询优化

Red Flags — When GRDB Queries Slow Down

危险信号——GRDB查询变慢的症状

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

如果出现以下任何症状：

❌ 复杂联表查询耗时10秒以上
❌ ValueObservation在每次数据变更时都触发（消耗电池）
❌ 无法解释旧版本中迁移执行两次的原因

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)

盲目添加索引（不清楚哪些列需要索引）
将逻辑移至Swift（过早脱离数据库层）
过度设计迁移（不信任系统）

DO

建议

Profile with
```
database.trace
```
Use
```
EXPLAIN QUERY PLAN
```
to understand execution
Trust GRDB's migration versioning system

使用
```
database.trace
```
进行性能分析
使用
```
EXPLAIN QUERY PLAN
```
理解查询执行过程
信任GRDB的迁移版本控制系统

Profiling Complex Queries

复杂查询性能分析

When query is slow (10+ seconds)

当查询变慢（耗时10秒以上）

swift

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

swift

var database = try DatabaseQueue(path: dbPath)

// 启用追踪查看SQL执行过程
database.trace { print($0) }

// 执行慢查询
try database.read { db in
    let results = try Track.fetchAll(db)  // 查看输出中的执行时间
}

// 使用EXPLAIN QUERY PLAN理解执行逻辑：
try database.read { db in
    let plan = try String(fetching: db, sql: "EXPLAIN QUERY PLAN SELECT ...")
    print(plan)
    // 注意区分SCAN（慢，全表扫描）和SEARCH（快，索引查找）
}

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

swift

// 为频繁查询的列添加索引
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)

分析：10分钟（启用追踪、执行查询、查看输出）
理解：5分钟（解读EXPLAIN QUERY PLAN）
修复：5分钟（添加索引）
总计：20分钟（相比盲目尝试解决方案的30+分钟更高效）

ValueObservation Performance

ValueObservation性能优化

When using reactive queries, know the costs

使用响应式查询时，需了解性能成本

swift

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

swift

// 数据库每次写入都会重新执行查询
ValueObservation.tracking { db in
    try Track.fetchAll(db)
}.start(in: database, onError: { }, onChange: { tracks in
    // 每次变更都会触发——导致CPU峰值！
})

Optimization patterns

优化模式

swift

// 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, ...)

swift

// 合并频繁更新（推荐）
ValueObservation.tracking { db in
    try Track.fetchAll(db)
}.removeDuplicates()  // 跳过重复结果
 .debounce(for: 0.5, scheduler: DispatchQueue.main)  // 批量更新
 .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

小型数据集（<1000条记录）：使用普通的
```
.tracking
```
中型数据集（1-10k条记录）：添加
```
.removeDuplicates()
```
+
```
.debounce()
```
大型数据集（10k+条记录）：使用明确的表依赖或谓词

Migration Versioning Guarantees

迁移版本控制保证

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

信任GRDB的DatabaseMigrator——它可防止迁移重复执行

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)

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保证：
// - 每个迁移仅执行一次
// - 按顺序执行（先v1，再v2）
// - 可安全多次调用migrate()
try migrator.migrate(dbQueue)

You don't need defensive SQL (IF NOT EXISTS)

无需编写防御性SQL（如IF NOT EXISTS）

GRDB tracks which migrations have run
Running
```
migrate()
```
twice only executes new ones
Over-engineering adds complexity without benefit

GRDB会追踪已执行的迁移
多次调用
```
migrate()
```
仅执行新的迁移
过度设计会增加复杂度而无实际收益

Trust it.

请信任该系统。

Common Mistakes

常见错误

❌ Not using transactions for batch writes

❌ 批量写入未使用事务

swift

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

Fix Single transaction with batches

swift

for track in 50000Tracks {
    try dbQueue.write { db in try track.insert(db) }  // 50000次事务！
}

修复使用单个事务分批处理

❌ 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

swift

let tracks = try dbQueue.read { db in try Track.fetchAll(db) }  // 阻塞UI

修复使用async/await或调度到后台队列

❌ Forgetting to add indexes

❌ 忘记添加索引

swift

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

Fix Create indexes on frequently queried columns

swift

// 无索引的慢查询
try Track.filter(Column("genre") == "Rock").fetchAll(db)

修复为频繁查询的列创建索引

❌ N+1 queries

❌ N+1查询问题

swift

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

swift

for track in tracks {
    let album = try Album.fetchOne(db, key: track.albumId)  // N次查询！
}

修复使用联表查询或批量读取

目标平台：iOS 13+、Swift 5.7+ 框架版本：GRDB.swift 6.0+ 历史变更：查看git日志了解详情