OutSystems Dev Tip: How to avoid breaking SQL when changing structures

Modifying data structures is a routine part of OutSystems development. However, when you change the output structure of an SQL query—especially by adding, removing, or updating attributes—you risk breaking your SQL if you’re not careful. Here’s a practical walkthrough from Senior Developer Elizabeth Sumalde on how to avoid these issues and keep your applications running smoothly.

The Scenario: Removing an Attribute

Imagine you have a screen displaying open tickets, powered by a block with an SQL query. The output structure includes several attributes, such as ModifiedOn. Suppose you decide ModifiedOn is no longer relevant and remove it from the structure.

After publishing this change, you might expect everything to work—but instead, you find your SQL code is broken.

Why Does This Happen?

When you remove an attribute from the output structure but don’t update your SQL query, the query still tries to select the now-missing attribute. OutSystems attempts to map the returned columns to the structure’s attributes in order, leading to:

• Data being stored in the wrong attributes (e.g., values intended for ModifiedOn end up in SLALabel)
• Data type mismatches (e.g., a date value mapped to a text attribute)
• SQL runtime errors and warnings about mismatched columns and structures

This is easy to spot if you only have a few warnings, but in larger apps with many warnings, it can be tricky to identify the root cause. 

How to Avoid Breaking Your SQL

Before making changes to any output structure, follow these steps:

• Check Structure Usages:
  Always review where the structure is used, especially as an SQL output structure. In OutSystems, you can sort usages by element and descending order to quickly find relevant references.
• Update the SQL Query:
  If you remove an attribute, also remove it from the list of selected columns in your SQL statement. This keeps the number and order of selected columns aligned with your output structure.
• Watch for Warnings:
  OutSystems will warn you if there’s a mismatch between selected columns and the output structure. Don’t ignore these warnings—they’re your first line of defense.
• Test Thoroughly:
  After making changes, test the affected screens and blocks to ensure everything works as expected.
• Apply the Same Care When Adding or Updating Attributes:
  The same principles apply when adding new attributes or modifying existing ones. Always synchronize your SQL queries with your structures.

Key Takeaways

• Changing an output structure without updating your SQL query can break your application.
• Always check where your structures are used and update your SQL accordingly.
• Pay attention to OutSystems warnings about mismatches—they’re there to help.
• Test after every change to confirm your app still works as intended.

By following these best practices, you can confidently manage OutSystems SQL changing structure and keep your applications robust and error-free.

Want to learn more valuable Dev Tips?  Sign up for our newsletter and stay updated with the latest insights! Also, don’t forget to check out our upcoming OutSystems courses to scale your career.