Sqd Core

SQLiteData Core Patterns

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.

Key Differences from SwiftData

Architecture Overview

SQLiteData is built on:

• GRDB (7.6.0+) — SQLite wrapper
• StructuredQueries — Type-safe SQL query builder (@Table, @Column, @Selection, #sql)
• swift-sharing — SharedReader for reactive observation
• swift-dependencies — Dependency injection (@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).

Critical Gotchas (check every time)

1. @ObservationIgnored required on @FetchAll/@FetchOne/@Fetch in @Observable classes — without it, observation fires twice
2. nonisolated before every @Table struct — required for Swift 6 strict concurrency
3. NOT NULL ON CONFLICT REPLACE DEFAULT <value> on all non-nullable columns in CloudKit-synced schemas — without ON CONFLICT REPLACE, cross-version sync breaks
4. STRICT on every CREATE TABLE — enforces type safety
5. prepareDependencies called exactly once at app startup — calling twice or calling after first access causes blank database
6. Never edit deployed migrations — always add new ones

1. Model Definition with @Table

Models 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:

• Use nonisolated before the struct for strict concurrency
• let id: UUID — primary key (auto-generated Draft type omits it)
• Default values map to SQL defaults
• Optional properties map to nullable columns
• The macro generates: Item.Draft (for inserts), Item.TableColumns, Item.Columns, query builders

Custom 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 { ... }

2. @Selection for Custom Projections

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.

3. Database Setup

Step-by-step setup:

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

Entry point setup:

@main
struct MyApp: App {
  init() {
    prepareDependencies {
      $0.defaultDatabase = try! appDatabase()
    }
  }
  var body: some Scene { ... }
}

Preview setup:

#Preview {
  let _ = prepareDependencies {
    $0.defaultDatabase = try! appDatabase()
  }
  ContentView()
}

Test setup:

@Suite(.dependency(\.defaultDatabase, try! appDatabase()))
struct MyTests { ... }

4. Schema Design (SQL)

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:

• Use STRICT mode for type safety
• NOT NULL ON CONFLICT REPLACE DEFAULT <value> — required for CloudKit sync compatibility (use /skill sqd-cloudkit for CloudKit details)
• UUID primary keys via DEFAULT (uuid()) — maps to CKRecord.ID in CloudKit (use /skill sqd-cloudkit for CloudKit details)
• Foreign keys with REFERENCES and ON DELETE CASCADE
• FTS5 for full-text search: CREATE VIRTUAL TABLE ... USING fts5(...)
• Foreign key indexes: CREATE INDEX IF NOT EXISTS "idx_items_listID" ON "items"("listID")

5. @FetchAll — Fetch Collections

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

Dynamic queries via load:

.task {
  try await $items.load(Item.where { $0.isCompleted == showCompleted })
}

6. @FetchOne — Fetch Single Values/Aggregates

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

7. @Fetch — Multiple Queries in One Transaction

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
}

8. Query Building Patterns

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

9. Writing Data

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

10. Computed Columns (on TableColumns)

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 }

11. Triggers (StructuredQueries)

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)

12. Enum Coding for Query Columns

enum Priority: Int, QueryBindable {
  case low = 1, medium, high
}
enum Status: Int, QueryBindable {
  case incomplete = 0, completing = 2, completed = 1
}

13. @Observable Model Pattern

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

Common Mistakes to Avoid

1. Missing nonisolated on @Table structs — causes concurrency warnings
2. Missing @ObservationIgnored on fetch property wrappers in @Observable classes
3. Using NOT NULL without ON CONFLICT REPLACE in CloudKit-synced schemas — schemas are additive-only once deployed (use /skill sqd-cloudkit for CloudKit details)
4. Editing frozen migrations — always add new migrations, never modify deployed ones
5. Missing foreign key indexes — always create indexes on foreign key columns
6. Forgetting STRICT on table definitions
7. Using prepareDependencies more than once — only call once at app startup