Services and Repositories

Overview

PROVESA Web implements a clean separation between business logic (Services) and data access (Repositories). This pattern provides:

Testability - Business logic can be tested independently of database
Maintainability - Changes to database queries don’t affect business logic
Reusability - Services can orchestrate multiple repositories
Type Safety - Drizzle ORM provides compile-time type checking

Architecture Pattern

┌─────────────────────────────────────────┐
│           Route Handler                 │
│  Handles HTTP, calls service methods    │
└──────────────┬──────────────────────────┘
               │
               ▼
┌─────────────────────────────────────────┐
│            Service Layer                │
│  • Business logic                       │
│  • Validation                           │
│  • Data transformation                  │
│  • File uploads (Cloudinary)            │
│  • Orchestrates repositories            │
└──────────────┬──────────────────────────┘
               │
               ▼
┌─────────────────────────────────────────┐
│         Repository Layer                │
│  • Database queries (Drizzle)           │
│  • CRUD operations                      │
│  • Data access abstraction              │
└──────────────┬──────────────────────────┘
               │
               ▼
┌─────────────────────────────────────────┐
│        PostgreSQL Database              │
└─────────────────────────────────────────┘

Repository Layer

Purpose

Repositories provide a consistent API for database operations, abstracting away SQL and ORM implementation details.

Standard Repository Pattern

Every repository exports an object with standard CRUD methods:

export const {domain}Repository = {
    async getAll() { /* ... */ },
    async getById(id) { /* ... */ },
    async create(data) { /* ... */ },
    async update(id, data) { /* ... */ },
    async remove(id) { /* ... */ }
};

Example: Products Repository

File: /src/lib/server/repositories/products.repository.js

import { db } from '$lib/server/db';
import { products } from '$lib/server/db/schema';
import { eq, asc } from 'drizzle-orm';

export const productsRepository = {
    /** Obtiene todos los productos ordenados */
    async getAll() {
        return await db.select().from(products).orderBy(asc(products.sortOrder));
    },

    /** Crea un nuevo producto */
    async create(data) {
        await db.insert(products).values(data);
    },

    /** Actualiza un producto por ID */
    async update(id, data) {
        await db
            .update(products)
            .set(data)
            .where(eq(products.id, id));
    },

    /** Elimina un producto por ID */
    async remove(id) {
        await db.delete(products).where(eq(products.id, id));
    }
};

Key Repository Patterns

1. Always Return Data

Repositories should return query results, not void:

// ✅ Good
async create(data) {
    const rows = await db.insert(products).values(data).returning();
    return rows[0];
}

// ❌ Avoid
async create(data) {
    await db.insert(products).values(data);
    // No return value
}

2. Use Type Inference

Drizzle provides type inference for insert/update:

// TypeScript will infer the correct type
async create(data) {
    // data is typed as: typeof products.$inferInsert
    return db.insert(products).values(data).returning();
}

3. Sort by Default

Always include default sorting for consistency:

async getAll() {
    return await db
        .select()
        .from(products)
        .orderBy(asc(products.sortOrder)); // Consistent ordering
}

4. Use Proper Operators

Drizzle provides type-safe operators:

import { eq, asc, desc, like, gte, lte } from 'drizzle-orm';

// Equality
where(eq(products.id, id))

// Sorting
orderBy(asc(products.sortOrder))
orderBy(desc(products.createdAt))

// LIKE queries
where(like(products.name, '%search%'))

// Comparisons
where(gte(products.price, 100))

Advanced Repository Example: Concursos

File: /src/lib/server/repositories/concursos.repository.js

import { db } from '$lib/server/db/index.js';
import { concursos, concursosGanadores } from '$lib/server/db/schema.js';
import { eq, asc } from 'drizzle-orm';

export const concursosRepository = {
    // ─── Concursos ───

    async getAll() {
        return db.select().from(concursos).orderBy(asc(concursos.sortOrder));
    },

    /** Get the currently active contest */
    async getActive() {
        const rows = await db
            .select()
            .from(concursos)
            .where(eq(concursos.isActive, true))
            .limit(1);
        return rows[0] || null;
    },

    async getById(id) {
        const rows = await db.select().from(concursos).where(eq(concursos.id, id)).limit(1);
        return rows[0] || null;
    },

    async create(data) {
        const rows = await db.insert(concursos).values(data).returning();
        return rows[0];
    },

    async update(id, data) {
        return db.update(concursos).set(data).where(eq(concursos.id, id));
    },

    async remove(id) {
        return db.delete(concursos).where(eq(concursos.id, id));
    },

    // ─── Ganadores ───

    async getAllGanadores() {
        return db.select().from(concursosGanadores).orderBy(asc(concursosGanadores.sortOrder));
    },

    /** Get winners for a specific contest */
    async getGanadoresByConcurso(concursoId) {
        return db.select().from(concursosGanadores)
            .where(eq(concursosGanadores.concursoId, concursoId))
            .orderBy(asc(concursosGanadores.sortOrder));
    },

    async createGanador(data) {
        const rows = await db.insert(concursosGanadores).values(data).returning();
        return rows[0];
    },

    async updateGanador(id, data) {
        return db.update(concursosGanadores).set(data).where(eq(concursosGanadores.id, id));
    },

    async removeGanador(id) {
        return db.delete(concursosGanadores).where(eq(concursosGanadores.id, id));
    }
};

Key Points:

Handles two related tables (concursos and concursosGanadores)
Provides specialized queries (getActive, getGanadoresByConcurso)
Returns null for not found instead of throwing

Service Layer

Purpose

Services contain business logic, validation, and orchestration. They:

Call one or more repositories
Transform and validate data
Handle file uploads
Calculate derived values
Enforce business rules

Standard Service Pattern

import { {domain}Repository } from '../repositories/{domain}.repository.js';

export const {domain}Service = {
    async getAll() { /* ... */ },
    async getById(id) { /* ... */ },
    async create(data) { /* ... */ },
    async update(id, data) { /* ... */ },
    async delete(id) { /* ... */ }
};

Example: Products Service

File: /src/lib/server/services/products.service.js

import { productsRepository } from '../repositories/products.repository.js';

export const productsService = {
    /** Obtiene todos los productos */
    async getAllProducts() {
        return await productsRepository.getAll();
    },

    /** Agrega un nuevo producto con sortOrder automático y JSON parsing */
    async addProduct({ name, description, align, accentColor, displayType, images, features, categories }) {
        // Calculate next sortOrder
        const existing = await productsRepository.getAll();
        const nextOrder = existing.length > 0 
            ? existing[existing.length - 1].sortOrder + 1 
            : 0;

        // Parse JSON fields if needed
        await productsRepository.create({
            name, 
            description, 
            align, 
            accentColor, 
            displayType,
            images: typeof images === 'string' ? JSON.parse(images) : images,
            features: typeof features === 'string' ? JSON.parse(features) : features,
            categories: typeof categories === 'string' ? JSON.parse(categories) : categories,
            sortOrder: nextOrder
        });
    },

    /** Actualiza un producto existente */
    async updateProduct(id, { name, description, align, accentColor, displayType, images, features, categories }) {
        await productsRepository.update(id, {
            name, 
            description, 
            align, 
            accentColor, 
            displayType,
            images: typeof images === 'string' ? JSON.parse(images) : images,
            features: typeof features === 'string' ? JSON.parse(features) : features,
            categories: typeof categories === 'string' ? JSON.parse(categories) : categories
        });
    },

    /** Elimina un producto */
    async deleteProduct(id) {
        await productsRepository.remove(id);
    }
};

Key Points:

Automatic sortOrder calculation
JSON parsing for JSONB fields
Delegates database operations to repository

Advanced Service Example: Concursos

File: /src/lib/server/services/concursos.service.js

import { concursosRepository } from '$lib/server/repositories/concursos.repository.js';
import { uploadRepository } from '$lib/server/repositories/upload.repository.js';

export const concursosService = {
    // ─── Public Data ───

    /** Retorna el concurso activo + sus ganadores para la página pública */
    async getPublicData() {
        const concurso = await concursosRepository.getActive();
        let ganadores = [];
        
        if (concurso) {
            ganadores = await concursosRepository.getGanadoresByConcurso(concurso.id);
        }
        
        return { concurso, ganadores };
    },

    // ─── Admin: Concursos ───

    async getAllConcursos() {
        return concursosRepository.getAll();
    },

    async getAllGanadores() {
        return concursosRepository.getAllGanadores();
    },

    async addConcurso(formData) {
        // Handle image upload
        const imageFile = formData.get('image');
        let imageUrl = '';

        if (imageFile && imageFile.size > 0) {
            const result = await uploadRepository.uploadImage(imageFile, {
                folder: 'provesa/concursos'
            });
            imageUrl = result.secure_url;
        }

        // Calculate sortOrder
        const existing = await concursosRepository.getAll();
        const sortOrder = existing.length > 0
            ? Math.max(...existing.map(c => c.sortOrder)) + 1
            : 0;

        return concursosRepository.create({
            title: formData.get('title')?.toString() || '',
            titleHighlight: formData.get('titleHighlight')?.toString() || '',
            description: formData.get('description')?.toString() || '',
            imageUrl,
            badgeText: formData.get('badgeText')?.toString() || 'Sorteo Activo',
            closeDate: formData.get('closeDate')?.toString() || '',
            prizeName: formData.get('prizeName')?.toString() || '',
            ctaText: formData.get('ctaText')?.toString() || 'Ver Marcas Auspiciantes',
            disclaimer: formData.get('disclaimer')?.toString() || '',
            isActive: formData.get('isActive') === 'true',
            sortOrder
        });
    },

    async updateConcurso(id, formData) {
        const data = {
            title: formData.get('title')?.toString() || '',
            titleHighlight: formData.get('titleHighlight')?.toString() || '',
            description: formData.get('description')?.toString() || '',
            badgeText: formData.get('badgeText')?.toString() || '',
            closeDate: formData.get('closeDate')?.toString() || '',
            prizeName: formData.get('prizeName')?.toString() || '',
            ctaText: formData.get('ctaText')?.toString() || '',
            disclaimer: formData.get('disclaimer')?.toString() || '',
            isActive: formData.get('isActive') === 'true'
        };

        // Handle optional image update
        const imageFile = formData.get('image');
        if (imageFile && imageFile.size > 0) {
            const result = await uploadRepository.uploadImage(imageFile, {
                folder: 'provesa/concursos'
            });
            data.imageUrl = result.secure_url;
        }

        return concursosRepository.update(id, data);
    },

    async deleteConcurso(id) {
        return concursosRepository.remove(id);
    },

    // ─── Admin: Ganadores ───

    async addGanador(formData) {
        const imageFile = formData.get('image');
        let imageUrl = '';

        if (imageFile && imageFile.size > 0) {
            const result = await uploadRepository.uploadImage(imageFile, {
                folder: 'provesa/concursos/ganadores'
            });
            imageUrl = result.secure_url;
        }

        const existing = await concursosRepository.getAllGanadores();
        const sortOrder = existing.length > 0
            ? Math.max(...existing.map(g => g.sortOrder)) + 1
            : 0;

        return concursosRepository.createGanador({
            concursoId: parseInt(formData.get('concursoId')?.toString() || '0') || null,
            winnerName: formData.get('winnerName')?.toString() || '',
            prize: formData.get('prize')?.toString() || '',
            testimonial: formData.get('testimonial')?.toString() || '',
            imageUrl,
            dateLabel: formData.get('dateLabel')?.toString() || '',
            sortOrder
        });
    },

    async updateGanador(id, formData) {
        const data = {
            concursoId: parseInt(formData.get('concursoId')?.toString() || '0') || null,
            winnerName: formData.get('winnerName')?.toString() || '',
            prize: formData.get('prize')?.toString() || '',
            testimonial: formData.get('testimonial')?.toString() || '',
            dateLabel: formData.get('dateLabel')?.toString() || ''
        };

        const imageFile = formData.get('image');
        if (imageFile && imageFile.size > 0) {
            const result = await uploadRepository.uploadImage(imageFile, {
                folder: 'provesa/concursos/ganadores'
            });
            data.imageUrl = result.secure_url;
        }

        return concursosRepository.updateGanador(id, data);
    },

    async deleteGanador(id) {
        return concursosRepository.removeGanador(id);
    }
};

Key Features:

Orchestrates multiple repositories (concursosRepository, uploadRepository)
Handles file uploads to Cloudinary
Extracts and validates FormData
Provides public API (getPublicData) and admin APIs
Manages related entities (contests and winners)

Common Patterns

Pattern 1: Auto-Incrementing Sort Order

Many tables use a sortOrder field for manual ordering:

// In service
const existing = await repository.getAll();
const nextOrder = existing.length > 0 
    ? Math.max(...existing.map(item => item.sortOrder)) + 1 
    : 0;

await repository.create({
    ...data,
    sortOrder: nextOrder
});

Pattern 2: File Upload Integration

Services handle file uploads, not repositories:

// In service
const imageFile = formData.get('image');
let imageUrl = '';

if (imageFile && imageFile.size > 0) {
    const result = await uploadRepository.uploadImage(imageFile, {
        folder: 'provesa/domain-name'
    });
    imageUrl = result.secure_url;
}

await repository.create({
    ...data,
    imageUrl
});

Pattern 3: JSON Field Parsing

JSONB fields often come as strings from FormData:

images: typeof images === 'string' ? JSON.parse(images) : images,
features: typeof features === 'string' ? JSON.parse(features) : features,

Pattern 4: Singleton Config Tables

Some tables should only have one row:

// In repository
async get() {
    const rows = await db.select().from(footerInfo).limit(1);
    return rows[0] || null;
},

async upsert(data) {
    const existing = await this.get();
    if (existing) {
        return db.update(footerInfo).set(data).where(eq(footerInfo.id, existing.id));
    } else {
        return db.insert(footerInfo).values(data);
    }
}

Pattern 5: Parallel Data Loading

Services can orchestrate multiple repositories in parallel:

// In route +page.server.js
const [slides, products, footer] = await Promise.all([
    slidesService.getAllSlides(),
    productsService.getAllProducts(),
    footerService.getFooterData()
]);

Route Integration

Loading Data

Routes call services in the load function:

// src/routes/+page.server.js
import { slidesService } from '$lib/server/services/slides.service.js';
import { productsService } from '$lib/server/services/products.service.js';

export const load = async () => {
    const [slides, productsList] = await Promise.all([
        slidesService.getAllSlides(),
        productsService.getAllProducts()
    ]);

    return {
        slides,
        products: productsList
    };
};

Form Actions

Routes call services in form actions:

export const actions = {
    addProduct: async ({ request, locals }) => {
        if (!locals.session) return fail(401);
        
        const formData = await request.formData();
        const name = formData.get('name')?.toString() || '';
        
        if (!name) {
            return fail(400, { error: 'El nombre del producto es requerido.' });
        }
        
        try {
            await productsService.addProduct({
                name,
                description: formData.get('description')?.toString() || '',
                // ... other fields
            });
            return { productAdded: true };
        } catch (e) {
            console.error('Error agregando producto:', e);
            return fail(500, { error: 'No se pudo agregar el producto.' });
        }
    }
};

Testing Considerations

Repository Testing

Repositories can be tested against a real database:

import { describe, it, expect } from 'vitest';
import { productsRepository } from './products.repository';

describe('productsRepository', () => {
    it('should fetch all products', async () => {
        const products = await productsRepository.getAll();
        expect(Array.isArray(products)).toBe(true);
    });
});

Service Testing

Services can be tested with mocked repositories:

import { describe, it, expect, vi } from 'vitest';
import { productsService } from './products.service';

vi.mock('../repositories/products.repository', () => ({
    productsRepository: {
        getAll: vi.fn(() => Promise.resolve([])),
        create: vi.fn()
    }
}));

describe('productsService', () => {
    it('should calculate sortOrder correctly', async () => {
        await productsService.addProduct({ /* ... */ });
        // Assertions
    });
});

Best Practices

DO:

✅ Keep repositories simple - only database operations
✅ Put business logic in services
✅ Use consistent naming ({domain}.service.js, {domain}.repository.js)
✅ Return typed results from repositories
✅ Handle errors in services, propagate to routes
✅ Use Promise.all() for independent operations
✅ Document complex business logic with comments

DON’T:

❌ Put business logic in repositories
❌ Call repositories directly from routes
❌ Mix database queries with file uploads in repositories
❌ Ignore type safety from Drizzle
❌ Swallow errors silently
❌ Use raw SQL unless absolutely necessary

Next Steps

Database Schema - Table definitions
Architecture Overview - System design
Project Structure - File organization

​Overview

​Architecture Pattern

​Repository Layer

​Purpose

​Standard Repository Pattern

​Example: Products Repository

​Key Repository Patterns

​1. Always Return Data

​2. Use Type Inference

​3. Sort by Default

​4. Use Proper Operators

​Advanced Repository Example: Concursos

​Service Layer

​Purpose

​Standard Service Pattern

​Example: Products Service

​Advanced Service Example: Concursos

​Common Patterns

​Pattern 1: Auto-Incrementing Sort Order

​Pattern 2: File Upload Integration

​Pattern 3: JSON Field Parsing

​Pattern 4: Singleton Config Tables

​Pattern 5: Parallel Data Loading

​Route Integration

​Loading Data

​Form Actions

​Testing Considerations

​Repository Testing

​Service Testing

​Best Practices

​DO:

​DON’T:

​Next Steps

Overview

Architecture Pattern

Repository Layer

Purpose

Standard Repository Pattern

Example: Products Repository

Key Repository Patterns

1. Always Return Data

2. Use Type Inference

3. Sort by Default

4. Use Proper Operators

Advanced Repository Example: Concursos

Service Layer

Purpose

Standard Service Pattern

Example: Products Service

Advanced Service Example: Concursos

Common Patterns

Pattern 1: Auto-Incrementing Sort Order

Pattern 2: File Upload Integration

Pattern 3: JSON Field Parsing

Pattern 4: Singleton Config Tables

Pattern 5: Parallel Data Loading

Route Integration

Loading Data

Form Actions

Testing Considerations

Repository Testing

Service Testing

Best Practices

DO:

DON’T:

Next Steps