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

# Kotlin Legacy→DQL Migration Guide

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

## Overview

This guide will help you successfully migrate your Ditto Kotlin Android 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 Kotlin Android 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 Kotlin Android 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 the diff-aware registerObserver overload (`(result, diff)` lambda) — do NOT construct DittoDiffer directly (its constructor is internal in v5)

  ---

  CORE MIGRATION AREAS:

  1. QUERY SYNTAX MIGRATION

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

  AFTER (DQL):
  ```
  ditto.store.execute(
      "SELECT * FROM cars WHERE color = :color",
      mapOf("color" to "red")
  )
  ```

  2. INSERT OPERATIONS

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

  AFTER (DQL):
  ```
  ditto.store.execute(
      "INSERT INTO cars DOCUMENTS (:car)",
      mapOf("car" to mapOf("_id" to id, "color" to "blue"))
  )
  ```

  3. UPDATE OPERATIONS

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

  AFTER (DQL):
  ```
  ditto.store.execute(
      "UPDATE cars SET color = :color WHERE _id = :id",
      mapOf("color" to "green", "id" to id)
  )
  ```

  4. DELETE OPERATIONS

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

  AFTER (DQL):
  ```
  ditto.store.execute(
      "DELETE FROM cars WHERE _id = :id",
      mapOf("id" to id)
  )
  ```

  5. EVICTION OPERATIONS

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

  AFTER (DQL):
  ```
  ditto.store.execute(
      "EVICT FROM cars WHERE _id = :id",
      mapOf("id" to id)
  )
  ```

  6. COUNTER OPERATIONS (PN_COUNTER)

  BEFORE (Legacy Query Builder):
  ```
  ditto.store.collection("cars")
      .findById(id)
      .update { doc ->
          doc["numUpdates"].counter?.increment(amount = 1.0)
      }
  ```

  AFTER (DQL with PN_INCREMENT):
  ```
  ditto.store.execute(
      "UPDATE cars APPLY numUpdates PN_INCREMENT BY :increment WHERE _id = :id",
      mapOf("increment" to 1, "id" to id)
  )
  ```

  IMPORTANT: Do NOT initialize counter fields in documents:
  ```
  // WRONG - Creates a register, not a counter
  mapOf("counter" to 0)

  // CORRECT - Omit counter field, it's created on first PN_INCREMENT
  mapOf("_id" to id, "color" to "blue")
  ```

  7. DOCUMENT FIELD ACCESS MIGRATION

  BEFORE (Legacy Query Builder):
  ```
  val document: DittoDocument = collection.findById(res.id).exec()
  val color = document.value["color"] as String
  ```

  AFTER (DQL):
  ```
  // store.execute(query, args) returns Unit. Use the handler form to inspect the
  // result (auto-closes), or store.executeRaw() and close it yourself.
  val color: String = ditto.store.execute(
      "SELECT color FROM cars WHERE _id = :id",
      mapOf("id" to id),
  ) { result ->
      var item = result.items.first()
      // item.value is DittoCborSerializable.Dictionary, not Map<String, Any>.
      // Use typed accessors — `.string`, `.stringOrNull`, `.int`, `.boolean`, etc.
      item.value["color"].string
  }
  ```

  8. LIVE QUERY MIGRATION (observeLocal → registerObserver)

  BEFORE (Legacy observeLocal):
  ```
  liveQuery = collection.find("_id.locationId == '${Constants.locationId}'")
      .observeLocal { docs, event ->
          when (event) {
              is DittoLiveQueryEvent.Update -> {
                  adapter.delete(event.deletions)
                  adapter.insert(event.insertions)
                  adapter.update(event.updates)
              }
              is DittoLiveQueryEvent.Initial -> {
                  adapter.setInitialCars(docs)
              }
          }
      }
  ```

  AFTER (DQL with the diff-aware `registerObserver` overload):
  ```
  import com.ditto.kotlin.Ditto
  import com.ditto.kotlin.DittoStoreObserver

  // In v5 the differ is internal — the observer owns it and closes it when the
  // observer is closed. Use the `(result, diff)` overload of registerObserver.
  class MainFragment : Fragment() {
      private var observer: DittoStoreObserver? = null
      private var previousDocumentIds: MutableList<String> = mutableListOf()

      private fun startLiveQuery() {
          observer = ditto.store.registerObserver(
              query = "SELECT * FROM cars WHERE _id.locationId = :locationId",
              arguments = mapOf("locationId" to Constants.locationId),
          ) { result, diff ->
              // Extract IDs as plain Kotlin strings inside the lambda — never
              // return or store the items themselves (use-after-free).
              val currentDocumentIds = result.items.map { item ->
                  val id = item.value["_id"].stringOrNull ?: "unknown"
                  item.dematerialize() // release the materialized value
                  id
              }

              // Handle deletions using stored IDs from the previous emission
              diff.deletions.forEach { index ->
                  previousDocumentIds.getOrNull(index)?.let { deletedId ->
                      // Handle deletion
                  }
              }

              // Handle insertions using current IDs
              diff.insertions.forEach { index ->
                  currentDocumentIds.getOrNull(index)?.let { insertedId ->
                      // Handle insertion
                  }
              }

              // Handle updates using current IDs
              diff.updates.forEach { index ->
                  currentDocumentIds.getOrNull(index)?.let { updatedId ->
                      // Handle update
                  }
              }

              previousDocumentIds = currentDocumentIds.toMutableList()
          }
      }
  }
  ```

  9. SYNC SUBSCRIPTIONS MIGRATION

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

  AFTER (DQL):
  ```
  val subscription = ditto.sync.registerSubscription(
      "SELECT * FROM cars WHERE color = :color",
      mapOf("color" to "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 mapOf()

  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 Leaks in Observers
     - Always call item.dematerialize() after extracting data
     - Store only IDs, not full query items

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

  ---

  MIGRATION CHECKLIST:

  Search for these legacy patterns and replace:
  - [ ] .collection(" → ditto.store.execute("SELECT * FROM
  - [ ] .find( → Convert to DQL WHERE clause with parameters
  - [ ] .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 (use the `(result, diff)` overload if you need diffs)
  - [ ] .subscribe() → Convert to ditto.sync.registerSubscription()
  - [ ] DittoDocument → DittoQueryResultItem
  - [ ] DittoSubscription → DittoSyncSubscription
  - [ ] item.value["x"] as String → item.value["x"].string (or .stringOrNull); .intOrNull / .booleanOrNull for other types
  - [ ] store.execute(query, args) used as a value → use the handler form `store.execute(q, a) { result -> ... }` or `store.executeRaw(q, a)`

  ---

  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
  3. Handling counter operations correctly with PN_INCREMENT
  4. Implementing proper memory management in observers
  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>

***

## Key API Changes Reference

### Query Syntax Migration

**Legacy Query Builder → DQL Query Syntax**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  ditto.store.execute(
      query = "SELECT * FROM cars WHERE color = :color",
      arguments = mapOf("color" to "red")
  )
  ```

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

### Data Operations Migration

**Legacy Query Builder → DQL Insert Operations**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  ditto.store.execute(
      query = "INSERT INTO cars DOCUMENTS (:car)",
      arguments = mapOf("car" to mapOf("_id" to id, "color" to "blue"))
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .upsert(mapOf("_id" to id, "color" to "blue"))
  ```
</CodeGroup>

**Legacy Query Builder → DQL Update Operations**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  ditto.store.execute(
      query = "UPDATE cars SET color = :color WHERE _id = :id",
      arguments = mapOf("color" to "green", "id" to id)
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  // Upsert document
  ditto.store.collection("cars")
      .upsert(mapOf("_id" to id, "color" to "green"))

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

**Legacy Query Builder → DQL Delete Operations**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  ditto.store.execute(
      query = "DELETE FROM cars WHERE _id = :id",
      arguments = mapOf("id" to id)
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  ditto.store.collection("cars").findById(id).remove()
  ```
</CodeGroup>

**Legacy Query Builder → DQL Eviction Operations**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  // Evict by ID
  ditto.store.execute(
      query = "EVICT FROM cars WHERE _id = :id",
      arguments = mapOf("id" to id)
  )

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

  ```kotlin KOTLIN 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>

### Document Field Access Migration

**Legacy Query Builder → Modern Field Access**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  // store.execute(query, args) returns Unit in v5. Pass a handler to inspect the
  // result (auto-closed when the lambda returns), or call store.executeRaw() and
  // close the result yourself.
  val color: String = ditto.store.execute(
      query = "SELECT color FROM cars WHERE _id = :id",
      arguments = mapOf("id" to id),
  ) { result ->
      // item.value is DittoCborSerializable.Dictionary. Use typed accessors —
      // e.g. .string / .stringOrNull / .intOrNull — not Kotlin casts.
      result.items.first().value["color"].string
  }
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  val document: DittoDocument = collection.findById(res.id).exec()
  val color = document.value["color"] as String
  ```
</CodeGroup>

**Legacy Query Builder → Modern Document Conversion**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  import kotlinx.serialization.Serializable
  import kotlinx.serialization.json.Json

  @Serializable
  data class Car(val _id: String, val color: String)

  private val carJson = Json { ignoreUnknownKeys = true }

  // Inside the handler (which auto-closes the result), decode each item to plain
  // Kotlin data via item.jsonString(). Do NOT return DittoQueryResultItem out of
  // the lambda — its native lifetime ends when the handler returns.
  val cars: List<Car> = ditto.store.execute(
      "SELECT * FROM cars",
  ) { result ->
      result.items.map { carJson.decodeFromString<Car>(it.jsonString()) }
  }
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  fun 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>
  ```kotlin KOTLIN DQL (v5) theme={null}
  import com.ditto.kotlin.DittoStoreObserver
  import kotlinx.coroutines.Dispatchers
  import kotlinx.coroutines.withContext

  // In v5, the differ is internal — the observer owns it. Use the `(result, diff)`
  // overload of registerObserver; closing the observer frees the differ.
  class MainFragment : Fragment() {
      private var observer: DittoStoreObserver? = null
      private var previousDocumentIds: List<String> = emptyList()

      private fun startLiveQuery() {
          observer?.close()

          observer = ditto.store.registerObserver(
              query = "SELECT * FROM cars WHERE _id.locationId = :locationId",
              arguments = mapOf("locationId" to Constants.locationId),
          ) { result, diff ->
              // Extract IDs as plain Kotlin strings inside the lambda — never store
              // result.items or DittoQueryResultItem instances; they are closed as
              // soon as this lambda returns (use-after-free otherwise).
              val currentDocumentIds = result.items.map { item ->
                  val id = item.value["_id"].stringOrNull ?: "unknown"
                  item.dematerialize()
                  id
              }

              for (index in diff.deletions) {
                  previousDocumentIds.getOrNull(index)?.let { deletedId ->
                      println("Deleted car with ID: $deletedId")
                  }
              }
              for (index in diff.insertions) {
                  currentDocumentIds.getOrNull(index)?.let { insertedId ->
                      println("Inserted car with ID: $insertedId")
                  }
              }
              for (index in diff.updates) {
                  currentDocumentIds.getOrNull(index)?.let { updatedId ->
                      println("Updated car with ID: $updatedId")
                  }
              }

              previousDocumentIds = currentDocumentIds

              // The handler is suspend — use withContext, not runOnUiThread.
              withContext(Dispatchers.Main) {
                  // Update your adapter / UI here using `currentDocumentIds` + diff.
              }
          }
      }

      override fun onDestroyView() {
          super.onDestroyView()
          observer?.close()
      }
  }
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  liveQuery = CarsApplication.carsCollection?.find("_id.locationId == '${Constants.locationId}'")?.observeLocal { docs, event ->
      println("Live query handler called. Event: $event Docs count: ${docs.count()}")
      when (event) {
          is DittoLiveQueryEvent.Update -> {
              requireActivity().runOnUiThread {
                  val adapter = (viewAdapter as CarsAdapter)
                  adapter.set(docs)
                  adapter.delete(event.deletions)
                  adapter.insert(event.insertions)
                  adapter.update(event.updates)
              }
          }
          is DittoLiveQueryEvent.Initial -> {
              requireActivity().runOnUiThread {
                  val carTotal = (viewAdapter as CarsAdapter).setInitialCars(docs)
                  if (carTotal > 0) {
                      recyclerView.scrollToPosition(carTotal - 1)
                  }
              }
          }
      }
  }
  ```
</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**

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

// Then register observer - queries will use the index
val observer = ditto.store.registerObserver(
    query = "SELECT * FROM cars WHERE _id.locationId = :locationId",
    arguments = mapOf("locationId" to Constants.locationId),
) { result ->
    // Process result.items inside the lambda; extract plain Kotlin data here
    // — do NOT return result.items or DittoQueryResultItem instances.
}
```

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>
  ```kotlin KOTLIN DQL (v5) theme={null}
  val subscription = ditto.sync.registerSubscription(
      query = "SELECT * FROM cars WHERE color = :color",
      arguments = mapOf("color" to "red")
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  val subscription = ditto.store.collection("cars")
      .find("color == $0", "red")
      .subscribe()
  ```
</CodeGroup>

**Subscribe with Parameters**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  val subscription = ditto.sync.registerSubscription(
      query = "SELECT * FROM cars WHERE _id.locationId = :locationId",
      arguments = mapOf("locationId" to Constants.locationId)
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  val subscription = ditto.store.collection("cars")
      .find("_id.locationId == '${Constants.locationId}'")
      .subscribe()
  ```
</CodeGroup>

**Multiple Subscriptions**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  val subscriptions = mutableListOf<DittoSyncSubscription>()
  subscriptions.add(
      ditto.sync.registerSubscription(
          query = "SELECT * FROM cars WHERE color = :color",
          arguments = mapOf("color" to "red")
      )
  )
  subscriptions.add(
      ditto.sync.registerSubscription(
          query = "SELECT * FROM cars WHERE year > :year",
          arguments = mapOf("year" to 2020)
      )
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  val subscriptions = mutableListOf<DittoSubscription>()
  subscriptions.add(
      ditto.store.collection("cars")
          .find("color == 'red'")
          .subscribe()
  )
  subscriptions.add(
      ditto.store.collection("cars")
          .find("year > 2020")
          .subscribe()
  )
  ```
</CodeGroup>

**Cancel Subscription**

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

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

**Subscribe to All Documents**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  val subscription = ditto.sync.registerSubscription(
      "SELECT * FROM cars"
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  val 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>
  ```kotlin KOTLIN DQL (v5) theme={null}
  ditto.store.execute(
      query = "UPDATE cars APPLY numUpdates PN_INCREMENT BY :increment WHERE _id = :id",
      arguments = mapOf("increment" to 1, "id" to id)
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .findById(id)
      .update { doc ->
          doc["numUpdates"].counter?.increment(amount = 1.0)
      }
  ```
</CodeGroup>

**Counter Decrement**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  ditto.store.execute(
      query = "UPDATE cars APPLY viewCount PN_INCREMENT BY :decrement WHERE _id = :id",
      arguments = mapOf("decrement" to -1, "id" to id)
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .findById(id)
      .update { doc ->
          doc["viewCount"].counter?.increment(amount = -1.0)
      }
  ```
</CodeGroup>

**Initialize Counter in Document**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  // Counter fields are automatically created on first PN_INCREMENT use
  ditto.store.execute(
      query = "INSERT INTO cars DOCUMENTS (:car)",
      arguments = mapOf("car" to mapOf(
          "_id" to id,
          "color" to "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
  ditto.store.execute(
      query = "UPDATE cars APPLY numUpdates PN_INCREMENT BY 1 WHERE _id = :id",
      arguments = mapOf("id" to id)
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  val carData = mapOf(
      "_id" to id,
      "color" to "blue",
      "numUpdates" to DittoCounter()
  )
  ditto.store.collection("cars").upsert(carData)
  ```
</CodeGroup>

**Multiple Counter Operations**

<CodeGroup>
  ```kotlin KOTLIN DQL (v5) theme={null}
  ditto.store.execute(
      """UPDATE cars
         APPLY likes PN_INCREMENT BY :likeIncrement,
               dislikes PN_INCREMENT BY :dislikeDecrement,
               views PN_INCREMENT BY :viewIncrement
         WHERE _id = :id""",
      mapOf(
          "likeIncrement" to 1,
          "dislikeDecrement" to -1,
          "viewIncrement" to 1,
          "id" to id
      )
  )
  ```

  ```kotlin KOTLIN LEGACY (v4) theme={null}
  ditto.store.collection("cars")
      .findById(id)
      .update { doc ->
          doc["likes"].counter?.increment(amount = 1.0)
          doc["dislikes"].counter?.increment(amount = -1.0)
          doc["views"].counter?.increment(amount = 1.0)
      }
  ```
</CodeGroup>

### Attachment Operations with DQL

**Attachment Creation and Storage**

```kotlin theme={null}
// Create attachment using store
val attachment = ditto.store.newAttachment(
    inputStream = fileInputStream,
    metadata = metadata,
)

// Store attachment with DQL — build the document inline as a plain map,
// with the attachment as the value of the ATTACHMENT-typed column.
ditto.store.execute(
    query = "INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)",
    arguments = mapOf("doc" to mapOf("_id" to "car-1", "image" to attachment)),
)
```

**Attachment Fetching**

```kotlin theme={null}
// Fetch attachment with progress callback and handle the final result
when (val fetchResult = ditto.store.fetchAttachment(
    token = attachmentToken,
    onFetchProgress = { downloadedBytes, totalBytes ->
        updateProgress(downloadedBytes, totalBytes)
    },
)) {
    is DittoAttachmentFetchResult.Completed -> {
        fetchResult.attachment.use { attachment ->
            // Process the fetched attachment data
        }
    }
    is DittoAttachmentFetchResult.Deleted -> {
        // Handle deletion of the attachment
    }
}
```

## 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:**

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

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

// Create index on nested field
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.

```kotlin theme={null}
// ❌ Wrong: String interpolation
val color = "red"
ditto.store.execute("SELECT * FROM cars WHERE color = '$color'")

// ✅ Correct: Using :paramName with mapOf()
ditto.store.execute(
    query = "SELECT * FROM cars WHERE color = :color",
    arguments = mapOf("color" to "red")
)
```

### 2. Missing Parameter Binding

**NEVER** use string interpolation in queries. Always use parameterized queries with `Map<String, Any>`.

```kotlin theme={null}
// ❌ Wrong: Direct string interpolation
val locationId = "loc_123"
ditto.store.execute(
    "SELECT * FROM cars WHERE _id.locationId = '$locationId'"
)

// ✅ Correct: Parameterized query
ditto.store.execute(
    query = "SELECT * FROM cars WHERE _id.locationId = :locationId",
    arguments = mapOf("locationId" to locationId)
)
```

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

```kotlin theme={null}
// ❌ Wrong: Initializing counter with a number (creates REGISTER, not COUNTER)
ditto.store.execute(
    query = "INSERT INTO items DOCUMENTS (:doc)",
    arguments = mapOf("doc" to mapOf("counter" to 0, "_id" to id))
)

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

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

// ✅ Correct: Decrement by passing negative value
ditto.store.execute(
    query = "UPDATE items APPLY counter PN_INCREMENT BY :value WHERE _id = :id",
    arguments = mapOf("value" to -1, "id" to id)
)
```

### 4. Memory Management with Observers

The `DittoQueryResult` passed to your handler — and every `DittoQueryResultItem` it
owns — is closed as soon as your lambda returns. **Never** return `result.items`
out of the lambda or store individual items in a field; doing so will throw
`IllegalStateException: Query result item has already been closed`. Use the
`(result, diff)` overload of `registerObserver` when you need a diff — the differ
is managed by the observer in v5; you do not construct one yourself.

```kotlin theme={null}
// ❌ Wrong: storing DittoQueryResultItem instances outside the lambda
var items: List<DittoQueryResultItem> = emptyList()
val observer = ditto.store.registerObserver(
    query = "SELECT * FROM cars"
) { result ->
    items = result.items  // CRASH on next emission: items are already closed
}

// ✅ Correct: extract plain Kotlin data from *only* the items pointed to by
// the diff, and maintain your own mirror across emissions. Never return
// DittoQueryResultItem instances out of the lambda. Close the observer in onDestroy
class MainFragment : Fragment() {
    // Local mirror of the observed list — updated incrementally via diffs.
    private val documentIds: MutableList<String> = mutableListOf()
    private var observer: DittoStoreObserver? = null

    private fun startLiveQuery() {
        observer = ditto.store.registerObserver(
            query = "SELECT * FROM cars",
        ) { result, diff ->
            // Only touch items pointed to by the diff
            diff.deletions.sortedDescending().forEach { documentIds.removeAt(it) }
            diff.insertions.sorted().forEach { i ->
                val item = result.items[i]
                documentIds.add(i, item.value["_id"].stringOrNull ?: "unknown")
                item.dematerialize()
            }
            diff.updates.forEach { i ->
                val item = result.items[i]
                documentIds[i] = item.value["_id"].stringOrNull ?: "unknown"
                item.dematerialize()
            }
        }
    }

    override fun onDestroyView() {
        super.onDestroyView()
        observer?.close()
    }
}
```

### 5. Attachment Handling

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

```kotlin theme={null}
// ❌ Wrong: Missing ATTACHMENT annotation — the attachment is inserted as
// an opaque nested map, not an attachment-typed column.
ditto.store.execute(
    query = "INSERT INTO cars DOCUMENTS (:doc)",
    arguments = mapOf("doc" to mapOf("_id" to id, "image" to attachment))
)

// ✅ Correct: Use ATTACHMENT annotation in COLLECTION definition
val attachment = ditto.store.newAttachment(
    inputStream = fileInputStream,
    metadata = metadata
)

ditto.store.execute(
    query = "INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)",
    arguments = mapOf("doc" to mapOf(
        "_id" to id,
        "image" to attachment
    ))
)
```


## Related topics

- [Kotlin V4→V5 API Migration Guide](/sdk/latest/migration-guides/kotlin-v4.md)
- [C++ Legacy→DQL Migration Guide](/sdk/latest/migration-guides/cpp-dql.md)
- [C# Legacy→DQL Migration Guide](/sdk/latest/migration-guides/c-sharp-dql.md)