# Read Operations

# Read Operations

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

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

- [src/repository/repository.ts](src/repository/repository.ts)
- [src/types/types.ts](src/types/types.ts)
- [test/repository-crud.test.ts](test/repository-crud.test.ts)

</details>



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**
```mermaid
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**
```mermaid
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
```typescript
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
```typescript
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]()

---