# Repository: CRUD and Finders

# Repository: CRUD and Finders

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

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

- [src/index.ts](src/index.ts)
- [src/repository/repository.ts](src/repository/repository.ts)

</details>



The `Repository` class is the central engine for data access in the Primebrick DAL. It provides a type-safe, high-level API for interacting with PostgreSQL tables defined via Entity decorators. While the `Dal` class manages connection pools and global configuration, it delegates all specific data operations—finding, writing, and bulk processing—to the `Repository` [[src/dal/dal.ts:208-212](), [src/repository/repository.ts:148-150]()].

### Core Abstractions

The `Repository` operates on a `Queryable` abstraction, which allows it to run queries against either a standard connection pool (`pg.Pool`) or a specific client within a transaction (`pg.PoolClient`) [[src/repository/repository.ts:43-43]()].

| Component | Responsibility |
| :--- | :--- |
| **Delegation** | The `Dal` instance provides helper methods (e.g., `dal.repo.find`) that proxy calls to an internal `Repository` instance [[src/dal/dal.ts:208-220]()]. |
| **Metadata Integration** | Uses `getEntityPersistenceMeta` to map class properties to SQL columns, identifying Primary Keys and auditable fields at runtime [[src/repository/repository.ts:159-161]()]. |
| **Query Building** | Leverages the Query DSL and `buildSelectQuery` to generate parameterized SQL, ensuring protection against SQL injection [[src/repository/repository.ts:163-167]()]. |

### System Architecture: From Dal to Database

The following diagram illustrates how the `Repository` acts as the bridge between the high-level `Dal` gateway and the low-level SQL execution.

**Data Flow Architecture**
```mermaid
graph TD
  subgraph "Application Space"
    DAL["Dal Singleton"]
    REPO["Repository Instance"]
  end

  subgraph "Code Entity Space"
    META["Entity Metadata (WeakMap)"]
    BUILDER["buildSelectQuery()"]
    COERCION["jsValueToPgParam()"]
  end

  subgraph "Infrastructure"
    POOL["pg.Pool / PoolClient"]
    DB[("PostgreSQL")]
  end

  DAL -- "delegates to" --> REPO
  REPO -- "inspects" --> META
  REPO -- "configures" --> BUILDER
  REPO -- "transforms" --> COERCION
  REPO -- "executes via .query()" --> POOL
  POOL -- "SQL + Params" --> DB
```
Sources: [src/dal/dal.ts:208-220](), [src/repository/repository.ts:148-150](), [src/repository/repository.ts:163-169](), [src/meta/entity-meta.ts:114-118]()

---

### Read Operations
Read operations are designed around the "Fail Fast" principle. By default, single-row finders like `findById` and `findByUUID` will throw a `NotFoundError` if the record is missing, though this can be toggled via `FindOptions` [[src/repository/repository.ts:158-175]()].

Key capabilities include:
*   **Filters and Joins**: Full support for the Query DSL for complex WHERE clauses and relations.
*   **Soft-Delete Awareness**: Automatically filters out records where `deleted_at` is set, unless `deletedRecords: 'INCLUDE'` is specified [[src/repository/repository.ts:166-166]()].
*   **Streaming**: For large datasets, `findAll` can return an `AsyncIterable` using `pg-query-stream` to prevent memory exhaustion [[src/repository/repository.ts:248-255]()].

For details, see [Read Operations](#4.1).

### Write Operations
Write operations handle the lifecycle of a single record, including automatic audit stamping and versioning. The DAL follows a strict "RETURNING *" policy, ensuring that the JavaScript object returned by an `add` or `update` call perfectly reflects the state of the database (including serial IDs and default values) [[src/repository/repository.ts:360-365]()].

Key capabilities include:
*   **Audit Stamping**: Automatically populates `created_by`, `updated_by`, and `deleted_at` based on the provided `actorId` [[src/repository/repository.ts:333-350]()].
*   **Upsert**: Uses PostgreSQL `INSERT ... ON CONFLICT` logic to handle atomic "create or update" scenarios [[src/repository/repository.ts:391-410]()].
*   **Soft Delete**: The `delete` method performs an `UPDATE` to set a deletion timestamp rather than a physical `DELETE`, preserving data for the audit trail [[src/repository/repository.ts:517-530]()].

For details, see [Write Operations](#4.2).

### Bulk Operations
For high-performance scenarios, the `Repository` provides optimized methods for handling thousands of rows. These methods bypass the overhead of single-row processing while maintaining safety limits [[src/repository/repository.ts:634-640]()].

Key capabilities include:
*   **Parameter Limit Protection**: `addMany` automatically batches inserts to stay under the PostgreSQL limit of 65,535 parameters [[src/repository/repository.ts:142-146]()].
*   **Temp Table Strategy**: `updateMany` utilizes a `CREATE TEMP TABLE ... ON COMMIT DROP` pattern. Data is batched into the temp table, followed by a single bulk `UPDATE` join, significantly reducing round-trips [[src/repository/repository.ts:771-790]()].

For details, see [Bulk Operations](#4.3).

---

### Entity to SQL Mapping

The `Repository` uses the `EntityPersistenceMeta` to translate between the "Natural Language Space" of TypeScript classes and the "Code Entity Space" of the database schema.

**Mapping Logic**
```mermaid
graph LR
  subgraph "TypeScript Class"
    Prop["@Column() propertyKey"]
    Entity["@Entity() ClassName"]
  end

  subgraph "Repository Translation"
    M1["getColumnName()"]
    M2["getQualifiedTableName()"]
  end

  subgraph "PostgreSQL Schema"
    Col["sql_column_name"]
    Tab["schema.table_name"]
  end

  Prop -- "meta lookup" --> M1
  M1 -- "renders" --> Col
  Entity -- "meta lookup" --> M2
  M2 -- "renders" --> Tab
```
Sources: [src/meta/entity-meta.ts:153-162](), [src/meta/entity-meta.ts:175-182](), [src/repository/repository.ts:5-11]()

### Error Hierarchy
The repository communicates state via a specific error hierarchy defined in `src/errors/errors.ts`. This allows consumers to distinguish between "Record Not Found" and "Database Connection Timeout" without parsing string messages [[src/repository/repository.ts:40-40]()].

| Error Class | Trigger |
| :--- | :--- |
| `NotFoundError` | Thrown by finders when `throwIfNotFound` is true and 0 rows return [[src/errors/errors.ts:16-20]()]. |
| `MultipleRowsError` | Thrown when a single-row finder finds >1 record [[src/errors/errors.ts:22-26]()]. |
| `ValidationError` | Thrown when required fields (like `matchBy` values) are missing [[src/errors/errors.ts:34-38]()]. |
| `UnknownColumnError` | Thrown if a query refers to a property not decorated with `@Column` [[src/errors/errors.ts:28-32]()]. |

Sources: [src/repository/repository.ts:40-40](), [src/errors/errors.ts:1-40]()

---