# How to Set Up Database Optimization in ECC: Complete Implementation Guide

> Master database optimization in ECC with this guide. Learn to implement selective query projection, N+1 prevention, Redis caching, and atomic transactions for peak performance.

- Repository: [Affaan Mustafa/ECC](https://github.com/affaan-m/ECC)
- Tags: how-to-guide
- Published: 2026-05-26

---

**Database optimization in ECC is achieved through four core patterns—selective query projection, N+1 query prevention, cache-aside with Redis, and atomic transactions—implemented across distinct repository, service, and API layers.**

The ECC ( Enterprise Component Collection) repository provides structured backend development patterns that guide developers toward efficient data access. According to the source code in `affaan-m/ECC`, the **database optimization** techniques are located within the Backend Development Patterns skill and demonstrate production-ready implementations using TypeScript and Supabase.

## The Four Core Patterns for Database Optimization in ECC

### Query Optimization: Select Only Required Columns

Reduce I/O and memory pressure by projecting specific columns rather than using `SELECT *`. In [`src/lib/repo/marketRepository.ts`](https://github.com/affaan-m/ECC/blob/main/src/lib/repo/marketRepository.ts), the optimized pattern explicitly lists fields to leverage database indexes and minimize payload size.

```typescript
// src/lib/repo/marketRepository.ts
const { data } = await supabase
  .from('markets')
  .select('id, name, status, volume')   // Select only needed fields
  .eq('status', 'active')
  .order('volume', { ascending: false })
  .limit(10);

```

This pattern, documented in the skill file【33†L33-L41】, ensures the query optimizer can use covering indexes and reduces network transfer overhead.

### N+1 Query Prevention: Batch Fetch Related Data

Eliminate the classic performance anti-pattern where iterating over parent records triggers individual queries for each child. The `MarketService` in [`src/services/marketService.ts`](https://github.com/affaan-m/ECC/blob/main/src/services/marketService.ts) demonstrates batching creator lookups into a single query.

```typescript
// src/services/marketService.ts
const markets = await getMarkets();                     // Single query for markets
const creatorIds = markets.map(m => m.creator_id);
const creators = await getUsers(creatorIds);           // Single batched query
const creatorMap = new Map(creators.map(c => [c.id, c]));

markets.forEach(m => {
  m.creator = creatorMap.get(m.creator_id);
});

```

This implementation replaces the naïve per-market lookup (see the "FAIL: BAD" example【48†L52-L56】) with a single batched query (the "PASS: GOOD" example【57†L58-L65】), cutting database round-trips from O(n) to O(1).

### Cache-Aside Strategy: Redis Layer Implementation

Implement a decorator pattern to wrap your base repository with Redis caching. The `CachedMarketRepository` in [`src/lib/repo/cachedMarketRepository.ts`](https://github.com/affaan-m/ECC/blob/main/src/lib/repo/cachedMarketRepository.ts) implements the cache-aside pattern, checking the cache before falling back to the database.

```typescript
// src/lib/repo/cachedMarketRepository.ts
class CachedMarketRepository implements MarketRepository {
  constructor(private baseRepo: MarketRepository, private redis: RedisClient) {}

  async findById(id: string): Promise<Market | null> {
    const cached = await this.redis.get(`market:${id}`);
    if (cached) return JSON.parse(cached);

    const market = await this.baseRepo.findById(id);
    if (market) await this.redis.setex(`market:${id}`, 300, JSON.stringify(market));
    return market;
  }

  async invalidateCache(id: string): Promise<void> {
    await this.redis.del(`market:${id}`);
  }
}

```

This mirrors the *Redis Caching Layer* example【71†L71-L84】 and the *Cache-Aside* snippet【84†L84-L90】, providing sub-millisecond reads for frequently accessed entities while maintaining data consistency through explicit invalidation.

### Transaction Pattern: Atomic Database Operations

Ensure consistency across multiple table writes by wrapping operations in database transactions. The `createMarketWithPosition` function in [`src/lib/transactions/marketTransaction.ts`](https://github.com/affaan-m/ECC/blob/main/src/lib/transactions/marketTransaction.ts) uses Supabase RPC to execute atomic stored procedures.

```typescript
// src/lib/transactions/marketTransaction.ts
async function createMarketWithPosition(
  marketData: CreateMarketDto,
  positionData: CreatePositionDto
) {
  const { data, error } = await supabase.rpc('create_market_with_position', {
    market_data: marketData,
    position_data: positionData,
  });

  if (error) throw new Error('Transaction failed');
  return data;
}

```

This implements the transaction pattern illustrated in the skill file【68†L68-L84】 and the corresponding SQL function【86†L86-L100】, guaranteeing that either both records persist or neither does, preventing partial data states.

## Architectural Layering in ECC

The database optimization patterns rely on strict separation of concerns across four layers:

1. **Repository Layer** – All data access is abstracted through interfaces like `MarketRepository`, allowing database swaps without affecting business logic.

2. **Service Layer** – `MarketService` orchestrates repositories, applies caching strategies, and handles N+1 prevention through batch operations.

3. **API/Middleware Layer** – Next.js API routes or Express handlers invoke services and manage HTTP concerns, delegating data access to the service layer.

4. **Cache & Error Handling** – The `CachedMarketRepository` decorates base repositories with Redis lookups, while centralized `ApiError` and `errorHandler` (using [`src/lib/logging/logger.ts`](https://github.com/affaan-m/ECC/blob/main/src/lib/logging/logger.ts)) ensure consistent failure logging.

This modular architecture means you can migrate from Supabase to native PostgreSQL or add additional caching layers without modifying service or API code, preserving your optimization guarantees.

## Summary

- **Query optimization** requires explicit column selection in Supabase queries to minimize I/O and leverage indexes.
- **N+1 prevention** is solved by batching related entity lookups into single queries using ID mapping techniques.
- **Cache-aside pattern** wraps repositories with Redis decorators to serve hot data from memory while maintaining database consistency.
- **Atomic transactions** use Supabase RPC calls to ensure multi-table writes succeed or fail together.
- **Layered architecture** separates concerns across repository, service, and API boundaries, enabling database technology swaps without business logic changes.

## Frequently Asked Questions

### How does the N+1 query problem manifest in ECC applications?

The N+1 problem occurs when code fetches a list of records (N), then iterates through them to fetch related data, issuing one additional query per record (total N+1 queries). In ECC, this is solved by extracting all foreign keys from the initial result set, batching the related queries into a single `getUsers(creatorIds)` call, then mapping relationships in memory using a `Map` data structure.

### When should I use the cache-aside pattern versus querying the database directly?

Use **cache-aside** when reading frequently accessed, relatively static data that can tolerate brief staleness (300-second TTL in the examples). Query the database directly for real-time critical operations or when cache invalidation would add unacceptable complexity. The `CachedMarketRepository` pattern allows you to decorate specific methods while leaving others uncached, providing granular control.

### Can I use these optimization patterns with databases other than Supabase?

Yes. The repository pattern abstracts the underlying data source, so `MarketRepository` implementations can be swapped between Supabase, PostgreSQL, or other databases. The transaction pattern uses Supabase RPC in the example, but equivalent implementations using native database transactions would follow the same architectural principles in [`src/lib/transactions/marketTransaction.ts`](https://github.com/affaan-m/ECC/blob/main/src/lib/transactions/marketTransaction.ts).

### How does ECC handle transaction failures and database errors?

ECC uses centralized error handling through `ApiError` classes and the `errorHandler` middleware, implemented in the logging utilities. Transaction failures trigger explicit error throws (e.g., `throw new Error('Transaction failed')`), which are caught by the error handling layer and logged consistently via [`src/lib/logging/logger.ts`](https://github.com/affaan-m/ECC/blob/main/src/lib/logging/logger.ts), ensuring observability without leaking sensitive database details to the client.