Querying Data

Querying Data#

A primary data source for dashboards is SQL databases, where data processed by Data Tasks is stored and optimised for querying. DUFT’s Query Engine, embedded within DUFT Server, executes these queries dynamically based on DUFT Config definitions.

Consider the following 3DL snippet:

<DataProvider>  
  <QueryData>
    SELECT COUNT(client_id) AS value 
    FROM fact_sentinel_event 
    WHERE last_viral_load_result_is_suppressed = 1
  </QueryData>
  <Tile title="Suppressed Cases" />  
</DataProvider>

When rendered, the Query Engine processes the SQL query and returns structured JSON for visualisation. The engine supports dynamic filtering, sorting, paging, and searching, which can be controlled via parameters in DUFT UI.

Key Engineering Decisions: Query Engine

Several key engineering decisions shaped the design and implementation of DUFT Security:

  • Dynamic Query Processing – Queries are enhanced with filtering, sorting, pagination, and full-text search.

  • Stored Queries for Security – SQL queries can be stored as DUFT Config files to mitigate SQL injection risks and keep sensitive queries hidden from the UI. They also make writing SQL queries easier, and less prone to errors.

  • Multi-DBMS Compatibility – DUFT supports SQLite, MySQL, PostgreSQL, and SQL Server, ensuring broad flexibility.

  • API-Driven Execution – The Query Engine is fully API-driven, with results returned as JSON for seamless integration with dashboards..

Note: DUFT UI also supports non-SQL data sources (e.g., APIs, OpenMRS, static datasets) via 3DL data adapters. These bypass the Query Engine and retrieve data entirely within the frontend.

Stored vs. Inline Queries#

Queries can be defined in DUFT Config in two ways:

1. Stored Queries (More Secure)#

Stored queries are defined as .sql files within DUFT Config, keeping SQL logic hidden from the UI.

<DataProvider>
  <QueryData name="last_refresh_date" />
  <Row columns="2">
    <Header>
      <DataString>%facility_name%</DataString>
    </Header>
    <Subheader>
      <DataString>Last updated: %last_refresh_date%</DataString>
    </Subheader>
  </Row>
</DataProvider>

The Query Engine will load and execute last_refresh_date.sql from DUFT Config instead of exposing raw SQL to the UI.

2. Inline Queries (Less Secure)#

Queries can also be embedded directly within dashboards, though this exposes them to the frontend. This is fine for closed settings, such as a health facility, but not recommended for centrally hosted dashboards.

<DataProvider>  
  <QueryData>
    SELECT COUNT(*) FROM fact_hiv_status 
    WHERE is_suppressed = 1
  </QueryData>
  <Tile title="Suppressed Cases" />  
</DataProvider>

Execution Pipeline#

The Query Engine follows a structured execution pipeline to process incoming requests:

  1. API Request Handling – The UI sends a request to execute a query, including optional filters, sorting, and pagination — which are sent as parameters.

  2. Query Transformation via Query Builder – The Query Builder applies user-supplied filters, search terms, and sorting instructions dynamically onto the query.

  3. Database Connection and Execution –  The processed query runs against the appropriate database connection from DUFT Config.

  4. Results Processing – Data is returned as JSON, optimised for UI rendering.

Query Builder Integration#

DUFT’s Query Builder dynamically applies filtering, sorting, and search parameters, enabling advanced querying capabilities:

  • Filtering – Users can apply conditions dynamically based on column values.

  • Sorting – Data can be ordered by any available column.

  • Full-Text Search – The Query Engine supports search across multiple columns.

  • Pagination – Large datasets are broken into manageable chunks.

The test below demonstrates how the Query Engine applies sorting, filtering, and pagination dynamically:

@override_feature(user_authentication=False)
def test_search_sort_paging_filter(self):
    url = reverse("run-query")
    request_data = {
        "query": "SELECT * FROM dim_client",
        "data_connection_id": "ANA",
        "filters": {"gender": "Female"},
        "search_text": "Wakanda",
        "search_columns": "residence_country",
        "sort_column": "client_id",
        "page_size": 10,
        "current_page": 1,
    }
    response = self.client.post(url, request_data,
      format="json")
    self.assertEqual(response.status_code,
      status.HTTP_200_OK)
    data = response.json()
    self.assertTrue(isinstance(data, list))
    self.assertLessEqual(len(data), 10)

The query builder takes the parameters and transforms them into a full SQL query. Note that this does not work with very complex SQL queries, in that case, implementers can manually specify where filters should be inserted into the query. Another (recommended) approach would be to simplify the query first into a view, and query that view.

Database Compatibility#

The Query Engine supports MySQL, SQL Server, PostgreSQL, and SQLite via DUFT Config. Connections are managed through data_connections.json, allowing queries to execute against multiple databases.

DUFT defaults to an Analysis (ANA) database, but alternative data connections can be specified when executing queries.

Future Enhancements#

Future enhancements to the Query Engine will include:

  • Granular Permissions – Currently, query execution is unrestricted, but fine-grained access controls are planned.

  • Custom Query Transformations – Future updates may allow query rewriting via custom adapters for non-standard databases, transferring some of the logic of React-based DataProviders to the server.

Contents