Ditto Query Language (DQL) offers a set of data types designed to accommodate any edge sync scenario.
A data type is different than a standard scalar type by declaring merge behaviors, operations, and the spectrum of scalar types accessible for individual fields:
In DQL, youโll use three data types:REGISTER, MAP, and ATTACHMENT type. By
default, fields in a DQL statement are assigned the REGISTER type unless
otherwise specified by way of type definition.Following are the key characteristics:
A Register supports scalar types, including primitive types, such as string
and boolean, as well as a JSON blob, encapsulating multiple fieldโvalue pairs
that function as a single object. The REGISTER can only be set to a specific
field.For example:
The MAP type supports inserting and tombstoning of fields using the functional
operators. Inserting a field is an implicit operation performed by assigning a
value to a field or a child of the field.
field1.sub1.s_sub1 = 1
-- With STRICT_MODE=true, to perform `MAP` operations, use the arrow `->`-- operator followed by parentheses `()`, which contain one or more operations-- on child fields of the `MAP`.field1 -> ( sub1 -> ( s_sub1 = 1 ))
A COUNTER is an enhanced version of the PN_COUNTER that combines positive-negative counter semantics with the ability to explicitly set the counter to a specific value. Like PN_COUNTER, itโs a CRDT type that can be incremented or decremented by any peer, but it also supports a RESTART operation that uses last-write-wins semantics.The counter is an integer value that automatically resolves conflicting increments and decrements from different peers by tracking operations and composing them to provide a final value. When peers perform RESTART operations concurrently, the last write wins.Counters are useful for tracking counts that multiple peers might update simultaneously, such as:
Like/vote counts with the ability to reset
Inventory counts that need periodic recalibration
Session counts that can be initialized to specific values
Metrics that require both incremental updates and explicit resets
Counter operations (INCREMENT BY, RESTART WITH, RESTART) are specified using the APPLY clause, not the SET clause. The APPLY clause is specifically designed for CRDT operations on special field types like counters.
The requirement to declare counter types in queries depends on your DQL_STRICT_MODE setting:When DQL_STRICT_MODE=true (default):
You must declare counter types in COLLECTION definitions for INSERT, UPDATE, and SELECT statements
Counter fields are only visible in queries when the type declaration is included
When DQL_STRICT_MODE=false:
Counter type declarations are not required for SELECT and UPDATE statements using APPLY
Counter type declarations are still required for INSERT statements
Counter fields are automatically visible in queries without type declarations
-- Must specify COUNTER type in all statementsINSERT INTO COLLECTION products (stock_count COUNTER)VALUES ({ '_id': '123', 'stock_count': 100 })UPDATE COLLECTION products (stock_count COUNTER)APPLY stock_count INCREMENT BY 5WHERE _id = '123'SELECT * FROM COLLECTION products (stock_count COUNTER)WHERE _id = '123'
-- Must specify COUNTER type for INSERTINSERT INTO COLLECTION products (stock_count COUNTER)VALUES ({ '_id': '123', 'stock_count': 100 })-- No type declaration needed for UPDATE with APPLYUPDATE productsAPPLY stock_count INCREMENT BY 5WHERE _id = '123'-- No type declaration needed for SELECTSELECT * FROM products WHERE _id = '123'
Using INSERT with type declaration: You can declare a field as a COUNTER in the COLLECTION definition when inserting a document. This allows you to initialize the counter with a specific value (type declaration is required for INSERT regardless of strict mode):
Using APPLY operations: Counter fields are automatically created when you first use INCREMENT or RESTART operations on them. This is useful when you want to create documents without counter fields initially:
Do not initialize counter fields by inserting an integer value without declaring the COUNTER type. This creates a register field, not a counter.
// Create document without counter fieldlet product = [ "_id": "123", "name": "Widget"]await ditto.store.execute( query: """ INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product) """, arguments: [ "product": product ])// Then increment the counter (creates it if doesn't exist)await ditto.store.execute( query: """ UPDATE COLLECTION products (stock_count COUNTER) APPLY stock_count INCREMENT BY 5 WHERE _id = '123' """)
// Create document without counter fieldvar product = mapOf( "_id" to "123", "name" to "Widget")ditto.store.execute(""" INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product) """, mapOf("product" to product))// Then increment the counter (creates it if doesn't exist)ditto.store.execute(""" UPDATE COLLECTION products (stock_count COUNTER) APPLY stock_count INCREMENT BY 5 WHERE _id = '123' """)
// Create document without counter fieldconst product = { _id: "123", name: "Widget"};await ditto.store.execute(` INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)`, { product });// Then increment the counter (creates it if doesn't exist)await ditto.store.execute(` UPDATE COLLECTION products (stock_count COUNTER) APPLY stock_count INCREMENT BY 5 WHERE _id = '123'`);
// Create document without counter fieldMap<String, Object> product = new HashMap<>();product.put("_id", "123");product.put("name", "Widget");DittoQueryResult result = (DittoQueryResult) ditto.store.execute( "INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)", Collections.singletonMap("product", product));// Then increment the counter (creates it if doesn't exist)ditto.store.execute( "UPDATE COLLECTION products (stock_count COUNTER) " + "APPLY stock_count INCREMENT BY 5 WHERE _id = '123'");
// Create document without counter fieldvar args = new Dictionary<string, object>();args.Add("product", new { _id = "123", name = "Widget" });await ditto.Store.ExecuteAsync( "INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)", args);// Then increment the counter (creates it if doesn't exist)await ditto.Store.ExecuteAsync( "UPDATE COLLECTION products (stock_count COUNTER) " + "APPLY stock_count INCREMENT BY 5 WHERE _id = '123'");
// Create document without counter fieldstd::map<std::string, std::any> product;product["_id"] = "123";product["name"] = "Widget";std::map<std::string, std::any> args;args["product"] = product;auto result = ditto.get_store().execute( "INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)", args).get();// Then increment the counter (creates it if doesn't exist)ditto.get_store().execute( "UPDATE COLLECTION products (stock_count COUNTER) " "APPLY stock_count INCREMENT BY 5 WHERE _id = '123'").get();
// Create document without counter fieldlet query_result = ditto .store() .execute_v2(( "INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)", serde_json::json!({ "product": { "_id": "123", "name": "Widget" } }), )).await?;// Then increment the counter (creates it if doesn't exist)ditto.store() .execute_v2(( "UPDATE COLLECTION products (stock_count COUNTER) \ APPLY stock_count INCREMENT BY 5 WHERE _id = '123'", serde_json::json!({}), )).await?;
// Create document without counter fieldconst product = { "_id": "123", "name": "Widget"};await ditto.execute(""" INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product)""", {"product": product},);// Then increment the counter (creates it if doesn't exist)await ditto.execute(""" UPDATE COLLECTION products (stock_count COUNTER) APPLY stock_count INCREMENT BY 5 WHERE _id = '123'""",);
To decrement a counter, use a negative value with INCREMENT:
The RESTART operation allows you to explicitly set a counter to a specific value or reset it to zero. This uses last-write-wins semantics, so if multiple peers restart a counter concurrently, the last write will win.Set counter to a specific value:
The key differences between COUNTER and PN_COUNTER:
Feature
COUNTER
PN_COUNTER
Increment/Decrement
โ
โ
Set to specific value
โ (RESTART WITH)
โ
Reset to zero
โ (RESTART)
โ
Conflict resolution
PN + LWW on RESTART
PN only
Available since
4.13+
4.11+
Use COUNTER when you need the ability to explicitly set or reset counter values. Use PN_COUNTER only for backward compatibility with older Ditto versions.
PN Counters are available in 4.11 and later. Read more
Users should use Counter which also provide the ability to set the counter value.
A counter is a special type of field that can be incremented or decremented. A
counter is a double-precision floating-point number. In 4.11 and above, ditto
offers PN_COUNTER, or positive-negative counter, which is a CRDT type that can
be incremented or decremented by any peer. Counters automatically resolve
conflicting increments and decrements from different peers by tracking the
operations and composing them to provide a final value.Counters are useful for tracking counts that multiple peers might update simultaneously, such as:
Like/vote counts
Number of views or interactions
Do not initialize counter fields by inserting a double value (e.g., 0.0). This creates a register field, not a counter. Counter fields are automatically created when you first use PN_INCREMENT on them.
To use a counter, apply the PN_INCREMENT operation directly on the field. If the field doesnโt exist, it will be created as a counter with the increment value. If you need to create a document first, insert it without the counter field or with other fields only:
// Create document without counter fieldlet product = [ "_id": "123", "updatedBy": "abc123"]await ditto.store.execute( query: """ INSERT INTO COLLECTION products INITIAL DOCUMENTS (:product) """, arguments: [ "product": product ])// Then increment the counter (creates it if doesn't exist)await ditto.store.execute( query: """ UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123' """)
// Create document without counter fieldvar product = mapOf( "_id" to "123", "updatedBy" to "abc123")ditto.store.execute(""" INSERT INTO products INITIAL DOCUMENTS (:product) """, mapOf("product", product))// Then increment the counter (creates it if doesn't exist)ditto.store.execute(""" UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123' """)
// Create document without counter fieldconst product = { _id: "123", updatedBy: "abc123"};await ditto.store.execute(` INSERT INTO products INITIAL DOCUMENTS (:product)`, { product });// Then increment the counter (creates it if doesn't exist)await ditto.store.execute(` UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'`);
// Create document without counter fieldMap<String, Object> product = new HashMap<>();product.put("_id", "123");product.put("updatedBy", "abc123");DittoQueryResult result = (DittoQueryResult) ditto.store.execute( "INSERT INTO products INITIAL DOCUMENTS (:product)", Collections.singletonMap("product", product));// Then increment the counter (creates it if doesn't exist)ditto.store.execute( "UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'");
// Create document without counter fieldvar args = new Dictionary<string, object>();args.Add("product", new { _id = "123", updatedBy = "abc123" });await ditto.Store.ExecuteAsync( "INSERT INTO products INITIAL DOCUMENTS (:product)", args);// Then increment the counter (creates it if doesn't exist)await ditto.Store.ExecuteAsync( "UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'");
// Create document without counter fieldstd::map<std::string, std::any> product;product["_id"] = "123";product["updatedBy"] = "abc123";std::map<std::string, std::any> args;args["product"] = product;auto result = ditto.get_store().execute( "INSERT INTO products INITIAL DOCUMENTS (:product)", args).get();// Then increment the counter (creates it if doesn't exist)ditto.get_store().execute( "UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'").get();
// Create document without counter fieldlet query_result = ditto .store() .execute_v2(( "INSERT INTO products INITIAL DOCUMENTS (:product)", serde_json::json!({ "product": { "_id": "123", "updatedBy": "abc123" } }), )).await?;// Then increment the counter (creates it if doesn't exist)ditto.store() .execute_v2(( "UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'", serde_json::json!({}), )).await?;
// Create document without counter fieldconst product = { "_id": "123", "updatedBy": "abc123"};await ditto.execute(""" INSERT INTO products INITIAL DOCUMENTS (:product)""", {"product": product},);// Then increment the counter (creates it if doesn't exist)await ditto.execute(""" UPDATE products APPLY in_stock PN_INCREMENT BY 5.0 WHERE _id = '123'""",);
To update a counter, use the APPLY keyword followed by the field name and then
the PN_INCREMENT keyword followed by the value. To decrement a counter, use a
negative value.
With strict mode enabled, all fields
are treated as a register by default. When enabled, every field in a document
must match the collection definition exactly โ including its CRDT type (e.g.,
map, register, counter).Disabling strict mode enables new functionality: when set to
false, collection definitions are no longer required. SELECT queries will return
and display all fields by default.
โ
A REGISTER is a data type in Ditto that stores a single scalar value and
uses last-write-wins merge strategy for handling conflicts.Key characteristics of REGISTER:
Stores primitive types (string, boolean) or JSON objects
Last-write-wins conflict resolution ensures consistent values across peers
With DQL_STRICT_MODE=false, if you want to force a JSON Object to use a
REGISTER data type instead of a MAP in DQL, it must be specified explicitly.
If you need to remove a register map, you need to use the UNSET statement at the top level. Because a register map is treated the same as a scalar value (such as string, int), you operate on the entire object as a whole, similar to a JSON blob.
In 4.11+ and DQL_STRICT_MODE=false, collection definitions for non-registers are no longer required.Read more
With DQL_STRICT_MODE=true, REGISTER is the default type in DQL. That means
that you need to specify the type definition when overriding with type MAP, PN_COUNTER, or
ATTACHMENT within your query.
Every document in Ditto must have a unique _id field that serves as the documentโs identifier. The following constraints apply:
Required: Every document must have an _id field
Type: The _id can be a string, number, or other scalar type
Uniqueness: Each _id must be unique within its collection
Null Restriction: null cannot be used as a document _id (enforced in SDK 5.0+)
Example - Valid Document IDs:
DQL
-- String IDINSERT INTO cars VALUES ({"_id": "car-123", "color": "blue"})-- Numeric IDINSERT INTO cars VALUES ({"_id": 42, "color": "red"})
Example - Invalid Document ID:
DQL
-- This will fail in SDK 5.0+INSERT INTO cars VALUES ({"_id": null, "color": "green"})
Starting in SDK version 5.0, attempting to use null as a document _id will result in an error. Earlier versions may have allowed this, but it should be avoided for forward compatibility.
DQL type definitions describe the schema of the documents within a specific
collection โ defining the field types within the collection and specifying the
assigned data types for each field.To explicitly declare the type definition as non-REGISTER type, add a prefix
of COLLECTION and the suffix of (field1 data_type, field2 data_type, ...) to
list the fields within the collection and their associated data types:
INSERT INTO COLLECTION your_collection_name (field1 MAP, field2 ATTACHMENT)DOCUMENTS (...)
MAP Type SpecificsThe MAP (Add-Wins Map) contains fields with their own data type. Data types for these fields are defined using parentheses following the MAP keyword. For example, MAP(sub1 data_type, sub2 data_type, ...):