Database API Design Patterns

Modern applications rely heavily on APIs to interact with databases efficiently. Well-designed database APIs improve performance, maintainability, and security.

1. Repository Pattern

The Repository Pattern abstracts database operations behind a clean interface, decoupling business logic from data access.

Implementation

interface UserRepository {
  findById(id: string): Promise<User | null>;
  save(user: User): Promise<void>;
  delete(id: string): Promise<void>;
}

class PostgresUserRepository implements UserRepository {
  constructor(private pool: Pool) {}

  async findById(id: string): Promise<User | null> {
    const result = await this.pool.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );
    return result.rows[0] || null;
  }

  async save(user: User): Promise<void> {
    await this.pool.query(
      'INSERT INTO users(id, name, email) VALUES($1, $2, $3) ON CONFLICT(id) DO UPDATE SET name = $2, email = $3',
      [user.id, user.name, user.email]
    );
  }
}

Benefits

• Testability: Easily mock repositories for unit tests.
• Flexibility: Swap database implementations without changing business logic.

2. Data Mapper Pattern

The Data Mapper Pattern separates domain objects from database schema, allowing independent evolution.

Implementation

class UserMapper:
    @staticmethod
    def to_domain(row: dict) -> User:
        return User(
            id=row['id'],
            name=row['name'],
            email=row['email']
        )

    @staticmethod
    def to_persistence(user: User) -> dict:
        return {
            'id': user.id,
            'name': user.name,
            'email': user.email
        }

# Usage
user = UserMapper.to_domain(db_row)
db_data = UserMapper.to_persistence(user)

Benefits

• Schema independence: Domain objects remain unchanged if the database schema evolves.
• Custom mapping logic: Handle complex transformations cleanly.

3. CQRS (Command Query Responsibility Segregation)

CQRS separates read and write operations, optimizing for performance and scalability.

Implementation

// Command (Write) Side
public class UserCommandService {
    private readonly IUserRepository _repository;

    public async Task CreateUser(UserCreateDto dto) {
        var user = new User(dto.Name, dto.Email);
        await _repository.Save(user);
    }
}

// Query (Read) Side
public class UserQueryService {
    private readonly IUserReadRepository _readRepository;

    public async Task<UserDto> GetUser(Guid id) {
        return await _readRepository.GetById(id);
    }
}

Benefits

• Performance: Optimize read models separately (e.g., denormalized views).
• Scalability: Scale read and write workloads independently.

4. Active Record Pattern

The Active Record Pattern combines data access and domain logic in a single object.

Implementation (Ruby on Rails)

class User < ApplicationRecord
  validates :email, presence: true, uniqueness: true

  def activate
    update(active: true)
  end
end

# Usage
user = User.find(1)
user.activate

Trade-offs

• Simplicity: Quick to implement for simple CRUD operations.
• Coupling: Tightly binds domain logic to persistence.

5. Connection Pooling

Connection pooling manages database connections efficiently, reducing overhead.

Implementation (Java with HikariCP)

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost/db");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(10);

HikariDataSource dataSource = new HikariDataSource(config);

// Usage
try (Connection conn = dataSource.getConnection()) {
    // Execute queries
}

Best Practices

• Size pools appropriately: Match application concurrency needs.
• Monitor usage: Track connection wait times and leaks.

6. Pagination and Filtering

Efficiently handle large datasets with pagination and filtering.

Implementation (GraphQL)

type Query {
  users(
    first: Int
    after: String
    filter: UserFilter
  ): UserConnection!
}

input UserFilter {
  name: String
  email: String
}

REST Example

GET /api/users?limit=20&offset=40&name=John

Considerations

• Cursor-based pagination: More stable than offset-based for large datasets.
• Indexed filters: Ensure filtered fields are indexed for performance.

7. Batch Operations

Reduce round-trips with batch inserts/updates.

Implementation (SQL)

-- Batch Insert
INSERT INTO users (name, email) VALUES 
  ('John', 'john@example.com'),
  ('Jane', 'jane@example.com');

-- Batch Update (PostgreSQL)
UPDATE users SET 
  name = tmp.name,
  email = tmp.email
FROM (VALUES 
  (1, 'John Updated', 'john@example.com'),
  (2, 'Jane Updated', 'jane@example.com')
) AS tmp(id, name, email)
WHERE users.id = tmp.id;

Performance Gains

• Reduced latency: Fewer network calls.
• Atomicity: Ensure consistency with transactions.

3. Implement monitoring to track API performance over time.