# Query DSL and SQL Builder

# Query DSL and SQL Builder

<details>
<summary>Relevant source files</summary>

The following files were used as context for generating this wiki page:

- [src/query/dsl.ts](src/query/dsl.ts)
- [src/query/query-builder.ts](src/query/query-builder.ts)
- [src/types/types.ts](src/types/types.ts)

</details>



The `@primebrick/dal-pg` package provides a functional Domain Specific Language (DSL) for constructing type-safe database queries. This DSL abstracts the complexities of SQL syntax while maintaining a strong link to the underlying Entity metadata. The `buildSelectQuery` engine transforms these DSL expressions into parameterized `SqlQuery` objects, ensuring protection against SQL injection through strict identifier validation and parameterization.

### Natural Language to Code Entity Space: DSL Components

This diagram maps the conceptual query components used in natural language to their corresponding classes and functions in the `src/query/dsl.ts` file.

**Query DSL Mapping**
```mermaid
graph TD
    subgraph "Natural Language Space"
        NL_Field["'The name field of the User entity'"]
        NL_Filter["'Where age is greater than 18'"]
        NL_Sort["'Sort by created_at descending'"]
        NL_Join["'Join User with Profile on ID'"]
        NL_Project["'Select only the email column'"]
    end

    subgraph "Code Entity Space (src/query/dsl.ts)"
        DSL_Field["field(User, 'name')"]
        DSL_Filter["Filter.fieldValue(...)"]
        DSL_Sort["Sort.by(...)"]
        DSL_Join["Join.on(...)"]
        DSL_Project["Project.field(...)"]
    end

    NL_Field --> DSL_Field
    NL_Filter --> DSL_Filter
    NL_Sort --> DSL_Sort
    NL_Join --> DSL_Join
    NL_Project --> DSL_Project

    DSL_Field -- "Returns" --> FieldRef["FieldRef<T, K>"]
    DSL_Filter -- "Returns" --> FilterExpr["FilterExpr"]
    DSL_Sort -- "Returns" --> SortingExpr["SortingExpr"]
    DSL_Join -- "Returns" --> JoinExpr["JoinExpr"]
    DSL_Project -- "Returns" --> FieldProjector["FieldProjector"]
```
**Sources:** [src/query/dsl.ts:23-124]()

---

## Functional Query DSL

The DSL is designed to be composable and type-safe by leveraging TypeScript generics to ensure that fields referenced in queries actually exist on the target entities.

### Field References
The foundation of the DSL is the `field()` function. it creates a `FieldRef` which binds a specific property of an `EntityClass` to its metadata [src/query/dsl.ts:28-33]().

### Filtering (`Filter`)
The `Filter` object provides static methods for creating `FilterExpr` objects:
*   `fieldValue(left, op, right)`: Compares a field to a literal value [src/query/dsl.ts:64-71]().
*   `fieldField(left, op, right)`: Compares two fields (e.g., for self-joins or cross-table comparisons) [src/query/dsl.ts:72-79]().
*   `raw(left, op, right)`: Allows raw SQL fragments (use with caution) [src/query/dsl.ts:80-82]().
*   `group(filters, operand)`: Groups multiple filters with `AND` or `OR` logic [src/query/dsl.ts:83-85]().

### Sorting, Joining, and Projection
*   **Sort**: `Sort.by(field, direction)` defines the `ORDER BY` clause [src/query/dsl.ts:89-93]().
*   **Join**: `Join.on(right, left, type, options)` defines table relationships. It supports `INNER`, `LEFT`, and `RIGHT` joins, along with explicit type casting [src/query/dsl.ts:102-111]().
*   **Project**: `Project.field(field, alias)` or `Project.expr(sql, alias)` defines which columns are returned. If no projection is provided, the builder defaults to all persisted columns defined in the entity metadata [src/query/dsl.ts:117-124]().

---

## SQL Builder Engine

The `buildSelectQuery` function (and its internal helpers in `src/query/query-builder.ts`) acts as the compiler that translates DSL objects into a `SqlQuery` containing a string `text` and an array of `values` [src/query/query-builder.ts:7-7]().

### Data Flow: DSL to SQL
The following diagram illustrates how the `ParamWriter` and various render functions cooperate to produce a safe, parameterized SQL statement.

**SQL Generation Pipeline**
```mermaid
graph LR
    subgraph "Input: DSL Objects"
        E["EntityClass"]
        F["FilterExpr[]"]
        J["JoinExpr[]"]
    end

    subgraph "Processing: src/query/query-builder.ts"
        PW["ParamWriter"]
        RP["renderProjection()"]
        RJ["renderJoins()"]
        RW["renderWhere()"]
        RO["renderOrderBy()"]
    end

    subgraph "Output: SqlQuery"
        SQL["text: string"]
        VALS["values: unknown[]"]
    end

    E --> RP
    E --> RW
    F --> RW
    J --> RJ
    J --> RP

    RW -- "add(value)" --> PW
    RJ -- "uses metadata" --> RP
    
    RP --> SQL
    RJ --> SQL
    RW --> SQL
    RO --> SQL
    PW --> VALS
```
**Sources:** [src/query/query-builder.ts:41-47](), [src/query/query-builder.ts:49-132](), [src/query/query-builder.ts:182-200]()

### Key Rendering Logic

| Function | Responsibility | Implementation Detail |
| :--- | :--- | :--- |
| `renderProjection` | Generates the `SELECT` clause. | Maps TS property names to SQL column names using `AS` aliases [src/query/query-builder.ts:49-89](). |
| `renderJoins` | Generates `JOIN ... ON` clauses. | Handles automatic type casting (e.g., `::uuid`) and includes regex guards for UUID joins [src/query/query-builder.ts:91-132](). |
| `renderWhere` | Generates the `WHERE` clause. | Injects soft-delete filters (`deleted_at IS NULL`) based on `WithDeletedRecords` mode [src/query/query-builder.ts:182-195](). |
| `renderFilterExpr` | Recursively processes filters. | Handles complex operators like `ILIKE`, `BETWEEN`, `IN`, and `IS NULL` [src/query/query-builder.ts:134-180](). |
| `ParamWriter` | Manages query parameters. | Collects values into an array and returns `$1`, `$2`, etc. placeholders [src/query/query-builder.ts:41-47](). |

---

## Security and Injection Safety

The builder employs a "Strict Identifier" strategy to prevent SQL injection via table or column names, which cannot be parameterized in standard PostgreSQL.

### Identifier Validation
Every identifier (table names, column names, aliases) is passed through `assertValidIdentPart(s, what)`. This function enforces a strict regex: `/^[A-Za-z_][A-Za-z0-9_]*$/` [src/query/query-builder.ts:9-15](). If an identifier contains spaces, semicolons, or comments, the system throws an error immediately.

### Quoting
The `quoteIdent(ident)` function ensures that valid identifiers are wrapped in double quotes (e.g., `"user_table"`) to handle PostgreSQL case-sensitivity and reserved keyword conflicts safely [src/query/query-builder.ts:17-20]().

### UUID Safety in Joins
When joining on UUID fields that might be stored as strings in one of the tables, the builder automatically injects a regex check: `~ '^[0-9a-fA-F-]{36}$'`. This prevents PostgreSQL from throwing a "badly formed hexadecimal UUID" error when attempting to compare a non-UUID string to a UUID column [src/query/query-builder.ts:123-127]().

**Sources:** [src/query/query-builder.ts:9-34](), [src/query/query-builder.ts:123-127](), [src/query/dsl.ts:1-125]()

---