Copy page

DAL Library

Query DSL and SQL Builder

Query DSL and SQL Builder

Relevant source files

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

• src/query/dsl.ts
• src/query/query-builder.ts
• src/types/types.ts

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

Code
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

Code
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

---