C++ Legacy→DQL Migration Guide

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

Copy AI Migration Prompt (Click to Expand)

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.

Document Query Syntax

Query All Documents

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

ditto->store()
    .collection("cars")
    .find_all()
    .exec()
    .then([](auto docs) {
        // Use docs
    });

Query Document by ID

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

ditto->store()
    .collection("cars")
    .find_by_id("abc123")
    .exec()
    .then([](auto doc) {
        // Use doc
    });

Query with Predicate

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

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

Query with Parameterized Arguments

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

std::string color = "red";
ditto->store()
    .collection("cars")
    .find("color == '" + color + "'")
    .exec()
    .then([](auto docs) {
        // Use docs
    });

Insert, Update, Delete, Eviction

Insert Document

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

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

Update Document

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

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

Delete Document

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

ditto->store()
    .collection("cars")
    .find_by_id("abc123")
    .remove()
    .then([](auto success) {
        std::cout << "Delete successful" << std::endl;
    });

Evict All Documents Matching Condition

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

ditto->store()
    .collection("cars")
    .find("miles > 100000")
    .remove()
    .then([](auto success) {
        std::cout << "Eviction successful" << std::endl;
    });

Query Response Handling

Working with Query Results

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

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

Observer Migration

Observing Query Results

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

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

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

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.

Best Practice: Create Indexes for Observer Queries

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

Sync Subscriptions Migration

Creating Sync Subscriptions

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

auto subscription = ditto->store()
    .collection("cars")
    .find("color == 'red'")
    .subscribe();

// Later: clean up
subscription.close();

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

Critical Change: Counters are now modified using the PN_INCREMENT BY operation in the APPLY clause, not by setting values directly.

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.

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

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

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

// 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;
    }
});

// ❌ 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"}}
);

Attachment Operations

Fetching Attachments

// 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
                    }
                }
            );
        }
    }
});

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

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:

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

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

For comprehensive information on indexing strategies, syntax, and best practices, see the DQL Indexing documentation.

Common Pitfalls to Avoid

1. DQL Syntax Errors

Use :paramName for parameters, not string literals or concatenation.

// ❌ 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>.

// ❌ 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.

// ❌ 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.

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

// ❌ 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}}
);

Overview & Setup

SDKs

Accessing Data

Syncing Data

Authentication and Authorization

Managing the Mesh

Deploying your App

C++ Legacy→DQL Migration Guide

Overview

AI Agent Prompt

Document Query Syntax

Query All Documents

Query Document by ID

Query with Predicate

Query with Parameterized Arguments

Insert, Update, Delete, Eviction

Insert Document

Update Document

Delete Document

Evict All Documents Matching Condition

Query Response Handling

Working with Query Results

Observer Migration

Observing Query Results

Sync Subscriptions Migration

Creating Sync Subscriptions

Counter Type Migration

Updating Counter Values

Attachment Operations

Fetching Attachments

Performance Enhancements

Indexes for Improved Query Performance

Common Pitfalls to Avoid

1. DQL Syntax Errors

2. Missing Parameter Binding

3. Counter Type Errors

4. Memory Management with Observers

5. Attachment Handling

Migration Checklist

​Overview

​AI Agent Prompt

​Document Query Syntax

​Query All Documents

​Query Document by ID

​Query with Predicate

​Query with Parameterized Arguments

​Insert, Update, Delete, Eviction

​Insert Document

​Update Document

​Delete Document

​Evict All Documents Matching Condition

​Query Response Handling

​Working with Query Results

​Observer Migration

​Observing Query Results

​Sync Subscriptions Migration

​Creating Sync Subscriptions

​Counter Type Migration

​Updating Counter Values

​Attachment Operations

​Fetching Attachments

​Performance Enhancements

​Indexes for Improved Query Performance

​Common Pitfalls to Avoid

​1. DQL Syntax Errors

​2. Missing Parameter Binding

​3. Counter Type Errors

​4. Memory Management with Observers

​5. Attachment Handling

​Migration Checklist

Overview

AI Agent Prompt

Document Query Syntax

Query All Documents

Query Document by ID

Query with Predicate

Query with Parameterized Arguments

Insert, Update, Delete, Eviction

Insert Document

Update Document

Delete Document

Evict All Documents Matching Condition

Query Response Handling

Working with Query Results

Observer Migration

Observing Query Results

Sync Subscriptions Migration

Creating Sync Subscriptions

Counter Type Migration

Updating Counter Values

Attachment Operations

Fetching Attachments

Performance Enhancements

Indexes for Improved Query Performance

Common Pitfalls to Avoid

1. DQL Syntax Errors

2. Missing Parameter Binding

3. Counter Type Errors

4. Memory Management with Observers

5. Attachment Handling

Migration Checklist