Legacy-to-DQL Reference

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'
  `)

On this page

Creating Documents
Inserting a Single Document
Inserting Multiple Documents
Inserting Initial/Default Documents
Upserting Documents
Reading Data
Querying Data
Querying Data by ID
Querying Data with Limits
Querying for Null Values
Querying with Arguments
Observing Data Changes
Updating Documents
Updating a Single Document
Updating Multiple Documents
Updating Multiple Fields
Updating Nested Fields
Counters
Registers
Deletion
Deleting Fields
Evicting Documents
Deleting Documents
Syncing Data From Other Peers

Creating Documents

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

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'
  `)

On this page

Creating Documents
Inserting a Single Document
Inserting Multiple Documents
Inserting Initial/Default Documents
Upserting Documents
Reading Data
Querying Data
Querying Data by ID
Querying Data with Limits
Querying for Null Values
Querying with Arguments
Observing Data Changes
Updating Documents
Updating a Single Document
Updating Multiple Documents
Updating Multiple Fields
Updating Nested Fields
Counters
Registers
Deletion
Deleting Fields
Evicting Documents
Deleting Documents
Syncing Data From Other Peers

​Creating Documents

​Inserting a Single Document

​Inserting Multiple Documents

​Inserting Initial/Default Documents

​Upserting Documents

​Reading Data

​Querying Data

​Querying Data by ID

​Querying Data with Limits

​Querying for Null Values

​Querying with Arguments

​Observing Data Changes

​Updating Documents

​Updating a Single Document

​Updating Multiple Documents

​Updating Multiple Fields

​Updating Nested Fields

​Counters

​Registers

​Deletion

​Deleting Fields

​Evicting Documents

​Deleting Documents

​Syncing Data From Other Peers

Ditto Operator

Ditto Operator API

Connectors

​Creating Documents

​Inserting a Single Document

​Inserting Multiple Documents

​Inserting Initial/Default Documents

​Upserting Documents

​Reading Data

​Querying Data

​Querying Data by ID

​Querying Data with Limits

​Querying for Null Values

​Querying with Arguments

​Observing Data Changes

​Updating Documents

​Updating a Single Document

​Updating Multiple Documents

​Updating Multiple Fields

​Updating Nested Fields

​Counters

​Registers

​Deletion

​Deleting Fields

​Evicting Documents

​Deleting Documents

​Syncing Data From Other Peers

Creating Documents

Inserting a Single Document

Inserting Multiple Documents

Inserting Initial/Default Documents

Upserting Documents

Reading Data

Querying Data

Querying Data by ID

Querying Data with Limits

Querying for Null Values

Querying with Arguments

Observing Data Changes

Updating Documents

Updating a Single Document

Updating Multiple Documents

Updating Multiple Fields

Updating Nested Fields

Counters

Registers

Deletion

Deleting Fields

Evicting Documents

Deleting Documents

Syncing Data From Other Peers

Creating Documents

Inserting a Single Document

Inserting Multiple Documents

Inserting Initial/Default Documents

Upserting Documents

Reading Data

Querying Data

Querying Data by ID

Querying Data with Limits

Querying for Null Values

Querying with Arguments

Observing Data Changes

Updating Documents

Updating a Single Document

Updating Multiple Documents

Updating Multiple Fields

Updating Nested Fields

Counters

Registers

Deletion

Deleting Fields

Evicting Documents

Deleting Documents

Syncing Data From Other Peers