Skip to content

Support fieldExpressions for dynamic SQL mappingΒ #51

@Zorin95670

Description

@Zorin95670

πŸ“Œ Context

We already have an entityMapping mechanism to map entity fields after retrieval.
However, we currently lack a way to define dynamic SQL expressions with parameters at the configuration level.

This becomes necessary for cases like PostgreSQL range types (tstzrange) or other computed fields where values must be built dynamically at query time.


🎯 Goal

Introduce a new configuration block: fieldExpressions, allowing us to define SQL expressions with parameter binding.

Example:

provider: POSTGRES
tasks:
access:
  patch:
    table: test_table_1
    assignmentFieldExpressions:
      period:
        expression: 'x({0}, {1})'
        parameters:
          - '{{ entity.startDate }}'
          - '{{ entity.endDate }}'
        dependsOn: ['startDate', 'endDate']
attributes:
  - name: id
    type: Integer
    required: true
    access:
      primaryKey: true
      column: id
  - name: startDate
    type: Date
    access:
      expression: lower(period)
  - name: endDate
    type: Date
    access:
      expression: upper(period)

βš™οΈ Behavior

πŸ”Ή General

  • expression is a SQL template using jOOQ-style indexed placeholders ({0}, {1}, ...)

  • parameters are resolved at runtime (e.g., via existing templating system)

    • Each parameter is converted into:
      • DSL.val(...) for values
      • or DSL.field(...) if it's a SQL expression (optional enhancement)
  • dependsOn List of attributes needed for the expression to be used

  • access :

    • expression : expression to access the data store if needed (with tstzrange for example)

πŸ“ Scope of application

This feature is applied to all operations except:

  • ❌ delete
  • ❌ findById
  • ❌ findAll

βœ… Supported operations

  • create
  • update
  • patch

🧩 Implementation notes

  • Build expressions using:
DSL.field("tstzrange({0}, {1}, {2})", SQLDataType.OTHER, fields...)
  • Dynamically construct placeholders depending on the number of parameters
  • Reuse existing template resolution logic ({{entity.xxx}})
  • Ensure proper binding (avoid raw SQL injection)

⚠️ Constraints

  • Must remain fully compatible with jOOQ parameter binding
  • No string concatenation for values
  • Expression must remain database-compatible (PostgreSQL)

Metadata

Metadata

Assignees

Labels

enhancementNew feature or request

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions