Legacy-to-DQL Reference

Collection Operations

Deprecated: store.collection("name")
DQL: Use collection name directly in queries

Creating Documents

In DQL syntax, enclose string literals within single quotes ('') or from version 5.0, double quotes(""); for example 'blue' or "blue".

Prior to version 5.0 only use single quotes in DQL queries. Incorrectly using double quotes (""), for example field = "blue", may raise an error or produce unexpected results (see: Quoted Identifiers).

Inserting a Single Document

Deprecated: collection.insert({...})
DQL: store.execute("INSERT INTO COLLECTION name DOCUMENTS (:doc)", {{"doc", json_doc}})

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

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

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

await ditto.store
.collection('cars')
.upsert(newCar)

Inserting Multiple Documents

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

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

// Not Supported

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 DOCUMENTS (:newCar)`,
  { newCar })

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

await ditto.store
.collection('cars')
.upsert(newCar, { writeStrategy: "insertDefaultIfAbsent" })

Upserting Documents

Deprecated: collection.upsert({...})
DQL: store.execute("INSERT INTO COLLECTION name DOCUMENTS (:doc) ON ID CONFLICT DO UPDATE", {{"doc", json_doc}})
Note: Use ON ID CONFLICT DO UPDATE to get true upsert behavior (insert or update)
Note: If just inserting new documents without an _id, omit the ON ID CONFLICT clause

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

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

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

await ditto.store
.collection('cars')
.upsert(newCar)

Reading Data

Querying Data

Deprecated: collection.find("field == $args.value").with_args({{"value", x}}).exec()
DQL: store.execute("SELECT * FROM COLLECTION name WHERE field = :value", {{"value", x}})

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

await ditto.store
.collection('cars')
.find('color == "blue"')

Querying Data by ID

Deprecated: collection.find_by_id(doc_id).exec()
DQL (4.11+): store.execute("SELECT * FROM COLLECTION name USE IDS :id", {{"id", doc_id}})
DQL (all versions): store.execute("SELECT * FROM COLLECTION name WHERE _id = :id", {{"id", doc_id}})

await ditto.store.execute(`
  SELECT *
  FROM cars
  USE IDS "123"
  `)

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

await ditto.store
.collection('cars')
.findById('123')

Querying Data with Limits

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

await ditto.store
.collection('cars')
.find('color == "blue"')
.limit(10)

Querying for Null Values

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

await ditto.store
.collection('cars')
.find('color == null')

Querying with Arguments

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

await ditto.store
.collection('cars')
.find(
  'color == $args.color',
  { color: 'blue' })

Observing Data Changes

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

const observer = ditto.store
.collection('cars')
.find('color = "blue"')
.observeLocal((result, event) => {
    // do something
  })

Updating Documents

Updating a Single Document

Deprecated: collection.find_by_id(id).update({ ... })
DQL: store.execute("UPDATE COLLECTION name SET field = :value WHERE _id = :id", params)

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

await ditto.store
.collection('cars')
.findByID('123')
.update((mutableDoc) => {
  mutableDoc.at('color').set('red')
})

Updating Multiple Documents

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

await ditto.store
  .collection('cars')
  .find('color == "blue"')
  .update((documents) => {
    for (const document of documents) {
      document.color = 'red'
    }
  })

Updating Multiple Fields

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

await ditto.store
.collection('cars')
.findByID('123')
.update((mutableDoc) => {
  mutableDoc.at('color').set('red')
  mutableDoc.at('mileage').set(3001)
})

Updating Nested Fields

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

await ditto.store
.collection('cars')
.findByID('123')
.update((mutableDoc) => {
  mutableDoc.at('metadata.color').set('red')
  mutableDoc.at('metadata.mileage').set(3001)
})

Counters

Available in 4.11 and later with DQL_STRICT_MODE=false.

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

await ditto.store.collection("products")
  .find("_id = '1234'")
  .update(doc in {
    doc["in_stock"].counter?.increment(1.0)
})

For more information on using Counters see here.

Attachments

Store methods (NOT deprecated):
- store.new_attachment(path, metadata) - Still available
- store.fetch_attachment(token, handler) - Still available
Collection methods (deprecated):
- collection.new_attachment() → Use store.new_attachment()
- collection.fetch_attachment() → Use store.fetch_attachment()

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

await ditto.store
.collection('cars')
.findByID('123') 
.update((mutableDoc) => {
  mutableDoc.at('updatedAt').set(DittoRegister({
    "datetime": "2025-02-27T4:47:30Z", 
    "authorId": "67c0faa40054d13a000c614a"
   })
  )
})

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

Deletion

Deleting Fields

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

ditto.store.collection("orders")
	.findById("my-id").update { doc in 
		doc["items.abc"].remove()
	}

Evicting Documents

EVICT FROM cars WHERE color = 'blue'

await ditto.store
.collection('cars')
.find('color == "blue"')
.evict()

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'

await ditto.store
.collection('cars')
.find('color == "blue"')
.remove()

Syncing Data From Other Peers

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

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

Statements

Types and Functions

Reference

Legacy-to-DQL Reference

Collection Operations

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

Attachments

Registers

Deletion

Deleting Fields

Evicting Documents

Deleting Documents

Syncing Data From Other Peers

​Collection Operations

​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

​Attachments

​Registers

​Deletion

​Deleting Fields

​Evicting Documents

​Deleting Documents

​Syncing Data From Other Peers

Collection Operations

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

Attachments

Registers

Deletion

Deleting Fields

Evicting Documents

Deleting Documents

Syncing Data From Other Peers