Flutter Legacy→DQL Migration Guide

Overview

This guide will help you successfully migrate your Ditto Flutter application from the legacy query builder APIs to the modern DQL (Ditto Query Language). After reviewing this documentation, you’ll understand how to convert method chaining patterns to DQL syntax and systematically update your data operations.

AI Agent Prompt

Use this prompt when working with an AI coding assistant to migrate your Ditto Flutter app from legacy query builder to DQL.

Copy AI Migration Prompt (Click to Expand)

I need help migrating a Ditto Flutter application from the legacy query builder APIs to modern DQL (Ditto Query Language). This migration involves converting method chaining patterns to SQL-like DQL syntax.

CRITICAL RULES:
1. All query builder method chains (.collection().find()) must be replaced with ditto.store.execute() using DQL
2. Use parameterized queries with :paramName syntax - NEVER string interpolation
3. Counter operations must use PN_INCREMENT BY in APPLY clause - do NOT initialize counter fields
4. Sync subscriptions must use ditto.sync.registerSubscription() instead of .find().subscribe()
5. observeLocal must be replaced with registerObserver

---

CORE MIGRATION AREAS:

1. QUERY SYNTAX MIGRATION

BEFORE (Legacy Query Builder):
```dart
ditto.store.collection('cars')
  .find('color == \$args.color', arguments: {'color': 'red'})
  .exec();
```

AFTER (DQL):
```dart
await ditto.store.execute(
  'SELECT * FROM cars WHERE color = :color',
  arguments: {'color': 'red'}
);
```

2. INSERT OPERATIONS

BEFORE (Legacy Query Builder):
```dart
ditto.store.collection('cars')
  .upsert({'_id': id, 'color': 'blue'});
```

AFTER (DQL):
```dart
await ditto.store.execute(
  'INSERT INTO cars DOCUMENTS (:car)',
  arguments: {'car': carData}
);
```

3. UPDATE OPERATIONS

BEFORE (Legacy Query Builder):
```dart
ditto.store.collection('cars')
  .findById(id)
  .update((doc) {
    doc['color'] = 'green';
  });
```

AFTER (DQL):
```dart
await ditto.store.execute(
  'UPDATE cars SET color = :color WHERE _id = :id',
  arguments: {'color': 'green', 'id': id}
);
```

4. DELETE OPERATIONS

BEFORE (Legacy Query Builder):
```dart
ditto.store.collection('cars').findById(id).remove();
```

AFTER (DQL):
```dart
await ditto.store.execute(
  'DELETE FROM cars WHERE _id = :id',
  arguments: {'id': id}
);
```

5. EVICTION OPERATIONS

BEFORE (Legacy Query Builder):
```dart
ditto.store.collection('cars').findById(id).evict();
```

AFTER (DQL):
```dart
await ditto.store.execute(
  'EVICT FROM cars WHERE _id = :id',
  arguments: {'id': id}
);
```

6. COUNTER OPERATIONS (PN_COUNTER)

BEFORE (Legacy Query Builder):
```dart
ditto.store.collection('cars')
  .findById(id)
  .update((doc) {
    doc['numUpdates'].counter?.increment(1);
  });
```

AFTER (DQL with PN_INCREMENT):
```dart
await ditto.store.execute(
  'UPDATE cars APPLY numUpdates PN_INCREMENT BY :increment WHERE _id = :id',
  arguments: {'increment': 1, 'id': id}
);
```

IMPORTANT: Do NOT initialize counter fields in documents:
```dart
// WRONG - Creates a register, not a counter
{'counter': 0}

// CORRECT - Omit counter field, it's created on first PN_INCREMENT
{'_id': id, 'color': 'blue'}
```

7. DOCUMENT FIELD ACCESS MIGRATION

BEFORE (Legacy Query Builder):
```dart
final document = ditto.store.collection('cars').findById(id).exec();
final color = document?.value['color'];
```

AFTER (DQL):
```dart
final result = await ditto.store.execute(dqlString);
final item = result.items.first;
final color = item.value['color'];
```

8. OBSERVER MIGRATION (observeLocal → registerObserver)

BEFORE (Legacy observeLocal):
```dart
final liveQuery = ditto.store.collection('cars')
  .find('_id.locationId == "${Constants.locationId}"')
  .observeLocal((docs, event) {
    if (event is DittoLiveQueryUpdate) {
      // Handle changes
    } else if (event is DittoLiveQueryInitial) {
      // Handle initial data
    }
  });
```

AFTER (DQL with registerObserver):
```dart
final subscription = ditto.store.registerObserver(
  'SELECT * FROM cars WHERE _id.locationId = :locationId',
  arguments: {'locationId': Constants.locationId},
  onChanged: (result) {
    // Process result items
    final items = result.items;

    // Update UI
    setState(() {
      _cars = items;
    });
  },
);
```

9. SYNC SUBSCRIPTIONS MIGRATION

BEFORE (Legacy Query Builder):
```dart
final subscription = ditto.store.collection('cars')
  .find('color == \$args.color', arguments: {'color': 'red'})
  .subscribe();
```

AFTER (DQL):
```dart
final subscription = ditto.sync.registerSubscription(
  'SELECT * FROM cars WHERE color = :color',
  arguments: {'color': 'red'}
);
```

---

COMMON PITFALLS TO AVOID:

1. DQL Syntax Errors
   - Use :paramName for parameters, not \$args or string interpolation

2. Missing Parameter Binding
   - NEVER use string interpolation in queries
   - Always use parameterized queries with arguments map

3. Counter Type Errors
   - Do NOT initialize counter fields with DittoCounter() or numbers
   - Use PN_INCREMENT BY in APPLY clause
   - Pass negative values for decrements

4. Memory Management with Observers
   - Store observer reference
   - Call subscription.cancel() in dispose()

5. Attachment Handling
   - Use ATTACHMENT annotation: "(image ATTACHMENT)"
   - Create attachments with ditto.store.newAttachment()

---

MIGRATION CHECKLIST:

Search for these legacy patterns and replace:
- [ ] .collection( → await ditto.store.execute('SELECT * FROM
- [ ] .find( → Convert to DQL WHERE clause with arguments
- [ ] .findById( → Convert to DQL WHERE _id = :id
- [ ] .upsert( → Convert to DQL INSERT INTO COLLECTION
- [ ] .update( → Convert to DQL UPDATE COLLECTION SET
- [ ] .remove( → Convert to DQL DELETE FROM
- [ ] .evict( → Convert to DQL EVICT FROM
- [ ] .counter?.increment( → Convert to PN_INCREMENT BY in APPLY clause
- [ ] DittoCounter() → Remove initialization, use PN_INCREMENT
- [ ] .observeLocal( → Convert to registerObserver
- [ ] .subscribe() → Convert to ditto.sync.registerSubscription()

---

Please help me convert all legacy query builder patterns in my codebase to DQL syntax. Focus on:
1. Maintaining the same functionality
2. Using proper parameterized queries with arguments map
3. Handling counter operations correctly with PN_INCREMENT
4. Implementing proper observer cleanup in dispose()
5. Converting all sync subscriptions to DQL

Start by identifying all uses of .collection() in my codebase and systematically converting each one to the appropriate DQL pattern.

Syntax Change Reference

Document Query Syntax

Document Query All

await ditto.store.execute(
  'SELECT * FROM cars'
);

Document Query by ID

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

Document Query with Predicate

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

Document Query with Arguments

await ditto.store.execute(
  'SELECT * FROM cars WHERE color = :color',
  arguments: {'color': 'red'}
);

Document Insert

await ditto.store.execute(
  'INSERT INTO cars DOCUMENTS (:car)',
  arguments: {'car': carData}
);

Document Update

await ditto.store.execute(
  'UPDATE cars SET color = :color WHERE _id = :id',
  arguments: {'color': 'green', 'id': id}
);

Document Delete

await ditto.store.execute(
  'DELETE FROM cars WHERE _id = :id',
  arguments: {'id': id}
);

Document Local Eviction

// Evict by ID
await ditto.store.execute(
  'EVICT FROM cars WHERE _id = :id',
  arguments: {'id': id}
);

// Evict all matching documents
await ditto.store.execute(
  'EVICT FROM cars WHERE color = :color',
  arguments: {'color': 'red'}
);

Query Response Handling

Legacy Query Builder → DQL Response

final result = await ditto.store.execute(dqlString);
final item = result.items.first;
final color = item.value['color'];

Legacy Query Builder → Modern Document Conversion

Car documentToCar(DittoQueryResultItem item) {
  return Car(
    id: item.value['_id'] as String,
    color: item.value['color'] as String,
  );
}

Observer Migration

Legacy Query Builder → DQL Store Observer Migration

final subscription = ditto.store.registerObserver(
  'SELECT * FROM cars WHERE _id.locationId = :locationId',
  arguments: {'locationId': Constants.locationId},
  onChanged: (result) {
    // Process result items
    final items = result.items;

    // Update UI
    setState(() {
      _cars = items;
    });
  },
);

Performance Consideration: DQL observers provide more advanced return results including aggregates and projections. This requires more database full scans to ensure consistent results compared to the legacy query builder.Use indexes on query fields to maintain and improve observer performance. Indexes ensure your observers remain functional with optimal query performance.

Best Practice: Create Indexes for Observer Queries

// Create index on frequently queried fields
await ditto.store.execute('''
  CREATE INDEX idx_cars_locationId
  ON cars (_id.locationId)
''');

// Then register observer - queries will use the index
final subscription = ditto.store.registerObserver(
  'SELECT * FROM cars WHERE _id.locationId = :locationId',
  arguments: {'locationId': Constants.locationId},
  onChanged: (result) {
    // Process results
  },
);

For more information on creating and managing indexes, see the DQL Indexing documentation.

Sync Subscriptions Migration

Legacy Query Builder → DQL Sync Subscriptions Subscribe with Query

final subscription = ditto.sync.registerSubscription(
  'SELECT * FROM cars WHERE color = :color',
  arguments: {'color': 'red'}
);

Subscribe with Parameters

final subscription = ditto.sync.registerSubscription(
  'SELECT * FROM cars WHERE _id.locationId = :locationId',
  arguments: {'locationId': Constants.locationId}
);

Multiple Subscriptions

final subscriptions = <DittoSyncSubscription>[];
subscriptions.add(
  ditto.sync.registerSubscription(
    'SELECT * FROM cars WHERE color = :color',
    arguments: {'color': 'red'}
  )
);
subscriptions.add(
  ditto.sync.registerSubscription(
    'SELECT * FROM cars WHERE year > :year',
    arguments: {'year': 2020}
  )
);

Cancel Subscription

subscription.cancel();

Subscribe to All Documents

final subscription = ditto.sync.registerSubscription(
  'SELECT * FROM cars'
);

Counter Type Migration

PN_COUNTER is the DQL equivalent of the legacy DittoCounter type. When migrating counter operations from the legacy query builder’s .counter?.increment() method, use PN_INCREMENT BY in the APPLY clause. This maintains full compatibility with existing counter data created by DittoCounter.

Counter Increment

await ditto.store.execute(
  'UPDATE cars APPLY numUpdates PN_INCREMENT BY :increment WHERE _id = :id',
  arguments: {'increment': 1, 'id': id}
);

Counter Decrement

await ditto.store.execute(
  'UPDATE cars APPLY viewCount PN_INCREMENT BY :decrement WHERE _id = :id',
  arguments: {'decrement': -1, 'id': id}
);

Initialize Counter in Document

// Counter fields are automatically created on first PN_INCREMENT use
await ditto.store.execute(
  'INSERT INTO cars DOCUMENTS (:car)',
  arguments: {
    'car': {
      '_id': id,
      'color': 'blue'
      // Do NOT initialize counter fields - they are created on first PN_INCREMENT
    }
  }
);

// Then use PN_INCREMENT with APPLY clause to create and increment the counter
await ditto.store.execute(
  'UPDATE cars APPLY numUpdates PN_INCREMENT BY 1 WHERE _id = :id',
  arguments: {'id': id}
);

Multiple Counter Operations

await ditto.store.execute(
  '''UPDATE cars
     APPLY likes PN_INCREMENT BY :likeIncrement,
           dislikes PN_INCREMENT BY :dislikeDecrement,
           views PN_INCREMENT BY :viewIncrement
     WHERE _id = :id''',
  arguments: {
    'likeIncrement': 1,
    'dislikeDecrement': -1,
    'viewIncrement': 1,
    'id': id
  }
);

Attachment Operations with DQL

Attachment Creation and Storage

// Create attachment using store
final attachment = await ditto.store.newAttachment(
  filePath,
  metadata: metadata
);

// Store attachment with DQL
await ditto.store.execute(
  'INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)',
  arguments: {'doc': docWithAttachment}
);

Attachment Fetching

// Fetch attachment with progress callback
final fetchResult = await ditto.store.fetchAttachment(
  attachmentToken,
  onProgress: (downloadedBytes, totalBytes) {
    updateProgress(downloadedBytes, totalBytes);
  }
);

Performance Enhancements

Indexes for Improved Query Performance

DQL observers and queries benefit significantly from proper indexing. When migrating from the legacy query builder to DQL, creating indexes on frequently queried fields is essential for maintaining optimal performance. Why Indexes Matter for DQL:

DQL observers support advanced features like aggregates and projections
These advanced features require full database scans to ensure consistent results
Indexes dramatically reduce query execution time by avoiding full scans
Combining indexes with observers provides better performance than legacy query builder

Creating Indexes:

// Create index on single field
await ditto.store.execute('''
  CREATE INDEX idx_cars_color
  ON cars (color)
''');

// Create compound index on multiple fields
await ditto.store.execute('''
  CREATE INDEX idx_cars_color_year
  ON cars (color, year)
''');

// Create index on nested field
await ditto.store.execute('''
  CREATE INDEX idx_cars_location
  ON cars (_id.locationId)
''');

Best Practices:

Create indexes on fields used in WHERE clauses
Create indexes before registering observers for those queries
Use compound indexes for queries with multiple filter conditions
Monitor query performance and add indexes as needed

For comprehensive information on indexing strategies, syntax, and best practices, see the DQL Indexing documentation.

Common Pitfalls to Avoid

1. DQL Syntax Errors

Use :paramName for parameters, not \$args.paramName or string interpolation.

// ❌ Wrong: String interpolation
final color = 'red';
await ditto.store.execute('SELECT * FROM cars WHERE color = \'$color\'');

// ✅ Correct: Using :paramName with Map<String, dynamic>
await ditto.store.execute(
  'SELECT * FROM cars WHERE color = :color',
  arguments: {'color': 'red'}
);

2. Missing Parameter Binding

NEVER use string interpolation in queries. Always use parameterized queries with Map<String, dynamic>.

// ❌ Wrong: Direct string interpolation
final locationId = 'loc_123';
await ditto.store.execute(
  'SELECT * FROM cars WHERE _id.locationId = \'$locationId\''
);

// ✅ Correct: Parameterized query
await ditto.store.execute(
  'SELECT * FROM cars WHERE _id.locationId = :locationId',
  arguments: {'locationId': locationId}
);

3. Counter Type Errors

Use COUNTER annotation in collection definitions. Do NOT use SET with COUNTER fields. Use APPLY with PN_INCREMENT BY. Pass negative values for decrements.

// ❌ Wrong: Initializing counter with a number (creates REGISTER, not COUNTER)
await ditto.store.execute(
  'INSERT INTO items DOCUMENTS (:doc)',
  arguments: {'doc': {'counter': 0, '_id': id}}
);

// ❌ Wrong: Using SET on counter field
await ditto.store.execute(
  'UPDATE items SET counter = 5 WHERE _id = :id',
  arguments: {'id': id}
);

// ✅ Correct: Use PN_INCREMENT BY with APPLY clause (creates counter on first use)
await ditto.store.execute(
  'UPDATE COLLECTION items (counter COUNTER) APPLY counter PN_INCREMENT BY :value WHERE _id = :id',
  arguments: {'value': 1, 'id': id}
);

// ✅ Correct: Decrement by passing negative value
await ditto.store.execute(
  'UPDATE items APPLY counter PN_INCREMENT BY :value WHERE _id = :id',
  arguments: {'value': -1, 'id': id}
);

4. Memory Management with Observers

Call item.close() after extracting data from QueryResultItems. Always cancel observers when disposing. Use indexes for improved memory and performance.

// ❌ Wrong: Storing QueryResultItems outside callback scope
class CarRepository {
  List<DittoQueryResultItem> _items = [];

  void setupObserver() {
    _observer = ditto.store.registerObserver(
      'SELECT * FROM cars',
      (result) {
        _items = result.items;  // Holds native memory
      }
    );
  }
}

// ✅ Correct: Extract data and close items immediately
class CarRepository extends ChangeNotifier {
  DittoStoreObserver? _observer;
  List<String> _carIds = [];

  void startObserving() {
    _observer = ditto.store.registerObserver(
      'SELECT * FROM cars',
      (result) {
        final ids = <String>[];
        for (final item in result.items) {
          final id = item.value['_id'] as String;
          item.close();  // Free native memory
          ids.add(id);
        }
        _carIds = ids;
        notifyListeners();
      }
    );
  }

  @override
  void dispose() {
    _observer?.cancel();  // Always cancel observer
    super.dispose();
  }
}

5. Attachment Handling

Use ATTACHMENT annotation in collection definitions. Create attachments with ditto.store.newAttachment().

// ❌ Wrong: Missing ATTACHMENT annotation
await ditto.store.execute(
  'INSERT INTO cars DOCUMENTS (:doc)',
  arguments: {'doc': docWithAttachment}
);

// ✅ Correct: Use ATTACHMENT annotation in COLLECTION definition
final attachment = await ditto.store.newAttachment(
  path: filePath,
  metadata: metadata
);

await ditto.store.execute(
  'INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)',
  arguments: {
    'doc': {
      '_id': id,
      'image': attachment
    }
  }
);

Overview & Setup

SDKs

Accessing Data

Syncing Data

Authentication and Authorization

Managing the Mesh

Deploying your App

Flutter Legacy→DQL Migration Guide

Overview

AI Agent Prompt

Syntax Change Reference

Document Query Syntax

Query Response Handling

Observer Migration

Sync Subscriptions Migration

Counter Type Migration

Attachment Operations with DQL

Performance Enhancements

Indexes for Improved Query Performance

Common Pitfalls to Avoid

1. DQL Syntax Errors

2. Missing Parameter Binding

3. Counter Type Errors

4. Memory Management with Observers

5. Attachment Handling

Overview & Setup

SDKs

Accessing Data

Syncing Data

Authentication and Authorization

Managing the Mesh

Deploying your App

Documentation Index

​Overview

​AI Agent Prompt

​Syntax Change Reference

​Document Query Syntax

​Query Response Handling

​Observer Migration

​Sync Subscriptions Migration

​Counter Type Migration

​Attachment Operations with DQL

​Performance Enhancements

​Indexes for Improved Query Performance

​Common Pitfalls to Avoid

​1. DQL Syntax Errors

​2. Missing Parameter Binding

​3. Counter Type Errors

​4. Memory Management with Observers

​5. Attachment Handling

Overview

AI Agent Prompt

Syntax Change Reference

Document Query Syntax

Query Response Handling

Observer Migration

Sync Subscriptions Migration

Counter Type Migration

Attachment Operations with DQL

Performance Enhancements

Indexes for Improved Query Performance

Common Pitfalls to Avoid

1. DQL Syntax Errors

2. Missing Parameter Binding

3. Counter Type Errors

4. Memory Management with Observers

5. Attachment Handling