> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.

# Legacy-to-DQL Reference

> This article covers example mappings from Legacy Query APIs to DQL for basic CRUD operations as well as syncing data with the SDK.

## Collection Operations

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

## Creating Documents

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

<Warning>
  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](/dql/ids-paths-strings-keywords#rules-for-identifiers)).
</Warning>

### Inserting a Single Document

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

<CodeGroup>
  ```javascript DQL theme={null}
  const newCar = {
    _id: '123',
    color: 'blue',
  }

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

  ```javascript Legacy theme={null}
  const newCar = {
    _id: '123',
    color: 'blue',
  }

  await ditto.store
  .collection('cars')
  .upsert(newCar)
  ```
</CodeGroup>

### Inserting Multiple Documents

<CodeGroup>
  ```javascript DQL theme={null}
  const car1 = {
    _id: '123',
    color: 'blue',
  }
  const car2 = {
    _id: '456',
    color: 'red',
  }

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

  ```javascript Legacy theme={null}
  // Not Supported
  ```
</CodeGroup>

### Inserting Initial/Default Documents

This is the ability to insert documents at time 0. For more information see [INSERT with INITIAL DOCUMENTS](/dql/insert#insert-with-initial-documents).

<CodeGroup>
  ```javascript DQL theme={null}
  const newCar = {
    _id: '123',
    color: 'blue',
  }

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

  ```javascript Legacy theme={null}
  const newCar = {
    _id: '123',
    color: 'blue',
  }

  await ditto.store
  .collection('cars')
  .upsert(newCar, { writeStrategy: "insertDefaultIfAbsent" })
  ```
</CodeGroup>

### 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

<CodeGroup>
  ```javascript DQL theme={null}
  const newCar = {
    _id: '123',
    color: 'blue',
  }

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

  ```javascript Legacy theme={null}
  const newCar = {
    _id: '123',
    color: 'blue',
  }

  await ditto.store
  .collection('cars')
  .upsert(newCar)
  ```
</CodeGroup>

## 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}})`

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    SELECT *
    FROM cars
    WHERE color = 'blue'
    `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .find('color == "blue"')
  ```
</CodeGroup>

### 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}})`

<CodeGroup>
  ```javascript DQL (4.11+) theme={null}
  await ditto.store.execute(`
    SELECT *
    FROM cars
    USE IDS "123"
    `)
  ```

  ```javascript DQL (all versions) theme={null}
  await ditto.store.execute(`
    SELECT *
    FROM cars
    WHERE _id = '123'
    `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .findById('123')
  ```
</CodeGroup>

### Querying Data with Limits

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    SELECT *
    FROM cars
    WHERE color = 'blue'
    LIMIT 10
    `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .find('color == "blue"')
  .limit(10)
  ```
</CodeGroup>

### Querying for Null Values

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    SELECT *
    FROM cars
    WHERE color IS NULL
    `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .find('color == null')
  ```
</CodeGroup>

### Querying with Arguments

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    SELECT *
    FROM cars
    WHERE color = :color`,
    { color: 'blue' })
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .find(
    'color == $args.color',
    { color: 'blue' })
  ```
</CodeGroup>

## Observing Data Changes

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

  ```javascript Legacy theme={null}
  const observer = ditto.store
  .collection('cars')
  .find('color = "blue"')
  .observeLocal((result, event) => {
      // do something
    })
  ```
</CodeGroup>

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

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    UPDATE cars
    SET color = 'red'
    WHERE _id = '123'
    `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .findByID('123')
  .update((mutableDoc) => {
    mutableDoc.at('color').set('red')
  })
  ```
</CodeGroup>

### Updating Multiple Documents

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    UPDATE cars
    SET color = 'red'
    WHERE color = 'blue'
    `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store
    .collection('cars')
    .find('color == "blue"')
    .update((documents) => {
      for (const document of documents) {
        document.color = 'red'
      }
    })
  ```
</CodeGroup>

### Updating Multiple Fields

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    UPDATE cars
    SET
      color = 'red',
      mileage = 3001
     WHERE _id = '123'
    `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .findByID('123')
  .update((mutableDoc) => {
    mutableDoc.at('color').set('red')
    mutableDoc.at('mileage').set(3001)
  })
  ```
</CodeGroup>

### Updating Nested Fields

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    UPDATE cars
    SET
      metadata.color = 'red',
      metadata.mileage = 3001
    WHERE _id = '123'
  `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .findByID('123')
  .update((mutableDoc) => {
    mutableDoc.at('metadata.color').set('red')
    mutableDoc.at('metadata.mileage').set(3001)
  })
  ```
</CodeGroup>

### Counters

<Warning>
  Available in 4.11 and later with DQL\_STRICT\_MODE=false.
</Warning>

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.

<CodeGroup>
  ```javascript DQL theme={null}
  // 4.11+
  await ditto.store.execute(`
    UPDATE products
    APPLY in_stock PN_INCREMENT BY 1.0
    SET updatedBy = 'my-author-id'
    WHERE _id = '1234'
  `)
  ```

  ```javascript Legacy theme={null}
  await ditto.store.collection("products")
    .find("_id = '1234'")
    .update(doc in {
      doc["in_stock"].counter?.increment(1.0)
  })
  ```
</CodeGroup>

For more information on using Counters see [here](/dql/types-and-definitions#counters).

### 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

<CodeGroup>
  ```javascript DQL theme={null}
  await ditto.store.execute(`
    UPDATE menus (updatedAt REGISTER)  
    SET updatedAt = {
      "datetime": "2025-02-27T4:47:30Z", 
      "authorId": "67c0faa40054d13a000c614a"
    }
    WHERE _id = 'my-id' 
  `)
  ```

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

For more information on using types and definitions, see [Registers](/dql/types-and-definitions#registers).

## Deletion

### Deleting Fields

<CodeGroup>
  ```sql DQL theme={null}
  -- 4.11+
  UPDATE orders UNSET items.abc WHERE _id = 'my-id'
  ```

  ```javascript Legacy theme={null}
  ditto.store.collection("orders")
  	.findById("my-id").update { doc in 
  		doc["items.abc"].remove()
  	}
  ```
</CodeGroup>

### Evicting Documents

<CodeGroup>
  ```sql DQL theme={null}
  EVICT FROM cars WHERE color = 'blue'
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .find('color == "blue"')
  .evict()
  ```
</CodeGroup>

### Deleting Documents

There are several ways to delete data in Ditto, and you can find more detail about them [here](/sdk/latest/crud/delete).

<CodeGroup>
  ```SQL DQL theme={null}
  -- 4.10+
  DELETE FROM cars WHERE color = 'blue'
  ```

  ```javascript Legacy theme={null}
  await ditto.store
  .collection('cars')
  .find('color == "blue"')
  .remove()
  ```
</CodeGroup>

## Syncing Data From Other Peers

<CodeGroup>
  ```javascript DQL theme={null}
  ditto.sync.registerSubscription(`
    SELECT *
    FROM cars
    WHERE color = 'blue'
    `)
  ```

  ```javascript Legacy theme={null}
  ditto.store
  .collection('cars')
  .find('color == "blue"')
  .subscribe()
  ```
</CodeGroup>