# How to Add New Database Operations Beyond Basic CRUD in Clean Architecture > Learn how to add custom database operations beyond basic CRUD in castrozan/tcc. Extend repository contracts, implement methods with parameterized SQL, and expose via use-cases and controllers. - Repository: [Lucas Zanoni⠀⠀⠀⠀⠀ ⠀╱|、 ⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀ (˚ˎ 。7 ⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀ |、˜〵 ⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀ じしˍ,)ノ/tcc](https://github.com/castrozan/tcc) - Tags: deep-dive - Published: 2026-02-27 --- **To add custom database operations beyond basic CRUD in the castrozan/tcc repository, extend the repository contract in the domain layer, implement the method using parameterized SQL queries in the infrastructure layer, and expose it through application use-cases and presentation controllers.** The castrozan/tcc repository demonstrates a strict clean architecture pattern for Node.js applications using SQLite and better-sqlite3. While basic CRUD operations handle standard data manipulation, production applications frequently require custom queries such as filtering by specific attributes, complex joins, or aggregations. This guide walks through the exact steps to add new database operations beyond basic CRUD while maintaining separation of concerns across the domain, infrastructure, application, and presentation layers. ## Understanding the Four-Layer Architecture The codebase organizes database access into four distinct layers: 1. **Domain Layer** – Defines entities (e.g., `Professional`, `Equipment`) and repository contracts (`IProfessionalRepository`, `IEquipmentRepository`). 2. **Infrastructure Layer** – Provides concrete SQLite implementations (`SQLiteProfessionalRepository`, `SQLiteEquipmentRepository`) using the `better-sqlite3` client. 3. **Application Layer** – Contains use-case classes that orchestrate repository calls without knowing implementation details. 4. **Presentation Layer** – HTTP controllers expose use-cases through OpenAPI routes. To add a new operation, you must extend each layer sequentially, starting from the domain contract and working up to the HTTP controller. ## Step 1: Extend the Repository Contract (Domain Layer) First, add the method signature to the repository interface. This establishes the contract that all implementations must fulfill. ```typescript // src/domain/interfaces/professionals/IProfessionalRepository.ts export interface IProfessionalRepository { create(article: Partial): Promise; findById(id: number): Promise; findAll(): Promise; /** NEW: Retrieve all professionals with a specific role */ findByRole(role: string): Promise; update(article: Partial): Promise; delete(id: number): Promise; } ``` *Reference:* [IProfessionalRepository](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/domain/interfaces/professionals/IProfessionalRepository.ts) ## Step 2: Implement the Custom Query (Infrastructure Layer) Next, implement the method in the concrete SQLite repository using parameterized queries to prevent SQL injection. The repository uses the singleton database client from [`sqlite-client.ts`](https://github.com/castrozan/tcc/blob/main/sqlite-client.ts). ```typescript // src/infrastructure/database/repositories/professionals/SQLiteProfessionalRepository.ts import { getDatabase } from '../../sqlite/sqlite-client'; export class SQLiteProfessionalRepository implements IProfessionalRepository { /* ... existing CRUD methods ... */ async findByRole(role: string): Promise { const db = getDatabase(); const rows = db .prepare('SELECT * FROM Professional WHERE role = ?') .all(role) as ProfessionalRow[]; return rows.map( row => new Professional( row.id, row.name, row.role, row.bio ?? null, row.imageUrl ?? null, new Date(row.createdAt), row.hierarchy ) ); } } ``` *Reference:* [SQLiteProfessionalRepository](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/infrastructure/database/repositories/professionals/SQLiteProfessionalRepository.ts) ## Step 3: Update the Database Schema (If Required) If your new query requires a column that does not exist, modify the migration script in [`migrations.ts`](https://github.com/castrozan/tcc/blob/main/migrations.ts) and increment the database version. For the `findByRole` example, the `role` column already exists, but adding a new column would look like this: ```typescript // src/infrastructure/database/sqlite/migrations.ts db.exec(` ALTER TABLE Professional ADD COLUMN seniority INTEGER DEFAULT 0; `); ``` *Reference:* [migrations.ts](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/infrastructure/database/sqlite/migrations.ts) ## Step 4: Create the Application Use Case Create a thin wrapper class in the application layer that delegates to the repository. This keeps the domain logic agnostic of delivery mechanisms. ```typescript // src/application/use-cases/professionals/FindProfessionalsByRoleUseCase.ts import { IProfessionalRepository } from '../../../domain/interfaces/professionals/IProfessionalRepository'; import { Professional } from '../../../domain/entities/professionals/Professional'; export class FindProfessionalsByRoleUseCase { constructor(private readonly repository: IProfessionalRepository) {} async execute(role: string): Promise { return this.repository.findByRole(role); } } ``` ## Step 5: Build the Presentation Controller Expose the operation via an HTTP endpoint using the OpenAPI route pattern established in the codebase. ```typescript // src/presentation/controllers/professionals/FindByRoleProfessionalController.ts import { FindProfessionalsByRoleUseCase } from '../../../application/use-cases/professionals/FindProfessionalsByRoleUseCase'; import { Bool, OpenAPIRoute } from 'chanfana'; import professionalRepository from '../../../infrastructure/database/repositories/professionals'; import { withErrorHandling } from '../../decorators'; import { z } from 'zod'; export class FindByRoleProfessionalController extends OpenAPIRoute { schema = { tags: ['Professionals'], summary: 'Retrieve professionals filtered by role', query: { role: { type: 'string', description: 'Professional role to filter by', required: true } }, responses: { '200': { description: 'Filtered list', content: { 'application/json': { schema: z.object({ success: Bool(), result: z.array( z.object({ id: z.number(), name: z.string(), role: z.string(), bio: z.string().nullable(), imageUrl: z.string().nullable(), createdAt: z.string(), hierarchy: z.number().nullable() }) ) }) } } } } }; @withErrorHandling async handle(request: any): Promise { const role = request.query.role; const useCase = new FindProfessionalsByRoleUseCase(professionalRepository); const professionals = await useCase.execute(role); return { success: true, result: professionals.map(p => ({ id: p.id, name: p.name, role: p.role, bio: p.bio, imageUrl: p.imageUrl, createdAt: p.createdAt.toISOString(), hierarchy: p.hierarchy })) }; } } ``` ## Step 6: Register the Route If your server uses automatic controller discovery, simply exporting the class suffices. Otherwise, explicitly register the route in your router configuration: ```typescript // Example router registration import { FindByRoleProfessionalController } from './presentation/controllers/professionals/FindByRoleProfessionalController'; router.get('/professionals', FindByRoleProfessionalController); ``` ## Key Files Reference | Purpose | File Path | Link | |---------|-----------|------| | Repository contract | [`src/domain/interfaces/professionals/IProfessionalRepository.ts`](https://github.com/castrozan/tcc/blob/main/src/domain/interfaces/professionals/IProfessionalRepository.ts) | [View on GitHub](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/domain/interfaces/professionals/IProfessionalRepository.ts) | | SQLite implementation | [`src/infrastructure/database/repositories/professionals/SQLiteProfessionalRepository.ts`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/repositories/professionals/SQLiteProfessionalRepository.ts) | [View on GitHub](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/infrastructure/database/repositories/professionals/SQLiteProfessionalRepository.ts) | | Database client | [`src/infrastructure/database/sqlite/sqlite-client.ts`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/sqlite/sqlite-client.ts) | [View on GitHub](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/infrastructure/database/sqlite/sqlite-client.ts) | | Schema migrations | [`src/infrastructure/database/sqlite/migrations.ts`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/sqlite/migrations.ts) | [View on GitHub](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/infrastructure/database/sqlite/migrations.ts) | | Example use-case | [`src/application/use-cases/professionals/FindAllProfessionalUseCase.ts`](https://github.com/castrozan/tcc/blob/main/src/application/use-cases/professionals/FindAllProfessionalUseCase.ts) | [View on GitHub](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/application/use-cases/professionals/FindAllProfessionalUseCase.ts) | | Example controller | [`src/presentation/controllers/professionals/FindAllProfessionalController.ts`](https://github.com/castrozan/tcc/blob/main/src/presentation/controllers/professionals/FindAllProfessionalController.ts) | [View on GitHub](https://github.com/castrozan/tcc/blob/master/professionals-dummy-app/src/presentation/controllers/professionals/FindAllProfessionalController.ts) | | Equipment repository (parallel example) | [`src/infrastructure/database/repositories/equipments/SQLiteEquipmentRepository.ts`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/repositories/equipments/SQLiteEquipmentRepository.ts) | [View on GitHub](https://github.com/castrozan/tcc/blob/master/equipments-dummy-app/src/infrastructure/database/repositories/equipments/SQLiteEquipmentRepository.ts) | ## Summary - **Extend the domain contract** first by adding the method signature to the repository interface (e.g., `IProfessionalRepository`). - **Implement in SQLite** using parameterized queries via `db.prepare().all()` or `.get()` to prevent SQL injection. - **Migrate schema** when new columns are required by updating [`migrations.ts`](https://github.com/castrozan/tcc/blob/main/migrations.ts) before implementing queries that depend on them. - **Wrap in use-cases** to keep domain logic isolated from delivery mechanisms. - **Expose via controllers** using the existing OpenAPI route pattern with `chanfana` for automatic documentation. - **Reuse patterns** across entities—the same approach works for `Equipment` or any future domain model. ## Frequently Asked Questions ### What is the correct order for adding a new database operation? Always work from the innermost layer outward: start with the **domain interface**, then the **infrastructure implementation**, followed by the **application use-case**, and finally the **presentation controller**. This ensures that each layer depends only on abstractions defined in the layers beneath it, maintaining the dependency inversion principle. ### How do I handle complex queries with joins or aggregations? Implement complex SQL directly in the infrastructure layer within the concrete repository class (e.g., `SQLiteProfessionalRepository`). Use `db.prepare()` with parameterized queries for any dynamic values, and map the raw row results to domain entities before returning them. Keep the SQL complexity isolated from the domain and application layers—the repository interface should return domain objects, not raw query results. ### Can I use the same pattern for the Equipment entity? Yes, the architecture is identical across entities. Reference [`SQLiteEquipmentRepository.ts`](https://github.com/castrozan/tcc/blob/main/SQLiteEquipmentRepository.ts) and `IEquipmentRepository` to see the same CRUD and custom operation patterns applied to the Equipment domain. Whether adding `findByCategory` for Equipment or `findByRole` for Professional, the four-layer extension process remains exactly the same.