SQLiteData Documentation

SQLiteData is built on two primary pillars:

1. GRDB: Used for the low-level interaction with SQLite, managing connections, transactions, and database observation.
2. StructuredQueries: A library used for building type-safe, expressive SQL queries and performing high-performance decoding of SQLite rows into Swift types.

This combination allows SQLiteData to provide a SwiftData-like experience (automatic SwiftUI updates) while maintaining the performance and flexibility of direct SQL access.

When using @FetchAll, @FetchOne, or @Fetch directly inside a SwiftUI View, a parent view refresh can overwrite your dynamically updated query with the initial value provided by the parent.

To prevent this and keep the dynamic query state local to the current view, use the combination of @State and the property wrapper: @State @FetchAll. To access the actual data stored within this local state, use the .wrappedValue property.

Every table participating in synchronization must have exactly one single, non-compound primary key. This rule applies even to join tables used for many-to-many relationships. While the primary key might not be required for your application's business logic, it is mandatory for the SyncEngine to facilitate CloudKit synchronization.

CREATE TABLE "reminderTags" (
  "id" TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT (uuid()),
  "reminderID" TEXT NOT NULL REFERENCES "reminders"("id") ON DELETE CASCADE,
  "tagID" TEXT NOT NULL REFERENCES "tags"("id") ON DELETE CASCADE
) STRICT

The IdentifierStringConvertible protocol allows types to be converted into a string representation suitable for use as identifiers within SQLiteData. This is useful when you want to use custom types (like UUID or Tagged types) as primary keys or foreign keys that map to string columns in the database.

By conforming to this protocol, you ensure that your domain models can be seamlessly translated to and from the string-based identifier format required by the underlying storage layer.

Conflicts between record edits are handled automatically using a field-wise last edit wins strategy.

When a column is edited, the library tracks the timestamp for that specific column. During a merge of two conflicting records, the library compares the timestamps for each column individually; the column with the most recent edit wins. This is not a CRDT-based synchronization, but a per-column timestamp-based merge.

Tables cannot have UNIQUE constraints on columns other than the primary key. Distributed creation of records makes uniqueness constraints problematic; if two devices create a record with the same 'unique' value simultaneously, a conflict occurs that cannot be resolved by simply discarding one record.

If you need a column to be unique, consider making that column the primary key of the table.

@Table
struct RemindersListAsset {
  @Column(primaryKey: true)
  let remindersListID: RemindersList.ID
  let image: Data
}

When using SyncEngine, you may want certain database triggers to behave differently depending on whether the write originates from your app's code or from the synchronization process (e.g., CloudKit).

To achieve this, use the SyncEngine.$isSynchronizing SQL expression. This expression returns true if the write originates from the sync engine.

For example, if you want a trigger to run only when the app (not the sync engine) performs an action, use WHEN NOT (SyncEngine.$isSynchronizing) in your SQL definition or !SyncEngine.$isSynchronizing when using StructuredQueries.

#sql(
  """
  CREATE TEMPORARY TRIGGER "…"
  AFTER DELETE ON "…"
  FOR EACH ROW WHEN NOT \(SyncEngine.$isSynchronizing)
  BEGIN
    …
  END
  """
)

The library automatically converts all BLOB columns in a table into CKAssets for seamless CloudKit synchronization.

Best Practice: To avoid performance issues (such as loading large amounts of data into memory during frequent queries or slowing down SQLite row access), store large binary blobs in a separate, related table rather than the main data table. Use a one-to-one relationship where the primary key of the blob table is a foreign key to the main table.

@Table
struct RemindersList: Identifiable {
  let id: UUID
  var title = ""
}

@Table
struct RemindersListCoverImage {
  @Column(primaryKey: true)
  let remindersListID: RemindersList.ID
  var image: Data
}

Starting with version 1.4, the load method for @FetchAll, @FetchOne, and @Fetch returns a FetchSubscription.

This subscription ties the database subscription lifecycle to the surrounding async context. By awaiting the FetchSubscription (or its .task property), you ensure that the database subscription is automatically managed alongside the lifecycle of the async task, which allows views to automatically unsubscribe from the database when they are no longer visible.