[Refactoring] Extract Prisma Query Builders into Reusable Utilities for Product Service

[syed-reza98]

Problem

The ProductService class in src/lib/services/product.service.ts contains 8 repeated Prisma include blocks for fetching product relations (category, brand, variants, _count). This duplication spans ~1,662 lines and creates multiple maintenance issues:

• Maintenance burden: Any change to the product query structure requires updating 8+ locations
• Inconsistency risk: Different methods may fetch slightly different data shapes
• Bundle size impact: Repeated object literals increase the compiled code size
• Cognitive overhead: Developers must remember which fields to include for each use case

Current Code Location

• File: src/lib/services/product.service.ts (1,662 lines)
• Methods affected: listProducts, getProductById, getProductBySlug, getLowStockProducts, createProduct, updateProduct, deleteProduct, restoreProduct
• Pattern: Each method defines its own Prisma include block

Proposed Refactoring

Extract Prisma query builders into centralized utilities

Create reusable query builder functions that encapsulate common Prisma query patterns:

// src/lib/services/product.query-builders.ts

/**
 * Standard product include pattern for list views
 * Returns minimal data needed for product cards/lists
 */
export const productListInclude = {
  category: {
    select: { id: true, name: true, slug: true },
  },
  brand: {
    select: { id: true, name: true, slug: true },
  },
  variants: {
    select: {
      id: true,
      name: true,
      sku: true,
      price: true,
      inventoryQty: true,
      isDefault: true,
      image: true,
    },
    orderBy: { isDefault: 'desc' },
  },
  _count: {
    select: {
      orderItems: true,
      reviews: true,
    },
  },
} as const;

/**
 * Full product include pattern for detail views
 * Returns all product data including attributes
 */
export const productDetailInclude = {
  ...productListInclude,
  variants: {
    select: {
      id: true,
      name: true,
      sku: true,
      barcode: true,
      price: true,
      compareAtPrice: true,
      inventoryQty: true,
      lowStockThreshold: true,
      weight: true,
      image: true,
      options: true,
      isDefault: true,
      createdAt: true,
      updatedAt: true,
    },
    orderBy: { isDefault: 'desc' },
  },
  attributes: {
    select: {
      id: true,
      productId: true,
      attributeId: true,
      value: true,
      attribute: {
        select: {
          id: true,
          name: true,
          values: true,
        },
      },
    },
  },
} as const;

/**
 * Minimal product include for inventory operations
 */
export const productInventoryInclude = {
  category: {
    select: { id: true, name: true, slug: true },
  },
  brand: {
    select: { id: true, name: true, slug: true },
  },
  variants: {
    select: {
      id: true,
      name: true,
      sku: true,
      price: true,
      inventoryQty: true,
      isDefault: true,
      image: true,
    },
    orderBy: { isDefault: 'desc' },
  },
  _count: {
    select: {
      orderItems: true,
      reviews: true,
    },
  },
} as const;

Benefits

1. Single source of truth: One place to modify product query patterns
2. Type safety: TypeScript infers types from const assertions
3. Consistency: All methods use the same query structure
4. Maintainability: Easy to add/remove fields or create new patterns
5. Testability: Query builders can be tested independently
6. Documentation: Clear semantic names explain intent (list vs detail vs inventory)
7. Bundle size: Shared references reduce duplication

Suggested Approach

Step 1: Create Query Builders Module

Create src/lib/services/product.query-builders.ts with the three include patterns above

Step 2: Refactor ProductService Methods

Replace inline include blocks with imported builders:

// Before
const product = await prisma.product.findFirst({
  where: { id: productId, storeId, deletedAt: null },
  include: {
    category: { select: { id: true, name: true, slug: true } },
    brand: { select: { id: true, name: true, slug: true } },
    variants: { /* ... */ },
    _count: { /* ... */ },
  },
});

// After
import { productDetailInclude } from './product.query-builders';

const product = await prisma.product.findFirst({
  where: { id: productId, storeId, deletedAt: null },
  include: productDetailInclude,
});

Step 3: Update All 8 Methods

• listProducts → use productListInclude
• getProductById → use productDetailInclude
• getProductBySlug → use productListInclude
• getLowStockProducts → use productInventoryInclude
• createProduct, updateProduct, restoreProduct → use productDetailInclude
• updateInventory, adjustInventory, decreaseStock → use productInventoryInclude

Step 4: Add Unit Tests

Test each query builder exports the correct structure

Code Example

Before:

async getProductById(
  productId: string,
  storeId: string
): Promise(ProductWithRelations | null) {
  const product = await prisma.product.findFirst({
    where: { id: productId, storeId, deletedAt: null },
    include: {
      category: {
        select: { id: true, name: true, slug: true },
      },
      brand: {
        select: { id: true, name: true, slug: true },
      },
      variants: {
        select: {
          id: true,
          name: true,
          sku: true,
          barcode: true,
          price: true,
          compareAtPrice: true,
          inventoryQty: true,
          lowStockThreshold: true,
          weight: true,
          image: true,
          options: true,
          isDefault: true,
          createdAt: true,
          updatedAt: true,
        },
        orderBy: { isDefault: 'desc' },
      },
      attributes: {
        select: {
          id: true,
          productId: true,
          attributeId: true,
          value: true,
          attribute: {
            select: {
              id: true,
              name: true,
              values: true,
            },
          },
        },
      },
      _count: {
        select: {
          orderItems: true,
          reviews: true,
        },
      },
    },
  });

  if (!product) return null;
  return this.normalizeProductFields(product) as unknown as ProductWithRelations;
}

After:

import { productDetailInclude } from './product.query-builders';

async getProductById(
  productId: string,
  storeId: string
): Promise(ProductWithRelations | null) {
  const product = await prisma.product.findFirst({
    where: { id: productId, storeId, deletedAt: null },
    include: productDetailInclude,
  });

  if (!product) return null;
  return this.normalizeProductFields(product) as unknown as ProductWithRelations;
}

Impact Assessment

• Effort: Medium - Requires creating new module, updating 8 methods, and testing
• Risk: Low - Pure refactoring with no logic changes; tests will catch regressions
• Benefit: High - Dramatically improves maintainability and reduces duplication
• Priority: High - Foundational improvement that will compound benefits over time

Related Files

• src/lib/services/product.service.ts (primary file)
• src/lib/services/inventory.service.ts (may benefit from similar patterns)
• src/lib/services/order.service.ts (may benefit from similar patterns)

Testing Strategy

1. Unit tests: Test each query builder exports correct structure
2. Integration tests: Verify all ProductService methods still return correct data shapes
3. Type safety: Ensure TypeScript properly infers types from const assertions
4. Regression tests: Run existing product-related tests to catch any issues

Additional Context

This refactoring follows the DRY (Don't Repeat Yourself) principle and the Single Responsibility Principle. By extracting query patterns, we separate the concern of "what data to fetch" from "how to use that data", making the codebase more maintainable and scalable.

This pattern can be extended to other services (CategoryService, OrderService, BrandService) for consistent query management across the entire codebase.

AI generated by Daily Codebase Analyzer - Semantic Function Extraction & Refactoring

• expires on Mar 7, 2026, 1:40 PM UTC