A query can execute successfully and still produce an API that is difficult to use. Invoice, customer, and line-item data may be returned as flat rows, enriched through a lookup, or shaped into nested objects and arrays. That choice affects payload size, consumer effort, and the cost of future change.
Why is data shape as important as data content?
Consider an invoice with one customer and many invoice items. A direct JOIN can produce one row per item, repeating invoice and customer values on every row. That representation is valid for a tabular report, but awkward for an application that expects one invoice object containing a customer child and an invoice_items array.
The response contract becomes a dependency between systems. Changing an object into an array or renaming a field requires every consumer to adapt. The shape should therefore be agreed with the API owner, reporting users, and the team responsible for the source—not left as an accidental consequence of the table structure.
When should you use a JOIN or a lookup?
A JOIN fits when table relationships are clear and filters must apply across the combined data. One example is retrieving invoices and customer names only for active customers. The database can plan the query as one operation when indexes and relationship conditions are appropriate.
A lookup is easier to understand as enrichment: the main flow carries customer_id, then customer data is fetched and merged. It is useful when the main record is already in the workflow, the enrichment source differs, or a team wants missing reference data handled separately. A per-item lookup can, however, create an N+1 pattern over a large collection. Tests should use realistic volume.
| Need | Starting choice | What to verify |
|---|---|---|
| Cross-table filtering | JOIN | Indexes, cardinality, duplicated rows |
| Enrich one record | Lookup | Behavior when no match exists |
| Parent-child response | Nested result | One/many, child order, limits |
Nested results represent relationships as relationships
A nested result shapes data according to the domain. A one relationship can produce a customer object, while a many relationship produces an invoice-item array. Consumers no longer have to regroup flat rows or guess whether repeated invoices are caused by the JOIN.
NitroNet's Read DB node provides visual choices for relationships, cardinality, child fields, filters, and ordering. Queries are still compiled as parameterized operations for the relevant supported driver. The “choose, do not type” approach makes intent visible, but it does not remove the need to inspect the real schema and estimate the amount of returned data.
Use a decision framework before building the query
- Identify the consumer.
A detail screen, report, export, and downstream service may require different structures.
- Define cardinality.
Separate required, optional, one, and many relationships so an empty result is not ambiguous.
- Limit fields.
Select only contract fields; do not make every table column a permanent API commitment.
- Design filters and pagination.
An unbounded child array can inflate both response size and processing time.
- Test empty and duplicate cases.
Confirm behavior when the parent is absent, children are empty, or reference data is not unique.
Example: an invoice-detail endpoint
A natural detail response uses one invoice as the root, one customer object, and an invoice_items array. A Receive Data node accepts invoice_id, Read DB filters the invoice, and nested relations fetch the customer and items. A Response node returns that structure or an explicit not-found result.
An invoice list may need a different contract. Repeating a small customer summary per row may be practical, while items can be omitted until the user opens a detail page. That prevents a list view from downloading every child record and keeps the response focused on its purpose.
Consider the cost of contract change as well. A new database column does not automatically belong in the response. Introduce it through a compatible change or version, document whether it can be null, and update request-response examples. Existing consumers should continue to operate through an agreed transition. This prevents an internal schema from accidentally becoming a public commitment without review.
Performance testing should compare several shapes over the same data: a flat JOIN, a lookup, and a nested result. Record query count, rows read, response size, and time spent in both database and workflow. Results depend on indexes, data distribution, driver, and network, so a single-invoice example is insufficient. Apply identical filters and pagination for a fair comparison, then select the contract that meets consumer needs with the lowest operational complexity.
Limitations and production checks
Define depth, child limits, ordering, and behavior for inconsistent relationships. Differences between PostgreSQL, MySQL, SQL Server, MongoDB, and SAP HANA must also be checked against the runtime schema.
Use Sandbox and test data that represents production cardinality before enabling an endpoint. Observe errors, execution time, and output size without assuming one structure fits every consumer. Continue with how agent-centric data flow works and the visual capabilities in NitroNet features to place query design inside a complete workflow.