axiom-sqlitedata

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQLiteData

Overview

概述

Type-safe SQLite persistence using SQLiteData by Point-Free. A fast, lightweight replacement for SwiftData with CloudKit synchronization support, built on GRDB and StructuredQueries.

Core principle: Value types (

struct

) +

@Table

macro +

database.write { }

blocks for all mutations.

For advanced patterns (CTEs, views, custom aggregates, schema composition), see the

axiom-sqlitedata-ref

reference skill.

Requires: iOS 17+, Swift 6 strict concurrency License: MIT

使用Point-Free开发的SQLiteData实现类型安全的SQLite持久化。这是SwiftData的一款轻量高速替代方案，支持CloudKit同步，基于GRDB和StructuredQueries构建。

核心原则： 值类型（

struct

） +

@Table

宏 +

database.write { }

块处理所有数据变更。

高级模式（CTE、视图、自定义聚合、Schema组合）请参考

axiom-sqlitedata-ref

参考技能。

要求： iOS 17+、Swift 6严格并发模式 许可证： MIT

When to Use SQLiteData

何时使用SQLiteData

Choose SQLiteData when you need:

Type-safe SQLite with compiler-checked queries
CloudKit sync with record sharing
Large datasets (50k+ records) with near-raw-SQLite performance
Value types (structs) instead of classes
Swift 6 strict concurrency support

Use SwiftData instead when:

Simple CRUD with native Apple integration
Prefer
```
@Model
```
classes over structs
Don't need CloudKit record sharing

Use raw GRDB when:

Complex SQL joins across 4+ tables
Custom migration logic beyond schema changes
Performance-critical operations needing manual SQL

当你需要以下功能时选择SQLiteData：

带编译器校验查询的类型安全SQLite
支持记录共享的CloudKit同步
大数据集（5万+条记录）且接近原生SQLite的性能
值类型（struct）而非类
Swift 6严格并发支持

当你需要以下情况时使用SwiftData：

简单CRUD操作且需要原生Apple集成
偏好
```
@Model
```
类而非struct
不需要CloudKit记录共享

当你需要以下情况时使用原生GRDB：

跨4张及以上表的复杂SQL连接
除Schema变更外的自定义迁移逻辑
需要手动SQL的性能关键型操作

Quick Reference

快速参考

swift

// MODEL
@Table nonisolated struct Item: Identifiable {
    let id: UUID                    // First let = auto primary key
    var title = ""                  // Default = non-nullable
    var notes: String?              // Optional = nullable
    @Column(as: Color.Hex.self)
    var color: Color = .blue        // Custom representation
    @Ephemeral var isSelected = false  // Not persisted
}

// SETUP
prepareDependencies { $0.defaultDatabase = try! appDatabase() }
@Dependency(\.defaultDatabase) var database

// FETCH
@FetchAll var items: [Item]
@FetchAll(Item.order(by: \.title).where(\.isInStock)) var items
@FetchOne(Item.count()) var count = 0

// FETCH (static helpers - v1.4.0+)
try Item.fetchAll(db)              // vs Item.all.fetchAll(db)
try Item.find(db, key: id)         // returns non-optional Item

// INSERT
try database.write { db in
    try Item.insert { Item.Draft(title: "New") }.execute(db)
}

// UPDATE (single)
try database.write { db in
    try Item.find(id).update { $0.title = "Updated" }.execute(db)
}

// UPDATE (bulk)
try database.write { db in
    try Item.where(\.isInStock).update { $0.notes = "" }.execute(db)
}

// DELETE
try database.write { db in
    try Item.find(id).delete().execute(db)
    try Item.where { $0.id.in(ids) }.delete().execute(db)  // bulk
}

// QUERY
Item.where(\.isActive)                     // Keypath (simple)
Item.where { $0.title.contains("phone") }  // Closure (complex)
Item.where { $0.status.eq(#bind(.done)) }  // Enum comparison
Item.order(by: \.title)                    // Sort
Item.order { $0.createdAt.desc() }         // Sort descending
Item.limit(10).offset(20)                  // Pagination

// RAW SQL (#sql macro)
#sql("SELECT * FROM items WHERE price > 100")  // Type-safe raw SQL
#sql("coalesce(date(\(dueDate)) = date(\(now)), 0)")  // Custom expressions

// CLOUDKIT (v1.2-1.4+)
prepareDependencies {
    $0.defaultSyncEngine = try SyncEngine(
        for: $0.defaultDatabase,
        tables: Item.self
    )
}
@Dependency(\.defaultSyncEngine) var syncEngine

// Manual sync control (v1.3.0+)
try await syncEngine.fetchChanges()  // Pull from CloudKit
try await syncEngine.sendChanges()   // Push to CloudKit
try await syncEngine.syncChanges()   // Bidirectional

// Sync state observation (v1.2.0+)
syncEngine.isSendingChanges    // true during upload
syncEngine.isFetchingChanges   // true during download
syncEngine.isSynchronizing     // either sending or fetching

swift

// 模型
@Table nonisolated struct Item: Identifiable {
    let id: UUID                    // 第一个let属性自动作为主键
    var title = ""                  // 默认值表示非空列
    var notes: String?              // 可选类型表示可空列
    @Column(as: Color.Hex.self)
    var color: Color = .blue        // 自定义类型映射
    @Ephemeral var isSelected = false  // 不持久化的属性
}

// 配置
prepareDependencies { $0.defaultDatabase = try! appDatabase() }
@Dependency(\.defaultDatabase) var database

// 数据获取
@FetchAll var items: [Item]
@FetchAll(Item.order(by: \.title).where(\.isInStock)) var items
@FetchOne(Item.count()) var count = 0

// 静态获取助手（v1.4.0+）
try Item.fetchAll(db)              // 替代Item.all.fetchAll(db)
try Item.find(db, key: id)         // 返回非可选的Item

// 插入数据
try database.write { db in
    try Item.insert { Item.Draft(title: "New") }.execute(db)
}

// 更新（单条）
try database.write { db in
    try Item.find(id).update { $0.title = "Updated" }.execute(db)
}

// 更新（批量）
try database.write { db in
    try Item.where(\.isInStock).update { $0.notes = "" }.execute(db)
}

// 删除数据
try database.write { db in
    try Item.find(id).delete().execute(db)
    try Item.where { $0.id.in(ids) }.delete().execute(db)  // 批量删除
}

// 查询
Item.where(\.isActive)                     // 键路径方式（简单查询）
Item.where { $0.title.contains("phone") }  // 闭包方式（复杂查询）
Item.where { $0.status.eq(#bind(.done)) }  // 枚举比较
Item.order(by: \.title)                    // 排序
Item.order { $0.createdAt.desc() }         // 降序排序
Item.limit(10).offset(20)                  // 分页

// 原生SQL（#sql宏）
#sql("SELECT * FROM items WHERE price > 100")  // 类型安全的原生SQL
#sql("coalesce(date(\(dueDate)) = date(\(now)), 0)")  // 自定义表达式

// CloudKit同步（v1.2-1.4+）
prepareDependencies {
    $0.defaultSyncEngine = try SyncEngine(
        for: $0.defaultDatabase,
        tables: Item.self
    )
}
@Dependency(\.defaultSyncEngine) var syncEngine

// 手动同步控制（v1.3.0+）
try await syncEngine.fetchChanges()  // 从CloudKit拉取变更
try await syncEngine.sendChanges()   // 向CloudKit推送变更
try await syncEngine.syncChanges()   // 双向同步

// 同步状态监听（v1.2.0+）
syncEngine.isSendingChanges    // 上传时为true
syncEngine.isFetchingChanges   // 下载时为true
syncEngine.isSynchronizing     // 上传或下载时为true

Anti-Patterns (Common Mistakes)

反模式（常见错误）

❌ Using

==

in predicates

❌ 在谓词中使用

==

swift

// WRONG — may not work in all contexts
.where { $0.status == .completed }

// CORRECT — use comparison methods
.where { $0.status.eq(#bind(.completed)) }

swift

// 错误——在某些上下文可能无法正常工作
.where { $0.status == .completed }

// 正确——使用比较方法
.where { $0.status.eq(#bind(.completed)) }

❌ Wrong update order

❌ 错误的更新顺序

swift

// WRONG — .update before .where
Item.update { $0.title = "X" }.where { $0.id == id }

// CORRECT — .find() for single, .where() before .update() for bulk
Item.find(id).update { $0.title = "X" }.execute(db)
Item.where(\.isOld).update { $0.archived = true }.execute(db)

swift

// 错误——.update在.where之前
Item.update { $0.title = "X" }.where { $0.id == id }

// 正确——单条数据用.find()，批量更新时.where()在.update()之前
Item.find(id).update { $0.title = "X" }.execute(db)
Item.where(\.isOld).update { $0.archived = true }.execute(db)

❌ Instance methods for insert

❌ 使用实例方法插入数据

swift

// WRONG — no instance insert method
let item = Item(id: UUID(), title: "Test")
try item.insert(db)

// CORRECT — static insert with .Draft
try Item.insert { Item.Draft(title: "Test") }.execute(db)

swift

// 错误——没有实例插入方法
let item = Item(id: UUID(), title: "Test")
try item.insert(db)

// 正确——使用静态insert方法配合.Draft
try Item.insert { Item.Draft(title: "Test") }.execute(db)

❌ Missing

nonisolated

❌ 缺少

nonisolated

swift

// WRONG — Swift 6 concurrency warning
@Table struct Item { ... }

// CORRECT
@Table nonisolated struct Item { ... }

swift

// 错误——Swift 6并发模式下会告警
@Table struct Item { ... }

// 正确
@Table nonisolated struct Item { ... }

❌ Awaiting inside write block

❌ 在write块内使用await

swift

// WRONG — write block is synchronous
try await database.write { db in ... }

// CORRECT — no await inside the block
try database.write { db in
    try Item.insert { ... }.execute(db)
}

swift

// 错误——write块是同步的
try await database.write { db in ... }

// 正确——块内不使用await
try database.write { db in
    try Item.insert { ... }.execute(db)
}

❌ Forgetting

.execute(db)

❌ 忘记

.execute(db)

swift

// WRONG — builds query but doesn't run it
try database.write { db in
    Item.insert { Item.Draft(title: "X") }  // Does nothing!
}

// CORRECT
try database.write { db in
    try Item.insert { Item.Draft(title: "X") }.execute(db)
}

swift

// 错误——仅构建查询但不执行
try database.write { db in
    Item.insert { Item.Draft(title: "X") }  // 无任何效果！
}

// 正确
try database.write { db in
    try Item.insert { Item.Draft(title: "X") }.execute(db)
}

@Table Model Definitions

@Table模型定义

Basic Table

基础表

swift

import SQLiteData

@Table
nonisolated struct Item: Identifiable {
    let id: UUID           // First `let` = auto primary key
    var title = ""
    var isInStock = true
    var notes = ""
}

Key patterns:

Use
```
struct
```
, not
```
class
```
(value types)
Add
```
nonisolated
```
for Swift 6 concurrency
First
```
let
```
property is automatically the primary key
Use defaults (
```
= ""
```
,
```
= true
```
) for non-nullable columns
Optional properties (
```
String?
```
) map to nullable SQL columns

swift

import SQLiteData

@Table
nonisolated struct Item: Identifiable {
    let id: UUID           // 第一个`let`属性自动作为主键
    var title = ""
    var isInStock = true
    var notes = ""
}

核心模式：

使用
```
struct
```
而非
```
class
```
（值类型）
为Swift 6并发添加
```
nonisolated
```
第一个
```
let
```
属性自动作为主键
使用默认值（
```
= ""
```
,
```
= true
```
）表示非空列
可选属性（
```
String?
```
）映射为可空SQL列

Custom Primary Key

自定义主键

swift

@Table
nonisolated struct Tag: Hashable, Identifiable {
    @Column(primaryKey: true)
    var title: String      // Custom primary key
    var id: String { title }
}

swift

@Table
nonisolated struct Tag: Hashable, Identifiable {
    @Column(primaryKey: true)
    var title: String      // 自定义主键
    var id: String { title }
}

Column Customization

列自定义

swift

@Table
nonisolated struct RemindersList: Hashable, Identifiable {
    let id: UUID

    @Column(as: Color.HexRepresentation.self)  // Custom type representation
    var color: Color = .blue

    var position = 0
    var title = ""
}

swift

@Table
nonisolated struct RemindersList: Hashable, Identifiable {
    let id: UUID

    @Column(as: Color.HexRepresentation.self)  // 自定义类型映射
    var color: Color = .blue

    var position = 0
    var title = ""
}

Foreign Keys

外键

swift

@Table
nonisolated struct Reminder: Hashable, Identifiable {
    let id: UUID
    var title = ""
    var remindersListID: RemindersList.ID  // Foreign key (explicit column)
}

@Table
nonisolated struct Attendee: Hashable, Identifiable {
    let id: UUID
    var name = ""
    var syncUpID: SyncUp.ID  // References parent
}

Note: SQLiteData uses explicit foreign key columns. Relationships are expressed through joins, not

@Relationship

macros.

swift

@Table
nonisolated struct Reminder: Hashable, Identifiable {
    let id: UUID
    var title = ""
    var remindersListID: RemindersList.ID  // 外键（显式列）
}

@Table
nonisolated struct Attendee: Hashable, Identifiable {
    let id: UUID
    var name = ""
    var syncUpID: SyncUp.ID  // 关联父表
}

注意： SQLiteData使用显式外键列。关系通过连接表达，而非

@Relationship

宏。

@Ephemeral — Non-Persisted Properties

@Ephemeral — 非持久化属性

Mark properties that exist in Swift but not in the database:

swift

@Table
nonisolated struct Item: Identifiable {
    let id: UUID
    var title = ""
    var price: Decimal = 0

    @Ephemeral
    var isSelected = false  // Not stored in database

    @Ephemeral
    var formattedPrice: String {  // Computed, not stored
        "$\(price)"
    }
}

Use cases:

UI state (selection, expansion, hover)
Computed properties derived from stored columns
Transient flags for business logic
Default values for properties not yet in schema

Important:

@Ephemeral

properties must have default values since they won't be populated from the database.

标记仅存在于Swift代码中但不存储到数据库的属性：

swift

@Table
nonisolated struct Item: Identifiable {
    let id: UUID
    var title = ""
    var price: Decimal = 0

    @Ephemeral
    var isSelected = false  // 不存储到数据库

    @Ephemeral
    var formattedPrice: String {  // 计算属性，不存储
        "$\(price)"
    }
}

适用场景：

UI状态（选中、展开、悬停）
从存储列派生的计算属性
业务逻辑的临时标记
Schema中尚未添加的属性默认值

重要提示：

@Ephemeral

属性必须有默认值，因为数据库不会填充这些属性。

Database Setup

数据库配置

Create Database

创建数据库

swift

import Dependencies
import SQLiteData
import GRDB

func appDatabase() throws -> any DatabaseWriter {
    var configuration = Configuration()
    configuration.prepareDatabase { db in
        // Configure database behavior
        db.trace { print("SQL: \($0)") }  // Optional SQL logging
    }

    let database = try DatabaseQueue(configuration: configuration)

    var migrator = DatabaseMigrator()

    // Register migrations
    migrator.registerMigration("v1") { db in
        try #sql(
            """
            CREATE TABLE "items" (
                "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
                "title" TEXT NOT NULL DEFAULT '',
                "isInStock" INTEGER NOT NULL DEFAULT 1,
                "notes" TEXT NOT NULL DEFAULT ''
            ) STRICT
            """
        )
        .execute(db)
    }

    try migrator.migrate(database)
    return database
}

swift

import Dependencies
import SQLiteData
import GRDB

func appDatabase() throws -> any DatabaseWriter {
    var configuration = Configuration()
    configuration.prepareDatabase { db in
        // 配置数据库行为
        db.trace { print("SQL: \($0)") }  // 可选SQL日志
    }

    let database = try DatabaseQueue(configuration: configuration)

    var migrator = DatabaseMigrator()

    // 注册迁移
    migrator.registerMigration("v1") { db in
        try #sql(
            """
            CREATE TABLE "items" (
                "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
                "title" TEXT NOT NULL DEFAULT '',
                "isInStock" INTEGER NOT NULL DEFAULT 1,
                "notes" TEXT NOT NULL DEFAULT ''
            ) STRICT
            """
        )
        .execute(db)
    }

    try migrator.migrate(database)
    return database
}

Register in Dependencies

在依赖中注册

swift

extension DependencyValues {
    var defaultDatabase: any DatabaseWriter {
        get { self[DefaultDatabaseKey.self] }
        set { self[DefaultDatabaseKey.self] = newValue }
    }
}

private enum DefaultDatabaseKey: DependencyKey {
    static let liveValue: any DatabaseWriter = {
        try! appDatabase()
    }()
}

// In app init or @main
prepareDependencies {
    $0.defaultDatabase = try! appDatabase()
}

swift

extension DependencyValues {
    var defaultDatabase: any DatabaseWriter {
        get { self[DefaultDatabaseKey.self] }
        set { self[DefaultDatabaseKey.self] = newValue }
    }
}

private enum DefaultDatabaseKey: DependencyKey {
    static let liveValue: any DatabaseWriter = {
        try! appDatabase()
    }()
}

// 在app初始化或@main中
prepareDependencies {
    $0.defaultDatabase = try! appDatabase()
}

Query Patterns

查询模式

Property Wrappers (@FetchAll, @FetchOne)

属性包装器（@FetchAll、@FetchOne）

The primary way to observe database changes in SwiftUI:

swift

struct ItemsList: View {
    @FetchAll(Item.order(by: \.title)) var items

    var body: some View {
        List(items) { item in
            Text(item.title)
        }
    }
}

Key behaviors:

Automatically subscribes to database changes
Updates when any
```
Item
```
changes
Runs on the main thread
Cancels observation when view disappears (iOS 17+)

在SwiftUI中监听数据库变更的主要方式：

swift

struct ItemsList: View {
    @FetchAll(Item.order(by: \.title)) var items

    var body: some View {
        List(items) { item in
            Text(item.title)
        }
    }
}

核心行为：

自动订阅数据库变更
当任何
```
Item
```
变更时更新视图
在主线程运行
视图消失时自动取消监听（iOS 17+）

@FetchOne for Aggregates

@FetchOne用于聚合查询

swift

struct StatsView: View {
    @FetchOne(Item.count()) var totalCount = 0
    @FetchOne(Item.where(\.isInStock).count()) var inStockCount = 0

    var body: some View {
        Text("Total: \(totalCount), In Stock: \(inStockCount)")
    }
}

swift

struct StatsView: View {
    @FetchOne(Item.count()) var totalCount = 0
    @FetchOne(Item.where(\.isInStock).count()) var inStockCount = 0

    var body: some View {
        Text("总计：\(totalCount)，库存中：\(inStockCount)")
    }
}

Lifecycle-Aware Fetching (v1.4.0+)

生命周期感知的获取（v1.4.0+）

Use

.task

to automatically cancel observation when view disappears:

swift

struct ItemsList: View {
    @Fetch(Item.all, animation: .default)
    private var items = [Item]()

    @State var searchQuery = ""

    var body: some View {
        List(items) { item in
            Text(item.title)
        }
        .searchable(text: $searchQuery)
        .task(id: searchQuery) {
            // Automatically cancels when view disappears or searchQuery changes
            try? await $items.load(
                Item.where { $0.title.contains(searchQuery) }
                    .order(by: \.title)
            ).task  // ← .task for auto-cancellation
        }
    }
}

Before v1.4.0 (manual cleanup):

swift

.task {
    try? await $items.load(query)
}
.onDisappear {
    Task { try await $items.load(Item.none) }
}

With v1.4.0 (automatic):

swift

.task {
    try? await $items.load(query).task  // Auto-cancels
}

使用

.task

在视图消失时自动取消监听：

swift

struct ItemsList: View {
    @Fetch(Item.all, animation: .default)
    private var items = [Item]()

    @State var searchQuery = ""

    var body: some View {
        List(items) { item in
            Text(item.title)
        }
        .searchable(text: $searchQuery)
        .task(id: searchQuery) {
            // 视图消失或searchQuery变更时自动取消
            try? await $items.load(
                Item.where { $0.title.contains(searchQuery) }
                    .order(by: \.title)
            ).task  // ← .task实现自动取消
        }
    }
}

v1.4.0之前（手动清理）：

swift

.task {
    try? await $items.load(query)
}
.onDisappear {
    Task { try await $items.load(Item.none) }
}

v1.4.0及之后（自动清理）：

swift

.task {
    try? await $items.load(query).task  // 自动取消
}

Filtering

过滤

swift

// Simple keypath filter
let active = Item.where(\.isActive)

// Complex closure filter
let recent = Item.where { $0.createdAt > lastWeek && !$0.isArchived }

// Contains/prefix/suffix
let matches = Item.where { $0.title.contains("phone") }
let starts = Item.where { $0.title.hasPrefix("iPhone") }

swift

// 简单键路径过滤
let active = Item.where(\.isActive)

// 复杂闭包过滤
let recent = Item.where { $0.createdAt > lastWeek && !$0.isArchived }

// 包含/前缀/后缀匹配
let matches = Item.where { $0.title.contains("phone") }
let starts = Item.where { $0.title.hasPrefix("iPhone") }

Sorting

排序

swift

// Single column
let sorted = Item.order(by: \.title)

// Descending
let descending = Item.order { $0.createdAt.desc() }

// Multiple columns
let multiSort = Item.order { ($0.priority, $0.createdAt.desc()) }

swift

// 单列排序
let sorted = Item.order(by: \.title)

// 降序排序
let descending = Item.order { $0.createdAt.desc() }

// 多列排序
let multiSort = Item.order { ($0.priority, $0.createdAt.desc()) }

Static Fetch Helpers (v1.4.0+)

静态获取助手（v1.4.0+）

Cleaner syntax for fetching:

swift

// OLD (verbose)
let items = try Item.all.fetchAll(db)
let item = try Item.find(id).fetchOne(db)  // returns Optional<Item>

// NEW (concise)
let items = try Item.fetchAll(db)
let item = try Item.find(db, key: id)      // returns Item (non-optional)

// Works with where clauses too
let active = try Item.where(\.isActive).find(db, key: id)

Key improvement:

.find(db, key:)

returns non-optional, throwing an error if not found.

更简洁的获取语法：

swift

// 旧版（冗长）
let items = try Item.all.fetchAll(db)
let item = try Item.find(id).fetchOne(db)  // 返回Optional<Item>

// 新版（简洁）
let items = try Item.fetchAll(db)
let item = try Item.find(db, key: id)      // 返回Item（非可选）

// 同样支持where子句
let active = try Item.where(\.isActive).find(db, key: id)

核心改进：

.find(db, key:)

返回非可选值，未找到时抛出错误。

Insert / Update / Delete

插入/更新/删除

Insert

插入

swift

try database.write { db in
    try Item.insert {
        Item.Draft(title: "New Item", isInStock: true)
    }
    .execute(db)
}

swift

try database.write { db in
    try Item.insert {
        Item.Draft(title: "新条目", isInStock: true)
    }
    .execute(db)
}

Insert with RETURNING (get generated ID)

插入并返回生成的ID

swift

let newId = try database.write { db in
    try Item.insert {
        Item.Draft(title: "New Item")
    }
    .returning(\.id)
    .fetchOne(db)
}

swift

let newId = try database.write { db in
    try Item.insert {
        Item.Draft(title: "新条目")
    }
    .returning(\.id)
    .fetchOne(db)
}

Update Single Record

更新单条记录

swift

try database.write { db in
    try Item.find(itemId)
        .update { $0.title = "Updated Title" }
        .execute(db)
}

swift

try database.write { db in
    try Item.find(itemId)
        .update { $0.title = "更新后的标题" }
        .execute(db)
}

Update Multiple Records

更新多条记录

swift

try database.write { db in
    try Item.where(\.isArchived)
        .update { $0.isDeleted = true }
        .execute(db)
}

swift

try database.write { db in
    try Item.where(\.isArchived)
        .update { $0.isDeleted = true }
        .execute(db)
}

Delete

删除

swift

// Delete single
try database.write { db in
    try Item.find(id).delete().execute(db)
}

// Delete multiple
try database.write { db in
    try Item.where { $0.createdAt < cutoffDate }
        .delete()
        .execute(db)
}

swift

// 删除单条
try database.write { db in
    try Item.find(id).delete().execute(db)
}

// 删除多条
try database.write { db in
    try Item.where { $0.createdAt < cutoffDate }
        .delete()
        .execute(db)
}

Upsert (Insert or Update)

Upsert（插入或更新）

SQLite's UPSERT (

INSERT ... ON CONFLICT ... DO UPDATE

) expresses "insert if missing, otherwise update" in one statement.

swift

try database.write { db in
    try Item.insert {
        item
    } onConflict: { cols in
        (cols.libraryID, cols.remoteID)   // Conflict target columns
    } doUpdate: { row, excluded in
        row.name = excluded.name           // Merge semantics
        row.notes = excluded.notes
    }
    .execute(db)
}

Parameters:

```
onConflict:
```
— Columns defining "same row" (must match UNIQUE constraint/index)
```
doUpdate:
```
— What to update on conflict
- ```
row
```
  = existing database row
- ```
excluded
```
  = proposed insert values (SQLite's
```
excluded
```
  table)

SQLite的UPSERT（

INSERT ... ON CONFLICT ... DO UPDATE

）实现"不存在则插入，存在则更新"的逻辑。

swift

try database.write { db in
    try Item.insert {
        item
    } onConflict: { cols in
        (cols.libraryID, cols.remoteID)   // 冲突目标列
    } doUpdate: { row, excluded in
        row.name = excluded.name           // 合并语义
        row.notes = excluded.notes
    }
    .execute(db)
}

参数说明：

```
onConflict:
```
— 定义"相同行"的列（必须匹配UNIQUE约束/索引）
```
doUpdate:
```
— 冲突时的更新逻辑
- ```
row
```
  = 数据库中已存在的行
- ```
excluded
```
  = 拟插入的值（SQLite的
```
excluded
```
  虚拟表）

With Partial Unique Index

配合部分唯一索引

When your UNIQUE index has a

WHERE

clause, add a conflict filter:

swift

try Item.insert {
    item
} onConflict: { cols in
    (cols.libraryID, cols.remoteID)
} where: { cols in
    cols.remoteID.isNot(nil)          // Match partial index condition
} doUpdate: { row, excluded in
    row.name = excluded.name
}
.execute(db)

Schema requirement:

sql

CREATE UNIQUE INDEX idx_items_sync_identity
ON items (libraryID, remoteID)
WHERE remoteID IS NOT NULL

当你的UNIQUE索引包含

WHERE

子句时，添加冲突过滤条件：

swift

try Item.insert {
    item
} onConflict: { cols in
    (cols.libraryID, cols.remoteID)
} where: { cols in
    cols.remoteID.isNot(nil)          // 匹配部分索引条件
} doUpdate: { row, excluded in
    row.name = excluded.name
}
.execute(db)

Schema要求：

sql

CREATE UNIQUE INDEX idx_items_sync_identity
ON items (libraryID, remoteID)
WHERE remoteID IS NOT NULL

Merge Strategies

合并策略

Replace all mutable fields (sync mirror):

swift

doUpdate: { row, excluded in
    row.name = excluded.name
    row.notes = excluded.notes
    row.updatedAt = excluded.updatedAt
}

Merge without clobbering (keep existing if new is NULL):

swift

doUpdate: { row, excluded in
    row.name = excluded.name.ifnull(row.name)
    row.notes = excluded.notes.ifnull(row.notes)
}

Last-write-wins (only update if newer) — use raw SQL:

swift

try db.execute(sql: """
    INSERT INTO items (id, name, updatedAt) VALUES (?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
        name = excluded.name,
        updatedAt = excluded.updatedAt
    WHERE excluded.updatedAt >= items.updatedAt
    """, arguments: [item.id, item.name, item.updatedAt])
// Use >= to handle timestamp ties (last arrival wins)

替换所有可变字段（同步镜像）：

swift

doUpdate: { row, excluded in
    row.name = excluded.name
    row.notes = excluded.notes
    row.updatedAt = excluded.updatedAt
}

合并且不覆盖（新值为NULL时保留原有值）：

swift

doUpdate: { row, excluded in
    row.name = excluded.name.ifnull(row.name)
    row.notes = excluded.notes.ifnull(row.notes)
}

最后写入获胜（仅当新值更新时才更新）——使用原生SQL：

swift

try db.execute(sql: """
    INSERT INTO items (id, name, updatedAt) VALUES (?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
        name = excluded.name,
        updatedAt = excluded.updatedAt
    WHERE excluded.updatedAt >= items.updatedAt
    """, arguments: [item.id, item.name, item.updatedAt])
// 使用>=处理时间戳相同的情况（最后到达的获胜）

❌ Common Upsert Mistakes

❌ 常见Upsert错误

Missing UNIQUE constraint:

swift

// WRONG — no index to conflict against
onConflict: { ($0.libraryID, $0.remoteID) }
// but table has no UNIQUE(libraryID, remoteID)

Using INSERT OR REPLACE:

swift

// WRONG — REPLACE deletes then inserts, breaking FK relationships
try db.execute(sql: "INSERT OR REPLACE INTO items ...")

// CORRECT — use ON CONFLICT for true upsert
try Item.insert { ... } onConflict: { ... } doUpdate: { ... }

缺少UNIQUE约束：

swift

// 错误——没有对应的冲突索引
onConflict: { ($0.libraryID, $0.remoteID) }
// 但表中没有UNIQUE(libraryID, remoteID)约束

使用INSERT OR REPLACE：

swift

// 错误——REPLACE会先删除再插入，破坏外键关系
try db.execute(sql: "INSERT OR REPLACE INTO items ...")

// 正确——使用ON CONFLICT实现真正的Upsert
try Item.insert { ... } onConflict: { ... } doUpdate: { ... }

Batch Operations

批量操作

Batch Insert

批量插入

swift

try database.write { db in
    try Item.insert {
        ($0.title, $0.isInStock)
    } values: {
        items.map { ($0.title, $0.isInStock) }
    }
    .execute(db)
}

swift

try database.write { db in
    try Item.insert {
        ($0.title, $0.isInStock)
    } values: {
        items.map { ($0.title, $0.isInStock) }
    }
    .execute(db)
}

Transaction Safety

事务安全性

All mutations inside

database.write { }

are wrapped in a transaction:

swift

try database.write { db in
    // These all succeed or all fail together
    try Item.insert { ... }.execute(db)
    try Item.find(id).update { ... }.execute(db)
    try OtherTable.find(otherId).delete().execute(db)
}

If any operation throws, the entire transaction rolls back.

database.write { }

内的所有变更都被包裹在事务中：

swift

try database.write { db in
    // 这些操作要么全部成功，要么全部失败
    try Item.insert { ... }.execute(db)
    try Item.find(id).update { ... }.execute(db)
    try OtherTable.find(otherId).delete().execute(db)
}

如果任何操作抛出错误，整个事务会回滚。

Raw SQL with #sql Macro

使用#sql宏的原生SQL

When you need custom SQL expressions beyond the type-safe query builder, use the

#sql

macro from StructuredQueries:

当你需要类型安全查询构建器之外的自定义SQL表达式时，使用StructuredQueries提供的

#sql

宏：

Custom Query Expressions

自定义查询表达式

swift

nonisolated extension Item.TableColumns {
    var isPastDue: some QueryExpression<Bool> {
        @Dependency(\.date.now) var now
        return !isCompleted && #sql("coalesce(date(\(dueDate)) < date(\(now)), 0)")
    }
}

// Use in queries
let overdue = try Item.where { $0.isPastDue }.fetchAll(db)

swift

nonisolated extension Item.TableColumns {
    var isPastDue: some QueryExpression<Bool> {
        @Dependency(\.date.now) var now
        return !isCompleted && #sql("coalesce(date(\(dueDate)) < date(\(now)), 0)")
    }
}

// 在查询中使用
let overdue = try Item.where { $0.isPastDue }.fetchAll(db)

Raw SQL Queries

原生SQL查询

swift

// Direct SQL with parameter interpolation
try #sql("SELECT * FROM items WHERE price > \(minPrice)").execute(db)

// Using \(raw:) for literal values
let tableName = "items"
try #sql("SELECT * FROM \(raw: tableName)").execute(db)

Why
#sql
?

Type-safe parameter binding (prevents SQL injection)
Compile-time syntax checking
Seamless integration with query builder
Parameter interpolation automatically escapes values

For schema creation (CREATE TABLE, migrations), see the

axiom-sqlitedata-ref

reference skill for complete examples.

swift

// 带参数插值的直接SQL
try #sql("SELECT * FROM items WHERE price > \(minPrice)").execute(db)

// 使用\(raw:)插入字面量值
let tableName = "items"
try #sql("SELECT * FROM \(raw: tableName)").execute(db)

为什么使用
#sql
？

类型安全的参数绑定（防止SQL注入）
编译时语法检查
与查询构建器无缝集成
参数插值自动转义值

Schema创建（CREATE TABLE、迁移）的完整示例请参考

axiom-sqlitedata-ref

参考技能。

CloudKit Sync

CloudKit同步

Basic Setup

基础配置

swift

import CloudKit

extension DependencyValues {
    var defaultSyncEngine: SyncEngine {
        get { self[DefaultSyncEngineKey.self] }
        set { self[DefaultSyncEngineKey.self] = newValue }
    }
}

private enum DefaultSyncEngineKey: DependencyKey {
    static let liveValue = {
        @Dependency(\.defaultDatabase) var database
        return try! SyncEngine(
            for: database,
            tables: Item.self,
            privateTables: SensitiveItem.self,  // Private database
            startImmediately: true
        )
    }()
}

// In app init
prepareDependencies {
    $0.defaultDatabase = try! appDatabase()
    $0.defaultSyncEngine = try! SyncEngine(
        for: $0.defaultDatabase,
        tables: Item.self
    )
}

swift

import CloudKit

extension DependencyValues {
    var defaultSyncEngine: SyncEngine {
        get { self[DefaultSyncEngineKey.self] }
        set { self[DefaultSyncEngineKey.self] = newValue }
    }
}

private enum DefaultSyncEngineKey: DependencyKey {
    static let liveValue = {
        @Dependency(\.defaultDatabase) var database
        return try! SyncEngine(
            for: database,
            tables: Item.self,
            privateTables: SensitiveItem.self,  // 私有数据库
            startImmediately: true
        )
    }()
}

// 在app初始化中
prepareDependencies {
    $0.defaultDatabase = try! appDatabase()
    $0.defaultSyncEngine = try! SyncEngine(
        for: $0.defaultDatabase,
        tables: Item.self
    )
}

Manual Sync Control (v1.3.0+)

手动同步控制（v1.3.0+）

Control when sync happens instead of automatic background sync:

swift

@Dependency(\.defaultSyncEngine) var syncEngine

// Pull changes from CloudKit
try await syncEngine.fetchChanges()

// Push local changes to CloudKit
try await syncEngine.sendChanges()

// Bidirectional sync
try await syncEngine.syncChanges()

Use cases:

User-triggered "Refresh" button
Sync after critical operations
Custom sync scheduling
Testing sync behavior

替代自动后台同步，手动控制同步时机：

swift

@Dependency(\.defaultSyncEngine) var syncEngine

// 从CloudKit拉取变更
try await syncEngine.fetchChanges()

// 向CloudKit推送本地变更
try await syncEngine.sendChanges()

// 双向同步
try await syncEngine.syncChanges()

适用场景：

用户触发的"刷新"按钮
关键操作后同步
自定义同步调度
同步行为测试

Sync State Observation (v1.2.0+)

同步状态监听（v1.2.0+）

Show UI feedback during sync:

swift

struct SyncStatusView: View {
    @Dependency(\.defaultSyncEngine) var syncEngine

    var body: some View {
        HStack {
            if syncEngine.isSynchronizing {
                ProgressView()
                if syncEngine.isSendingChanges {
                    Text("Uploading...")
                } else if syncEngine.isFetchingChanges {
                    Text("Downloading...")
                }
            } else {
                Image(systemName: "checkmark.circle")
                Text("Synced")
            }
        }
    }
}

Observable properties:

```
isSendingChanges: Bool
```
— True during CloudKit upload
```
isFetchingChanges: Bool
```
— True during CloudKit download
```
isSynchronizing: Bool
```
— True if either sending or fetching
```
isRunning: Bool
```
— True if sync engine is active

同步过程中显示UI反馈：

swift

struct SyncStatusView: View {
    @Dependency(\.defaultSyncEngine) var syncEngine

    var body: some View {
        HStack {
            if syncEngine.isSynchronizing {
                ProgressView()
                if syncEngine.isSendingChanges {
                    Text("上传中...")
                } else if syncEngine.isFetchingChanges {
                    Text("下载中...")
                }
            } else {
                Image(systemName: "checkmark.circle")
                Text("已同步")
            }
        }
    }
}

可观察属性：

```
isSendingChanges: Bool
```
— 向CloudKit上传时为true
```
isFetchingChanges: Bool
```
— 从CloudKit下载时为true
```
isSynchronizing: Bool
```
— 上传或下载时为true
```
isRunning: Bool
```
— 同步引擎处于活动状态时为true

Query Sync Metadata (v1.3.0+)

查询同步元数据（v1.3.0+）

Access CloudKit sync information for records:

swift

import CloudKit

// Get sync metadata for a record
let metadata = try SyncMetadata.find(item.syncMetadataID).fetchOne(db)

// Join items with their sync metadata
let itemsWithSync = try Item.all
    .leftJoin(SyncMetadata.all) { $0.syncMetadataID.eq($1.id) }
    .select { (item: $0, metadata: $1) }
    .fetchAll(db)

// Check if record is shared
let sharedItems = try Item.all
    .join(SyncMetadata.all) { $0.syncMetadataID.eq($1.id) }
    .where { $1.isShared }
    .fetchAll(db)

访问记录的CloudKit同步信息：

swift

import CloudKit

// 获取某条记录的同步元数据
let metadata = try SyncMetadata.find(item.syncMetadataID).fetchOne(db)

// 关联查询条目及其同步元数据
let itemsWithSync = try Item.all
    .leftJoin(SyncMetadata.all) { $0.syncMetadataID.eq($1.id) }
    .select { (item: $0, metadata: $1) }
    .fetchAll(db)

// 查询已共享的记录
let sharedItems = try Item.all
    .join(SyncMetadata.all) { $0.syncMetadataID.eq($1.id) }
    .where { $1.isShared }
    .fetchAll(db)

Migration Helpers

迁移助手

Migrate primary keys when switching sync strategies:

swift

try await syncEngine.migratePrimaryKeys(
    from: OldItem.self,
    to: NewItem.self
)

切换同步策略时迁移主键：

swift

try await syncEngine.migratePrimaryKeys(
    from: OldItem.self,
    to: NewItem.self
)

When to Drop to GRDB

何时使用原生GRDB

SQLiteData is built on GRDB. Use raw GRDB when you need:

Complex joins:

swift

let sql = try database.read { db in
    try Row.fetchAll(db, sql:
        """
        SELECT items.*, categories.name as categoryName
        FROM items
        JOIN categories ON items.categoryID = categories.id
        JOIN tags ON items.id = tags.itemID
        WHERE tags.name IN (?, ?)
        """,
        arguments: ["electronics", "sale"]
    )
}

Window functions:

swift

let ranked = try database.read { db in
    try Row.fetchAll(db, sql:
        """
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank
        FROM items
        """
    )
}

Performance-critical paths: When you've profiled and confirmed SQLiteData's query builder is the bottleneck, drop to raw SQL.

SQLiteData基于GRDB构建。当你需要以下功能时使用原生GRDB：

复杂连接：

swift

let sql = try database.read { db in
    try Row.fetchAll(db, sql:
        """
        SELECT items.*, categories.name as categoryName
        FROM items
        JOIN categories ON items.categoryID = categories.id
        JOIN tags ON items.id = tags.itemID
        WHERE tags.name IN (?, ?)
        """,
        arguments: ["electronics", "sale"]
    )
}

窗口函数：

swift

let ranked = try database.read { db in
    try Row.fetchAll(db, sql:
        """
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank
        FROM items
        """
    )
}

性能关键路径： 当你通过性能分析确认SQLiteData的查询构建器是性能瓶颈时，切换到原生SQL。

Resources

资源

GitHub: pointfreeco/sqlite-data, pointfreeco/swift-structured-queries, groue/GRDB.swift

Skills: axiom-sqlitedata-ref, axiom-sqlitedata-migration, axiom-database-migration, axiom-grdb

Targets: iOS 17+, Swift 6 Framework: SQLiteData 1.4+ History: See git log for changes

GitHub：pointfreeco/sqlite-data, pointfreeco/swift-structured-queries, groue/GRDB.swift

技能：axiom-sqlitedata-ref, axiom-sqlitedata-migration, axiom-database-migration, axiom-grdb

目标平台： iOS 17+、Swift 6 框架版本： SQLiteData 1.4+ 历史变更： 查看git日志了解详情

axiom-sqlitedata

Original

Translation

SQLiteData

SQLiteData

Overview

概述

When to Use SQLiteData

何时使用SQLiteData

Quick Reference

快速参考

Anti-Patterns (Common Mistakes)

反模式（常见错误）

❌ Using == in predicates

❌ 在谓词中使用==

❌ Wrong update order

❌ 错误的更新顺序

❌ Instance methods for insert

❌ 使用实例方法插入数据

❌ Missing nonisolated

❌ 缺少nonisolated

❌ Awaiting inside write block

❌ 在write块内使用await

❌ Forgetting .execute(db)

❌ 忘记.execute(db)

@Table Model Definitions

@Table模型定义

Basic Table

基础表

Custom Primary Key

自定义主键

Column Customization

列自定义

Foreign Keys

外键

@Ephemeral — Non-Persisted Properties

@Ephemeral — 非持久化属性

Database Setup

数据库配置

Create Database

创建数据库

Register in Dependencies

在依赖中注册

Query Patterns

查询模式

Property Wrappers (@FetchAll, @FetchOne)

属性包装器（@FetchAll、@FetchOne）

@FetchOne for Aggregates

@FetchOne用于聚合查询

Lifecycle-Aware Fetching (v1.4.0+)

生命周期感知的获取（v1.4.0+）

Filtering

过滤

Sorting

排序

Static Fetch Helpers (v1.4.0+)

静态获取助手（v1.4.0+）

Insert / Update / Delete

插入/更新/删除

Insert

插入

Insert with RETURNING (get generated ID)

插入并返回生成的ID

Update Single Record

更新单条记录

Update Multiple Records

更新多条记录

Delete

删除

Upsert (Insert or Update)

Upsert（插入或更新）

With Partial Unique Index

配合部分唯一索引

Merge Strategies

合并策略

❌ Common Upsert Mistakes

❌ 常见Upsert错误

Batch Operations

批量操作

Batch Insert

❌ Using
`==`
in predicates

❌ 在谓词中使用
`==`

❌ Missing
`nonisolated`

❌ 缺少
`nonisolated`

❌ Forgetting
`.execute(db)`

❌ 忘记
`.execute(db)`