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

# C++ Legacy→DQL Migration Guide

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

## Overview

This guide covers migrating from Ditto's legacy query builder API to DQL (Ditto Query Language) in C++. DQL provides a SQL-like syntax that's more powerful and intuitive for querying and manipulating data.

**Note:** This migration is required alongside the [V4→V5 API Migration](./cpp-v4) for a complete upgrade.

***

## AI Agent Prompt

Use this prompt when working with an AI coding assistant to migrate your Ditto C++ app to DQL.

<Accordion title="Copy AI Migration Prompt (Click to Expand)">
  ````text theme={null}
  I need help migrating a Ditto C++ application from legacy query builder to DQL (Ditto Query Language). Focus on these critical changes:

  QUERIES:
  Replace collection().find() with DQL SELECT statements
  - Use std::map<std::string, CborValue> for parameterized queries
  - Use empty map {} for queries without parameters
  - Quote string literals in queries or use parameters

  MUTATIONS:
  Replace upsert/update/remove with DQL INSERT/UPDATE/EVICT
  - Use parameterized arguments for all values
  - Results use .then() and .on_error() callbacks

  OBSERVERS:
  Convert to register_observer(query, args, callback)
  - Results are std::vector<CborValue> instead of std::vector<DittoDocument>
  - Same cleanup pattern with .stop()

  SUBSCRIPTIONS:
  Convert to register_subscription(query, args)
  - Cleanup uses .cancel() instead of .close()

  COUNTERS:
  Critical change - use PN_INCREMENT BY in APPLY clause
  - Never initialize counter fields
  - Use APPLY clause: "APPLY viewCount = PN_INCREMENT BY :amount"

  BEFORE (LEGACY):
  ```cpp
  ditto->store()
      .collection("cars")
      .find("color == 'red'")
      .exec()
      .then([](auto docs) {
          // Use docs
      });

  ditto->store()
      .collection("cars")
      .find_by_id("abc123")
      .update([](auto& mutable_doc) {
          mutable_doc["viewCount"].as_counter().increment(1);
      });
  ```

  AFTER (DQL):
  ```cpp
  std::map<std::string, CborValue> args = {{"color", "red"}};
  ditto->store().execute(
      "SELECT * FROM cars WHERE color = :color",
      args
  ).then([](auto result) {
      auto cars = result.items();
      // Use cars
  });

  std::map<std::string, CborValue> args = {
      {"id", "abc123"},
      {"amount", 1}
  };
  ditto->store().execute(
      "UPDATE cars APPLY viewCount = PN_INCREMENT BY :amount WHERE _id = :id",
      args
  );
  ```

  COMMON PITFALLS:
  - Always quote string literals or use parameters
  - Never initialize counter fields to 0
  - Use APPLY (not SET) for counter operations
  - Use {} for queries without parameters
  - Use .as_string(), .as_int() for type conversion

  Work through the codebase systematically. Show me each file's changes.
  ````
</Accordion>

***

## Document Query Syntax

### Query All Documents

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  ditto->store().execute(
      "SELECT * FROM cars",
      {}
  ).then([](auto result) {
      auto cars = result.items();
      // Use cars
  }).on_error([](auto error) {
      std::cerr << "Query failed: " << error.message() << std::endl;
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find_all()
      .exec()
      .then([](auto docs) {
          // Use docs
      });
  ```
</CodeGroup>

### Query Document by ID

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  std::map<std::string, CborValue> args = {{"id", "abc123"}};

  ditto->store().execute(
      "SELECT * FROM cars WHERE _id = :id",
      args
  ).then([](auto result) {
      if (!result.items().empty()) {
          auto car = result.items()[0];
          // Use car
      }
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find_by_id("abc123")
      .exec()
      .then([](auto doc) {
          // Use doc
      });
  ```
</CodeGroup>

### Query with Predicate

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  ditto->store().execute(
      "SELECT * FROM cars WHERE color = 'red'",
      {}
  ).then([](auto result) {
      auto red_cars = result.items();
      // Use red_cars
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find("color == 'red'")
      .exec()
      .then([](auto docs) {
          // Use docs
      });
  ```
</CodeGroup>

### Query with Parameterized Arguments

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  std::map<std::string, CborValue> args = {{"color", "red"}};

  ditto->store().execute(
      "SELECT * FROM cars WHERE color = :color",
      args
  ).then([](auto result) {
      auto red_cars = result.items();
      // Use red_cars
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  std::string color = "red";
  ditto->store()
      .collection("cars")
      .find("color == '" + color + "'")
      .exec()
      .then([](auto docs) {
          // Use docs
      });
  ```
</CodeGroup>

***

## Insert, Update, Delete, Eviction

### Insert Document

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  std::map<std::string, CborValue> doc = {
      {"_id", "abc123"},
      {"color", "red"},
      {"miles", 20000}
  };

  ditto->store().execute(
      "INSERT INTO cars DOCUMENTS (:doc)",
      {{"doc", doc}}
  ).then([](auto result) {
      std::cout << "Insert successful" << std::endl;
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  std::map<std::string, CborValue> content = {
      {"color", "red"},
      {"miles", 20000}
  };

  ditto->store()
      .collection("cars")
      .upsert(content, "abc123")
      .then([](auto id) {
          std::cout << "Insert successful" << std::endl;
      });
  ```
</CodeGroup>

### Update Document

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  std::map<std::string, CborValue> args = {
      {"id", "abc123"},
      {"miles", 25000}
  };

  ditto->store().execute(
      "UPDATE cars SET miles = :miles WHERE _id = :id",
      args
  ).then([](auto result) {
      std::cout << "Update successful" << std::endl;
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find_by_id("abc123")
      .update([](auto& mutable_doc) {
          mutable_doc["miles"] = 25000;
      })
      .then([](auto updated) {
          std::cout << "Update successful" << std::endl;
      });
  ```
</CodeGroup>

### Delete Document

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  std::map<std::string, CborValue> args = {{"id", "abc123"}};

  ditto->store().execute(
      "EVICT FROM cars WHERE _id = :id",
      args
  ).then([](auto result) {
      std::cout << "Delete successful" << std::endl;
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find_by_id("abc123")
      .remove()
      .then([](auto success) {
          std::cout << "Delete successful" << std::endl;
      });
  ```
</CodeGroup>

### Evict All Documents Matching Condition

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  std::map<std::string, CborValue> args = {{"miles", 100000}};

  ditto->store().execute(
      "EVICT FROM cars WHERE miles > :miles",
      args
  ).then([](auto result) {
      std::cout << "Eviction successful" << std::endl;
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find("miles > 100000")
      .remove()
      .then([](auto success) {
          std::cout << "Eviction successful" << std::endl;
      });
  ```
</CodeGroup>

***

## Query Response Handling

### Working with Query Results

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  ditto->store().execute(
      "SELECT * FROM cars",
      {}
  ).then([](auto result) {
      auto items = result.items();

      for (const auto& car : items) {
          auto id = car["_id"].as_string();
          auto color = car["color"].as_string();
          auto miles = car["miles"].as_int();

          std::cout << "Car " << id << " is " << color << " with " << miles << " miles" << std::endl;
      }
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find_all()
      .exec()
      .then([](auto docs) {
          for (const auto& doc : docs) {
              auto id = doc.id();
              auto color = doc["color"].as_string();
              auto miles = doc["miles"].as_int();

              std::cout << "Car " << id << " is " << color << " with " << miles << " miles" << std::endl;
          }
      });
  ```
</CodeGroup>

***

## Observer Migration

### Observing Query Results

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  std::string query = "SELECT * FROM cars WHERE color = :color";
  std::map<std::string, CborValue> args = {{"color", "red"}};

  auto observer = ditto->store().register_observer(
      query,
      args,
      [](auto result) {
          auto red_cars = result.items();
          // Update UI with red_cars
      }
  );

  // Later: clean up
  observer.stop();
  ```

  ```cpp C++ (LEGACY) theme={null}
  auto live_query = ditto->store()
      .collection("cars")
      .find("color == 'red'")
      .observe([](auto docs, auto event) {
          // Update UI with docs
      });

  // Later: clean up
  live_query.stop();
  ```
</CodeGroup>

**Key Differences:**

* DQL observers use `register_observer(query, args, callback)` instead of `.observe()`
* Results are `std::vector<CborValue>` instead of `std::vector<DittoDocument>`
* Same cleanup pattern with `.stop()`

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

```cpp theme={null}
// Create index on frequently queried fields
ditto->store().execute(
    "CREATE INDEX idx_cars_color ON cars (color)",
    {}
).then([](auto result) {
    std::cout << "Index created" << std::endl;
});

// Then register observer - queries will use the index
auto observer = ditto->store().register_observer(
    "SELECT * FROM cars WHERE color = :color",
    {{"color", "red"}},
    [](auto result) {
        // Process results
    }
);
```

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

***

## Sync Subscriptions Migration

### Creating Sync Subscriptions

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  std::string query = "SELECT * FROM cars WHERE color = :color";
  std::map<std::string, CborValue> args = {{"color", "red"}};

  auto subscription = ditto->sync().register_subscription(
      query,
      args
  );

  // Later: clean up
  subscription.cancel();
  ```

  ```cpp C++ (LEGACY) theme={null}
  auto subscription = ditto->store()
      .collection("cars")
      .find("color == 'red'")
      .subscribe();

  // Later: clean up
  subscription.close();
  ```
</CodeGroup>

**Key Differences:**

* DQL subscriptions use `register_subscription(query, args)` instead of `.subscribe()`
* Cleanup now uses `.cancel()` instead of `.close()`

***

## Counter Type Migration

### Updating Counter Values

<Warning>
  **Critical Change**: Counters are now modified using the `PN_INCREMENT BY` operation in the `APPLY` clause, not by setting values directly.
</Warning>

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

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  // Increment a counter
  std::map<std::string, CborValue> args = {
      {"id", "abc123"},
      {"amount", 1}
  };

  ditto->store().execute(
      "UPDATE cars "
      "APPLY viewCount = PN_INCREMENT BY :amount "
      "WHERE _id = :id",
      args
  );
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find_by_id("abc123")
      .update([](auto& mutable_doc) {
          if (!mutable_doc.contains("viewCount")) {
              mutable_doc["viewCount"] = DittoCounter();
          }
          mutable_doc["viewCount"].as_counter().increment(1);
      });
  ```
</CodeGroup>

**Important Notes:**

* **Never initialize counter fields** in INSERT statements - they start at 0 automatically when incremented
* Use `PN_INCREMENT BY` in the `APPLY` clause for counter operations
* Positive values increment, negative values decrement
* Counter values are read as integers in SELECT queries

<CodeGroup>
  ```cpp C++ (DQL - CORRECT) theme={null}
  // First increment - no initialization needed
  ditto->store().execute(
      "UPDATE cars "
      "APPLY viewCount = PN_INCREMENT BY 1 "
      "WHERE _id = :id",
      {{"id", "abc123"}}
  );

  // Reading counter value
  ditto->store().execute(
      "SELECT viewCount FROM cars WHERE _id = :id",
      {{"id", "abc123"}}
  ).then([](auto result) {
      if (!result.items().empty()) {
          auto count = result.items()[0]["viewCount"].as_int();
          std::cout << "View count: " << count << std::endl;
      }
  });
  ```

  ```cpp C++ (DQL - WRONG) theme={null}
  // ❌ WRONG: Don't initialize counters to 0
  std::map<std::string, CborValue> doc = {
      {"_id", "abc123"},
      {"viewCount", 0}  // ❌ WRONG
  };
  ditto->store().execute(
      "INSERT INTO cars DOCUMENTS (:doc)",
      {{"doc", doc}}
  );

  // ❌ WRONG: Don't SET counters
  ditto->store().execute(
      "UPDATE cars SET viewCount = viewCount + 1 WHERE _id = :id",
      {{"id", "abc123"}}
  );
  ```
</CodeGroup>

***

## Attachment Operations

### Fetching Attachments

<CodeGroup>
  ```cpp C++ (DQL) theme={null}
  // Get attachment token from query
  ditto->store().execute(
      "SELECT avatar FROM cars WHERE _id = :id",
      {{"id", "abc123"}}
  ).then([ditto](auto result) {
      if (!result.items().empty()) {
          auto car = result.items()[0];
          auto token = car["avatar"].as_attachment_token();

          if (token) {
              auto fetcher = ditto->store().fetch_attachment(
                  token,
                  [](auto attachment, auto error) {
                      if (!error) {
                          auto data = attachment.data();
                          // Use attachment data
                      }
                  }
              );
          }
      }
  });
  ```

  ```cpp C++ (LEGACY) theme={null}
  ditto->store()
      .collection("cars")
      .find_by_id("abc123")
      .exec()
      .then([](auto doc) {
          auto attachment = doc.attachment("avatar");
          if (attachment) {
              auto data = attachment.data();
              // Use attachment data
          }
      });
  ```
</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:**

```cpp theme={null}
// Create index on single field
ditto->store().execute(
    "CREATE INDEX idx_cars_color ON cars (color)",
    {}
).then([](auto result) {
    std::cout << "Index created" << std::endl;
});

// 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 string literals or concatenation.

```cpp theme={null}
// ❌ Wrong: String concatenation
std::string color = "red";
ditto->store().execute("SELECT * FROM cars WHERE color = '" + color + "'", {});

// ✅ Correct: Using :paramName with std::map<std::string, CborValue>
std::map<std::string, CborValue> args = {{"color", "red"}};
ditto->store().execute("SELECT * FROM cars WHERE color = :color", args);
```

### 2. Missing Parameter Binding

**NEVER** use string concatenation in queries. Always use parameterized queries with `std::map<std::string, CborValue>`.

```cpp theme={null}
// ❌ Wrong: String concatenation
std::string locationId = "loc_123";
ditto->store().execute(
    "SELECT * FROM cars WHERE _id.locationId = '" + locationId + "'",
    {}
);

// ✅ Correct: Parameterized query
std::map<std::string, CborValue> args = {{"locationId", locationId}};
ditto->store().execute(
    "SELECT * FROM cars WHERE _id.locationId = :locationId",
    args
);
```

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

```cpp theme={null}
// ❌ Wrong: Initializing counter with a number (creates REGISTER, not COUNTER)
std::map<std::string, CborValue> doc = {
    {"_id", id},
    {"counter", 0}
};
ditto->store().execute(
    "INSERT INTO items DOCUMENTS (:doc)",
    {{"doc", doc}}
);

// ❌ Wrong: Using SET on counter field
std::map<std::string, CborValue> args = {{"id", id}};
ditto->store().execute(
    "UPDATE items SET counter = 5 WHERE _id = :id",
    args
);

// ✅ Correct: Use PN_INCREMENT BY with APPLY clause (creates counter on first use)
std::map<std::string, CborValue> args = {{"value", 1}, {"id", id}};
ditto->store().execute(
    "UPDATE COLLECTION items (counter COUNTER) APPLY counter PN_INCREMENT BY :value WHERE _id = :id",
    args
);

// ✅ Correct: Decrement by passing negative value
std::map<std::string, CborValue> args = {{"value", -1}, {"id", id}};
ditto->store().execute(
    "UPDATE items APPLY counter PN_INCREMENT BY :value WHERE _id = :id",
    args
);
```

### 4. Memory Management with Observers

Call `item->close()` after extracting data from QueryResultItems. Always close observers when done. Use indexes for improved memory and performance.

```cpp theme={null}
// ❌ Wrong: Storing QueryResultItems without closing
class CarRepository {
private:
    std::vector<std::shared_ptr<DittoQueryResultItem>> items;

    void setupObserver() {
        observer = ditto->store().register_observer(
            "SELECT * FROM cars",
            {},
            [this](DittoQueryResult result) {
                items = result.items();  // Holds native memory
            }
        );
    }
};

// ✅ Correct: Extract data and close items immediately
class CarRepository {
private:
    std::shared_ptr<DittoStoreObserver> observer;
    std::vector<std::string> carIds;

public:
    void startObserving() {
        observer = ditto->store().register_observer(
            "SELECT * FROM cars",
            {},
            [this](DittoQueryResult result) {
                std::vector<std::string> ids;
                for (auto& item : result.items()) {
                    std::string id = item->value()["_id"].as_string();
                    item->close();  // Free native memory
                    ids.push_back(id);
                }
                this->carIds = ids;
                // Update UI with extracted data
            }
        );
    }

    void stopObserving() {
        if (observer) {
            observer->close();  // Always close observer
        }
    }
};
```

### 5. Attachment Handling

Use `ATTACHMENT` annotation in collection definitions. Create attachments with `ditto->store().new_attachment()`.

```cpp theme={null}
// ❌ Wrong: Missing ATTACHMENT annotation
ditto->store().execute(
    "INSERT INTO cars DOCUMENTS (:doc)",
    {{"doc", docWithAttachment}}
);

// ✅ Correct: Use ATTACHMENT annotation in COLLECTION definition
auto attachment = ditto->store().new_attachment(
    filePath,
    metadata
);

std::map<std::string, CborValue> doc = {
    {"_id", id},
    {"image", attachment}
};

ditto->store().execute(
    "INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)",
    {{"doc", doc}}
);
```

***

## Migration Checklist

* [ ] Replace all `.collection().find()` with DQL SELECT queries
* [ ] Replace all `.collection().find_by_id()` with DQL SELECT with `_id` filter
* [ ] Replace all `.collection().upsert()` with DQL INSERT statements
* [ ] Replace all `.update()` lambdas with DQL UPDATE statements
* [ ] Replace all `.remove()` with DQL EVICT statements
* [ ] Update all observers to use `register_observer(query, args, callback)`
* [ ] Update all subscriptions to use `register_subscription(query, args)`
* [ ] Convert counter operations to use `PN_INCREMENT BY` in APPLY clause
* [ ] Remove counter field initialization from INSERT statements
* [ ] Use empty maps `{}` for queries without parameters
* [ ] Update result handling from `DittoDocument` to `CborValue`
* [ ] Verify attachment fetching uses tokens from query results
* [ ] Use proper type conversion methods (`.as_string()`, `.as_int()`, etc.)