---
name: outsystems-sql
description: >-
  OutSystems SQL query authoring and flowmo database tooling for both O11 (MSSQL)
  and ODC (Aurora PostgreSQL / ANSI-92). Covers entity references, aggregate syntax,
  advanced SQL, joins, functions, parameterized queries, platform-specific syntax
  differences, running and testing queries with flowmo db:query, setting up seed data,
  and the full write-test-iterate workflow. Also handles converting ODC data models
  into Flowmo-compatible PostgreSQL schemas. Use when writing or testing .advance.sql
  files, working with aggregates, running queries, seeding data, or mirroring an ODC
  schema locally.
compatibility: Designed for OutSystems Service Studio (O11) and ODC Studio (ODC). Requires knowledge of target platform.
metadata:
  version: "1.0"
  source: "OutSystems documentation and SQL best practices"
---

# OutSystems SQL Skill

## Platform Detection

Before writing any SQL, determine the target platform:

- **O11 (OutSystems 11)** — Uses Microsoft SQL Server syntax (T-SQL)
- **ODC (OutSystems Developer Cloud)** — SQL nodes use `{Entity}.[Attribute]` notation (same as O11) but ODC has **two sub-modes** that ODC Studio selects automatically:
  - **Internal entities only** (native ODC entities) → PostgreSQL-dialect SQL. Functions and clauses use PostgreSQL syntax: `LIMIT`, `||`, `RANDOM()`, `NOW()`, etc. LIKE on text columns requires `caseaccent_normalize()`.
  - **External entities (Data Fabric) or mixed** → ANSI-92 syntax. ODC normalizes and translates queries to the target system's dialect. Qualifying column lists with `{Entity}.[Attr]` notation is recommended.
  - You cannot select the sub-mode manually — ODC Studio switches automatically based on the entities in your query.

Ask the developer which platform they are targeting if not already clear. Despite the shared notation, function and clause differences cause runtime errors if O11 and ODC syntax is mixed.

## Entity & Attribute References

### O11 (MSSQL)
```sql
SELECT {Entity}.[Attribute1], {Entity}.[Attribute2]
FROM {Entity}
WHERE {Entity}.[IsActive] = 1
```
- Entities: `{EntityName}` in curly braces
- Attributes: `[AttributeName]` in square brackets
- Boolean: `1` / `0` (stored as integer)

### ODC (Internal entities — PostgreSQL dialect)
```sql
SELECT {Entity}.[Attribute1], {Entity}.[Attribute2]
FROM {Entity}
WHERE {Entity}.[IsActive] = 1
```
- Entities: `{EntityName}` in curly braces — same notation as O11
- Attributes: `[AttributeName]` in square brackets — same notation as O11
- Boolean: `1` / `0` (stored as integer, same as O11)
- `SELECT *` is **not valid** — always qualify as `SELECT {Entity}.*`
- Functions/clauses use PostgreSQL syntax: `NOW()`, `LIMIT`, `||`, `RANDOM()`, etc.
- For external entities (Data Fabric), ODC Studio switches to ANSI-92 mode automatically — same notation, but qualifying column lists with `{Entity}.[Attr]` is recommended

## Syntax Differences Quick Reference

Both O11 and ODC SQL nodes use `{Entity}.[Attribute]` notation. The differences are in functions, operators, and clause syntax.

| Operation | O11 (MSSQL) | ODC (ANSI-92 / Aurora PostgreSQL) |
|-----------|-------------|-----------------------------------|
| Entity/attribute notation | `{Entity}.[Attribute]` | `{Entity}.[Attribute]` — same |
| Boolean values | `1` / `0` (integer) | `1` / `0` (integer — same) |
| Select all columns | `SELECT * FROM {Entity}` | `SELECT {Entity}.* FROM {Entity}` — must qualify |
| Current date/time | `GETDATE()` | `NOW()` |
| Top N rows | `SELECT TOP N * FROM {Entity}` | `SELECT {Entity}.* FROM {Entity} LIMIT N` |
| Random rows | `ORDER BY NEWID()` | `ORDER BY RANDOM()` |
| Null coalesce | `ISNULL(x, default)` | `COALESCE(x, default)` |
| String concat | `'a' + 'b'` | `'a' \|\| 'b'` |
| Date diff | `DATEDIFF(day, d1, d2)` | `d2 - d1` or `EXTRACT(...)` |
| Substring | `SUBSTRING(s, start, len)` | `SUBSTRING(s FROM start FOR len)` |
| Type cast | `CAST(x AS INT)` | `CAST(x AS INTEGER)` or `x::integer` |
| IF/ELSE in query | `IIF(cond, t, f)` | `CASE WHEN cond THEN t ELSE f END` |
| String length | `LEN(s)` | `LENGTH(s)` |
| Trim | `LTRIM(RTRIM(s))` | `TRIM(s)` |
| LIKE (case-insensitive) | `{Entity}.[Name] LIKE '%val%'` | `caseaccent_normalize({Entity}.[Name] collate "default") LIKE caseaccent_normalize('%val%')` — bare LIKE fails on text columns |
| Pagination | `OFFSET N ROWS FETCH NEXT M ROWS ONLY` | `LIMIT M OFFSET N` |
| INSERT column list | `INSERT INTO {Entity} ({Entity}.[Attr1])` | Internal: `INSERT INTO {Entity} ([Attr1])` — no prefix. External (ANSI-92): `INSERT INTO {Entity} ({Entity}.[Attr1])` — qualify with entity prefix (recommended) |
| UPDATE SET clause | `UPDATE {Entity} SET {Entity}.[Attr] = val` | `UPDATE {Entity} SET [Attr] = val` — no entity prefix in SET |
| UPSERT | `MERGE INTO ... USING ...` | `UPSERT INTO {Entity} ({Entity}.[Id], {Entity}.[Attr]) VALUES (@Id, @Value)` — requires non-generated PK; never returns a result |

## Common Query Patterns

Read `references/query-patterns.md` whenever you need example queries — CRUD (create, read, update, delete), aggregation, pagination, subqueries, and CTEs for both O11 and ODC.

## Parameters

Always use parameters (`@ParamName`) for input values — NEVER concatenate user input into SQL strings. OutSystems enforces this in Advanced SQL but ensure it in any generated queries.

```sql
-- CORRECT (O11)
WHERE {Entity}.[Name] LIKE '%' + @SearchTerm + '%'

-- CORRECT (ODC) — LIKE on text columns requires caseaccent_normalize
WHERE caseaccent_normalize({Entity}.[Name] collate "default") LIKE caseaccent_normalize('%' || @SearchTerm || '%')

-- WRONG (SQL injection risk)
WHERE {Entity}.[Name] LIKE '%' + 'user input here' + '%'
```

## OutSystems Aggregates vs Advanced SQL

Prefer **Aggregates** (visual query builder) for simple queries:
- Single entity or simple joins
- Standard filters, sorting, pagination
- Calculated attributes

Use **Advanced SQL** when you need:
- Complex joins (3+ entities, self-joins)
- CTEs, window functions, recursive queries
- UNION / INTERSECT / EXCEPT
- Database-specific functions
- Bulk operations
- Performance-critical queries with hints

## Validation

After writing a SQL query, verify:
1. All input values use `@ParamName` parameters — no string concatenation of user input
2. `Max Records` is explicitly set on the Advanced SQL node (never leave it unlimited)
3. The Output Structure is defined and its attributes match the SELECT column names
4. Entity/attribute references use `{Entity}.[Attribute]` notation for **both O11 and ODC** — ODC uses ANSI-92 syntax, not raw PostgreSQL in SQL nodes
5. Reserved words (`Order`, `User`, `Group`, `Table`) — `{Entity}` notation handles escaping automatically in ODC SQL nodes; no manual quoting needed

If any check fails, fix the query before presenting the output.

## Gotchas

1. **OutSystems Nulls**: In O11, `NullTextIdentifier()`, `NullDate()`, etc. map to NULL in SQL. In ODC, use standard NULL comparisons.
2. **Max Records**: Always set `Max Records` on Advanced SQL queries. Default is unlimited — this can cause performance issues.
3. **Output Structure**: Advanced SQL queries must have an Output Structure defined. The column names in SELECT must match the Output Structure attributes.
4. **Test Queries**: O11 allows testing SQL in Service Studio. ODC requires deployment to test. Always validate syntax for the target platform.
5. **Reserved Words and `{Entity}` notation**: `{Order}`, `{User}`, `{Group}` are automatically escaped by the ODC SQL node runtime — no manual quoting. However, the **Flowmo `.advance.sql` parser** translates these to raw PostgreSQL for local testing, where reserved words must be quoted. The parser handles `{User}` → `"user"` automatically. If you write raw SQL (non-`.advance.sql`), you must quote manually:

   ```sql
   -- .advance.sql (parser handles it automatically)
   SELECT {User}.[Id], {User}.[Name], {User}.[Email]
   FROM {User}
   WHERE {User}.[Id] = @UserId
   -- Parsed to: SELECT "user".id, "user".name, "user".email FROM "user" WHERE "user".id = $1

   -- Raw SQL — must quote manually
   SELECT u.id, u.name, u.email
   FROM "user" u
   WHERE u.id = $1
   ```

   **OutSystems User entity fields:**

   | OutSystems Attribute | Type | Local column |
   |---|---|---|
   | `Id` | Text (GUID) | `id TEXT` |
   | `Name` | Text | `name TEXT` |
   | `Email` | Text  | `email TEXT` |
   | `PhotoUrl` | Text | `photo_url TEXT` |
   | `Username` | Text | `username TEXT` |

   In Flowmo, if your project has a dedicated user table, create it directly as `"user"`:

   ```sql
   -- Simple standalone user table
   CREATE TABLE "user" (
     id         TEXT    PRIMARY KEY,  -- OutSystems GUID (e.g. 'user-001')
     name       TEXT    NOT NULL,
     email      TEXT    NOT NULL,
     photo_url  TEXT,
     username   TEXT    NOT NULL,
     is_active  INTEGER NOT NULL DEFAULT 1
   );
   ```

6. **`SELECT *` is invalid in ODC SQL nodes** — always qualify as `SELECT {Entity}.*`. Bare `SELECT *` causes a runtime error.
7. **LIKE in ODC requires `caseaccent_normalize()`** — ODC uses non-deterministic collations for all text columns. Bare `LIKE` pattern matching on text columns fails at runtime with a collation error. Always wrap both sides:
   ```sql
   WHERE caseaccent_normalize({Entity}.[Name] collate "default") LIKE caseaccent_normalize('%' || @SearchTerm || '%')
   ```
   The `collate "default"` part is only needed when applying the pattern to a column (non-deterministic collation). You can omit it for literal-only patterns:
   ```sql
   WHERE caseaccent_normalize({Entity}.[Name] collate "default") LIKE caseaccent_normalize('%something%')
   ```
8. **Date Comparisons**: O11 uses `BETWEEN` or `DATEDIFF`. ODC prefers range comparisons with `>=` and `<` or `EXTRACT()`.
9. **Aggregate Functions in WHERE**: Use `HAVING` for aggregate conditions — `WHERE` runs before `GROUP BY`.
10. **Index Awareness**: Filter on indexed attributes when possible. In O11 check Query Analyzer; in ODC check the database logs.
11. **Never use `/* */` block comments in ODC Advanced SQL — use `--` line comments only.** ODC's Advanced Query preprocessor does a text-level `{Entity}`/`[Attribute]` substitution over the whole query text; it is not a real SQL-aware comment stripper and can mishandle `/* */` blocks (especially large multi-line ones, or ones whose prose mentions `{EntityName}`). This can leave a literal `{` in the SQL actually sent to the database, which Postgres then rejects with `OS-BERT-60407: syntax error at or near "{"` — even though the exact same query text runs cleanly when tested directly (e.g. via the Developer Dashboard's Test SQL tool, which just runs raw Postgres). Confirmed on ssr-test: a `GetInvoiceHierarchyJson_Edit.advance.sql` with a `/* */` doc-header threw this error when deployed; converting every comment (including large doc-header blocks) to `--` fixed it, with no logic change. Applies to header/doc comments and inline comments alike — before considering any `.advance.sql` change done, confirm the file contains no `/*` or `*/`.

---

## ODC to Flowmo Schema

Read `references/odc-schema.md` when generating a local Flowmo PostgreSQL schema (`database/schema.os.sql`) from an ODC entity model. It covers the type mapping table, conversion rules, a full example, and the `db:setup` workflow.

If you have a live OutSystems MCP connection this session (check for OutSystems MCP tools in your own tool list — named `mcp__outsystems__*` or `outsystems_*` depending on your harness — or ask the user to run `/mcp`), read `references/mcp-schema-sync.md` instead for a fully automated version of this loop — it also covers pushing validated local work back to OutSystems and verifying it landed, not just pulling the schema.

---

## Testing Queries with the Flowmo CLI

Read `references/flowmo-cli.md` when running or testing queries locally with the Flowmo CLI. It covers `db:setup`/`db:seed`/`db:reset`, `db:query` usage, flags, parameter types, and a workflow checklist.

`db:query` tests against the local PGLite mirror; to test against the live tenant DB, load `outsystems-live-testing`.