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.

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

Legacy

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

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

DQL

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

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

Inserting Multiple Documents

Legacy

// Not Supported

DQL

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

await ditto.store.execute(`
  INSERT INTO cars
  DOCUMENTS (: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.

Legacy

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

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

DQL

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

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

Upserting Documents

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

Legacy

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

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

DQL

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

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

Reading Data

Querying Data

Legacy

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

DQL

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

Querying Data by ID

Legacy

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

DQL

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

Querying Data with Limits

Legacy

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

DQL

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

Querying for Null Values

Legacy

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

DQL

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

Querying with Arguments

Legacy

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

DQL

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

Observing Data Changes

Legacy

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

DQL

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

Updating Documents

Updating a Single Document

Legacy

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

DQL

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

Updating Multiple Documents

Legacy

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

DQL

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

Updating Multiple Fields

Legacy

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

DQL

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

Deleting Documents

Evicting Documents

Legacy

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

DQL

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

Deleting Documents

Legacy

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

DQL

// Not Supported (use EVICT)

Syncing Data From Other Peers

Legacy

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

DQL

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