What if your queries got leaner, your data easier to shape, and your toolchain covered more platforms—all in one release? Ditto SDK 4.12 levels up DQL with projections and aggregates, adds indexes (including simple single-field indexes you can create in DQL), expands Flutter support to Linux and macOS, and begins deprecating the legacy query builder as DQL becomes the path forward. In this post, I’ll walk through a quick refresh of my MFlix movie app that I used to demo the Ditto MongoDb Connector to show what the 4.12 upgrade feels like in practice.  You can find the full source code on GitHub:  https://github.com/ditto-examples/mflix-mongodb-connector

Using Projections to Improve Query Efficiency

One habit I’ve developed over the years is validating performance ideas before I start changing code. If I think there’s a way to make the app run more efficiently, I want to measure the potential benefit up front so I know whether the idea is worth pursuing.

For the main movie listing screen in my Mflix demo app, I realized I was pulling back entire movie documents—even though that screen only uses a handful of fields. The movies collection schema contains a lot of data, including cast lists, full reviews, and multiple nested objects. None of that is needed for a simple listing view.

To test my theory, I turned to the Ditto Portal’s DQL editor. My baseline query was:

SELECT * FROM movies WHERE rated = 'PG' OR rated = 'G'

Exporting the results from the Portal produced 4.3 MB of data. That’s a lot of in-memory payload just to draw a list of movie titles, posters, plot, year, and rating information.  Next, I rewrote the query to use projections—only selecting the fields my listing screen actually needs:

SELECT 
      _id,  
       title,
       poster,
       plot, 
       year,
       tomatoes.viewer.rating AS rottenRating,
       imdb.rating AS imdbRating
FROM movies 
WHERE rated = 'PG' OR rated = 'G'

The difference was dramatic. Exporting this query produced a file of just 950 KB—less than a quarter of the original size. That’s a significant reduction in memory usage and data transfer, which should translate into faster load times and lower resource usage, especially on mobile devices.

The best part is that when a user taps a movie to view its details, I can still fetch the full document on demand from the database.  After updating my code, I saw a noticeable boost in rendering speed for the movie list and a drop in memory usage. The only change required was adding a query to load the full movie document when a user taps on a movie, a small adjustment for a substantial performance gain.

Using Simple Indexes to add Search functionality

One of my testing patterns is to insert or update data frequently to make sure those parts of the app are working as expected. The challenge is that in a large dataset, finding the movie I just added or updated can be slow and tedious, especially when I want to validate it quickly. At the same time, giving users the ability to search for a movie by title would be a nice usability improvement.

With Ditto 4.12, I decided to try a simple index on the title field. An index in the 4.12 version of the Ditto SDK allows you to index a single field in a document for faster lookups. The documentation is here. Creating the index was straightforward, and I added it to my Ditto initialization code so it’s always ready when the app starts:

CREATE INDEX IF NOT EXISTS movies_title_idx ON movies(title)

Since simple indexes are only supported in the SDK (not the Portal), I couldn’t test it there, but the implementation in my apps was trivial. I then added a search field to my form and wired it up so whatever the user types is used in the query. Here’s the Swift version (I implemented the same pattern in other languages, too):

let results = try await ditto.store.execute(
    query: """
        SELECT _id, plot, poster, title, year, imdb.rating AS imdbRating,
               tomatoes.viewer.rating as rottenRating
        FROM movies
        WHERE title LIKE :searchTerm
          AND (rated = 'G' OR rated = 'PG')
        ORDER BY year DESC
    """,
    arguments: ["searchTerm": "%\(title)%"]
)

I expected the LIKE %search% pattern to be a performance killer—especially since I initially skipped adding debounce logic, meaning the query runs on every keystroke. To my surprise, results were nearly instantaneous. In testing with physical devices,  results came back in as little as 120 ms, and even on a very old Android phone with slow storage, the longest query took around 400 ms.

For a production app, I’d still recommend adding debounce logic to reduce excessive disk I/O and prevent unnecessary battery drain. For a demo, though, the real-time updates are pretty fun to watch. In Flutter, I added debouncing because on older Android devices with slower storage, performance suffered without it. The index not only made the search possible, but also fast enough to feel instant, a big win for both development testing and potential end-user search experiences.

After seeing how effective simple indexes can be, I thought it would be useful, especially for a technical demo app, to give myself (and anyone trying the app) a way to inspect what indexes are currently set on the database. That way, I could quickly confirm that my index creation code is working and see what’s available at any given time.

Getting this information is straightforward. Ditto exposes a special system:indexes collection that lists all defined indexes. Running the following query will return that list:

SELECT * FROM system:indexes

I used this as the basis for a small System tab in the app. It fetches the indexes and displays them in a clean UI, making it easy to see the index name, the collection it’s on, and the indexed field(s).

While index support in Ditto will continue to evolve (adding capabilities like multi-field indexes) the current iteration in 4.12 of simple indexes on a single field can already provide meaningful performance improvements for apps. 

Building a Sync Status UI with the New Monitoring API

As part of the app refresh, I wanted to take advantage of Ditto’s expanded platform support in 4.12 to build a SwiftUI version that runs on macOS, alongside a Flutter version that now runs on macOS and Linux. Getting peer-to-peer sync running on Apple platforms requires some extra setup in your Info.plist file, something that can trip up developers who are new to it. The steps are documented in Ditto’s Swift installation guide.

For this release, I also wanted a real-time view of sync status across my running apps.  This can help me to validate quickly on my SwiftUI MacOS version that I have the entitlements set up properly.  Ditto SDK 4.12 introduces a new API for monitoring sync status, documented here. I decided to use the special system:data_sync_info collection to drive a live sync status display. Here’s the observer I added:

syncStatusObserver = try ditto.store.registerObserver(
    query: """
        SELECT * 
        FROM system:data_sync_info 
        ORDER BY documents.sync_session_status, documents.last_update_received_time DESC
    """,
    deliverOn: backgroundQueue
) { [weak self] result in
    let syncStatusInfos = result.items.compactMap { item in
        return SyncStatusInfo(item.jsonData())
    }
    Task { @MainActor in
        self?.onSyncStatusUpdate?(syncStatusInfos)
    }
}

A few things are worth noting here:

• Threading for performance – I explicitly deliver results to a backgroundQueue so the JSON serialization work stays off the main thread. In SwiftUI, this helps keep the UI responsive, and in my experience (and based on feedback from our customer experience team), many developer performance issues stem from doing too much work on the main thread.
  
  
• Ordering the results – I sort by sync_session_status so that connected devices are listed first, and then by last_update_received_time so recent activity floats to the top.
  
  
• Model mapping – After reviewing the system:data_sync_info document structure in the docs, I created a SyncStatusInfo model to deserialize the results into something easy to work with in SwiftUI.
  
  

Finally, I built a simple SwiftUI screen to display the sync status list in real time. Now I can see, at a glance, which devices are connected, what their sync state is, and when they last received updates—all without leaving the app.

When working with the new sync status features, one thing that can confuse developers is the commitId. In my own setup, you can see in the screenshot that the macOS version of the app (on the left) shows a different commitId than the iPhone Simulator version (on the right). This is completely normal—commitIds are tied to the database level, and they behave like commits in any other database system.

A neat addition in Ditto 4.12 is that when you run an execute statement that inserts, updates, or deletes data, the result now includes the database commitId. I decided to surface this in my app so that whenever a user adds or updates a movie, the resulting commitId is shown in a pop up alert. You can see how I implemented this in DittoService.swift, line 360.

One important thing to note: you may notice commitIds decrease and then later increase again. This does not mean something is broken. What’s happening is that a sync is in progress, and the database is processing CRDTs (Conflict-free Replicated Data Types) to resolve changes. Once that process finishes, the commitId will move forward again.

So, if you see the commitId jumping around, remember, this is just Ditto doing the hard work of conflict resolution for you, ensuring your data stays consistent across devices.

Using Aggregate Functions for Pagination

Ditto 4.12 introduces aggregate functions—COUNT, SUM, AVG, MIN, and MAX—which open the door for more powerful and efficient queries. While I didn’t have time to fully integrate them into the movies app, I did experiment with them in another project focused on pagination.

By combining COUNT with LIMIT and OFFSET, I was able to build a clean, responsive pagination feature. The COUNT function let me determine the total number of records up front, so I could calculate the exact number of pages and dynamically enable/disable navigation controls. Then, LIMIT and OFFSET handled retrieving only the slice of data needed for the current page, keeping queries light and fast.

It’s a small addition, but aggregate functions like this make a big difference in building smooth, data-driven UIs. (I’ll drop in a screenshot from my other app here so you can see it in action.)

Moving Forward with DQL

In this article, I’ve covered a lot of exciting new capabilities that DQL brings to the table. Over the past year, our engineering team has put in a tremendous effort to make DQL a fully featured, production-ready query interface. We’ve now reached the point where anything you could previously build with the legacy Query Builder can be built with DQL—only better. DQL offers more flexibility, richer functionality, and a more expressive syntax for shaping and retrieving your data.

With that milestone achieved, we’re beginning the process of deprecating the Query Builder API. To make the transition as smooth as possible, we’ve published a migration guide to help you update your apps and take full advantage of what DQL offers today.

I see this as an exciting turning point. DQL is now the clear path forward for building fast, powerful, and maintainable queries in Ditto—and it’s only going to get better from here. I’m looking forward to showing you what’s coming next in future releases as our engineering team continues to expand DQL’s capabilities.

Continue the conversation live!

Want to dive deeper into what’s new in Ditto 4.12? Join us for a LinkedIn Live session on Thursday, August 28 from 12:30–1:00 PM Eastern, hosted by me, Aaron Labeau, developer advocate. We’ll walk through the biggest changes in this release, share practical tips for upgrading your apps, and answer your questions live. You can RSVP and join the conversation through the link below.