How to Add New Database Operations Beyond Basic CRUD in Clean Architecture

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.

// src/domain/interfaces/professionals/IProfessionalRepository.ts
export interface IProfessionalRepository {
    create(article: Partial<Professional>): Promise<Professional>;
    findById(id: number): Promise<Professional | null>;
    findAll(): Promise<Professional[]>;
    /** NEW: Retrieve all professionals with a specific role */
    findByRole(role: string): Promise<Professional[]>;
    update(article: Partial<Professional>): Promise<Professional>;
    delete(id: number): Promise<void>;
}

Reference: IProfessionalRepository

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.

// 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<Professional[]> {
        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

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 and increment the database version. For the findByRole example, the role column already exists, but adding a new column would look like this:

// src/infrastructure/database/sqlite/migrations.ts
db.exec(`
    ALTER TABLE Professional ADD COLUMN seniority INTEGER DEFAULT 0;
`);

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

// 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<Professional[]> {
        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.

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

// 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	View on GitHub
SQLite implementation	src/infrastructure/database/repositories/professionals/SQLiteProfessionalRepository.ts	View on GitHub
Database client	src/infrastructure/database/sqlite/sqlite-client.ts	View on GitHub
Schema migrations	src/infrastructure/database/sqlite/migrations.ts	View on GitHub
Example use-case	src/application/use-cases/professionals/FindAllProfessionalUseCase.ts	View on GitHub
Example controller	src/presentation/controllers/professionals/FindAllProfessionalController.ts	View on GitHub
Equipment repository (parallel example)	src/infrastructure/database/repositories/equipments/SQLiteEquipmentRepository.ts	View on GitHub

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

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}