> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.

# Swift Legacy→DQL Migration Guide

> Guide for migrating Swift apps from legacy query builder APIs to DQL (Ditto Query Language) syntax.

## Overview

This guide will help you successfully migrate your Ditto Swift application from the legacy query builder APIs to the modern DQL (Ditto Query Language). After reviewing this documentation, you'll understand how to convert method chaining patterns to DQL syntax and systematically update your data operations.

***

## AI Agent Prompt

Use this prompt when working with an AI coding assistant to migrate your Ditto Swift app from legacy query builder to DQL.

<Accordion title="Copy AI Migration Prompt (Click to Expand)">
  ````text theme={null}
  I need help migrating a Ditto Swift application from the legacy query builder APIs to modern DQL (Ditto Query Language). This migration involves converting method chaining patterns to SQL-like DQL syntax.

  CRITICAL RULES:
  1. All query builder method chains (.collection().find()) must be replaced with ditto.store.execute() using DQL
  2. Use parameterized queries with :paramName syntax - NEVER string interpolation
  3. Counter operations must use PN_INCREMENT BY in APPLY clause - do NOT initialize counter fields
  4. Sync subscriptions must use ditto.sync.registerSubscription() instead of .find().subscribe()
  5. observeLocal must be replaced with registerObserver

  ---

  CORE MIGRATION AREAS:

  1. QUERY SYNTAX MIGRATION

  BEFORE (Legacy Query Builder):
  ```
  ditto.store.collection("cars")
      .find("color == $0", args: "red")
      .exec()
  ```

  AFTER (DQL):
  ```
  try await ditto.store.execute(
      query: "SELECT * FROM cars WHERE color = :color",
      arguments: ["color": "red"])
  ```

  2. INSERT OPERATIONS

  BEFORE (Legacy Query Builder):
  ```
  ditto.store.collection("cars")
      .upsert(["_id": id, "color": "blue"])
  ```

  AFTER (DQL):
  ```
  try await ditto.store.execute(
      query: "INSERT INTO cars DOCUMENTS (:car)",
      arguments: ["car": carData])
  ```

  3. UPDATE OPERATIONS

  BEFORE (Legacy Query Builder):
  ```
  ditto.store.collection("cars")
      .findByID(id)
      .update { doc in
          doc?["color"].set("green")
      }
  ```

  AFTER (DQL):
  ```
  try await ditto.store.execute(
      query: "UPDATE cars SET color = :color WHERE _id = :id",
      arguments: ["color": "green", "id": id])
  ```

  4. DELETE OPERATIONS

  BEFORE (Legacy Query Builder):
  ```
  ditto.store.collection("cars").findByID(id).remove()
  ```

  AFTER (DQL):
  ```
  try await ditto.store.execute(
      query: "DELETE FROM cars WHERE _id = :id",
      arguments: ["id": id])
  ```

  5. EVICTION OPERATIONS

  BEFORE (Legacy Query Builder):
  ```
  ditto.store.collection("cars").findByID(id).evict()
  ```

  AFTER (DQL):
  ```
  try await ditto.store.execute(
      query: "EVICT FROM cars WHERE _id = :id",
      arguments: ["id": id])
  ```

  6. COUNTER OPERATIONS (PN_COUNTER)

  BEFORE (Legacy Query Builder):
  ```
  ditto.store.collection("cars")
      .findByID(id)
      .update { doc in
          doc?["numUpdates"].counter?.increment(by: 1.0)
      }
  ```

  AFTER (DQL with PN_INCREMENT):
  ```
  try await ditto.store.execute(
      query: "UPDATE cars APPLY numUpdates PN_INCREMENT BY :increment WHERE _id = :id",
      arguments: ["increment": 1, "id": id])
  ```

  IMPORTANT: Do NOT initialize counter fields in documents:
  ```
  // WRONG - Creates a register, not a counter
  ["counter": 0]

  // CORRECT - Omit counter field, it's created on first PN_INCREMENT
  ["_id": id, "color": "blue"]
  ```

  7. DOCUMENT FIELD ACCESS MIGRATION

  BEFORE (Legacy Query Builder):
  ```
  let document = ditto.store.collection("cars").findByID(id).exec()
  let color = document?.value["color"] as? String
  ```

  AFTER (DQL):
  ```
  let result = try await ditto.store.execute(query: dqlString)
  let item = result.items.first
  let color = item?.value["color"] as? String
  ```

  8. OBSERVER MIGRATION (observeLocal → registerObserver)

  BEFORE (Legacy observeLocal):
  ```
  liveQuery = ditto.store.collection("cars")
      .find("_id.locationId == '\(Constants.locationId)'")
      .observeLocal { docs, event in
          switch event {
          case .update(let changes):
              // Handle changes
          case .initial:
              // Handle initial data
          }
      }
  ```

  AFTER (DQL with registerObserver):
  ```
  observer = ditto.store.registerObserver(
      query: "SELECT * FROM cars WHERE _id.locationId = :locationId",
      arguments: ["locationId": Constants.locationId]
  ) { result in
      // Extract data within callback — do not pass QueryResultItems outside this scope
      let cars = result.items.compactMap { item -> Car? in
          let car = Car(item.jsonData())
          item.dematerialize()
          return car
      }

      // Update UI on main thread with extracted model objects
      DispatchQueue.main.async {
          self.updateUI(cars)
      }
  }

  // Don't forget cleanup in deinit
  deinit {
      observer?.stop()
  }
  ```

  9. SYNC SUBSCRIPTIONS MIGRATION

  BEFORE (Legacy Query Builder):
  ```
  let subscription = ditto.store.collection("cars")
      .find("color == $0", args: "red")
      .subscribe()
  ```

  AFTER (DQL):
  ```
  let subscription = try ditto.sync.registerSubscription(
      query: "SELECT * FROM cars WHERE color = :color",
      arguments: ["color": "red"])
  ```

  ---

  COMMON PITFALLS TO AVOID:

  1. DQL Syntax Errors
     - Use :paramName for parameters, not $0 or string interpolation

  2. Missing Parameter Binding
     - NEVER use string interpolation in queries
     - Always use parameterized queries with arguments dictionary

  3. Counter Type Errors
     - Do NOT initialize counter fields with DittoCounter() or numbers
     - Use PN_INCREMENT BY in APPLY clause
     - Pass negative values for decrements

  4. Memory Management with Observers
     - Always store observer reference
     - Call observer?.stop() in deinit

  5. Attachment Handling
     - Use ATTACHMENT annotation: "(image ATTACHMENT)"
     - Create attachments with ditto.store.newAttachment()

  ---

  MIGRATION CHECKLIST:

  Search for these legacy patterns and replace:
  - [ ] .collection( → try await ditto.store.execute(query: "SELECT * FROM
  - [ ] .find( → Convert to DQL WHERE clause with arguments
  - [ ] .findByID( → Convert to DQL WHERE _id = :id
  - [ ] .upsert( → Convert to DQL INSERT INTO
  - [ ] .update( → Convert to DQL UPDATE SET
  - [ ] .remove( → Convert to DQL DELETE FROM
  - [ ] .evict( → Convert to DQL EVICT FROM
  - [ ] .counter?.increment( → Convert to PN_INCREMENT BY in APPLY clause
  - [ ] DittoCounter() → Remove initialization, use PN_INCREMENT
  - [ ] .observeLocal( → Convert to registerObserver
  - [ ] .subscribe() → Convert to ditto.sync.registerSubscription()
  - [ ] DittoDocument → DittoQueryResultItem
  - [ ] DittoSubscription → DittoSyncSubscription

  ---

  Please help me convert all legacy query builder patterns in my codebase to DQL syntax. Focus on:
  1. Maintaining the same functionality
  2. Using proper parameterized queries with arguments dictionary
  3. Handling counter operations correctly with PN_INCREMENT
  4. Implementing proper observer cleanup in deinit
  5. Converting all sync subscriptions to DQL

  Start by identifying all uses of .collection() in my codebase and systematically converting each one to the appropriate DQL pattern.
  ````
</Accordion>

***

## Syntax Change Reference

### Document Query Syntax

**Document Query All**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "SELECT * FROM cars"
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars").exec()
  ```
</CodeGroup>

**Document Query by ID**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "SELECT * FROM cars WHERE _id = '123'"
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .find_by_id("123")
      .exec()
  ```
</CodeGroup>

**Document Query with Predicate**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "SELECT * FROM cars WHERE color = 'blue'"
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .find("color == 'blue'")
      .exec()
  ```
</CodeGroup>

**Document Query with Arguments**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "SELECT * FROM cars WHERE color = :color",
      arguments: ["color": "red"])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .find("color == $0", args: "red")
      .exec()
  ```
</CodeGroup>

**Document Insert**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "INSERT INTO cars DOCUMENTS (:car)",
      arguments: ["car": carData])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .upsert(["_id": id, "color": "blue"])
  ```
</CodeGroup>

**Document Update**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "UPDATE cars SET color = :color WHERE _id = :id",
      arguments: ["color": "green", "id": id])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  // Upsert document
  ditto.store.collection("cars")
      .upsert(["_id": id, "color": "green"])

  // Update with closure
  ditto.store.collection("cars")
      .findByID(id)
      .update { doc in
          doc?["color"].set("green")
      }
  ```
</CodeGroup>

**Document Delete**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "DELETE FROM cars WHERE _id = :id",
      arguments: ["id": id])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars").findByID(id).remove()
  ```
</CodeGroup>

**Document Local Eviction**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // Evict by ID
  try await ditto.store.execute(
      query: "EVICT FROM cars WHERE _id = :id",
      arguments: ["id": id])

  // Evict all matching documents
  try await ditto.store.execute(
      query: "EVICT FROM cars WHERE color = :color",
      arguments: ["color": "red"])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  // Evict by ID
  ditto.store.collection("cars").findByID(id).evict()

  // Evict all matching documents
  ditto.store.collection("cars").findAll().evict()
  ```
</CodeGroup>

### Query Response Handling

**Legacy Query Builder → DQL Response**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  let result = try await ditto.store.execute(query: dqlString)
  let item = result.items.first
  let color = item?.value["color"] as? String
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  let document = ditto.store.collection("cars").findByID(id).exec()
  let color = document?.value["color"] as? String
  ```
</CodeGroup>

**Legacy Query Builder → Modern Document Conversion**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // Extract data and dematerialize within the same scope
  func documentToCar(item: DittoQueryResultItem) -> Car {
      let model = item.value["_id"] as! String
      let color = item.value["color"] as! String

      // Prune to free memory — do not use `item` after this point
      item.dematerialize()

      return Car(id: model, color: color)
  }

  // Usage — call within the callback or observer scope only
  let cars = result.items.compactMap { documentToCar(item: $0) }
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  func documentToCar(doc: DittoDocument) -> Car {
      return Car(
          id: doc.value["_id"] as! String,
          color: doc.value["color"] as! String
      )
  }
  ```
</CodeGroup>

### Observer Migration

**Legacy Query Builder → DQL Store Observer Migration**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  observer = ditto.store.registerObserver(
      query: "SELECT * FROM cars WHERE _id.locationId = :locationId",
      arguments: ["locationId": Constants.locationId]
  ) { result in
      // Extract data within callback scope — do not pass QueryResultItems outside this closure
      // Using jsonData() is the most memory-efficient approach in Swift
      let cars = result.items.compactMap { item -> Car? in
          let car = Car(item.jsonData())
          item.dematerialize() // Release native memory
          return car
      }

      // Update UI on main thread with extracted model objects
      DispatchQueue.main.async {
          self.updateUI(cars)
      }
  }
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  liveQuery = ditto.store.collection("cars")
      .find("_id.locationId == '\(Constants.locationId)'")
      .observeLocal { docs, event in
          print("Live query handler called. Event: \(event) Docs count: \(docs.count)")
          switch event {
          case .update(let changes):
              DispatchQueue.main.async {
                  // Handle deletions, insertions, updates
              }
          case .initial:
              DispatchQueue.main.async {
                  // Handle initial data
              }
          }
      }
  ```
</CodeGroup>

<Warning>
  **Performance Consideration**: DQL observers provide more advanced return results including aggregates and projections. This requires more database full scans to ensure consistent results compared to the legacy query builder.

  **Use indexes on query fields** to maintain and improve observer performance. Indexes ensure your observers remain functional with optimal query performance.
</Warning>

**Best Practice: Create Indexes for Observer Queries**

```swift theme={null}
// Create index on frequently queried fields
try await ditto.store.execute("""
    CREATE INDEX idx_cars_locationId
    ON cars (_id.locationId)
""")

// Then register observer - queries will use the index
observer = ditto.store.registerObserver(
    query: "SELECT * FROM cars WHERE _id.locationId = :locationId",
    arguments: ["locationId": Constants.locationId]
) { result in
    // Process results
}
```

For more information on creating and managing indexes, see the [DQL Indexing documentation](/dql/indexing).

### Sync Subscriptions Migration

**Legacy Query Builder → DQL Sync Subscriptions**

**Subscribe with Query**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  let subscription = try ditto.sync.registerSubscription(
      query: "SELECT * FROM cars WHERE color = :color",
      arguments: ["color": "red"])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  let subscription = ditto.store.collection("cars")
      .find("color == $0", args: "red")
      .subscribe()
  ```
</CodeGroup>

**Subscribe with Parameters**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  let subscription = try ditto.sync.registerSubscription(
      query: "SELECT * FROM cars WHERE _id.locationId = :locationId",
      arguments: ["locationId": Constants.locationId])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  let subscription = ditto.store.collection("cars")
      .find("_id.locationId == '\(Constants.locationId)'")
      .subscribe()
  ```
</CodeGroup>

**Multiple Subscriptions**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  var subscriptions: [DittoSyncSubscription] = []
  subscriptions.append(
      try ditto.sync.registerSubscription(
          query: "SELECT * FROM cars WHERE color = :color",
          arguments: ["color": "red"])
  )
  subscriptions.append(
      try ditto.sync.registerSubscription(
          query: "SELECT * FROM cars WHERE year > :year",
          arguments: ["year": 2020])
  )
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  var subscriptions: [DittoSubscription] = []
  subscriptions.append(
      ditto.store.collection("cars")
          .find("color == 'red'")
          .subscribe()
  )
  subscriptions.append(
      ditto.store.collection("cars")
          .find("year > 2020")
          .subscribe()
  )
  ```
</CodeGroup>

**Cancel Subscription**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  subscription.cancel()
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  subscription.cancel()
  ```
</CodeGroup>

**Subscribe to All Documents**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  let subscription = try ditto.sync.registerSubscription(
      query: "SELECT * FROM cars")
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  let subscription = ditto.store.collection("cars")
      .findAll()
      .subscribe()
  ```
</CodeGroup>

### Counter Type Migration

<Note>
  **PN\_COUNTER is the DQL equivalent of the legacy `DittoCounter` type.** When migrating counter operations from the legacy query builder's `.counter?.increment()` method, use `PN_INCREMENT BY` in the `APPLY` clause. This maintains full compatibility with existing counter data created by `DittoCounter`.
</Note>

**Counter Increment**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "UPDATE cars APPLY numUpdates PN_INCREMENT BY :increment WHERE _id = :id",
      arguments: ["increment": 1, "id": id])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .findByID(id)
      .update { doc in
          doc?["numUpdates"].counter?.increment(by: 1.0)
      }
  ```
</CodeGroup>

**Counter Decrement**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: "UPDATE cars APPLY viewCount PN_INCREMENT BY :decrement WHERE _id = :id",
      arguments: ["decrement": -1, "id": id])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .findByID(id)
      .update { doc in
          doc?["viewCount"].counter?.increment(by: -1.0)
      }
  ```
</CodeGroup>

**Initialize Counter in Document**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // Counter fields are automatically created on first PN_INCREMENT use
  try await ditto.store.execute(
      query: "INSERT INTO cars DOCUMENTS (:car)",
      arguments: ["car": [
          "_id": id,
          "color": "blue"
          // Do NOT initialize counter fields - they are created on first PN_INCREMENT
      ]])

  // Then use PN_INCREMENT with APPLY clause to create and increment the counter
  try await ditto.store.execute(
      query: "UPDATE cars APPLY numUpdates PN_INCREMENT BY 1 WHERE _id = :id",
      arguments: ["id": id])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  let carData: [String: Any?] = [
      "_id": id,
      "color": "blue",
      "numUpdates": DittoCounter()
  ]
  ditto.store.collection("cars").upsert(carData)
  ```
</CodeGroup>

**Multiple Counter Operations**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  try await ditto.store.execute(
      query: """
      UPDATE cars
      APPLY likes PN_INCREMENT BY :likeIncrement,
            dislikes PN_INCREMENT BY :dislikeDecrement,
            views PN_INCREMENT BY :viewIncrement
      WHERE _id = :id
      """,
      arguments: [
          "likeIncrement": 1,
          "dislikeDecrement": -1,
          "viewIncrement": 1,
          "id": id
      ])
  ```

  ```swift SWIFT LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .findByID(id)
      .update { doc in
          doc?["likes"].counter?.increment(by: 1.0)
          doc?["dislikes"].counter?.increment(by: -1.0)
          doc?["views"].counter?.increment(by: 1.0)
      }
  ```
</CodeGroup>

### Attachment Operations with DQL

**Attachment Creation and Storage**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // Create attachment using store
  let attachment = try ditto.store.newAttachment(
      path: filePath,
      metadata: metadata)

  // Store attachment with DQL
  try await ditto.store.execute(
      query: "INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)",
      arguments: ["doc": docWithAttachment])
  ```
</CodeGroup>

**Attachment Fetching**

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // Fetch attachment with progress callback
  let fetchResult = try await ditto.store.fetchAttachment(
      token: attachmentToken,
      onFetchEvent: { event in
          if case let .progress(downloadedBytes, totalBytes) = event {
              updateProgress(downloadedBytes, totalBytes)
          }
      })
  ```
</CodeGroup>

***

## Performance Enhancements

### Indexes for Improved Query Performance

DQL observers and queries benefit significantly from proper indexing. When migrating from the legacy query builder to DQL, creating indexes on frequently queried fields is essential for maintaining optimal performance.

**Why Indexes Matter for DQL:**

* DQL observers support advanced features like aggregates and projections
* These advanced features require full database scans to ensure consistent results
* Indexes dramatically reduce query execution time by avoiding full scans
* Combining indexes with observers provides better performance than legacy query builder

**Creating Indexes:**

```swift theme={null}
// Create index on single field
try await ditto.store.execute("""
    CREATE INDEX idx_cars_color
    ON cars (color)
""")

// Create compound index on multiple fields
try await ditto.store.execute("""
    CREATE INDEX idx_cars_color_year
    ON cars (color, year)
""")

// Create index on nested field
try await ditto.store.execute("""
    CREATE INDEX idx_cars_location
    ON cars (_id.locationId)
""")
```

**Best Practices:**

1. Create indexes on fields used in `WHERE` clauses
2. Create indexes before registering observers for those queries
3. Use compound indexes for queries with multiple filter conditions
4. Monitor query performance and add indexes as needed

For comprehensive information on indexing strategies, syntax, and best practices, see the [DQL Indexing documentation](/dql/indexing).

***

## Common Pitfalls to Avoid

### 1. DQL Syntax Errors

Use `:paramName` for parameters, not `$0` or string interpolation.

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // ❌ Wrong: String interpolation
  let color = "red"
  try await ditto.store.execute(query: "SELECT * FROM cars WHERE color = '\(color)'")

  // ✅ Correct: Using :paramName with arguments dictionary
  try await ditto.store.execute(
      query: "SELECT * FROM cars WHERE color = :color",
      arguments: ["color": "red"])
  ```
</CodeGroup>

### 2. Missing Parameter Binding

**NEVER** use string interpolation in queries. Always use parameterized queries with `[String: Any]` arguments dictionary.

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // ❌ Wrong: Direct string interpolation
  let locationId = "loc_123"
  try await ditto.store.execute(
      query: "SELECT * FROM cars WHERE _id.locationId = '\(locationId)'")

  // ✅ Correct: Parameterized query
  try await ditto.store.execute(
      query: "SELECT * FROM cars WHERE _id.locationId = :locationId",
      arguments: ["locationId": locationId])
  ```
</CodeGroup>

### 3. Counter Type Errors

Use `COUNTER` annotation in collection definitions. Do **NOT** use `SET` with `COUNTER` fields. Use `APPLY` with `PN_INCREMENT BY`. Pass negative values for decrements.

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // ❌ Wrong: Initializing counter with a number (creates REGISTER, not COUNTER)
  try await ditto.store.execute(
      query: "INSERT INTO items DOCUMENTS (:doc)",
      arguments: ["doc": ["counter": 0, "_id": id]])

  // ❌ Wrong: Using SET on counter field
  try await ditto.store.execute(
      query: "UPDATE items SET counter = 5 WHERE _id = :id",
      arguments: ["id": id])

  // ✅ Correct: Use PN_INCREMENT BY with APPLY clause (creates counter on first use)
  try await ditto.store.execute(
      query: "UPDATE COLLECTION items (counter COUNTER) APPLY counter PN_INCREMENT BY :value WHERE _id = :id",
      arguments: ["value": 1, "id": id])

  // ✅ Correct: Decrement by passing negative value
  try await ditto.store.execute(
      query: "UPDATE items APPLY counter PN_INCREMENT BY :value WHERE _id = :id",
      arguments: ["value": -1, "id": id])
  ```
</CodeGroup>

### 4. Memory Management with Observers

Extract data immediately within the callback using `jsonData()` or `item.value`, then call `dematerialize()` to free native memory. Always call `observer?.stop()` in `deinit`. Use indexes for improved memory and performance.

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // ❌ Wrong: Storing QueryResultItems outside callback scope
  var items: [DittoQueryResultItem] = []
  observer = ditto.store.registerObserver(query: "SELECT * FROM cars") { result in
      self.items = result.items  // Holds native memory
  }

  // ✅ Correct: Extract data and dematerialize immediately
  class ViewController: UIViewController {
      var observer: DittoStoreObserver?
      var cars: [Car] = []

      func setupObserver() {
          observer = ditto.store.registerObserver(
              query: "SELECT * FROM cars",
              arguments: [:]
          ) { result in
              // Extract using jsonData() - most memory-efficient
              let cars = result.items.compactMap { item -> Car? in
                  let car = Car(item.jsonData())
                  item.dematerialize()  // Free native memory
                  return car
              }
              DispatchQueue.main.async { self.cars = cars }
          }
      }

      deinit {
          observer?.stop()  // Always stop observer
      }
  }
  ```
</CodeGroup>

### 5. Attachment Handling

Use `ATTACHMENT` annotation in collection definitions. Create attachments with `ditto.store.newAttachment()`.

<CodeGroup>
  ```swift SWIFT DQL (v5) theme={null}
  // ❌ Wrong: Missing ATTACHMENT annotation
  try await ditto.store.execute(
      query: "INSERT INTO cars DOCUMENTS (:doc)",
      arguments: ["doc": docWithAttachment])

  // ✅ Correct: Use ATTACHMENT annotation in COLLECTION definition
  let attachment = try ditto.store.newAttachment(
      path: filePath,
      metadata: metadata)

  try await ditto.store.execute(
      query: "INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)",
      arguments: ["doc": [
          "_id": id,
          "image": attachment
      ]])
  ```
</CodeGroup>