SQM - Structured Query Model for Java

Description

SQM (Structured Query Model) is a Java framework for representing SQL as a typed immutable model and running end-to-end SQL pipelines. It supports parse, validate, transform/rewrite, render, serialize, and runtime policy enforcement across multiple dialects and transports.

Live dialect confidence is backed by real-engine execution coverage: shipped PostgreSQL, MySQL, and SQL Server syntax is exercised in Docker/Testcontainers suites against the actual database engines, not only parser/renderer round trips.

Wiki

Project wiki with feature guides and examples: https://github.com/icher-g/sqm/wiki

Direct wiki guides:

• SQL Server syntax guide: wiki-src/SQL-Server-Dialect.md
• Unsupported features and scope boundaries: wiki-src/Unsupported-Features.md

Contributor Rules

Repository development rules for contributors and coding agents are defined in AGENTS.md.

---

Features

• Typed immutable SQL model - composable AST for statements (queries + DML), expressions, predicates, joins, typed hints, and dialect-specific nodes.
• Dialect support - ANSI + PostgreSQL + MySQL + SQL Server parser/renderer/spec implementations.
• Validation framework - schema-aware statement validation with configurable limits and access policies (principal/tenant aware).
• Rewrite and normalization pipeline - built-in and custom rewrite rules (limit injection, qualification, canonicalization, tenant predicate, etc.).
• SQL transpilation - source-to-target PostgreSQL/MySQL/SQL Server slice with exact, approximate, and unsupported diagnostics, including warning-based dropping of dialect-local hints across non-native targets.
• Middleware decision engine - analyze/enforce/explain workflow with guardrails, telemetry, auditing, and flow control.
• Transport hosts - REST and MCP runtimes for externalized middleware usage.
• DSL and code generation - fluent SQL construction plus SQL-file-to-Java generation for queries and DML statements.
• JSON support - model serialization/deserialization via Jackson mixins.
• Extensibility - registries/contracts for dialects, functions, validation rules, and rewrite strategies.

---

Architecture Overview

Base SQL model flow:

       ┌─────────────┐
       │   SQL Text  │
       └──────┬──────┘
              │ parse
              ▼
       ┌─────────────┐
       │    Model    │ <───> JSON / DSL
       └──────┬──────┘
              │ transform / rewrite / optimize
              ▼
       ┌─────────────┐
       │    Model    │
       └──────┬──────┘
              │ render
              ▼
       ┌─────────────┐
       │   SQL Text  │
       └─────────────┘

Middleware decision flow:

       ┌─────────────┐
       │   SQL Text  │
       └──────┬──────┘
              │ parse
              ▼
       ┌─────────────┐
       │    Model    │
       └──────┬──────┘
              │ validate
              ▼
       ┌─────────────┐
       │ Guardrails  │
       │ + Policies  │
       └──────┬──────┘
              │ transform / rewrite / optimize
              ▼
       ┌─────────────┐
       │  Rewritten  │
       │    Model    │
       └──────┬──────┘
              │ render
              ▼
       ┌─────────────┐
       │   SQL Text  │
       │ + Bind Data │
       └──────┬──────┘
              │ decide
              ▼
       ┌─────────────┐
       │  Decision   │
       │ allow/deny/ │
       │   rewrite   │
       └─────────────┘

Core components:

• Model (sqm-core) - AST nodes, visitor/transformer infrastructure, match API, DSL primitives.
• Parsers (sqm-parser-*) - dialect parsing into model nodes.
• Renderers (sqm-render-*) - dialect rendering from model nodes.
• Validation (sqm-validate)* - schema/function/access-policy semantic checks.
• Transpilation (sqm-transpile) - source-to-target dialect conversion with rule diagnostics.
• Catalog (sqm-catalog)* - schema sources (JSON/JDBC) and type mapping.
• Control/Middleware (sqm-control, sqm-middleware-*) - decision engine, rewrites, runtime hosts, telemetry/audit.
• JSON (sqm-json) - serialization mixins.
• Codegen (sqm-codegen)* - SQL file code generation and plugin integration.
• Integration tests (sqm-it) - cross-module/runtime verification.

---

Model Hierarchy

SQM defines a rich, type-safe model (AST) to represent SQL statements internally. This model is shared across DSL, parser, renderer, validator, transform/rewrite, middleware, JSON, and codegen modules.

Model presence and dialect support are intentionally different concepts:

• a node in sqm-core is representable by the framework
• parser, renderer, validation, and transpilation support remain dialect-specific
• support details and ambiguous node notes live in docs/model/MODEL.md

➡️ View the full hierarchy in docs/model/MODEL.md

DML Statement Support

DML foundation is delivered with a statement-level model and ANSI baseline parser/renderer support for:

• INSERT INTO ... VALUES (...) and INSERT INTO ... SELECT ...
• UPDATE ... SET ... [WHERE ...]
• DELETE FROM ... [WHERE ...]

PostgreSQL DML extensions are delivered for:

• MERGE (PostgreSQL 15+)
• SQL Server MERGE with TOP (...) and OUTPUT
• INSERT ... RETURNING
• UPDATE ... FROM
• DELETE ... USING
• INSERT ... ON CONFLICT DO NOTHING / DO UPDATE
• writable CTE INSERT ... RETURNING, UPDATE ... RETURNING, and DELETE ... RETURNING shapes

MySQL DML extensions are delivered for:

• INSERT IGNORE
• INSERT ... ON DUPLICATE KEY UPDATE
  • REPLACE INTO
  • joined UPDATE
  • qualified joined-UPDATE assignment targets
  • optimizer hint comments on SELECT, UPDATE, and DELETE
  • canonical DELETE FROM ... USING ... JOIN ...
  • alias and index-hint canonicalization for prioritized joined DML edge cases
  • STRAIGHT_JOIN

Current scope boundary:

• Baseline DML is cross-dialect through ANSI base components.
• PostgreSQL writable CTE coverage includes INSERT ... RETURNING, UPDATE ... RETURNING, and DELETE ... RETURNING.
• MySQL RETURNING remains capability-gated and unsupported for current supported MySQL versions.
• MySQL joined DML support includes qualified assignment targets such as SET u.name = ....
• SQL Server baseline support includes query + shared-model DML with:
  • bracket-quoted identifiers
  • TOP, TOP ... PERCENT, TOP ... WITH TIES, and OFFSET/FETCH
  • table hints WITH (NOLOCK), WITH (UPDLOCK), and WITH (HOLDLOCK) in supported statement contexts
  • first-wave SQL Server functions such as LEN, DATEADD, DATEDIFF, ISNULL, and STRING_AGG
  • baseline INSERT, UPDATE, and DELETE
  • OUTPUT and OUTPUT ... INTO through shared ResultClause support
• SQL Server MERGE support includes:
  • WHEN MATCHED update/delete
  • WHEN NOT MATCHED insert
  • WHEN NOT MATCHED BY SOURCE update/delete
  • clause predicates (WHEN ... AND ...)
  • OUTPUT
  • TOP (...)
  • TOP (...) PERCENT

Typed hint modeling is also delivered across the shared model:

• statement-owned hints are exposed structurally through Statement.hints()
• table-owned hints are exposed structurally through Table.hints()
• DSL and codegen use typed hint(...), statementHint(...), and tableHint(...) construction
• cross-dialect transpilation currently drops vendor-native MySQL and SQL Server hints with warnings rather than trying to translate them semantically

PostgreSQL DML Example

var parseCtx = ParseContext.of(new PostgresSpecs());
var renderCtx = RenderContext.of(new PostgresDialect());

var statement = parseCtx.parse(Statement.class, """
    WITH ins AS (
        INSERT INTO users (name)
        VALUES ('alice')
        RETURNING id
    )
    SELECT id FROM ins
    """).value();

var sql = renderCtx.render(statement).sql();
System.out.println(sql);

Supported PostgreSQL DML examples include:

MERGE INTO users AS u USING incoming_users AS s ON u.id = s.id WHEN MATCHED AND s.active = true THEN UPDATE SET name = s.name WHEN NOT MATCHED BY SOURCE AND u.name IS NOT NULL THEN DO NOTHING WHEN NOT MATCHED AND s.name IS NOT NULL THEN INSERT (id, name) VALUES (s.id, s.name) RETURNING u.id
INSERT INTO users (name) VALUES ('alice') RETURNING id
UPDATE users u SET name = src.name FROM source_users src WHERE u.id = src.id
DELETE FROM users USING source_users src WHERE users.id = src.id
INSERT INTO users (id, name) VALUES (1, 'alice') ON CONFLICT (id) DO UPDATE SET name = 'alice2'
WITH ins AS ( INSERT INTO users (name) VALUES ('alice') RETURNING id ) SELECT id FROM ins
WITH upd AS ( UPDATE users SET name = 'alice' WHERE id = 1 RETURNING id ) SELECT id FROM upd
WITH del AS ( DELETE FROM users WHERE id = 1 RETURNING id ) SELECT id FROM del

Current PostgreSQL MERGE scope:

• supported: WHEN MATCHED update/delete, WHEN NOT MATCHED insert, WHEN NOT MATCHED BY SOURCE update/delete, clause predicates (WHEN ... AND ...), DO NOTHING, and RETURNING

MySQL DML Example

var parseCtx = ParseContext.of(new MySqlSpecs());
var renderCtx = RenderContext.of(new MySqlDialect());

var statement = parseCtx.parse(Statement.class, """
    UPDATE users USE INDEX (idx_users_name) AS u
    INNER JOIN orders FORCE INDEX FOR JOIN (idx_orders_user) AS o ON u.id = o.user_id
    SET name = 'alice'
    WHERE o.state = 'closed'
    """).value();

var sql