From sqlitedata-swift
Use when building with SQLiteData — covers @Table models, @FetchAll/@FetchOne/@Fetch property wrappers, FetchKeyRequest, database setup, migrations, and query building. NOT for CloudKit sync (use router) or debugging errors (use diag)
How this skill is triggered — by the user, by Claude, or both
Slash command
/sqlitedata-swift:sqd-coreThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
You are working with **SQLiteData** (by Point-Free), a fast, lightweight replacement for SwiftData powered by SQLite via GRDB. This skill covers the core patterns for using the library.
You are working with SQLiteData (by Point-Free), a fast, lightweight replacement for SwiftData powered by SQLite via GRDB. This skill covers the core patterns for using the library.
| SwiftData | SQLiteData |
|---|---|
@Model | @Table (from StructuredQueries) |
@Query | @FetchAll / @FetchOne / @Fetch |
@Relationship | SQL REFERENCES + joins |
ModelContainer | prepareDependencies { $0.defaultDatabase = ... } |
ModelContext | @Dependency(\.defaultDatabase) + .write { db in } |
#Predicate | .where { $0.field == value } or #sql(...) |
FetchDescriptor | StructuredQueries query builders |
VersionedSchema | DatabaseMigrator with raw SQL |
| Automatic CloudKit | SyncEngine (explicit, configurable) |
SQLiteData is built on:
@Table, @Column, @Selection, #sql)SharedReader for reactive observation@Dependency, prepareDependencies)The library re-exports key GRDB types: Database, DatabaseWriter, DatabaseReader, DatabaseQueue, DatabasePool, Configuration, DatabaseMigrator, DatabaseError, ValueObservationScheduler.
It also re-exports StructuredQueriesSQLite (which gives @Table, @Column, @Selection, #sql, query builders).
@ObservationIgnored required on @FetchAll/@FetchOne/@Fetch in @Observable classes — without it, observation fires twicenonisolated before every @Table struct — required for Swift 6 strict concurrencyNOT NULL ON CONFLICT REPLACE DEFAULT <value> on all non-nullable columns in CloudKit-synced schemas — without ON CONFLICT REPLACE, cross-version sync breaksSTRICT on every CREATE TABLE — enforces type safetyprepareDependencies called exactly once at app startup — calling twice or calling after first access causes blank databaseModels are plain Swift structs decorated with the @Table macro:
@Table
nonisolated struct Item: Identifiable {
let id: UUID
var title: String = ""
var isCompleted: Bool = false
var position: Int = 0
var dueDate: Date?
var listID: RemindersList.ID // Foreign key
}
Key rules:
nonisolated before the struct for strict concurrencylet id: UUID — primary key (auto-generated Draft type omits it)Item.Draft (for inserts), Item.TableColumns, Item.Columns, query buildersCustom column options:
@Column(primaryKey: true) // Non-standard primary key
let remindersListID: RemindersList.ID
@Column(as: Color.HexRepresentation.self) // Custom coding
var color: Color = .blue
Custom table name:
@Table("remindersTags")
nonisolated struct ReminderTag: Identifiable { ... }
Use @Selection to define custom types that hold query results from joins or aggregates:
@Selection
struct ReminderListState: Identifiable {
var id: RemindersList.ID { remindersList.id }
var remindersCount: Int
var remindersList: RemindersList
@Column(as: CKShare?.SystemFieldsRepresentation.self)
var share: CKShare?
}
@Selection
struct Stats {
var allCount = 0
var flaggedCount = 0
var scheduledCount = 0
var todayCount = 0
}
The macro generates a .Columns(...) initializer used in .select { } clauses.
import OSLog
import SQLiteData
func appDatabase() throws -> any DatabaseWriter {
@Dependency(\.context) var context
var configuration = Configuration()
configuration.foreignKeysEnabled = true // If using foreign keys
// Optional: attach metadatabase for CloudKit metadata queries
// (enables joining SyncMetadata for CKRecord/CKShare data)
configuration.prepareDatabase { db in
try db.attachMetadatabase()
}
// Optional: query tracing in DEBUG
#if DEBUG
configuration.prepareDatabase { db in
db.trace(options: .profile) {
if context == .preview {
print("\($0.expandedDescription)")
} else {
logger.debug("\($0.expandedDescription)")
}
}
}
#endif
let database = try defaultDatabase(configuration: configuration)
// Migrations
var migrator = DatabaseMigrator()
#if DEBUG
migrator.eraseDatabaseOnSchemaChange = true
#endif
migrator.registerMigration("Create tables") { db in
try #sql("""
CREATE TABLE "items" (
"id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()),
"title" TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',
"isCompleted" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0,
"position" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0,
"dueDate" TEXT,
"listID" TEXT NOT NULL REFERENCES "lists"("id") ON DELETE CASCADE
) STRICT
""").execute(db)
}
try migrator.migrate(database)
return database
}
private let logger = Logger(subsystem: "MyApp", category: "Database")
@main
struct MyApp: App {
init() {
prepareDependencies {
$0.defaultDatabase = try! appDatabase()
}
}
var body: some Scene { ... }
}
#Preview {
let _ = prepareDependencies {
$0.defaultDatabase = try! appDatabase()
}
ContentView()
}
@Suite(.dependency(\.defaultDatabase, try! appDatabase()))
struct MyTests { ... }
Always use raw SQL for table definitions (they are frozen in time):
CREATE TABLE "items" (
"id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()),
"title" TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',
"isCompleted" INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0,
"priority" INTEGER,
"listID" TEXT NOT NULL REFERENCES "lists"("id") ON DELETE CASCADE
) STRICT
Critical rules:
STRICT mode for type safetyNOT NULL ON CONFLICT REPLACE DEFAULT <value> — required for CloudKit sync compatibility (use /skill sqd-cloudkit for CloudKit details)DEFAULT (uuid()) — maps to CKRecord.ID in CloudKit (use /skill sqd-cloudkit for CloudKit details)REFERENCES and ON DELETE CASCADECREATE VIRTUAL TABLE ... USING fts5(...)CREATE INDEX IF NOT EXISTS "idx_items_listID" ON "items"("listID")// Fetch all items (default order)
@FetchAll var items: [Item]
// With query
@FetchAll(Item.order(by: \.title))
var items
// Complex query with join and select
@FetchAll(
RemindersList
.group(by: \.id)
.order(by: \.position)
.leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) && !$1.isCompleted }
.select {
ListState.Columns(
remindersCount: $1.id.count(),
remindersList: $0
)
},
animation: .default
)
var lists
// With animation (iOS 17+)
@FetchAll(Item.order(by: \.title), animation: .default)
var items
Properties available:
wrappedValue: [Element] — the data$items.loadError — last error$items.isLoading — loading state$items.publisher — Combine publisher$items.load(newQuery) — reload with different queryDynamic queries via load:
.task {
try await $items.load(Item.where { $0.isCompleted == showCompleted })
}
// Count
@FetchOne(Reminder.count())
var remindersCount = 0
// Filtered count
@FetchOne(Reminder.where(\.isCompleted).count())
var completedCount = 0
// Complex aggregate with @Selection
@FetchOne(
Reminder.select {
Stats.Columns(
allCount: $0.count(filter: !$0.isCompleted),
flaggedCount: $0.count(filter: $0.isFlagged && !$0.isCompleted),
scheduledCount: $0.count(filter: $0.isScheduled),
todayCount: $0.count(filter: $0.isToday)
)
}
)
var stats = Stats()
For bundling multiple queries into a single database transaction:
struct PlayersRequest: FetchKeyRequest {
struct Value {
let injuredPlayerCount: Int
let players: [Player]
}
func fetch(_ db: Database) throws -> Value {
try Value(
injuredPlayerCount: Player.where(\.isInjured).fetchCount(db),
players: Player
.where { !$0.isInjured }
.order(by: \.name)
.limit(10)
.fetchAll(db)
)
}
}
// Usage:
@Fetch(PlayersRequest()) var response = PlayersRequest.Value()
// Access:
response.players // [Player]
response.injuredPlayerCount // Int
FetchKeyRequest protocol:
public protocol FetchKeyRequest<Value>: Hashable, Sendable {
associatedtype Value
func fetch(_ db: Database) throws -> Value
}
// Basic CRUD
Item.all // SELECT * FROM items
Item.where { $0.isCompleted } // WHERE isCompleted
Item.where(\.isCompleted) // Same, shorter
Item.order(by: \.title) // ORDER BY title
Item.order { $0.title.desc() } // ORDER BY title DESC
Item.limit(10) // LIMIT 10
Item.find(someID) // WHERE id = ?
// Chaining
Item.where { !$0.isCompleted }
.order(by: \.title, \.position)
.limit(20)
// Joins
Item.join(List.all) { $0.listID.eq($1.id) }
Item.leftJoin(Tag.all) { $0.id.eq($1.itemID) }
// Group by
Item.group(by: \.id)
.leftJoin(Tag.all) { $0.id.eq($1.itemID) }
.having { $1.count().gt(0) }
// Aggregates
Item.count()
Item.select { $0.title.count(filter: $0.isCompleted) }
// Raw SQL via #sql macro
#sql("SELECT * FROM items WHERE isCompleted ORDER BY title DESC")
#sql("SELECT \(Item.columns) FROM \(Item.self) WHERE \(Item.isCompleted)")
@Dependency(\.defaultDatabase) var database
// Insert with Draft (omits primary key — DB generates UUID)
try database.write { db in
try Item.insert {
Item.Draft(title: "Get milk", listID: someListID)
}.execute(db)
}
// Upsert
try database.write { db in
try Item.upsert {
Item.Draft(title: "Get milk")
}.execute(db)
}
// Update
try database.write { db in
try Item.find(id)
.update { $0.title = "Updated title" }
.execute(db)
}
// Delete
try database.write { db in
try Item.where { $0.id.in(ids) }
.delete()
.execute(db)
}
// Batch update with Case expression
try database.write { db in
try Item
.where { $0.id.in(ids) }
.update {
let enumerated = Array(ids.enumerated())
let (first, rest) = (enumerated.first!, enumerated.dropFirst())
$0.position = rest
.reduce(Case($0.id).when(first.element, then: first.offset)) { cases, id in
cases.when(id.element, then: id.offset)
}
.else($0.position)
}
.execute(db)
}
nonisolated extension Reminder.TableColumns {
var isCompleted: some QueryExpression<Bool> {
status.neq(Reminder.Status.incomplete)
}
var isPastDue: some QueryExpression<Bool> {
@Dependency(\.date.now) var now
return !isCompleted && #sql("coalesce(date(\(dueDate)) < date(\(now)), 0)")
}
}
Use these in queries: Reminder.where { $0.isPastDue }
try Item.createTemporaryTrigger(
after: .insert { new in
Item.find(new.id)
.update { $0.position = Item.select { ($0.position.max() ?? -1) + 1 } }
}
).execute(db)
try Item.createTemporaryTrigger(
after: .delete { old in
ItemText.where { $0.rowid.eq(old.rowid) }.delete()
}
).execute(db)
try Item.createTemporaryTrigger(
after: .update { ($0.title, $0.notes) }
forEachRow: { _, new in
ItemText.where { $0.rowid.eq(new.rowid) }
.update { $0.title = new.title; $0.notes = new.notes }
}
).execute(db)
enum Priority: Int, QueryBindable {
case low = 1, medium, high
}
enum Status: Int, QueryBindable {
case incomplete = 0, completing = 2, completed = 1
}
@MainActor
@Observable
class ItemsModel {
@ObservationIgnored
@FetchAll(Item.order(by: \.title), animation: .default)
var items
@ObservationIgnored
@Dependency(\.defaultDatabase) private var database
func delete(at offsets: IndexSet) {
withErrorReporting {
let ids = offsets.map { items[$0].id }
try database.write { db in
try Item.where { $0.id.in(ids) }.delete().execute(db)
}
}
}
}
Key pattern: Use @ObservationIgnored on @FetchAll/@FetchOne/@Fetch properties in @Observable classes to prevent double-observation.
nonisolated on @Table structs — causes concurrency warnings@ObservationIgnored on fetch property wrappers in @Observable classesNOT NULL without ON CONFLICT REPLACE in CloudKit-synced schemas — schemas are additive-only once deployed (use /skill sqd-cloudkit for CloudKit details)STRICT on table definitionsprepareDependencies more than once — only call once at app startupSearches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.
Guides Payload CMS config (payload.config.ts), collections, fields, hooks, access control, APIs. Debugs validation errors, security, relationships, queries, transactions, hook behavior.
Implements vector databases with Pinecone, Weaviate, Qdrant, Milvus, pgvector for semantic search, RAG, recommendations, and similarity systems. Optimizes embeddings, indexing, and hybrid search.
npx claudepluginhub sitapix/sqlitedata-swift-skills --plugin sqlitedata-swift