DQL — Ditto Query Language

DQL is Ditto's SQL-like language for querying and mutating documents in a CRDT-aware, peer-to-peer database. It looks like SQL, but the semantics are different in three ways that matter every day:

1. SQL compatibility is version-gated. Check the target SDK before using a SQL construct; JOINs require 5.1+.
2. NULL and MISSING are distinct. NULL = field exists, value is null. MISSING = field is absent. They behave differently in every operator.
3. Every field is a CRDT. The CRDT type (REGISTER, MAP, COUNTER, PN_COUNTER, ATTACHMENT) controls how concurrent edits merge.

Scope: this skill covers DQL syntax and semantics. For schema design — CRDT type selection, embed-vs-split decisions, document size budgets, reviewing raw JSON models — use the data-modeling skill.

First: which SDK version is the user on?

DQL features land version by version. Before answering a non-trivial DQL question, confirm the SDK version. If the user has not said which Ditto SDK they're on, ask:

"Which Ditto SDK version is this for? I support guidance for 4.14.x, 5.0.x, 5.1.x, and 5.2.0."

If they don't know, ask them to check the SDK package version in their project (Ditto.swift / build.gradle / package.json) — or to run SHOW dql_strict_mode to identify the v4 strict-default vs v5 lenient-default behavior. Then filter the rest of the conversation to features available in that version (see references/versions.md).

Strict mode: this skill assumes dql_strict_mode = false (lenient) on every peer — the 5.x default and the migration target for 4.14.x meshes. Examples therefore use no MAP/REGISTER keywords on reads or writes (objects infer MAP; reads see every type). If a 4.x user reports fields missing from query results or whole-object overwrites on merge, suspect an un-migrated strict peer first — see references/types-mutators.md §7 and best-practices.md "Strict Mode".

For “latest” or a new POC, resolve the latest stable SDK through the language’s package manager. 5.2.0 is the latest version this skill knows about, not a permanent dependency recommendation. Check release notes and validate queries if the resolved release is newer.

Where to look

Read only the reference section matching the task. For a long file, read its contents first, then the selected section; do not load every reference or the README. In large matrices or catalogs, find the relevant feature row and its version notes. Open references/schema.md only when adapting the movies/reviews examples and their field names or types are unclear; it is not needed for a query against the user's own schema.

Five things that bite people

1. Use AND / OR / NOT keywords, not && / || / !.
2. Single and double quotes both delimit strings; backticks delimit identifiers. WHERE title = "x" and WHERE title = 'x' are equivalent. Prefer single quotes for consistency with JSON-style examples.
3. = NULL is always NULL, not TRUE. Use IS NULL / IS NOT NULL / IS MISSING.
4. INSERT requires an explicit ON ID CONFLICT for upserts. Default is FAIL.
5. EVICT is local-only and reversible (other peers will re-sync). DELETE is mesh-wide and permanent (subject to TTL) — it writes a replicating tombstone, and is the mesh-wide delete available from an Edge SDK. Don't mix them up.

Compatibility

This skill covers Ditto SDK 4.14.x, 5.0.x, 5.1.x, and 5.2.0. Every feature in the reference files is tagged with its minimum SDK version. See references/versions.md for the full matrix.

Rejected on every tested SDK version (verified against 4.14.6 / 5.0.3 / 5.1.0): XOR, TOMBSTONE (Big-Peer-only), EXCLUDE MISSING index keys, the parenthesised USE IDS ('a', 'b') tuple, inline PN_COUNTER declarations on INSERT, and structured PROFILE output. Note that multi-id USE IDS does work everywhere in its comma form (USE IDS 'a', 'b').

On 4.14.x under its default strict mode, APPLY ... INCREMENT BY on a not-yet-existing field silently does nothing.

DO MERGE is supported on every tested SDK version as an alias for DO UPDATE. See references/versions.md for the full matrix.

Decision tree for query problems

Need to read documents?
├── By known id(s)?         → USE IDS on SELECT (references/select.md)
├── With aggregation?       → GROUP BY + aggregates (references/select.md)
├── Across array elements?  → ANY ... IN ... SATISFIES (references/expressions.md, v5.0+)
└── Otherwise               → WHERE clause (references/select.md, references/operators.md)

Need to write documents?
├── Adding new?       → INSERT (references/statements.md)
├── Modifying?        → UPDATE with SET / UNSET / APPLY (references/statements.md)
└── Removing?         → EVICT (local) or DELETE (mesh) (references/statements.md)

Need to make it fast?
├── See the plan?     → EXPLAIN (references/indexing.md)
├── See timing?       → #profile directive (references/indexing.md, 5.0+)
└── Add an index?     → CREATE INDEX (references/indexing.md)