​
Creating Documents

In DQL syntax, enclose string literals within single quotes (''); for example 'blue' .

Use single quotes in DQL queries to ensure accuracy. Incorrectly using double quotes (""); for example "blue", may prevent document matching. Currently, Ditto does not throw a warning for this issue.

​
Inserting a Single Document

const newCar = {
  _id: '123',
  color: 'blue',
}

await ditto.store.execute(`
  INSERT INTO cars
  VALUES (:newCar)`,
  { newCar })

​
Inserting Multiple Documents

const car1 = {
  _id: '123',
  color: 'blue',
}
const car2 = {
  _id: '456',
  color: 'red',
}

await ditto.store.execute(`
  INSERT INTO cars
  VALUES (:car1),(:car2)`,
  { car1, car2 })

​
Inserting Initial/Default Documents

This is the ability to insert documents at time 0. For more information see INSERT with INITIAL DOCUMENTS.

const newCar = {
  _id: '123',
  color: 'blue',
}

await ditto.store.execute(`
  INSERT INTO cars
  INITIAL VALUES (:newCar)`,
  { newCar })

​
Upserting Documents

Legacy supports upserting by default where DQL requires the policy to be explicitly set.

const newCar = {
  _id: '123',
  color: 'blue',
}

await ditto.store.execute(`
  INSERT INTO cars
  DOCUMENTS (:newCar)
  ON ID CONFLICT DO MERGE`,
  { newCar })

​
Reading Data

​
Querying Data

await ditto.store.execute(`
  SELECT *
  FROM cars
  WHERE color = 'blue'
  `)

​
Querying Data by ID

await ditto.store.execute(`
  SELECT *
  FROM cars
  WHERE _id = '123'
  `)

​
Querying Data with Limits

await ditto.store.execute(`
  SELECT *
  FROM cars
  WHERE color = 'blue'
  LIMIT 10
  `)

​
Querying for Null Values

await ditto.store.execute(`
  SELECT *
  FROM cars
  WHERE color IS NULL
  `)

​
Querying with Arguments

await ditto.store.execute(`
  SELECT *
  FROM cars
  WHERE color = :color`,
  { color: 'blue' })

​
Observing Data Changes

ditto.store.registerObserver(`
  SELECT *
  FROM cars
  WHERE color = 'blue'`,
  (result) => {
    // handle change
  })

​
Updating Documents

​
Updating a Single Document

await ditto.store.execute(`
  UPDATE cars
  SET color = 'red'
  WHERE _id = '123'
  `)

​
Updating Multiple Documents

await ditto.store.execute(`
  UPDATE cars
  SET color = 'red'
  WHERE color = 'blue'
  `)

​
Updating Multiple Fields

await ditto.store.execute(`
  UPDATE cars
  SET
    color = 'red',
    mileage = 3001
   WHERE _id = '123'
  `)

​
Updating Nested Fields

await ditto.store.execute(`
  UPDATE cars
  SET
    metadata.color = 'red',
    metadata.mileage = 3001
  WHERE _id = '123'
`)

​
Counters

Available in 4.11 and later with DQL_STRICT_MODE=false.

Counters are now supported in DQL. To use them you can use the APPLY keyword followed by the field name and then the PN_INCREMENT keyword followed by the value you want to increment the value by.

To decrement a counter, use a negative value.

// 4.11+
await ditto.store.execute(`
  UPDATE products
  APPLY in_stock PN_INCREMENT BY 1.0
  SET updatedBy = 'my-author-id'
  WHERE _id = '1234'
`)

For more information on using Counters see here.

​
Registers

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 strict mode disabled, if you want a REGISTER Map data type in DQL, it must be specified explicitly
await ditto.store.execute(`
  UPDATE menus (updatedAt REGISTER)  
  SET updatedAt = {
    "datetime": "2025-02-27T4:47:30Z", 
    "authorId": "67c0faa40054d13a000c614a"
  }
  WHERE _id = 'my-id' 
`)

For more information on using types and definitions, see Registers.

​
Deletion

​
Deleting Fields

-- 4.11+
UPDATE orders UNSET items.abc WHERE _id = 'my-id'

​
Evicting Documents

EVICT FROM cars WHERE color = 'blue'

​
Deleting Documents

There are several ways to delete data in Ditto, and you can find more detail about them here.

-- 4.10+
DELETE FROM cars WHERE color == "blue"

​
Syncing Data From Other Peers

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