Copy page

DAL Library

Read Operations

Read Operations

Relevant source files

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

• src/repository/repository.ts
• src/types/types.ts
• test/repository-crud.test.ts

The Repository class provides a comprehensive suite of finder methods designed to handle common data retrieval patterns. These operations leverage the underlying Query DSL to build parameterized SQL, ensuring type safety and protection against SQL injection. Read operations in the DAL are characterized by their strict handling of soft-deleted records and configurable error behavior for missing data.

Finder Methods

The repository implements several specialized finder methods to cater to different lookup requirements, from primary key lookups to complex filtered sets.

Single Row Lookups

• findById: Retrieves a single record by its primary key (decorated with @Key). src/repository/repository.ts:153-176.
• findByUUID: Retrieves a single record by its uuid column. This method requires the entity to have a column physically named uuid. src/repository/repository.ts:178-204.
• find: Retrieves the first record matching a set of filters. src/repository/repository.ts:206-233.

Collection Lookups

• findAll: Returns an array of all matching records. If stream: true is passed in FindOptions, it returns an AsyncIterable instead. src/repository/repository.ts:235-263.
• findByPage: Executes a paginated query. It uses a window function (COUNT(*) OVER()) to calculate the total number of records matching the filters in a single round-trip. src/repository/repository.ts:265-300.
• count: Returns the total count of records matching the provided filters. src/repository/repository.ts:302-321.

Data Flow: findById Execution

The following diagram illustrates the flow from a high-level findById call down to the SQL execution via the pg driver.

Sequence: findById Implementation

Code
sequenceDiagram
    participant App as "Application Code"
    participant Repo as "Repository.ts"
    participant Meta as "Entity Metadata"
    participant QB as "Query Builder"
    participant DB as "pg.Pool / PoolClient"

    App->>Repo: findById(SimpleEntity, 123, options)
    Repo->>Meta: getEntityPersistenceMeta(SimpleEntity)
    Meta-->>Repo: meta (table_name, columns)
    Repo->>Repo: findPkColumn(meta)
    Repo->>QB: buildSelectQuery({ entity, filters: [PK = 123] })
    QB-->>Repo: { text: "SELECT ...", values: [123] }
    Repo->>DB: query(text, values)
    DB-->>Repo: { rows: [...] }
    Repo->>Repo: Validate rows (throwIfNotFound logic)
    Repo-->>App: Entity object or null

Sources: src/repository/repository.ts:153-176, src/query/query-builder.ts:32-75

FindOptions and Behavior

All read operations accept a configuration object that controls filtering, sorting, and visibility.

FindOptions Structure

Option	Type	Description
filters	FilterExpr[]	Array of DSL filters (e.g., Filter.fieldValue(...)). src/types/types.ts:25
sorting	SortingExpr[]	Array of sort instructions (e.g., Sort.by(...)). src/types/types.ts:26
joins	JoinExpr[]	Definitions for LEFT/INNER joins. src/types/types.ts:27
deletedRecords	WithDeletedRecords	Controls soft-delete visibility (EXCLUDED, ONLY, INCLUDED). src/types/types.ts:24
stream	boolean	If true, returns an AsyncIterable via QueryStream. src/types/types.ts:29

Soft-Delete Visibility Control

The deletedRecords option (type WithDeletedRecords) dictates how the query handles rows where the deleted_at column is non-null. src/types/types.ts:13.

• EXCLUDED (Default): Adds WHERE deleted_at IS NULL. test/repository-crud.test.ts:164-172.
• ONLY: Adds WHERE deleted_at IS NOT NULL. test/repository-crud.test.ts:174-184.
• INCLUDED: Does not add any filter on deleted_at, showing both active and deleted records. test/repository-crud.test.ts:186-195.

The throwIfNotFound Pattern

By default, findById, findByUUID, and find are configured with throwIfNotFound: true. src/repository/repository.ts:158, src/repository/repository.ts:183.

• If no row is found, a NotFoundError is thrown. src/repository/repository.ts:173.
• If more than one row is found for a unique lookup, a MultipleRowsError is thrown. src/repository/repository.ts:174.
• Setting throwIfNotFound: false causes the method to return null when no record exists. test/repository-crud.test.ts:90-95.

Sources: src/types/types.ts:12-36, src/repository/repository.ts:153-233, src/errors/errors.ts:1-20

Implementation Details

Pagination with Window Functions

The findByPage method implements a efficient pagination strategy. Instead of running two separate queries (one for data and one for the total count), it injects a window function into the projection.

Natural Language to Code: Pagination Logic

Code
graph TD
    A["Request Page 2 (Size 10)"] --> B["Repository.findByPage"]
    B --> C["buildSelectQuery"]
    C --> D["SQL: SELECT *, COUNT(*) OVER() as total_records"]
    D --> E["SQL: LIMIT 10 OFFSET 10"]
    E --> F["Execute Query"]
    F --> G["Extract total_records from first row"]
    G --> H["Return PaginatedEntity<T>"]

Sources: src/repository/repository.ts:265-300, src/query/query-builder.ts:68-73

Streaming Large Result Sets

When stream: true is passed to findAll, the repository bypasses the standard db.query (which buffers all rows in memory) and uses createStream. This returns an AsyncIterable that fetches rows in batches using a PostgreSQL cursor.

Code Mapping: Streaming Components

Component	Code Entity	Role
Stream Factory	createStream	Wraps pg-query-stream into a JS iterator. src/query/streaming.ts:14
Option Flag	FindOptions.stream	Triggers the streaming branch in findAll. src/types/types.ts:29
Repository Logic	Repository.findAll	Switches return type based on stream flag. src/repository/repository.ts:251-262

Sources: src/repository/repository.ts:235-263, src/query/streaming.ts:1-40

Usage Examples

Complex Filtered Find

Code
const users = await repo.findAll(UserEntity, null, {
  filters: [
    Filter.fieldValue(field(UserEntity, "status"), "=", "ACTIVE"),
    Filter.fieldValue(field(UserEntity, "age"), ">", 18)
  ],
  sorting: [Sort.by(field(UserEntity, "createdAt"), "DESC")],
  deletedRecords: "EXCLUDED"
});

Sources: test/repository-crud.test.ts:128-142, src/query/dsl.ts:100-150

Paginated Results

Code
const { entities, total_records } = await repo.findByPage(
  ProductEntity,
  { page: 1, limit: 20 },
  null,
  { deletedRecords: "INCLUDED" }
);

Sources: src/repository/repository.ts:265-300, test/repository-crud.test.ts:199-215

---