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
When to Use 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 Quick Reference // 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
Anti-Patterns (Common Mistakes) ❌ Using == in predicates // WRONG — may not work in all contexts .where { $0.status == .completed }
// CORRECT — use comparison methods .where { $0.status.eq(#bind(.completed)) }
❌ Wrong update order // 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)
❌ Instance methods for insert // 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)
❌ Missing nonisolated // WRONG — Swift 6 concurrency warning @Table struct Item { ... }
// CORRECT @Table nonisolated struct Item { ... }
❌ Awaiting inside write block // 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) }
❌ Forgetting .execute(db) // 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) }
@Table Model Definitions Basic Table 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 Custom Primary Key @Table nonisolated struct Tag: Hashable, Identifiable { @Column(primaryKey: true) var title: String // Custom primary key var id: String { title } }
Column Customization @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 = ""
}
Foreign Keys @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.
@Ephemeral — Non-Persisted Properties
Mark properties that exist in Swift but not in the database:
@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.
Database Setup Create Database 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
}
Register in Dependencies 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() }
Query Patterns Property Wrappers (@FetchAll, @FetchOne)
The primary way to observe database changes in SwiftUI:
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+) @FetchOne for Aggregates 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)")
}
}
Lifecycle-Aware Fetching (v1.4.0+)
Use .task to automatically cancel observation when view disappears:
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):
.task { try? await $items.load(query) } .onDisappear { Task { try await $items.load(Item.none) } }
With v1.4.0 (automatic):
.task { try? await $items.load(query).task // Auto-cancels }
Filtering // 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") }
Sorting // 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()) }
Static Fetch Helpers (v1.4.0+)
Cleaner syntax for fetching:
// OLD (verbose)
let items = try Item.all.fetchAll(db)
let item = try Item.find(id).fetchOne(db) // returns Optional
// 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.
Insert / Update / Delete Insert try database.write { db in try Item.insert { Item.Draft(title: "New Item", isInStock: true) } .execute(db) }
Insert with RETURNING (get generated ID) let newId = try database.write { db in try Item.insert { Item.Draft(title: "New Item") } .returning(.id) .fetchOne(db) }
Update Single Record try database.write { db in try Item.find(itemId) .update { $0.title = "Updated Title" } .execute(db) }
Update Multiple Records try database.write { db in try Item.where(.isArchived) .update { $0.isDeleted = true } .execute(db) }
Delete // 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) }
Upsert (Insert or Update)
SQLite's UPSERT (INSERT ... ON CONFLICT ... DO UPDATE) expresses "insert if missing, otherwise update" in one statement.
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) With Partial Unique Index
When your UNIQUE index has a WHERE clause, add a conflict filter:
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:
CREATE UNIQUE INDEX idx_items_sync_identity ON items (libraryID, remoteID) WHERE remoteID IS NOT NULL
Merge Strategies
Replace all mutable fields (sync mirror):
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):
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:
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)
❌ Common Upsert Mistakes
Missing UNIQUE constraint:
// WRONG — no index to conflict against onConflict: { ($0.libraryID, $0.remoteID) } // but table has no UNIQUE(libraryID, remoteID)
Using INSERT OR REPLACE:
// 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: { ... }
Batch Operations Batch Insert 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:
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.
Raw SQL with #sql Macro
When you need custom SQL expressions beyond the type-safe query builder, use the #sql macro from StructuredQueries:
Custom Query Expressions
nonisolated extension Item.TableColumns {
var isPastDue: some QueryExpression
// Use in queries let overdue = try Item.where { $0.isPastDue }.fetchAll(db)
Raw SQL Queries // 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.
CloudKit Sync Basic Setup 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 ) }
Manual Sync Control (v1.3.0+)
Control when sync happens instead of automatic background sync:
@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 Sync State Observation (v1.2.0+)
Show UI feedback during sync:
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 Query Sync Metadata (v1.3.0+)
Access CloudKit sync information for records:
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)
Migration Helpers
Migrate primary keys when switching sync strategies:
try await syncEngine.migratePrimaryKeys( from: OldItem.self, to: NewItem.self )
When to Drop to GRDB
SQLiteData is built on GRDB. Use raw GRDB when you need:
Complex joins:
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:
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.
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