Best Practices - Query Builder

This guide covers strategies for building queries that are maintainable, efficient, and reusable across workspace modules and workflows. Following these patterns helps Creators avoid common pitfalls and build queries that scale as applications grow.

Query Design Principles

Apply these principles before building any query to reduce rework and improve long-term maintainability.

Start Simple

Begin with the minimum viable query: a data source and basic filters. Add lookups, unions, and complex filter logic only when the use case requires them. Simple queries are easier to debug, faster to execute, and less fragile when data structures change.

Test Early and Often

Use the Preview feature to test queries as they are built. Preview each stage: filters alone, then filters plus lookups, then the full query with sorting and projection. Testing incrementally makes it easier to isolate issues.

Note: Preview is only available in non-production environments. In production environments, test queries by referencing them in a development module before deploying to end-users.

Design for Reuse

Queries are workspace-level resources. When building a query, consider whether other modules or workflows might need similar data. A well-designed reusable query reduces duplication and keeps data retrieval logic consistent across the workspace.

Filter Builder Best Practices

Well-structured filters improve query performance and make logic easier to review and debug.

Use Specific Fields First

When building multi-condition filters, place the most restrictive conditions first. For example, if a query filters by both Status and Region, and Status equals "Active" eliminates 90% of records, place that rule before the Region filter. This does not affect correctness because AND groups evaluate all conditions regardless of order, but it makes the intent clearer to future reviewers.

Avoid Over-Nesting Groups

Deeply nested groups (groups inside groups inside groups) are hard to read and maintain. If a filter requires more than three levels of nesting, consider whether the query is trying to do too much. Break complex queries into multiple simpler queries or restructure the filter logic.

Use OR Groups Sparingly

OR groups are powerful but can become performance bottlenecks when they combine many conditions. An OR group with 20 conditions forces the database to evaluate all 20 possibilities for every record. When possible, restructure OR logic into separate queries and combine results with unions instead.

Test Edge Cases in Filters

When building filters that compare dates, numbers, or ranges, test boundary conditions. For example:

• Does Created Date is after "2024-01-01" include records created on January 1st, or only records created after January 1st?

• Does Age greater than 18 include 18, or only values above 18?

Run previews with known edge-case records to confirm the filter behaves as expected.

Lookup Best Practices

Lookups add power but also complexity. These patterns keep joined data manageable and queries efficient.

Limit Joined Data

Lookups can return multiple matching records per result record. When a lookup might return many matches, set a Limit on the lookup to cap the number of joined records. For example, if an Order query joins Customer Orders and a customer has 500 orders, including all 500 in every result record bloats the output and slows rendering.

Use the lookup's Limit setting to return only the most recent or most relevant joined records.

Project Only Needed Fields

By default, lookups include all fields from the source Data Model. This is rarely necessary. Use the lookup's Projection setting to include only the fields the query results need. Fewer fields means less data transferred and faster queries.

Name Output Fields Clearly

The Field Name for each lookup should clearly indicate what the joined data represents. Avoid generic names like data or lookup1. Use descriptive names like customerDetails, assignedUser, or relatedOrders. Clear names make query results self-documenting.

Avoid Chaining Lookups

The Query Builder does not support lookups on lookup results. If query results need data from Data Model A, which references Data Model B, which references Data Model C, the query cannot chain lookups to traverse that . Build separate queries instead, or denormalize the data structure so related fields are accessible from a single lookup.

Union Best Practices

Unions are best used for combining structurally similar data. These patterns prevent common union failures and keep result sets predictable.

Ensure Field Compatibility

Union queries combine results from multiple data sources. The combined result set includes only fields that exist in all unioned queries. Before adding a union, verify that the main query and each union query select compatible fields with matching names and types.

Use the Projection section in each union to align field names when the underlying Data Models use different naming conventions.

Test Unions Independently

Before combining queries with unions, test each union query independently using the Preview feature. Verify that each union returns the expected records and fields. Debugging a multi-union query is significantly harder than debugging individual queries in isolation.

Limit Union Count

Each union adds overhead to query execution. Queries with many unions (more than 3–5) can become slow and difficult to maintain. If a query requires many unions, consider whether the data model design could be simplified to reduce the need for unions.

Use Unions for Similar Data, Not Different Data

Unions work best when combining structurally similar data from multiple sources, like Active Customers and Archived Customers combined into a single result set. Unions are not a substitute for lookups. If the query must join different types of data (Orders plus Customers), use lookups instead.

Projection Best Practices

Projection controls which fields a query returns. Restricting it improves performance and reduces unnecessary data exposure.

Default to All Fields for Prototyping

When building a query for the first time, leave the projection empty to return all fields. This makes it easier to explore the data and understand what fields are available. Once the query is working, restrict the projection to only the fields the query needs.

Restrict Projection for Production Queries

In production queries referenced by modules and workflows, always use projection to include only the required fields. Returning unnecessary fields increases data volume, slows queries, and wastes processing time in modules that consume the results.

Include ID Fields

When restricting projection, always include the record ID field. Many modules and workflows need the ID to update or delete records. Queries that omit the ID field cannot be used for write operations.

Sort Best Practices

Sorting adds overhead to every query execution. Apply it only when order meaningfully affects how results are used.

Sort Only When Necessary

Sorting adds processing overhead. If the order of query results does not matter, skip the sort stage. For example, if the results are displayed in a table where end-users can sort columns themselves, let the consuming module or workflow handle sorting instead.

Combine Sort with Limit for Top-N Queries

When a query needs only the top N records (for example, the 10 most recent orders), combine sorting and limiting:

1. Sort by the relevant field (for example, Created Date descending).

2. Set the limit to the desired count (for example, 10).

This pattern retrieves exactly the top N records without loading the entire result set.

Avoid Multi-Field Sorting Unless Required

Sorting by multiple fields is useful when the first sort field has many duplicate values, but it adds complexity. Only add secondary sort fields when they are necessary to produce a deterministic order.

Limit Best Practices

Without limits, queries can return far more data than any module or workflow needs. These patterns keep result sets appropriately sized.

Use Limits to Control Query Size

Queries without limits can return thousands or millions of records. Large result sets slow query execution, consume memory, and overwhelm modules that attempt to process or display the data. Set a reasonable limit based on how the query results will be used.

For example:

• A query that populates a dashboard widget might limit results to 100 records.

• A query that feeds a paginated table might limit results to 50 records per page, relying on the consuming module to request subsequent pages.

Test Queries Without Limits Before Deploying

Before deploying a query to production, test it without a limit in a non-production environment to see how many records it returns. If the query returns an unexpectedly large result set, adjust the filters or add a limit to prevent performance issues.

Performance Considerations

Query performance affects module load times and end-user experience. These patterns identify and address the most common sources of slow queries.

Avoid Wide Filters

Filters that match most records in the data source force the database to scan nearly the entire dataset. For example, Status not equals "Deleted" when only 1% of records are deleted provides little value and adds processing overhead. Restructure the query to filter for the positive case instead. For example, Status equals "Active".

Index Commonly Filtered Fields

If a query filters or sorts by the same field repeatedly, coordinate with a workspace administrator or data architect to ensure that field is indexed in the underlying Data Model schema. Indexed fields significantly improve query performance.

Limit Result Set Size

Large result sets slow queries and consume resources in the modules that process them. Use filters, limits, and projection to keep result sets as small as practical. A query that returns 10,000 records but only uses 100 is inefficient.

Maintainability Best Practices

Queries are shared workspace resources. These patterns make them easier to understand, update, and evolve as applications grow.

Name Queries Descriptively

Query names should describe what the query retrieves and, when relevant, what filters it apply. Good query names are self-documenting. For example:

• Good: Active Customers in North Region

• Bad: Customer Query 1

Descriptive names make it easier to find the right query when building modules and workflows.

Document Complex Queries

The Query Builder does not support inline comments. For queries with complex filter logic, lookups, or unions, document the query's purpose and logic in a workspace-level reference document or in the module that consumes the query. Future Creators reviewing the query will need context to understand why it was built the way it was.

Version Queries Carefully

Queries are workspace-level resources. Updating a query updates every module and workflow that references it. Before saving changes to a widely used query, verify that the changes are backward-compatible with all consumers. If breaking changes are necessary, create a new query with a versioned name (for example, Customer Query v2) and migrate consumers incrementally.

Security and Access Control

Query access is governed by the same permissions as the underlying data sources. These patterns help Creators build queries that respect those boundaries.

Respect Data Source Permissions

Query results are subject to the same access controls as the underlying Data Model or application module. Creators without access to a data source cannot execute queries against that source, even if they have Write permissions for the Query Builder.

When building queries for use by multiple Creators, verify that all intended Creators have access to the query's data source.

Avoid Exposing Sensitive Data in Projections

If a Data Model includes sensitive fields (for example, Social Security numbers, credit card details, or internal notes), use projection to exclude those fields from query results unless absolutely necessary. This reduces the risk of accidental exposure in modules or workflows that consume the query.

Common Pitfalls

These mistakes are easy to make and can cause unexpected behavior. Knowing them in advance saves debugging time.

Forgetting to Save Before Previewing

Preview always executes the saved version of a query. Unsaved changes are not reflected in preview results. Save the query before running a preview to test the latest changes.

Changing Data Sources Without Reviewing Dependents

Changing a query's data source clears all filters, lookups, and unions because field definitions differ between data sources. Modules and workflows that reference the query will break if they depend on fields that no longer exist after the data source change. Before changing a data source, identify all consumers of the query and plan for the necessary updates.

Building Queries That Are Too Generic

Overly generic queries (for example, a query that returns all records from a Data Model with no filters) are rarely useful in practice. Generic queries force consuming modules to duplicate filter logic, defeating the purpose of a reusable query. Build queries that are specific enough to be immediately useful.

Ignoring Query Execution Time

Slow queries delay module load times and degrade the end-user experience. If a query takes more than a few seconds to execute in internal environments, investigate whether filters, lookups, or result set size can be optimized. Slow queries in internal environments are even slower in production environments with larger datasets.