---
title: Advanced Repository Features
description: Transactions, hidden properties, and performance optimization
difficulty: intermediate
---

# Advanced Repository Features

Transactions, hidden properties, performance optimization, type inference, and debugging.

## Prerequisites

Before reading this document, you should understand:

- [Basic Repository Operations](./index.md) - CRUD operations and basic filtering
- [Filter System](../filter-system/) - Advanced query building
- Database transactions - ACID properties and isolation levels
- TypeScript advanced types - Utility types and type inference

## Transactions

Orchestrate atomic operations across multiple repositories.

### Basic Transaction

```typescript
const tx = await repo.beginTransaction();

try {
  // All operations use the same transaction
  const user = await userRepo.create({
    data: { name: 'Alice', email: 'alice@example.com' },
    options: { transaction: tx }
  });

  const profile = await profileRepo.create({
    data: { userId: user.data.id, bio: 'Hello!' },
    options: { transaction: tx }
  });

  // Commit if all succeeded
  await tx.commit();

  return { user: user.data, profile: profile.data };
} catch (error) {
  // Rollback on any error
  await tx.rollback();
  throw error;
}
```

### Isolation Levels

Control how transactions interact with concurrent operations:

```typescript
const tx = await repo.beginTransaction({
  isolationLevel: 'SERIALIZABLE'
});
```

| Level | Description | Use Case |
|-------|-------------|----------|
| `READ COMMITTED` | Default. See committed data only | Most applications |
| `REPEATABLE READ` | Consistent reads within transaction | Reports, analytics |
| `SERIALIZABLE` | Full isolation, prevents anomalies | Financial, inventory |

### Transaction with Multiple Repositories

```typescript
async function transferFunds(fromId: string, toId: string, amount: number) {
  const tx = await accountRepo.beginTransaction();

  try {
    // Debit source account
    await accountRepo.updateById({
      id: fromId,
      data: { balance: sql`balance - ${amount}` },
      options: { transaction: tx }
    });

    // Credit destination account
    await accountRepo.updateById({
      id: toId,
      data: { balance: sql`balance + ${amount}` },
      options: { transaction: tx }
    });

    // Record the transfer
    await transferRepo.create({
      data: { fromId, toId, amount, status: 'completed' },
      options: { transaction: tx }
    });

    await tx.commit();
  } catch (error) {
    await tx.rollback();
    throw error;
  }
}
```


## Row-Level Locking

Acquire pessimistic locks on selected rows within a transaction using PostgreSQL's `SELECT ... FOR UPDATE/SHARE` syntax.

### Basic Usage

Pass `lock` in options alongside a `transaction`:

```typescript
const tx = await repo.beginTransaction();

try {
  // Lock the row — other transactions will wait
  const item = await repo.findOne({
    filter: { where: { id: '123' } },
    options: {
      transaction: tx,
      lock: { strength: 'update' },
    },
  });

  // Safe to modify — no concurrent changes possible
  await repo.updateById({
    id: '123',
    data: { quantity: item.quantity - 1 },
    options: { transaction: tx },
  });

  await tx.commit();
} catch (error) {
  await tx.rollback();
  throw error;
}
```

### Lock Strengths

Use the `LockStrengths` constant class or string literals:

```typescript
import { LockStrengths } from '@venizia/ignis';

// Using constant
lock: { strength: LockStrengths.UPDATE }

// Using string literal
lock: { strength: 'update' }
```

| Strength | SQL | Use Case |
|----------|-----|----------|
| `update` | `FOR UPDATE` | Exclusive lock for writes |
| `no key update` | `FOR NO KEY UPDATE` | Exclusive lock, allows concurrent `FOR KEY SHARE` |
| `share` | `FOR SHARE` | Shared read lock, prevents writes |
| `key share` | `FOR KEY SHARE` | Weakest lock, only prevents key changes |

### Wait Behavior

Control what happens when rows are already locked:

```typescript
// Skip locked rows (queue-style worker pattern)
const items = await repo.find({
  filter: { where: { status: 'pending' }, limit: 10 },
  options: {
    transaction: tx,
    lock: { strength: 'update', config: { skipLocked: true } },
  },
});

// Fail immediately instead of waiting
const item = await repo.findOne({
  filter: { where: { id: '123' } },
  options: {
    transaction: tx,
    lock: { strength: 'update', config: { noWait: true } },
  },
});
```

| Config | SQL | Behavior |
|--------|-----|----------|
| *(none)* | `FOR UPDATE` | Wait until lock is released |
| `{ noWait: true }` | `FOR UPDATE NOWAIT` | Throw error immediately if locked |
| `{ skipLocked: true }` | `FOR UPDATE SKIP LOCKED` | Silently skip locked rows |

### Constraints

> [!WARNING]
> Row-level locking requires a **transaction** and is **incompatible with `include`/`fields`** in the filter (these use the Drizzle Query API which does not support `.for()`).

```typescript
// Error — no transaction
await repo.findOne({
  filter: { where: { id: '123' } },
  options: { lock: { strength: 'update' } },
});

// Error — include uses Query API
await repo.findOne({
  filter: { where: { id: '123' }, include: [{ relation: 'posts' }] },
  options: { transaction: tx, lock: { strength: 'update' } },
});
```

**Supported methods:** `find`, `findOne`, `findById`


## Hidden Properties

Automatically exclude sensitive fields from query results.

### Configuration

Define hidden properties in your model:

```typescript
@model({
  type: 'entity',
  settings: {
    hiddenProperties: ['password', 'secret', 'apiKey'],
  },
})
export class User extends BaseEntity<typeof User.schema> {
  static override schema = userTable;
}
```

### Automatic Exclusion

Hidden properties are excluded at the **SQL level** for maximum security:

```typescript
// Read operations exclude hidden properties
const user = await userRepo.findById({ id: '123' });
// Result: { id: '123', email: 'john@example.com', name: 'John' }
// Note: password, secret, apiKey are NOT included

// Write operations exclude hidden from RETURNING clause
const created = await userRepo.create({
  data: { email: 'new@example.com', password: 'hashed_secret' }
});
// Result: { count: 1, data: { id: '456', email: 'new@example.com' } }
// Note: password stored in DB but not returned
```

### Filtering by Hidden Properties

You **can** filter by hidden properties - you just can't see them in results:

```typescript
// This works! Finds user but password not in result
const user = await userRepo.findOne({
  filter: { where: { password: 'hashed_value' } }
});
```

### Relations with Hidden Properties

Hidden properties are also excluded from included relations:

```typescript
const post = await postRepo.findOne({
  filter: {
    include: [{ relation: 'author' }]
  }
});
// post.author will NOT include password, secret, etc.
```

### Accessing Hidden Data

When you need hidden fields (e.g., for authentication), bypass the repository:

```typescript
// Direct connector access - includes all fields
const connector = userRepo.getConnector();
const [fullUser] = await connector
  .select()
  .from(User.schema)
  .where(eq(User.schema.email, 'john@example.com'));
// fullUser includes password, secret, apiKey
```


## Performance Optimization

### Core API for Flat Queries

The repository automatically uses Drizzle's Core API (faster) for simple queries:

```typescript
// Automatically optimized - uses Core API
const users = await repo.find({
  filter: {
    where: { status: 'active' },
    limit: 10,
    order: ['createdAt DESC']
  }
});
// Uses: db.select().from(table).where(...).orderBy(...).limit(10)

// Uses Query API (has relations)
const usersWithPosts = await repo.find({
  filter: {
    where: { status: 'active' },
    include: [{ relation: 'posts' }]
  }
});
// Uses: db.query.tableName.findMany({ with: { posts: true }, ... })
```

| Filter Options | API Used | Performance |
|----------------|----------|-------------|
| `where`, `limit`, `order`, `offset`/`skip` only | Core API | ~15-20% faster |
| Has `include` (relations) | Query API | Standard |
| Has `fields` selection | Query API | Standard |

### Always Use Limit

Prevent memory exhaustion on large tables:

```typescript
// Good - bounded result set
await repo.find({
  filter: {
    where: { status: 'active' },
    limit: 100
  }
});

// Dangerous - could return millions of rows
await repo.find({
  filter: { where: { status: 'active' } }
});
```

> [!NOTE]
> The default limit is `10` when using the `FilterSchema` Zod validation (via `LimitSchema`). However, when calling repository methods directly without schema validation, no default limit is applied.

### Pagination with Data Range

Use `shouldQueryRange` to get both data and total count in a single call:

```typescript
const result = await userRepo.find({
  filter: {
    where: { status: 'active' },
    limit: 20,
    skip: 40,
    order: ['createdAt DESC']
  },
  options: { shouldQueryRange: true }
});

// Result type: { data: User[], range: { start: number, end: number, total: number } }
// range follows HTTP Content-Range standard (inclusive end index)
// Example: { data: [...20 users], range: { start: 40, end: 59, total: 150 } }
```

This runs `find` and `count` in parallel via `Promise.all` for optimal performance.

### WeakMap Cache

The filter builder caches table column metadata, avoiding repeated reflection:

```typescript
// Internal optimization - automatic
// First query: getTableColumns(schema) -> cached in WeakMap
// Subsequent queries: retrieved from WeakMap cache
```


## TypeScript Return Types

### shouldReturn Inference

Repository methods infer return types based on `shouldReturn`:

```typescript
// shouldReturn: false - TypeScript knows data is null
const result1 = await repo.create({
  data: { name: 'John' },
  options: { shouldReturn: false }
});
// Type: Promise<{ count: number; data: undefined | null }>

// shouldReturn: true (default) - TypeScript knows data is the entity
const result2 = await repo.create({
  data: { name: 'John' },
  options: { shouldReturn: true }
});
// Type: Promise<{ count: number; data: User }>
console.log(result2.data.name); // 'John' - fully typed!

// Array operations
const results = await repo.createAll({
  data: [{ name: 'John' }, { name: 'Jane' }],
  options: { shouldReturn: true }
});
// Type: Promise<{ count: number; data: User[] }>
```

### Generic Return Types

Override return types for queries with relations:

```typescript
// Define expected return type
type UserWithPosts = User & {
  posts: Post[];
};

// Use generic override
const user = await userRepo.findOne<UserWithPosts>({
  filter: {
    where: { id: '123' },
    include: [{ relation: 'posts' }]
  }
});

// TypeScript knows the structure!
if (user) {
  console.log(user.posts[0].title); // Fully typed
}
```

**Supported Methods:**
- `find<R>()`, `findOne<R>()`, `findById<R>()`
- `create<R>()`, `createAll<R>()`
- `updateById<R>()`, `updateAll<R>()`, `updateBy<R>()`
- `deleteById<R>()`, `deleteAll<R>()`, `deleteBy<R>()`


## Debugging

### Log Option

Enable logging for specific operations:

```typescript
// Enable debug logging
await repo.create({
  data: { name: 'John', email: 'john@example.com' },
  options: {
    log: { use: true, level: 'debug' }
  }
});
// Output: [_create] Executing with opts: { data: [...], options: {...} }

// Available levels: 'debug', 'info', 'warn', 'error'
await repo.updateById({
  id: '123',
  data: { name: 'Jane' },
  options: { log: { use: true, level: 'info' } }
});
```

**Available on:** `create`, `createAll`, `updateById`, `updateAll`, `updateBy`, `deleteById`, `deleteAll`, `deleteBy` (all write operations that go through `_create`, `_update`, or `_delete` internal methods)

### Query Interface Validation

The repository validates schema registration on startup:

```typescript
// If schema key doesn't match, you get a helpful error:
// Error: [UserRepository] Schema key mismatch
// | Entity name 'User' not found in connector.query
// | Available keys: [Configuration, Post]
// | Ensure the model's TABLE_NAME matches the schema registration key
```


## Safety Features

### Empty Where Protection

Prevents accidental mass updates/deletes:

```typescript
// Throws error - empty where without force
await repo.deleteAll({ where: {} });

// Explicit force flag - logs warning, proceeds
await repo.deleteAll({
  where: {},
  options: { force: true }
});
// Warning: [_delete] Entity: User | Performing delete with empty condition
```

| Scenario | `force: false` (default) | `force: true` |
|----------|-------------------------|---------------|
| Empty `where` | Throws error | Logs warning, proceeds |
| Valid `where` | Executes normally | Executes normally |

> [!NOTE]
> This protection applies to `updateAll`, `updateBy`, `deleteAll`, and `deleteBy`. The `updateById` and `deleteById` methods always have a non-empty where (`{ id }`) so they are not affected.

### Transaction Safety

The `resolveConnector` method validates transaction state before use:

```typescript
// If a transaction has already been committed or rolled back:
// Error: [UserRepository][resolveConnector] Transaction is no longer active
```


## Direct Connector Access

For advanced queries not supported by the repository API:

```typescript
// Get the Drizzle connector
const connector = repo.getConnector();

// Raw Drizzle query
const results = await connector
  .select({
    userId: userTable.id,
    postCount: sql<number>`count(${postTable.id})`,
  })
  .from(userTable)
  .leftJoin(postTable, eq(userTable.id, postTable.authorId))
  .groupBy(userTable.id)
  .having(sql`count(${postTable.id}) > 5`);

// Use with caution - bypasses repository features like hidden properties
```


## Repository Class Hierarchy

| Class | Scope | Description |
|-------|-------|-------------|
| `AbstractRepository` | N/A | Abstract base class, defines all method signatures, combines `FieldsVisibilityMixin` + `DefaultFilterMixin` |
| `ReadableRepository` | `READ_ONLY` | Read-only operations (`find`, `findOne`, `findById`, `count`, `existsWith`). Write operations throw errors. |
| `PersistableRepository` | `READ_WRITE` | Adds write operations (`create`, `update`, `delete`) with `UpdateBuilder` |
| `DefaultCRUDRepository` | `READ_WRITE` | Extends `PersistableRepository` with no additional logic - **recommended default** |
| `SoftDeletableRepository` | `READ_WRITE` | Extends `DefaultCRUDRepository` with soft delete + restore operations |

### Creating a Read-Only Repository

```typescript
@repository({ model: AuditLog, dataSource: PostgresDataSource })
export class AuditLogRepository extends ReadableRepository<typeof AuditLog.schema> {
  // Only has: find, findOne, findById, count, existsWith
  // Write operations throw "NOT ALLOWED" error
}
```

### Alias Methods

`AbstractRepository` provides two alias methods for convenience:

- `updateBy(opts)` - Alias for `updateAll(opts)`. Delegates directly.
- `deleteBy(opts)` - Alias for `deleteAll(opts)`. Delegates directly.

Both accept the same parameters (`where`, `data`/`options`) and support `shouldReturn` and `force` options.


## Default Filter Bypass

When models have a `defaultFilter` configured, you can bypass it for admin/maintenance operations:

```typescript
// Normal query - default filter applies
await repo.find({
  filter: { where: { status: 'active' } }
});
// WHERE isDeleted = false AND status = 'active' (if model has soft-delete default)

// Admin query - bypass default filter
await repo.find({
  filter: { where: { status: 'active' } },
  options: { shouldSkipDefaultFilter: true }
});
// WHERE status = 'active' (includes deleted records)
```

**Supported on all operations:**

```typescript
// Read operations
await repo.find({ filter, options: { shouldSkipDefaultFilter: true } });
await repo.findOne({ filter, options: { shouldSkipDefaultFilter: true } });
await repo.count({ where, options: { shouldSkipDefaultFilter: true } });

// Write operations
await repo.updateAll({ where, data, options: { shouldSkipDefaultFilter: true } });
await repo.deleteAll({ where, options: { shouldSkipDefaultFilter: true, force: true } });
```

**Combined with transactions:**

```typescript
const tx = await repo.beginTransaction();
await repo.updateAll({
  where: { status: 'archived' },
  data: { isDeleted: true },
  options: {
    transaction: tx,
    shouldSkipDefaultFilter: true
  }
});
await tx.commit();
```

> [!TIP]
> See [Default Filter](../filter-system/default-filter.md) for full documentation on configuring model default filters.


## Nested JSON Updates

Repositories support updating specific fields within `json` or `jsonb` columns without overwriting the entire object. This is achieved using **JSON Path Notation** in the update data via the `UpdateBuilder`.

### Basic Usage

Use dot notation keys to target nested properties:

```typescript
// Assume 'metadata' is a JSONB column
// Current value: { theme: 'light', notifications: { email: true } }

await repo.updateById({
  id: '123',
  data: {
    // Update only the theme, preserving other fields
    'metadata.theme': 'dark'
  }
});

// New value: { theme: 'dark', notifications: { email: true } }
```

### Supported Features

- **Deep Nesting:** Update properties at any depth (e.g., `settings.display.font.size`).
- **Array Access:** Update array elements by index (e.g., `tags[0]`).
- **Auto-Creation:** Creates missing intermediate keys automatically (`jsonb_set` with `create_missing = true`).
- **Type Safety:** Validates that the target column is a JSON/JSONB type.
- **Multiple Updates:** Multiple updates to the same column are chained as nested `jsonb_set` calls.
- **Mixed Updates:** Combine regular column updates with JSON path updates in a single call.

### Examples

#### Deeply Nested Updates

```typescript
await repo.updateById({
  id: '123',
  data: {
    'metadata.settings.display.fontSize': 16,
    'metadata.settings.display.showSidebar': true
  }
});
```

#### Array Element Updates

```typescript
await repo.updateById({
  id: '123',
  data: {
    // Set the first address as primary
    'metadata.addresses[0].primary': true
  }
});
```

#### Mixed Updates (Regular + JSON)

You can mix regular column updates with JSON path updates:

```typescript
await repo.updateById({
  id: '123',
  data: {
    status: 'active',           // Regular column
    'metadata.lastLogin': now,  // JSON path
    'preferences.lang': 'en'   // Another JSON path
  }
});
```

### Security & Validation

The framework validates JSON paths to prevent SQL injection:
- **Allowed Characters:** Path components must match `/^[a-zA-Z_][a-zA-Z0-9_-]*$|^\d+$/` (identifiers, kebab-case, or array indices).
- **Column Type Validation:** Only `json` and `jsonb` columns are allowed. Other column types throw an error.
- **Values:** Values are serialized to JSONB literals with proper escaping.

> [!NOTE]
> This feature uses PostgreSQL's `jsonb_set` function. It is only available for columns defined as `json` or `jsonb`.


## ExtraOptions Reference

All repository operations accept an `options` parameter with these fields:

| Option | Type | Default | Description |
|--------|------|---------|-------------|
| `transaction` | `ITransaction` | - | Transaction context for the operation |
| `log` | `{ use: boolean; level?: TLogLevel }` | - | Enable operation logging |
| `shouldSkipDefaultFilter` | `boolean` | `false` | Bypass the default filter from model settings |
| `lock` | `TLockOptions` | - | Row-level locking (requires transaction, Core API only) |

Write operations additionally support:

| Option | Type | Default | Description |
|--------|------|---------|-------------|
| `shouldReturn` | `boolean` | `true` | Return the created/updated/deleted data |
| `force` | `boolean` | `false` | Allow empty `where` condition on bulk operations |
| `shouldQueryRange` | `boolean` | `false` | Return `{ data, range }` with total count (find only) |


## Quick Reference

| Feature | Code |
|---------|------|
| Start transaction | `const tx = await repo.beginTransaction()` |
| Use transaction | `options: { transaction: tx }` |
| Commit | `await tx.commit()` |
| Rollback | `await tx.rollback()` |
| Bypass default filter | `options: { shouldSkipDefaultFilter: true }` |
| Lock rows for update | `options: { transaction: tx, lock: { strength: 'update' } }` |
| Lock + skip locked | `options: { transaction: tx, lock: { strength: 'update', config: { skipLocked: true } } }` |
| Enable logging | `options: { log: { use: true, level: 'debug' } }` |
| Force delete all | `options: { force: true }` |
| Skip returning data | `options: { shouldReturn: false }` |
| Get data + count | `options: { shouldQueryRange: true }` |
| Access connector | `repo.getConnector()` |


## Next Steps

- [Overview](./index.md) - Repository basics
- [Filter System](../filter-system/) - Query operators
- [Default Filter](../filter-system/default-filter.md) - Automatic filter configuration
- [Repository Mixins](./mixins.md) - Composable features
- [Relations & Includes](./relations.md) - Eager loading
- [Soft-Deletable Repository](./soft-deletable.md) - Soft delete operations
- [JSON Path Filtering](../filter-system/json-filtering) - JSONB queries
- [Array Operators](../filter-system/array-operators) - PostgreSQL arrays

## See Also

- **Related Concepts:**
  - [Repositories Overview](./index) - Core repository operations
  - [Transactions](/guides/core-concepts/persistent/transactions) - Transaction guide
  - [DataSources](/guides/core-concepts/persistent/datasources) - Database connections

- **Related Topics:**
  - [Repository Mixins](./mixins) - Composable mixin features
  - [Relations & Includes](./relations) - Loading related data
  - [Filter System](/references/base/filter-system/) - Query operators

- **Best Practices:**
  - [Performance Optimization](/best-practices/performance-optimization) - Query optimization
  - [Data Modeling](/best-practices/data-modeling) - Repository patterns