Intended for those developers already using Ditto with their app to sync, this guide provides an overview of how to adopt DQL and the corresponding usage APIs.

​
Introduction

There are two primary ways to use DQL within the Ditto SDK:

DQL for Local CRUD Operations

DQL for Data Sync Subscriptions

​
DQL for Local CRUD Operations

Local CRUD operations using DQL include the ditto.store.execute(...) and ditto.store.observeLocal(...) APIs. These operations are only performed against the local data store and can be used alongside existing legacy builder queries.
  • Local CRUD operations are backward-compatible
  • Local CRUD operations can be used alongside legacy builder queries
  • Local CRUD operations include all operations except those using .subscribe()
    • For example: find(...) , findById(...) , upsert(...) , and so on.

​
Update to 4.11

Upgrade to v4.11 and disable DQL_STRICT_MODE on all devices 
ditto.store.exec("ALTER SYSTEM SET DQL_STRICT_MODE = false")
This enables backward compatibility with Legacy builder to ensure that Ditto returns the most up-to-date version of your document fields.

​
Read Operations

DQL for CRUD operations can be adopted quickly or slowly depending on your business needs. For most customers, it will be best to take a phased approach to adopting DQL where there are both legacy queries and DQL queries used together. This is because it’s often not practical to change all operations in a single pass, either due to complexity or risk of introducing regressions.
  • Replace observe: Use DQL’s registerObserver statements instead of the legacy observe method.
  • Replace exec queries: Update your legacy execution queries to their DQL equivalents.
  • Test Compatibility: Verify that your application behaves correctly between mixed versions of your app.
await ditto.store.execute("
  SELECT * FROM orders
  WHERE createdAt < '2025-05-14T04:59:59.999Z'
  AND locationId = '1234'
")

​
Upsert Operations

INSERT ... DO UPDATE statements behave the same as the legacy query builder upsert. 
let doc = ["_id": "my-id", "status": [statusCode: "delivered"]]
await ditto.store.execute(
  query: "INSERT INTO orders DOCUMENTS (:doc)
    ON ID CONFLICT DO UPDATE"
  arguments: ["doc": doc]
)

​
Update Operations

The new update operations mimic the legacy behavior while allowing for more flexible document updates. Consider these examples: Updating a single document 
ditto.store.exec("UPDATE orders SET items.abc123 = true WHERE _id = 'my-id'")
Updating multiple documents in the same collection 
ditto.store.exec("UPDATE orders SET items.abc123 = true WHERE locationId = '1234' ")
Updating multiple documents in multiple collections in a single transaction Use the transaction() method if you need to execute multiple database operations in one atomic transaction. Read more at Transactions
DQL
let result: DittoQueryResult? = try await ditto.store.transaction { transaction in
    let queryResult1 = try await transaction.execute(/* query 1 */)
    let queryResult2 = try await transaction.execute(/* query 2 */)
    // ...
    let queryResultN = try await transaction.execute(/* query N */)
    return queryResultN
}
Handling placeholders in field identifiers It isn’t possible to use a placeholder in a field identifier in an UPDATE statement in DQL. Instead, use INSERT ... DO UPDATE as follows: 
let doc = ["_id": "my-id", "status": [statusCode: "delivered"]]
ditto.store.execute(
  query: "INSERT INTO orders DOCUMENTS (:doc) 
  ON ID CONFLICT DO UPDATE", 
  args: [doc: doc]
)

​
Serialization

When reading and writing documents from the database, the serialization format (CBOR) is the same as the legacy query builder, so on-disk format is comatible between both query engines. However, when using DQL, you must implement your own serialization for the INSERT and SELECT statements. Ditto no longer supports built-in variations such as Codable in Swift. For example, the following code shows how to use Swift’s JSONDecoder to serialize a document before inserting it into the database:
Swift
let doc = ["_id": UUID().uuidString, "inStock": 0.0, "name": "fries"]
let jsonData = try JSONEncoder().encode(doc)
let jsonString = String(data: jsonData, encoding: .utf8)
await ditto.store.execute(
    query: "INSERT INTO items DOCUMENTS (:jsonString) 
    ON ID CONFLICT DO UPDATE",
    arguments: ["jsonString": jsonString]
)
This example shows how to use JSONDecoder to deserialize a document when reading a QueryResultItem from the database:
Swift
extension ItemDittoModel: Codable {
    /// Returns optional instance decoded from QueryResultItem.jsonString()
    init?(_ json: String) {
        do {
            self = try JSONDecoder().decode(Self.self, from: Data(json.utf8))
        } catch {
            print("ERROR:", error.localizedDescription)
            return nil
        }
    }
    init(from decoder: Decoder) throws {
        let container = try decoder.container(keyedBy: CodingKeys.self)
        _id = try container.decode(String.self, forKey: ._id)
        inStock = try container.decodeIfPresent(Double.self, forKey: .inStock) ?? 0.0
        name = try container.decodeIfPresent(String.self, forKey: .name) ?? ""
    }
    private enum CodingKeys: String, CodingKey {
        case _id
        case inStock
        case name
    }
}

let queryResult = try await ditto.store.execute("SELECT * FROM items")
for item in queryResult.items {
    if let jsonString = item.jsonString() {
        let itemModel = ItemDittoModel(jsonString)
        print(itemModel)
    }
}

​
DQL for Data Sync Subscriptions

DQL Subscriptions are forward-compatible starting at v4.5.
This means that devices using DQL and the ditto.sync.registerSubscription(...) API on v4.5 or later will only be able to sync with other devices on v4.5 or later.
1

Ensure all devices in your production environment are using v4.5 and above

This is the minimum version required to use DQL for data sync subscriptions.
2

Success!

Now you are safe to start using DQL via the ditto.sync.registerSubscription(...)
To introduce DQL for data subscriptions in a phased approach, we recommend you consider:
  • Using DQL for new subscriptions
  • Updating existing subscriptions one at a time

​
Reference Example

Legacy

JS
ditto.store
.collection('cars')
.find('color == "blue"')
.subscribe();

DQL

JS
ditto.sync.registerSubscription(`
  SELECT *
  FROM cars
  WHERE color = 'blue'
  `);

​
Key Notes

  • Subscriptions behave the same regardless of whether strict mode is enabled or disabled.
  • v4.0-v4.4 clients will not fail or crash when they receive a DQL request from a v4.5 client
  • v4.5 clients will continue to send data to v4.0-4.4 clients
  • Legacy query subscriptions will continue to work across all v4 versions including v4.5 e.g. ditto.store.collection("cars").find("cars == 'blue'").subscribe()
  • Legacy query subscriptions will work alongside DQL subscriptions

​
DQL and Ditto Server

DQL is rolled out across all Ditto Server deployments, and v4.5 and above with DQL will work without any additional changes.