Advanced Repository Features

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

Prerequisites

Before reading this document, you should understand:

• Basic Repository Operations - CRUD operations and basic filtering
• 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

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:

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

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;
  }
}

Hidden Properties

Automatically exclude sensitive fields from query results.

Configuration

Define hidden properties in your model:

@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:

// 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:

// 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:

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:

// 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:

// 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:

// 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:

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:

// 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:

// 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:

// 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:

// 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:

// 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:

// 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:

// 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:

// 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

@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:

// 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:

// 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:

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 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:

// 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

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

Array Element Updates

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:

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

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 }
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 - Repository basics
• Filter System - Query operators
• Default Filter - Automatic filter configuration
• Repository Mixins - Composable features
• Relations & Includes - Eager loading
• Soft-Deletable Repository - Soft delete operations
• JSON Path Filtering - JSONB queries
• Array Operators - PostgreSQL arrays

See Also

• Related Concepts:

  • Repositories Overview - Core repository operations
  • Transactions - Transaction guide
  • DataSources - Database connections

• Related Topics:

  • Repository Mixins - Composable mixin features
  • Relations & Includes - Loading related data
  • Filter System - Query operators

• Best Practices:

  • Performance Optimization - Query optimization
  • Data Modeling - Repository patterns