Business logic entangled with SQL queries creates
maintenance nightmares. Change databases? Rewrite everything. Test logic? Need a real database. Mock data access?
Hundreds of places to change. The Repository pattern solves this by abstracting data access behind a clean
interface—transforming tight database coupling into testable, maintainable domain logic.
This guide covers production-ready repository patterns that
separate business logic from data access. We’ll build clean, testable applications where switching databases is a
configuration change, not a rewrite.
Why Repository Pattern Transforms Architecture
The Inline SQL Problem
SQL scattered throughout code suffers from:
- Tight coupling: Business logic depends on database structure
- Duplication: Same queries repeated everywhere
- Hard to test: Need database for every test
- Database lock-in: Switching databases requires massive rewrite
- No abstraction: Domain logic polluted with data access
- Difficult refactoring: Schema changes break many files
Repository Pattern Benefits
- Clean separation: Domain logic separate from data access
- Testability: Mock repositories for unit tests
- Centralized queries: One place to optimize
- Database flexibility: Swap implementations easily
- Query reuse: Common queries in one place
- Domain-centric: Repository speaks business language
Pattern 1: Basic Repository Interface
Generic CRUD Repository
// Generic repository interface
public interface IRepository<T> where T : class
{
Task<T> GetByIdAsync(string id);
Task<IEnumerable<T>> GetAllAsync();
Task<T> AddAsync(T entity);
Task UpdateAsync(T entity);
Task DeleteAsync(string id);
}
// Entity
public class Product
{
public string Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public string Category { get; set; }
public int StockQuantity { get; set; }
}
// ❌ BAD: Direct SQL in service
public class ProductService
{
public async Task<Product> GetProductBad(string id)
{
using var connection = new SqlConnection(connectionString);
var sql = "SELECT * FROM Products WHERE Id = @Id";
return await connection.QueryFirstOrDefaultAsync<Product>(sql,
new { Id = id });
}
}
// ✅ GOOD: Repository abstraction
public class ProductService
{
private readonly IRepository<Product> _productRepository;
public ProductService(IRepository<Product> productRepository)
{
_productRepository = productRepository;
}
public async Task<Product> GetProduct(string id)
{
return await _productRepository.GetByIdAsync(id);
}
public async Task<Product> CreateProduct(CreateProductRequest request)
{
var product = new Product
{
Id = Guid.NewGuid().ToString(),
Name = request.Name,
Price = request.Price,
Category = request.Category,
StockQuantity = request.InitialStock
};
return await _productRepository.AddAsync(product);
}
}
// SQL Server implementation
public class SqlProductRepository : IRepository<Product>
{
private readonly DbContext _dbContext;
public async Task<Product> GetByIdAsync(string id)
{
return await _dbContext.Products.FindAsync(id);
}
public async Task<IEnumerable<Product>> GetAllAsync()
{
return await _dbContext.Products.ToListAsync();
}
public async Task<Product> AddAsync(Product entity)
{
_dbContext.Products.Add(entity);
await _dbContext.SaveChangesAsync();
return entity;
}
public async Task UpdateAsync(Product entity)
{
_dbContext.Products.Update(entity);
await _dbContext.SaveChangesAsync();
}
public async Task DeleteAsync(string id)
{
var product = await GetByIdAsync(id);
if (product != null)
{
_dbContext.Products.Remove(product);
await _dbContext.SaveChangesAsync();
}
}
}
// Benefits:
// - ProductService has no SQL knowledge
// - Easy to test with mock repository
// - Can swap database without changing business logic
Pattern 2: Domain-Specific Repository
Business-Focused Query Methods
// Domain-specific repository interface
interface IOrderRepository {
// Rich, business-focused methods
findById(orderId: string): Promise<Order | null>;
findByCustomer(customerId: string): Promise<Order[]>;
findPendingOrders(): Promise<Order[]>;
findOrdersAboveAmount(amount: number): Promise<Order[]>;
findRecentOrders(days: number): Promise<Order[]>;
save(order: Order): Promise<Order>;
delete(orderId: string): Promise<void>;
}
// PostgreSQL implementation
class PostgresOrderRepository implements IOrderRepository {
constructor(private db: Database) {}
async findById(orderId: string): Promise<Order | null> {
const row = await this.db.query(
'SELECT * FROM orders WHERE id = $1',
[orderId]
);
return row ? this.mapToOrder(row) : null;
}
async findByCustomer(customerId: string): Promise<Order[]> {
const rows = await this.db.query(
`SELECT * FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC`,
[customerId]
);
return rows.map(r => this.mapToOrder(r));
}
async findPendingOrders(): Promise<Order[]> {
const rows = await this.db.query(
`SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at ASC`
);
return rows.map(r => this.mapToOrder(r));
}
async findOrdersAboveAmount(amount: number): Promise<Order[]> {
const rows = await this.db.query(
`SELECT * FROM orders
WHERE total_amount > $1
ORDER BY total_amount DESC`,
[amount]
);
return rows.map(r => this.mapToOrder(r));
}
async findRecentOrders(days: number): Promise<Order[]> {
const rows = await this.db.query(
`SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '$1 days'
ORDER BY created_at DESC`,
[days]
);
return rows.map(r => this.mapToOrder(r));
}
async save(order: Order): Promise<Order> {
const result = await this.db.query(
`INSERT INTO orders (id, customer_id, total_amount, status, created_at)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (id)
DO UPDATE SET
customer_id = EXCLUDED.customer_id,
total_amount = EXCLUDED.total_amount,
status = EXCLUDED.status
RETURNING *`,
[order.id, order.customerId, order.totalAmount,
order.status, order.createdAt]
);
return this.mapToOrder(result);
}
private mapToOrder(row: any): Order {
return new Order(
row.id,
row.customer_id,
row.total_amount,
row.status,
new Date(row.created_at)
);
}
}
// Usage in service
class OrderService {
constructor(private orderRepo: IOrderRepository) {}
async getCustomerOrders(customerId: string): Promise<Order[]> {
// Business language, no SQL
return await this.orderRepo.findByCustomer(customerId);
}
async processLargeOrders(threshold: number): Promise<void> {
const largeOrders = await this.orderRepo.findOrdersAboveAmount(threshold);
for (const order of largeOrders) {
await this.sendVipNotification(order);
}
}
}
Pattern 3: Specification Pattern
Composable Query Criteria
// Specification interface
public interface Specification<T> {
boolean isSatisfiedBy(T entity);
Predicate<T> toPredicate();
}
// Base specifications
public class CustomerIsActiveSpec implements Specification<Customer> {
public boolean isSatisfiedBy(Customer customer) {
return customer.isActive();
}
public Predicate<Customer> toPredicate() {
return Customer::isActive;
}
}
public class CustomerInCountrySpec implements Specification<Customer> {
private final String country;
public CustomerInCountrySpec(String country) {
this.country = country;
}
public boolean isSatisfiedBy(Customer customer) {
return customer.getCountry().equals(country);
}
public Predicate<Customer> toPredicate() {
return c -> c.getCountry().equals(country);
}
}
public class CustomerWithRevenueAboveSpec implements Specification<Customer> {
private final BigDecimal threshold;
public CustomerWithRevenueAboveSpec(BigDecimal threshold) {
this.threshold = threshold;
}
public boolean isSatisfiedBy(Customer customer) {
return customer.getTotalRevenue().compareTo(threshold) > 0;
}
public Predicate<Customer> toPredicate() {
return c -> c.getTotalRevenue().compareTo(threshold) > 0;
}
}
// Composite specifications
public class AndSpecification<T> implements Specification<T> {
private final Specification<T> left;
private final Specification<T> right;
public AndSpecification(Specification<T> left, Specification<T> right) {
this.left = left;
this.right = right;
}
public boolean isSatisfiedBy(T entity) {
return left.isSatisfiedBy(entity) && right.isSatisfiedBy(entity);
}
public Predicate<T> toPredicate() {
return left.toPredicate().and(right.toPredicate());
}
}
// Repository with specifications
public interface ICustomerRepository {
List<Customer> find(Specification<Customer> spec);
}
// Usage
public class CustomerService {
private final ICustomerRepository repository;
public List<Customer> getVipCustomersInUS() {
// Compose specifications
var spec = new AndSpecification<>(
new AndSpecification<>(
new CustomerIsActiveSpec(),
new CustomerInCountrySpec("US")
),
new CustomerWithRevenueAboveSpec(new BigDecimal("100000"))
);
return repository.find(spec);
}
}
Pattern 4: Unit of Work Pattern
Coordinate Multiple Repositories
// Unit of Work interface
public interface IUnitOfWork : IDisposable
{
IRepository<Order> Orders { get; }
IRepository<Customer> Customers { get; }
IRepository<Product> Products { get; }
IRepository<Invoice> Invoices { get; }
Task<int> SaveChangesAsync();
Task BeginTransactionAsync();
Task CommitTransactionAsync();
Task RollbackTransactionAsync();
}
// Implementation
public class UnitOfWork : IUnitOfWork
{
private readonly DbContext _context;
private IDbContextTransaction _transaction;
public UnitOfWork(DbContext context)
{
_context = context;
Orders = new Repository<Order>(_context);
Customers = new Repository<Customer>(_context);
Products = new Repository<Product>(_context);
Invoices = new Repository<Invoice>(_context);
}
public IRepository<Order> Orders { get; private set; }
public IRepository<Customer> Customers { get; private set; }
public IRepository<Product> Products { get; private set; }
public IRepository<Invoice> Invoices { get; private set; }
public async Task<int> SaveChangesAsync()
{
return await _context.SaveChangesAsync();
}
public async Task BeginTransactionAsync()
{
_transaction = await _context.Database.BeginTransactionAsync();
}
public async Task CommitTransactionAsync()
{
try
{
await _context.SaveChangesAsync();
await _transaction.CommitAsync();
}
catch
{
await _transaction.RollbackAsync();
throw;
}
}
public async Task RollbackTransactionAsync()
{
await _transaction.RollbackAsync();
}
public void Dispose()
{
_transaction?.Dispose();
_context?.Dispose();
}
}
// Usage: Coordinated multi-repository operations
public class OrderService
{
private readonly IUnitOfWork _unitOfWork;
public async Task<Order> PlaceOrderAsync(PlaceOrderRequest request)
{
await _unitOfWork.BeginTransactionAsync();
try
{
// 1. Create order
var order = new Order
{
Id = Guid.NewGuid().ToString(),
CustomerId = request.CustomerId,
TotalAmount = request.TotalAmount
};
await _unitOfWork.Orders.AddAsync(order);
// 2. Update inventory
foreach (var item in request.Items)
{
var product = await _unitOfWork.Products.GetByIdAsync(item.ProductId);
product.StockQuantity -= item.Quantity;
await _unitOfWork.Products.UpdateAsync(product);
}
// 3. Create invoice
var invoice = new Invoice
{
OrderId = order.Id,
Amount = order.TotalAmount,
DueDate = DateTime.Now.AddDays(30)
};
await _unitOfWork.Invoices.AddAsync(invoice);
// All or nothing
await _unitOfWork.CommitTransactionAsync();
return order;
}
catch
{
await _unitOfWork.RollbackTransactionAsync();
throw;
}
}
}
Pattern 5: In-Memory Repository for Testing
Test Without Database
from typing import List, Optional, Dict
from abc import ABC, abstractmethod
# Repository interface
class IUserRepository(ABC):
@abstractmethod
async def find_by_id(self, user_id: str) -> Optional['User']:
pass
@abstractmethod
async def find_by_email(self, email: str) -> Optional['User']:
pass
@abstractmethod
async def save(self, user: 'User') -> 'User':
pass
@abstractmethod
async def delete(self, user_id: str) -> None:
pass
# In-memory implementation (for tests)
class InMemoryUserRepository(IUserRepository):
def __init__(self):
self._users: Dict[str, User] = {}
async def find_by_id(self, user_id: str) -> Optional[User]:
return self._users.get(user_id)
async def find_by_email(self, email: str) -> Optional[User]:
for user in self._users.values():
if user.email == email:
return user
return None
async def save(self, user: User) -> User:
self._users[user.id] = user
return user
async def delete(self, user_id: str) -> None:
if user_id in self._users:
del self._users[user_id]
# PostgreSQL implementation (for production)
class PostgresUserRepository(IUserRepository):
def __init__(self, db_connection):
self.db = db_connection
async def find_by_id(self, user_id: str) -> Optional[User]:
row = await self.db.fetchone(
'SELECT * FROM users WHERE id = $1',
user_id
)
return self._map_to_user(row) if row else None
async def find_by_email(self, email: str) -> Optional[User]:
row = await self.db.fetchone(
'SELECT * FROM users WHERE email = $1',
email
)
return self._map_to_user(row) if row else None
async def save(self, user: User) -> User:
await self.db.execute(
'''INSERT INTO users (id, email, name, created_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email, name = EXCLUDED.name''',
user.id, user.email, user.name, user.created_at
)
return user
async def delete(self, user_id: str) -> None:
await self.db.execute('DELETE FROM users WHERE id = $1', user_id)
# Service (works with any repository)
class UserService:
def __init__(self, user_repo: IUserRepository):
self.user_repo = user_repo
async def register_user(self, email: str, name: str) -> User:
# Check if user exists
existing = await self.user_repo.find_by_email(email)
if existing:
raise ValueError(f"User with email {email} already exists")
# Create new user
user = User(
id=str(uuid.uuid4()),
email=email,
name=name,
created_at=datetime.now()
)
return await self.user_repo.save(user)
# Test with in-memory repository
async def test_register_user():
repo = InMemoryUserRepository()
service = UserService(repo)
# Test registration
user = await service.register_user("test@example.com", "Test User")
assert user.email == "test@example.com"
# Test duplicate
try:
await service.register_user("test@example.com", "Duplicate")
assert False, "Should have raised error"
except ValueError:
pass # Expected
# Production with PostgreSQL repository
async def production_usage():
db = await create_db_connection()
repo = PostgresUserRepository(db)
service = UserService(repo)
user = await service.register_user("prod@example.com", "Prod User")
Real-World Example: E-Commerce
Complete Repository Layer
// Product repository with rich queries
public interface IProductRepository
{
Task<Product> GetByIdAsync(string id);
Task<IEnumerable<Product>> SearchAsync(string query);
Task<IEnumerable<Product>> GetByCategoryAsync(string category);
Task<IEnumerable<Product>> GetLowStockAsync(int threshold);
Task<IEnumerable<Product>> GetTopSellingAsync(int count);
Task<Product> AddAsync(Product product);
Task UpdateAsync(Product product);
Task DeleteAsync(string id);
}
// Order repository
public interface IOrderRepository
{
Task<Order> GetByIdAsync(string id);
Task<IEnumerable<Order>> GetByCustomerAsync(string customerId);
Task<IEnumerable<Order>> GetPendingAsync();
Task<Order> AddAsync(Order order);
Task UpdateStatusAsync(string orderId, OrderStatus status);
}
// Service using repositories
public class ECommerceService
{
private readonly IProductRepository _products;
private readonly IOrderRepository _orders;
private readonly IUnitOfWork _unitOfWork;
public async Task<OrderResult> PlaceOrderAsync(CreateOrderRequest request)
{
await _unitOfWork.BeginTransactionAsync();
try
{
// 1. Validate inventory
var orderItems = new List<OrderItem>();
foreach (var item in request.Items)
{
var product = await _products.GetByIdAsync(item.ProductId);
if (product.StockQuantity < item.Quantity)
{
return OrderResult.Failed($"Insufficient stock for {product.Name}");
}
orderItems.Add(new OrderItem
{
ProductId = product.Id,
ProductName = product.Name,
Quantity = item.Quantity,
UnitPrice = product.Price
});
}
// 2. Create order
var order = new Order
{
Id = Guid.NewGuid().ToString(),
CustomerId = request.CustomerId,
Items = orderItems,
TotalAmount = orderItems.Sum(i => i.Quantity * i.UnitPrice),
Status = OrderStatus.Pending,
CreatedAt = DateTime.UtcNow
};
await _orders.AddAsync(order);
// 3. Update inventory
foreach (var item in orderItems)
{
var product = await _products.GetByIdAsync(item.ProductId);
product.StockQuantity -= item.Quantity;
await _products.UpdateAsync(product);
}
await _unitOfWork.CommitTransactionAsync();
return OrderResult.Success(order);
}
catch (Exception ex)
{
await _unitOfWork.RollbackTransactionAsync();
return OrderResult.Failed(ex.Message);
}
}
public async Task<InventoryReport> GetInventoryReportAsync()
{
// Rich repository methods enable clean service code
var lowStock = await _products.GetLowStockAsync(10);
var topSelling = await _products.GetTopSellingAsync(10);
return new InventoryReport
{
LowStockProducts = lowStock.ToList(),
TopSellingProducts = topSelling.ToList(),
GeneratedAt = DateTime.UtcNow
};
}
}
Best Practices
- One repository per aggregate: Not one per table
- Business-focused methods: findActiveCustomers() not query()
- Return domain objects: Not database rows
- Use specifications: For complex, composable queries
- Unit of Work: Coordinate multiple repositories
- Interface first: Define contract before implementation
- Avoid generic CRUD waste: Add domain methods as needed
Common Pitfalls
- Repository per table: Should be per aggregate root
- Leaking IQueryable: Exposes data access details
- Generic CRUD only: Missing domain-specific methods
- No Unit of Work: Transaction management scattered
- Bypassing repository: Direct DbContext access
- Over-abstraction: Too many repository layers
Key Takeaways
- Repository pattern abstracts data access from business logic
- Define repositories per aggregate root, not per table
- Use domain-focused methods: findActiveCustomers() over generic queries
- In-memory implementations enable fast, database-free testing
- Unit of Work coordinates multiple repositories transactionally
- Specification pattern enables composable query criteria
- Interface-based design allows swapping implementations
- Return domain objects, not database rows or DTOs
The Repository pattern is fundamental to clean architecture. By abstracting data access behind domain-focused
interfaces, you create testable, maintainable code that doesn’t care whether data comes from SQL Server,
MongoDB, or an in-memory dictionary. It’s the foundation for true persistence ignorance.
Discover more from C4: Container, Code, Cloud & Context
Subscribe to get the latest posts sent to your email.