T-SQL to PostgreSQL translator accumulates edge cases with no test coverage

[ccisnedev]

Problem

The modular_api GraphQL runtime internally generates SQL using SQL Server (T-SQL) syntax. When PostgreSQL is used as the backing database, a translator must convert this T-SQL to valid PostgreSQL before execution.

This translator currently handles:

• Bracket-quoted identifiers [name] → unquoted or double-quoted
• SELECT TOP (1) → LIMIT 1
• OFFSET @p ROWS FETCH NEXT @p ROWS ONLY → LIMIT / OFFSET
• COUNT_BIG(1) → COUNT(*)
• Named parameters @p0 → positional $1
• String concatenation + → || in LIKE expressions

Each of these was added reactively when a pattern was discovered at runtime. There is no comprehensive test suite for the translator, and no inventory of patterns the GraphQL runtime can generate.

As the GraphQL runtime evolves and generates new SQL patterns, the translator will silently fail on any pattern it does not cover. The failure mode is a runtime exception or wrong query results, not a build-time error.

Impact

• Every new GraphQL feature (new filter operators, aggregations, subqueries) is a potential silent breakage for PostgreSQL users
• The translator is not documented — it is not clear which T-SQL patterns are covered and which are not
• No regression tests mean fixes for one pattern can break another

Proposed solution

1. Document the complete set of T-SQL patterns the GraphQL runtime can emit (exhaustive list, not examples)
2. Write a unit test suite for the translator covering all known patterns with exact input/output assertions
3. Long-term: make the runtime dialect-aware so it generates SQL for the target database directly, eliminating the translation layer entirely

The long-term solution is the clean architectural fix. The test suite is the immediate mitigation.

Related

• Affects: PostgreSQL SDK (modular_api_postgres) and any consumer using PostgreSQL as the GraphQL read backend
• The translator currently lives in the consuming project (PostgresReadExecutor) — it should be part of modular_api_postgres with its own test suite