axiom-sqlitedata-ref by charleswiltgen/axiom
npx skills add https://github.com/charleswiltgen/axiom --skill axiom-sqlitedata-refSQLiteData 的高级查询模式和模式组合技术,由 Point-Free 开发。基于 GRDB 和 StructuredQueries。
关于核心模式(CRUD、CloudKit 设置、@Table 基础),请参阅 axiom-sqlitedata 技能。
本文档涵盖高级查询、模式组合、视图和自定义聚合。
要求 iOS 17+、Swift 6 严格并发 框架 SQLiteData 1.4+
SQLiteData 提供了强大的工具来组合模式类型,支持代码复用、更好的组织以及单表继承模式。
使用 @Selection 将相关列分组为可复用的类型:
// 定义可复用的列组
@Selection
struct Timestamps {
let createdAt: Date
let updatedAt: Date?
}
// 在多个表中使用
@Table
nonisolated struct RemindersList: Identifiable {
let id: UUID
var title = ""
let timestamps: Timestamps // 嵌入的列组
}
@Table
nonisolated struct Reminder: Identifiable {
let id: UUID
var title = ""
var isCompleted = false
let timestamps: Timestamps // 相同的组,复用
}
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
重要提示: SQLite 没有列组的概念。在 CREATE TABLE 语句中需要将所有分组扁平化:
CREATE TABLE "remindersLists" (
"id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
"title" TEXT NOT NULL DEFAULT '',
"createdAt" TEXT NOT NULL,
"updatedAt" TEXT
) STRICT
使用点语法访问组内的字段:
// 查询组内的字段
RemindersList
.where { $0.timestamps.createdAt <= cutoffDate }
.fetchAll(db)
// 比较整个组(在 SQL 中会扁平化为元组)
RemindersList
.where {
$0.timestamps <= Timestamps(createdAt: date1, updatedAt: date2)
}
在自定义查询结果中使用列组:
@Selection
struct Row {
let reminderTitle: String
let listTitle: String
let timestamps: Timestamps // 嵌套组
}
let results = try Reminder
.join(RemindersList.all) { $0.remindersListID.eq($1.id) }
.select {
Row.Columns(
reminderTitle: $0.title,
listTitle: $1.title,
timestamps: $0.timestamps // 传递整个组
)
}
.fetchAll(db)
使用 @CasePathable @Selection 枚举来建模多态数据——这是类继承的一种值类型替代方案:
import CasePaths
@Table
nonisolated struct Attachment: Identifiable {
let id: UUID
let kind: Kind
@CasePathable @Selection
enum Kind {
case link(URL)
case note(String)
case image(URL)
}
}
注意: @CasePathable 是必需的,它来自 Point-Free 的 CasePaths 库。
将所有 case 扁平化为可空列:
CREATE TABLE "attachments" (
"id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
"link" TEXT,
"note" TEXT,
"image" TEXT
) STRICT
// 获取所有记录——解码时确定是哪个 case
let attachments = try Attachment.all.fetchAll(db)
// 按 case 过滤
let images = try Attachment
.where { $0.kind.image.isNot(nil) }
.fetchAll(db)
try Attachment.insert {
Attachment.Draft(kind: .note("Hello world!"))
}
.execute(db)
// 插入: (id, NULL, 'Hello world!', NULL)
try Attachment.find(id).update {
$0.kind = #bind(.link(URL(string: "https://example.com")!))
}
.execute(db)
// 设置 link 列,将 note 和 image 列设为 NULL
枚举 case 可以使用嵌套的 @Selection 类型来保存结构化数据:
@Table
nonisolated struct Attachment: Identifiable {
let id: UUID
let kind: Kind
@CasePathable @Selection
enum Kind {
case link(URL)
case note(String)
case image(Attachment.Image) // 完全限定嵌套类型
}
@Selection
struct Image {
var caption = ""
var url: URL
}
}
SQL 模式会扁平化所有嵌套字段:
CREATE TABLE "attachments" (
"id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
"link" TEXT,
"note" TEXT,
"caption" TEXT,
"url" TEXT
) STRICT
使用列组,@DatabaseFunction 可以接受整个表行:
@DatabaseFunction
func isPastDue(reminder: Reminder) -> Bool {
!reminder.isCompleted && reminder.dueDate < Date()
}
// 在查询中使用——列会自动扁平化/重组
let pastDue = try Reminder
.where { $isPastDue(reminder: $0) }
.fetchAll(db)
| 方法 | SQLiteData | SwiftData |
|---|---|---|
| 类型 | 值类型(枚举/结构体) | 引用类型(类) |
| 穷尽性 | 编译器强制检查的 switch | 运行时类型检查 |
| 冗长度 | 简洁的枚举 case | 冗长的类层次结构 |
| 继承 | 通过枚举实现单表继承 | @Model 类继承 |
| 可复用列 | @Selection 组 | 手动重复 |
SwiftData 等价实现(更冗长):
@Model class Attachment { var isActive: Bool }
@Model class Link: Attachment { var url: URL }
@Model class Note: Attachment { var note: String }
@Model class Image: Attachment { var url: URL }
// 每个都需要显式调用 super.init 的初始化器
将可复用的作用域构建为静态属性/方法:
extension Item {
static let active = Item.where { !$0.isArchived && !$0.isDeleted }
static let inStock = Item.where(\.isInStock)
static func createdAfter(_ date: Date) -> Where<Item> {
Item.where { $0.createdAt > date }
}
}
// 链式调用作用域
let results = try Item.active.inStock.order(by: \.title).fetchAll(db)
// 作为 @FetchAll 的基础
@FetchAll(Item.active) var items
扩展 Where<Item> 以添加可组合的过滤器:
extension Where<Item> {
func matching(_ search: String) -> Where<Item> {
self.where { $0.title.contains(search) || $0.notes.contains(search) }
}
}
let results = try Item.inStock.matching(searchText).fetchAll(db)
当您需要在单个读取事务中获取多个数据片段时使用 @Fetch(对于单表查询,请使用 @FetchAll/@FetchOne):
struct DashboardRequest: FetchKeyRequest {
struct Value: Sendable {
let totalItems: Int
let activeItems: [Item]
let categories: [Category]
}
func fetch(_ db: Database) throws -> Value {
try Value(
totalItems: Item.count().fetchOne(db) ?? 0,
activeItems: Item.where { !$0.isArchived }.order(by: \.updatedAt.desc()).limit(10).fetchAll(db),
categories: Category.order(by: \.name).fetchAll(db)
)
}
}
@Fetch(DashboardRequest()) var dashboard
使用 .load() 进行动态加载:
@Fetch var results = SearchRequest.Value()
.task(id: query) {
try? await $results.load(SearchRequest(query: query), animation: .default)
}
主要优势:原子读取、自动观察、类型安全的结果。
| 函数 | 用法 | SQL |
|---|---|---|
upper() / lower() | $0.title.upper() | UPPER/LOWER |
trim() / ltrim() / rtrim() | $0.title.trim() | TRIM |
substr(start, len) | $0.title.substr(0, 3) | SUBSTR |
replace(old, new) | $0.title.replace("old", "new") | REPLACE |
length() | $0.title.length() | LENGTH |
instr(search) | $0.title.instr("search") > 0 | INSTR |
like(pattern) | $0.title.like("%phone%") | LIKE |
hasPrefix / hasSuffix / contains | $0.title.contains("Max") | Swift 风格 |
collate(.nocase) | $0.title.collate(.nocase).eq(#bind("X")) | COLLATE |
// Coalesce —— 第一个非空值
let name = try User.select { $0.nickname ?? $0.firstName ?? "Anonymous" }.fetchAll(db)
// 空值检查
let withDue = try Reminder.where { $0.dueDate.isNot(nil) }.fetchAll(db)
let noDue = try Reminder.where { $0.dueDate.is(nil) }.fetchAll(db)
// 空值安全排序
let sorted = try Item.order { $0.priority.desc(nulls: .last) }.fetchAll(db)
// IN(集合或子查询)
let selected = try Item.where { $0.id.in(selectedIds) }.fetchAll(db)
let inActive = try Item.where { $0.categoryID.in(
Category.where(\.isActive).select(\.id)
)}.fetchAll(db)
// NOT IN
let excluded = try Item.where { !$0.id.in(excludedIds) }.fetchAll(db)
// BETWEEN(或 Swift 范围语法)
let midRange = try Item.where { $0.price.between(10, and: 100) }.fetchAll(db)
// 基于偏移量
let items = try Item.order(by: \.createdAt).limit(20, offset: page * 20).fetchAll(db)
// 基于游标(对于深度分页更高效)
let items = try Item.where { $0.id > lastSeenId }.order(by: \.id).limit(20).fetchAll(db)
let categories = try Item.select(\.category).distinct().fetchAll(db)
从 INSERT、UPDATE 或 DELETE 操作中获取生成的值:
// 插入并获取自动生成的 ID
let newId = try Item.insert { Item.Draft(title: "New Item") }
.returning(\.id).fetchOne(db)
// 更新并返回新值
let updates = try Item.find(id).update { $0.count += 1 }
.returning { ($0.id, $0.count) }.fetchOne(db)
// 在删除前捕获已删除的记录
let deleted = try Item.where { $0.isArchived }.delete()
.returning(Item.self).fetchAll(db)
使用 RETURNING 可以避免为自动生成的 ID 进行第二次查询、审计删除或验证更新。
// INNER JOIN —— 仅匹配的行
let items = try Item.join(Category.all) { $0.categoryID.eq($1.id) }.fetchAll(db)
// LEFT JOIN —— 左表全部,右表匹配(可为空)
let items = try Item.leftJoin(Category.all) { $0.categoryID.eq($1.id) }
.select { ($0, $1) } // (Item, Category?)
.fetchAll(db)
同样可用:.rightJoin()(右表全部)和 .fullJoin()(两表全部)。
多表连接自然链式:
extension Reminder {
static let withTags = group(by: \.id)
.leftJoin(ReminderTag.all) { $0.id.eq($1.reminderID) }
.leftJoin(Tag.all) { $1.tagID.eq($2.primaryKey) }
}
struct ManagerAlias: TableAlias { typealias Table = Employee }
let employeesWithManagers = try Employee
.leftJoin(Employee.all.as(ManagerAlias.self)) { $0.managerID.eq($1.id) }
.select { (employeeName: $0.name, managerName: $1.name) }
.fetchAll(db)
// 简单 case —— 映射值
let labels = try Item.select {
Case($0.priority).when(1, then: "Low").when(2, then: "Medium")
.when(3, then: "High").else("Unknown")
}.fetchAll(db)
// 搜索 case —— 布尔条件
let status = try Order.select {
Case().when($0.shippedAt.isNot(nil), then: "Shipped")
.when($0.paidAt.isNot(nil), then: "Paid").else("Unknown")
}.fetchAll(db)
// 更新中的 case(切换模式)
try Reminder.find(id).update {
$0.status = Case($0.status)
.when(#bind(.incomplete), then: #bind(.completing))
.when(#bind(.completing), then: #bind(.completed))
.else(#bind(.incomplete))
}.execute(db)
// 单个 CTE
let expensiveItems = try With {
Item.where { $0.price > 1000 }
} query: { expensive in
expensive.order(by: \.price).limit(10)
}.fetchAll(db)
// 多个 CTE
let report = try With {
Customer.where { $0.totalSpent > 10000 }
} with: {
Order.where { $0.createdAt > lastMonth }
} query: { highValue, recentOrders in
highValue.join(recentOrders) { $0.id.eq($1.customerID) }
.select { ($0.name, $1.total) }
}.fetchAll(db)
使用 CTE 将复杂查询分解为可读的部分,复用子查询,或改进查询计划。
查询分层数据(树、组织结构图、线程化评论):
@Table
nonisolated struct Category: Identifiable {
let id: UUID
var name = ""
var parentID: UUID? // 自引用
}
// 获取根类别的所有后代
let allDescendants = try With {
Category.where { $0.id.eq(#bind(rootCategoryId)) } // 基础情况
} recursiveUnion: { cte in
Category.all.join(cte) { $0.parentID.eq($1.id) }.select { $0 } // 递归情况
} query: { cte in
cte.order(by: \.name)
}.fetchAll(db)
反转连接条件($0.id.eq($1.parentID))可以向上遍历树而不是向下。
@Table
struct ReminderText: FTS5 {
let rowid: Int
let title: String
let notes: String
let tags: String
}
// 在迁移中创建 FTS 表
try #sql(
"""
CREATE VIRTUAL TABLE "reminderTexts" USING fts5(
"title", "notes", "tags",
tokenize = 'trigram'
)
"""
)
.execute(db)
// 高亮搜索词
let results = try ItemText.where { $0.match(query) }
.select { ($0.rowid, $0.title.highlight("<b>", "</b>")) }.fetchAll(db)
// 带上下文的片段
let snippets = try ItemText.where { $0.match(query) }
.select { $0.description.snippet("<b>", "</b>", "...", 64) }.fetchAll(db)
// BM25 相关性排序
let ranked = try ItemText.where { $0.match(query) }
.order { $0.bm25().desc() }.fetchAll(db)
// groupConcat —— 每个项目的逗号分隔标签
let itemsWithTags = try Item.group(by: \.id)
.leftJoin(ItemTag.all) { $0.id.eq($1.itemID) }
.leftJoin(Tag.all) { $1.tagID.eq($2.id) }
.select { ($0.title, $2.name.groupConcat(separator: ", ")) }
.fetchAll(db)
// ("iPhone", "electronics, mobile, apple")
// jsonGroupArray —— 聚合为 JSON 数组
let itemsJson = try Store.group(by: \.id)
.leftJoin(Item.all) { $0.id.eq($1.storeID) }
.select { ($0.name, $1.title.jsonGroupArray()) }
.fetchAll(db)
选项:.groupConcat(distinct: true)、.groupConcat(order: { $0.asc() })、.jsonGroupArray(filter: $1.isActive)、jsonObject("key", $0.value)。
所有聚合函数都接受 filter: 参数:
let stats = try Item.select {
Stats.Columns(
total: $0.count(),
activeCount: $0.count(filter: $0.isActive),
avgActivePrice: $0.price.avg(filter: $0.isActive),
totalRevenue: $0.revenue.sum(filter: $0.status.eq(#bind(.completed)))
)
}.fetchOne(db)
.where() 在分组前过滤行;.having() 在聚合后过滤组:
let frequentCustomers = try Customer.group(by: \.id)
.leftJoin(Order.all) { $0.id.eq($1.customerID) }
.having { $1.count() > 5 }
.select { ($0.name, $1.count()) }
.fetchAll(db)
#sql 宏支持类型安全的原始 SQL,用于模式创建和迁移。
migrator.registerMigration("Create initial tables") { 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,
"price" REAL NOT NULL DEFAULT 0.0,
"createdAt" TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT
""").execute(db)
}
\(value) → 自动转义(对用户输入安全)\(raw: value) → 字面插入(仅用于您控制的标识符)\(raw: userInput) —— SQL 注入漏洞// CREATE INDEX(可选的 WHERE 用于部分索引)
try #sql("""CREATE INDEX "idx_items_search" ON "items" ("title") WHERE "isArchived" = 0""").execute(db)
// CREATE TRIGGER
try #sql("""
CREATE TRIGGER "update_timestamp" AFTER UPDATE ON "items"
BEGIN UPDATE "items" SET "updatedAt" = datetime('now') WHERE "id" = NEW."id"; END
""").execute(db)
// ALTER TABLE
try #sql("""ALTER TABLE "items" ADD COLUMN "notes" TEXT NOT NULL DEFAULT ''""").execute(db)
使用 #sql 进行 DDL(CREATE、ALTER、索引、触发器)。对于常规 CRUD,使用查询构建器。
migrator.registerMigration("Create tables with foreign keys") { db in
try #sql("""
CREATE TABLE "itemCategories" (
"itemID" TEXT NOT NULL REFERENCES "items"("id") ON DELETE CASCADE,
"categoryID" TEXT NOT NULL REFERENCES "categories"("id") ON DELETE CASCADE,
PRIMARY KEY ("itemID", "categoryID")
) STRICT
""").execute(db)
}
关键: 启用外键强制执行 —— SQLite 默认禁用:
var configuration = Configuration()
configuration.prepareDatabase { db in
try db.execute(sql: "PRAGMA foreign_keys = ON")
}
如果没有 PRAGMA foreign_keys = ON,REFERENCES 和 ON DELETE CASCADE 会被静默忽略。
将批量操作包装在显式事务中,以确保原子性和性能:
try database.write { db in
// 所有操作共享一个事务
for item in items {
try Item.insert { Item.Draft(title: item.title) }.execute(db)
}
}
// 成功时提交一次,失败时完全回滚
database.write { } 块已经是一个事务。对于读取密集的批量分析,使用 database.read { },它提供一致的快照。
@Selection 生成一个 .Columns 类型,用于编译时验证的查询结果:
@Selection
struct ReminderWithList: Identifiable {
var id: Reminder.ID { reminder.id }
let reminder: Reminder
let remindersList: RemindersList
}
@FetchAll(
Reminder.join(RemindersList.all) { $0.remindersListID.eq($1.id) }
.select { ReminderWithList.Columns(reminder: $0, remindersList: $1) }
)
var reminders: [ReminderWithList]
同样适用于聚合查询——请参阅上面的条件聚合部分。
对于可复用的复杂查询,结合使用 @Table @Selection 和 createTemporaryView:
@Table @Selection
private struct ReminderWithList {
let reminderTitle: String
let remindersListTitle: String
}
try database.write { db in
try ReminderWithList.createTemporaryView(
as: Reminder.join(RemindersList.all) { $0.remindersListID.eq($1.id) }
.select { ReminderWithList.Columns(reminderTitle: $0.title, remindersListTitle: $1.title) }
).execute(db)
}
// 像表一样查询——隐藏了连接的复杂性
let results = try ReminderWithList.order { ($0.remindersListTitle, $0.reminderTitle) }.fetchAll(db)
临时视图在连接生命周期内存在。对于持久化视图,请在迁移中使用 #sql("CREATE VIEW ...")。
要使视图可写,请添加 createTemporaryTrigger(insteadOf: .insert { ... }) 以将操作重定向到底层表。
在 Swift 中编写复杂的聚合,避免扭曲的 SQL 子查询:
// 1. 定义——接受 Sequence<T?>,返回聚合结果
@DatabaseFunction
func mode(priority priorities: some Sequence<Reminder.Priority?>) -> Reminder.Priority? {
var occurrences: [Reminder.Priority: Int] = [:]
for priority in priorities {
guard let priority else { continue }
occurrences[priority, default: 0] += 1
}
return occurrences.max { $0.value < $1.value }?.key
}
// 2. 注册
configuration.prepareDatabase { db in db.add(function: $mode) }
// 3. 在查询中使用
let results = try RemindersList.group(by: \.id)
.leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) }
.select { ($0.title, $mode(priority: $1.priority)) }
.fetchAll(db)
常见用途:众数、中位数、加权平均值、自定义过滤。函数在 Swift 中运行(而不是 SQLite 的 C 引擎),因此尽可能使用内置聚合(count、sum、avg、min、max)。
对于高容量同步(50K+ 记录),使用缓存语句而不是类型安全 API:
func batchUpsert(_ items: [Item], in db: Database) throws {
let statement = try db.cachedStatement(sql: """
INSERT INTO items (id, name, libraryID, remoteID, updatedAt)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(libraryID, remoteID) DO UPDATE SET
name = excluded.name, updatedAt = excluded.updatedAt
WHERE excluded.updatedAt >= items.updatedAt
""")
for item in items {
try statement.execute(arguments: [item.id, item.name, item.libraryID, item.remoteID, item.updatedAt])
}
}
对于更高的吞吐量,构建多行 VALUES 子句。在运行时查询变量限制:sqlite3_limit(db.sqliteConnection, SQLITE_LIMIT_VARIABLE_NUMBER, -1)(iOS 14+ 上为 32,766,iOS 13 上为 999)。
| 模式 | 吞吐量 | 权衡 |
|---|---|---|
| 类型安全 upsert | ~1K 行/秒 | 最佳开发者体验,编译时检查 |
| 缓存语句 | ~10K 行/秒 | 良好平衡 |
| 多行 VALUES | ~50K 行/秒 | 最复杂 |
try database.write { db in
try Reminder.createTemporaryTrigger(
after: .insert { new in
Reminder
.find(new.id)
.update {
$0.position = Reminder.select { ($0.position.max() ?? -1) + 1 }
}
}
)
.execute(db)
}
extension Updates<Reminder> {
mutating func toggleStatus() {
self.status = Case(self.status)
.when(#bind(.incomplete), then: #bind(.completing))
.else(#bind(.incomplete))
}
}
// 用法
try Reminder.find(reminder.id).update { $0.toggleStatus() }.execute(db)
enum Priority: Int, QueryBindable {
case low = 1
case medium = 2
case high = 3
}
enum Status: Int, QueryBindable {
case incomplete = 0
case completing = 1
case completed = 2
}
@Table
nonisolated struct Reminder: Identifiable {
let id: UUID
var priority: Priority?
var status: Status = .incomplete
}
// UNION(去重),UNION ALL(保留重复项)
let all = try Customer.select(\.email).union(Supplier.select(\.email)).fetchAll(db)
// INTERSECT(两者都有),EXCEPT(在第一个中但不在第二个中)
let shared = try Customer.select(\.email).intersect(Supplier.select(\.email)).fetchAll(db)
GitHub : pointfreeco/sqlite-data, pointfreeco/swift-structured-queries, groue/GRDB.swift
技能 : axiom-sqlitedata, axiom-sqlitedata-migration, axiom-database-migration, axiom-grdb
目标平台: iOS 17+, Swift 6 框架: SQLiteData 1.4+ 历史: 查看 git 日志了解变更
每周安装次数
102
仓库
GitHub 星标数
610
首次出现
2026年1月21日
安全审计
安装于
opencode85
codex79
cursor77
claude-code76
gemini-cli74
github-copilot71
Advanced query patterns and schema composition techniques for SQLiteData by Point-Free. Built on GRDB and StructuredQueries.
For core patterns (CRUD, CloudKit setup, @Table basics), see the axiom-sqlitedata discipline skill.
This reference covers advanced querying, schema composition, views, and custom aggregates.
Requires iOS 17+, Swift 6 strict concurrency Framework SQLiteData 1.4+
SQLiteData provides powerful tools for composing schema types, enabling reuse, better organization, and single-table inheritance patterns.
Group related columns into reusable types with @Selection:
// Define a reusable column group
@Selection
struct Timestamps {
let createdAt: Date
let updatedAt: Date?
}
// Use in multiple tables
@Table
nonisolated struct RemindersList: Identifiable {
let id: UUID
var title = ""
let timestamps: Timestamps // Embedded column group
}
@Table
nonisolated struct Reminder: Identifiable {
let id: UUID
var title = ""
var isCompleted = false
let timestamps: Timestamps // Same group, reused
}
Important: SQLite has no concept of grouped columns. Flatten all groupings in your CREATE TABLE:
CREATE TABLE "remindersLists" (
"id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
"title" TEXT NOT NULL DEFAULT '',
"createdAt" TEXT NOT NULL,
"updatedAt" TEXT
) STRICT
Access fields inside groups with dot syntax:
// Query a field inside the group
RemindersList
.where { $0.timestamps.createdAt <= cutoffDate }
.fetchAll(db)
// Compare entire group (flattens to tuple in SQL)
RemindersList
.where {
$0.timestamps <= Timestamps(createdAt: date1, updatedAt: date2)
}
Use column groups in custom query results:
@Selection
struct Row {
let reminderTitle: String
let listTitle: String
let timestamps: Timestamps // Nested group
}
let results = try Reminder
.join(RemindersList.all) { $0.remindersListID.eq($1.id) }
.select {
Row.Columns(
reminderTitle: $0.title,
listTitle: $1.title,
timestamps: $0.timestamps // Pass entire group
)
}
.fetchAll(db)
Model polymorphic data using @CasePathable @Selection enums — a value-type alternative to class inheritance:
import CasePaths
@Table
nonisolated struct Attachment: Identifiable {
let id: UUID
let kind: Kind
@CasePathable @Selection
enum Kind {
case link(URL)
case note(String)
case image(URL)
}
}
Note: @CasePathable is required and comes from Point-Free's CasePaths library.
Flatten all cases into nullable columns:
CREATE TABLE "attachments" (
"id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
"link" TEXT,
"note" TEXT,
"image" TEXT
) STRICT
// Fetch all — decoding determines which case
let attachments = try Attachment.all.fetchAll(db)
// Filter by case
let images = try Attachment
.where { $0.kind.image.isNot(nil) }
.fetchAll(db)
try Attachment.insert {
Attachment.Draft(kind: .note("Hello world!"))
}
.execute(db)
// Inserts: (id, NULL, 'Hello world!', NULL)
try Attachment.find(id).update {
$0.kind = #bind(.link(URL(string: "https://example.com")!))
}
.execute(db)
// Sets link column, NULLs note and image columns
Enum cases can hold structured data using nested @Selection types:
@Table
nonisolated struct Attachment: Identifiable {
let id: UUID
let kind: Kind
@CasePathable @Selection
enum Kind {
case link(URL)
case note(String)
case image(Attachment.Image) // Fully qualify nested types
}
@Selection
struct Image {
var caption = ""
var url: URL
}
}
SQL schema flattens all nested fields:
CREATE TABLE "attachments" (
"id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()),
"link" TEXT,
"note" TEXT,
"caption" TEXT,
"url" TEXT
) STRICT
With column groups, @DatabaseFunction can accept entire table rows:
@DatabaseFunction
func isPastDue(reminder: Reminder) -> Bool {
!reminder.isCompleted && reminder.dueDate < Date()
}
// Use in queries — columns are flattened/reconstituted automatically
let pastDue = try Reminder
.where { $isPastDue(reminder: $0) }
.fetchAll(db)
| Approach | SQLiteData | SwiftData |
|---|---|---|
| Type | Value types (enums/structs) | Reference types (classes) |
| Exhaustivity | Compiler-enforced switch | Runtime type checking |
| Verbosity | Concise enum cases | Verbose class hierarchy |
| Inheritance | Single-table via enum | @Model class inheritance |
| Reusable columns | @Selection groups | Manual repetition |
SwiftData equivalent (more verbose):
@Model class Attachment { var isActive: Bool }
@Model class Link: Attachment { var url: URL }
@Model class Note: Attachment { var note: String }
@Model class Image: Attachment { var url: URL }
// Each needs explicit init calling super.init
Build reusable scopes as static properties/methods:
extension Item {
static let active = Item.where { !$0.isArchived && !$0.isDeleted }
static let inStock = Item.where(\.isInStock)
static func createdAfter(_ date: Date) -> Where<Item> {
Item.where { $0.createdAt > date }
}
}
// Chain scopes
let results = try Item.active.inStock.order(by: \.title).fetchAll(db)
// Use as base for @FetchAll
@FetchAll(Item.active) var items
Extend Where<Item> to add composable filters:
extension Where<Item> {
func matching(_ search: String) -> Where<Item> {
self.where { $0.title.contains(search) || $0.notes.contains(search) }
}
}
let results = try Item.inStock.matching(searchText).fetchAll(db)
Use @Fetch when you need multiple pieces of data in a single read transaction (use @FetchAll/@FetchOne for single-table queries):
struct DashboardRequest: FetchKeyRequest {
struct Value: Sendable {
let totalItems: Int
let activeItems: [Item]
let categories: [Category]
}
func fetch(_ db: Database) throws -> Value {
try Value(
totalItems: Item.count().fetchOne(db) ?? 0,
activeItems: Item.where { !$0.isArchived }.order(by: \.updatedAt.desc()).limit(10).fetchAll(db),
categories: Category.order(by: \.name).fetchAll(db)
)
}
}
@Fetch(DashboardRequest()) var dashboard
Dynamic loading with .load():
@Fetch var results = SearchRequest.Value()
.task(id: query) {
try? await $results.load(SearchRequest(query: query), animation: .default)
}
Key benefits: atomic reads, automatic observation, type-safe results.
| Function | Usage | SQL |
|---|---|---|
upper() / lower() | $0.title.upper() | UPPER/LOWER |
trim() / ltrim() / rtrim() | $0.title.trim() | TRIM |
substr(start, len) |
// Coalesce — first non-null value
let name = try User.select { $0.nickname ?? $0.firstName ?? "Anonymous" }.fetchAll(db)
// Null checks
let withDue = try Reminder.where { $0.dueDate.isNot(nil) }.fetchAll(db)
let noDue = try Reminder.where { $0.dueDate.is(nil) }.fetchAll(db)
// Null-safe ordering
let sorted = try Item.order { $0.priority.desc(nulls: .last) }.fetchAll(db)
// IN (set or subquery)
let selected = try Item.where { $0.id.in(selectedIds) }.fetchAll(db)
let inActive = try Item.where { $0.categoryID.in(
Category.where(\.isActive).select(\.id)
)}.fetchAll(db)
// NOT IN
let excluded = try Item.where { !$0.id.in(excludedIds) }.fetchAll(db)
// BETWEEN (or Swift range syntax)
let midRange = try Item.where { $0.price.between(10, and: 100) }.fetchAll(db)
// Offset-based
let items = try Item.order(by: \.createdAt).limit(20, offset: page * 20).fetchAll(db)
// Cursor-based (more efficient for deep pages)
let items = try Item.where { $0.id > lastSeenId }.order(by: \.id).limit(20).fetchAll(db)
let categories = try Item.select(\.category).distinct().fetchAll(db)
Fetch generated values from INSERT, UPDATE, or DELETE operations:
// Insert and get auto-generated ID
let newId = try Item.insert { Item.Draft(title: "New Item") }
.returning(\.id).fetchOne(db)
// Update and return new values
let updates = try Item.find(id).update { $0.count += 1 }
.returning { ($0.id, $0.count) }.fetchOne(db)
// Capture deleted records before removal
let deleted = try Item.where { $0.isArchived }.delete()
.returning(Item.self).fetchAll(db)
Use RETURNING to avoid a second query for auto-generated IDs, audit deletions, or verify updates.
// INNER JOIN — only matching rows
let items = try Item.join(Category.all) { $0.categoryID.eq($1.id) }.fetchAll(db)
// LEFT JOIN — all from left, matching from right (nullable)
let items = try Item.leftJoin(Category.all) { $0.categoryID.eq($1.id) }
.select { ($0, $1) } // (Item, Category?)
.fetchAll(db)
Also available: .rightJoin() (all from right) and .fullJoin() (all from both).
Multi-table joins chain naturally:
extension Reminder {
static let withTags = group(by: \.id)
.leftJoin(ReminderTag.all) { $0.id.eq($1.reminderID) }
.leftJoin(Tag.all) { $1.tagID.eq($2.primaryKey) }
}
struct ManagerAlias: TableAlias { typealias Table = Employee }
let employeesWithManagers = try Employee
.leftJoin(Employee.all.as(ManagerAlias.self)) { $0.managerID.eq($1.id) }
.select { (employeeName: $0.name, managerName: $1.name) }
.fetchAll(db)
// Simple case — map values
let labels = try Item.select {
Case($0.priority).when(1, then: "Low").when(2, then: "Medium")
.when(3, then: "High").else("Unknown")
}.fetchAll(db)
// Searched case — boolean conditions
let status = try Order.select {
Case().when($0.shippedAt.isNot(nil), then: "Shipped")
.when($0.paidAt.isNot(nil), then: "Paid").else("Unknown")
}.fetchAll(db)
// Case in updates (toggle pattern)
try Reminder.find(id).update {
$0.status = Case($0.status)
.when(#bind(.incomplete), then: #bind(.completing))
.when(#bind(.completing), then: #bind(.completed))
.else(#bind(.incomplete))
}.execute(db)
// Single CTE
let expensiveItems = try With {
Item.where { $0.price > 1000 }
} query: { expensive in
expensive.order(by: \.price).limit(10)
}.fetchAll(db)
// Multiple CTEs
let report = try With {
Customer.where { $0.totalSpent > 10000 }
} with: {
Order.where { $0.createdAt > lastMonth }
} query: { highValue, recentOrders in
highValue.join(recentOrders) { $0.id.eq($1.customerID) }
.select { ($0.name, $1.total) }
}.fetchAll(db)
Use CTEs to break complex queries into readable parts, reuse subqueries, or improve query plans.
Query hierarchical data (trees, org charts, threaded comments):
@Table
nonisolated struct Category: Identifiable {
let id: UUID
var name = ""
var parentID: UUID? // Self-referential
}
// Get all descendants of a root category
let allDescendants = try With {
Category.where { $0.id.eq(#bind(rootCategoryId)) } // Base case
} recursiveUnion: { cte in
Category.all.join(cte) { $0.parentID.eq($1.id) }.select { $0 } // Recursive case
} query: { cte in
cte.order(by: \.name)
}.fetchAll(db)
Reverse the join condition ($0.id.eq($1.parentID)) to walk up the tree instead of down.
@Table
struct ReminderText: FTS5 {
let rowid: Int
let title: String
let notes: String
let tags: String
}
// Create FTS table in migration
try #sql(
"""
CREATE VIRTUAL TABLE "reminderTexts" USING fts5(
"title", "notes", "tags",
tokenize = 'trigram'
)
"""
)
.execute(db)
// Highlight search terms
let results = try ItemText.where { $0.match(query) }
.select { ($0.rowid, $0.title.highlight("<b>", "</b>")) }.fetchAll(db)
// Snippets with context
let snippets = try ItemText.where { $0.match(query) }
.select { $0.description.snippet("<b>", "</b>", "...", 64) }.fetchAll(db)
// BM25 relevance ranking
let ranked = try ItemText.where { $0.match(query) }
.order { $0.bm25().desc() }.fetchAll(db)
// groupConcat — comma-separated tags per item
let itemsWithTags = try Item.group(by: \.id)
.leftJoin(ItemTag.all) { $0.id.eq($1.itemID) }
.leftJoin(Tag.all) { $1.tagID.eq($2.id) }
.select { ($0.title, $2.name.groupConcat(separator: ", ")) }
.fetchAll(db)
// ("iPhone", "electronics, mobile, apple")
// jsonGroupArray — aggregate into JSON array
let itemsJson = try Store.group(by: \.id)
.leftJoin(Item.all) { $0.id.eq($1.storeID) }
.select { ($0.name, $1.title.jsonGroupArray()) }
.fetchAll(db)
Options: .groupConcat(distinct: true), .groupConcat(order: { $0.asc() }), .jsonGroupArray(filter: $1.isActive), jsonObject("key", $0.value).
All aggregate functions accept a filter: parameter:
let stats = try Item.select {
Stats.Columns(
total: $0.count(),
activeCount: $0.count(filter: $0.isActive),
avgActivePrice: $0.price.avg(filter: $0.isActive),
totalRevenue: $0.revenue.sum(filter: $0.status.eq(#bind(.completed)))
)
}.fetchOne(db)
.where() filters rows before grouping; .having() filters groups after aggregation:
let frequentCustomers = try Customer.group(by: \.id)
.leftJoin(Order.all) { $0.id.eq($1.customerID) }
.having { $1.count() > 5 }
.select { ($0.name, $1.count()) }
.fetchAll(db)
The #sql macro enables type-safe raw SQL for schema creation and migrations.
migrator.registerMigration("Create initial tables") { 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,
"price" REAL NOT NULL DEFAULT 0.0,
"createdAt" TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT
""").execute(db)
}
\(value) → Automatically escaped (safe for user input)\(raw: value) → Inserted literally (only for identifiers you control)\(raw: userInput) — SQL injection vulnerability// CREATE INDEX (with optional WHERE for partial indexes)
try #sql("""CREATE INDEX "idx_items_search" ON "items" ("title") WHERE "isArchived" = 0""").execute(db)
// CREATE TRIGGER
try #sql("""
CREATE TRIGGER "update_timestamp" AFTER UPDATE ON "items"
BEGIN UPDATE "items" SET "updatedAt" = datetime('now') WHERE "id" = NEW."id"; END
""").execute(db)
// ALTER TABLE
try #sql("""ALTER TABLE "items" ADD COLUMN "notes" TEXT NOT NULL DEFAULT ''""").execute(db)
Use #sql for DDL (CREATE, ALTER, indexes, triggers). Use the query builder for regular CRUD.
migrator.registerMigration("Create tables with foreign keys") { db in
try #sql("""
CREATE TABLE "itemCategories" (
"itemID" TEXT NOT NULL REFERENCES "items"("id") ON DELETE CASCADE,
"categoryID" TEXT NOT NULL REFERENCES "categories"("id") ON DELETE CASCADE,
PRIMARY KEY ("itemID", "categoryID")
) STRICT
""").execute(db)
}
Critical : Enable foreign key enforcement — SQLite disables it by default:
var configuration = Configuration()
configuration.prepareDatabase { db in
try db.execute(sql: "PRAGMA foreign_keys = ON")
}
Without PRAGMA foreign_keys = ON, REFERENCES and ON DELETE CASCADE are silently ignored.
Wrap batch operations in explicit transactions for atomicity and performance:
try database.write { db in
// All operations share one transaction
for item in items {
try Item.insert { Item.Draft(title: item.title) }.execute(db)
}
}
// Commits once on success, rolls back entirely on failure
The database.write { } block is already a transaction. For read-heavy batch analysis, use database.read { } which provides a consistent snapshot.
@Selection generates a .Columns type for compile-time verified query results:
@Selection
struct ReminderWithList: Identifiable {
var id: Reminder.ID { reminder.id }
let reminder: Reminder
let remindersList: RemindersList
}
@FetchAll(
Reminder.join(RemindersList.all) { $0.remindersListID.eq($1.id) }
.select { ReminderWithList.Columns(reminder: $0, remindersList: $1) }
)
var reminders: [ReminderWithList]
Also works for aggregate queries — see the Conditional Aggregation section above.
For reusable complex queries, combine @Table @Selection and createTemporaryView:
@Table @Selection
private struct ReminderWithList {
let reminderTitle: String
let remindersListTitle: String
}
try database.write { db in
try ReminderWithList.createTemporaryView(
as: Reminder.join(RemindersList.all) { $0.remindersListID.eq($1.id) }
.select { ReminderWithList.Columns(reminderTitle: $0.title, remindersListTitle: $1.title) }
).execute(db)
}
// Query like a table — join complexity hidden
let results = try ReminderWithList.order { ($0.remindersListTitle, $0.reminderTitle) }.fetchAll(db)
Temporary views exist for the connection lifetime. For persistent views, use #sql("CREATE VIEW ...") in migrations.
To make views writable, add createTemporaryTrigger(insteadOf: .insert { ... }) to reroute operations to underlying tables.
Write complex aggregation in Swift with @DatabaseFunction, avoiding contorted SQL subqueries:
// 1. Define — takes Sequence<T?>, returns aggregate result
@DatabaseFunction
func mode(priority priorities: some Sequence<Reminder.Priority?>) -> Reminder.Priority? {
var occurrences: [Reminder.Priority: Int] = [:]
for priority in priorities {
guard let priority else { continue }
occurrences[priority, default: 0] += 1
}
return occurrences.max { $0.value < $1.value }?.key
}
// 2. Register
configuration.prepareDatabase { db in db.add(function: $mode) }
// 3. Use in queries
let results = try RemindersList.group(by: \.id)
.leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) }
.select { ($0.title, $mode(priority: $1.priority)) }
.fetchAll(db)
Common uses: mode, median, weighted average, custom filtering. Functions run in Swift (not SQLite's C engine), so use built-in aggregates (count, sum, avg, min, max) when possible.
For high-volume sync (50K+ records), use cached statements instead of the type-safe API:
func batchUpsert(_ items: [Item], in db: Database) throws {
let statement = try db.cachedStatement(sql: """
INSERT INTO items (id, name, libraryID, remoteID, updatedAt)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(libraryID, remoteID) DO UPDATE SET
name = excluded.name, updatedAt = excluded.updatedAt
WHERE excluded.updatedAt >= items.updatedAt
""")
for item in items {
try statement.execute(arguments: [item.id, item.name, item.libraryID, item.remoteID, item.updatedAt])
}
}
For even higher throughput, build multi-row VALUES clauses. Query the variable limit at runtime: sqlite3_limit(db.sqliteConnection, SQLITE_LIMIT_VARIABLE_NUMBER, -1) (32,766 on iOS 14+, 999 on iOS 13).
| Pattern | Throughput | Trade-off |
|---|---|---|
| Type-safe upsert | ~1K rows/sec | Best DX, compile-time checks |
| Cached statement | ~10K rows/sec | Good balance |
| Multi-row VALUES | ~50K rows/sec | Most complex |
try database.write { db in
try Reminder.createTemporaryTrigger(
after: .insert { new in
Reminder
.find(new.id)
.update {
$0.position = Reminder.select { ($0.position.max() ?? -1) + 1 }
}
}
)
.execute(db)
}
extension Updates<Reminder> {
mutating func toggleStatus() {
self.status = Case(self.status)
.when(#bind(.incomplete), then: #bind(.completing))
.else(#bind(.incomplete))
}
}
// Usage
try Reminder.find(reminder.id).update { $0.toggleStatus() }.execute(db)
enum Priority: Int, QueryBindable {
case low = 1
case medium = 2
case high = 3
}
enum Status: Int, QueryBindable {
case incomplete = 0
case completing = 1
case completed = 2
}
@Table
nonisolated struct Reminder: Identifiable {
let id: UUID
var priority: Priority?
var status: Status = .incomplete
}
// UNION (deduplicated), UNION ALL (keep duplicates)
let all = try Customer.select(\.email).union(Supplier.select(\.email)).fetchAll(db)
// INTERSECT (in both), EXCEPT (in first but not second)
let shared = try Customer.select(\.email).intersect(Supplier.select(\.email)).fetchAll(db)
GitHub : pointfreeco/sqlite-data, pointfreeco/swift-structured-queries, groue/GRDB.swift
Skills : axiom-sqlitedata, axiom-sqlitedata-migration, axiom-database-migration, axiom-grdb
Targets: iOS 17+, Swift 6 Framework: SQLiteData 1.4+ History: See git log for changes
Weekly Installs
102
Repository
GitHub Stars
610
First Seen
Jan 21, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
opencode85
codex79
cursor77
claude-code76
gemini-cli74
github-copilot71
SQL查询优化指南:PostgreSQL、Snowflake、BigQuery高性能SQL编写技巧与方言参考
1,100 周安装
CRA迁移Next.js指南:148条规则,从React Router到App Router完整迁移
231 周安装
Elasticsearch 审计日志配置指南:启用、管理与安全事件监控
239 周安装
Node.js依赖更新技能bump-deps:智能提示Major更新,自动应用Minor/Patch更新
229 周安装
Encore API 端点开发指南:TypeScript API 创建与配置教程
230 周安装
Claude AI 与 Claude Code 能力对比参考 - 最新功能、限制与使用指南
230 周安装
前端XSS漏洞扫描器 - React/Vue/Angular/JavaScript代码安全检测工具
236 周安装
$0.title.substr(0, 3) |
| SUBSTR |
replace(old, new) | $0.title.replace("old", "new") | REPLACE |
length() | $0.title.length() | LENGTH |
instr(search) | $0.title.instr("search") > 0 | INSTR |
like(pattern) | $0.title.like("%phone%") | LIKE |
hasPrefix / hasSuffix / contains | $0.title.contains("Max") | Swift-style |
collate(.nocase) | $0.title.collate(.nocase).eq(#bind("X")) | COLLATE |