General SQL Database Repository

Table of Contents

1. Introduction
2. Project Structure
3. Core Components
4. Architecture Overview
5. Detailed Component Analysis
6. Dependency Analysis
7. Performance Considerations
8. Troubleshooting Guide
9. Conclusion
10. Appendix

Introduction

This document targets multi-database support scenarios, systematically explains Sparrow general SQL database repository design and implementation, covers the following topics:

• Abstract Design and Interface Contract: Through generic repository interface and base repository abstraction, unifies CRUD and query capabilities of different database implementations.
• Database Independence: Through unified entity interface and query option model, shields underlying differences.
• SQL Generation Strategy: Dynamic SQL building, parameter binding, condition assembly, pagination and sorting.
• Dialect Support and Connection Management: Supports different placeholders and syntax differences between sqlite3 and PostgreSQL; connection pool and transaction management.
• Dynamic SQL and Parameter Binding: IN clause, LIKE, NULL condition and other dynamic assembly and safe binding.
• Query Optimization: Soft delete filtering, default sorting, LIMIT/OFFSET, COUNT statistics.
• Error Handling and Retry: Unified repository error type, retry strategy and graceful shutdown.
• Migration and Version Compatibility: Through test-driven database containers and DDL change validation.

Project Structure

Around the core domain of "repository", project adopts layered and domain responsibility-based organization:

• usecase: Defines repository interface and query model (generic, condition, sorting, pagination).
• entity: Defines entity interface and base entity struct.
• persistence/repo: Concrete repository implementations (database/sql-based general implementation and sqlx-based PostgreSQL implementation).
• bootstrap: Application startup, container registration, database connection configuration and retry mechanism.
• config: Database connection configuration model and DSN generation.
• errs: Unified error type (repository error).

Core Components

Repository Interface and Query Model

• Repository interface defines Save, Find, Update, Delete, batch operations, pagination, statistics, conditional query, random sampling and other capabilities.
• Query model includes QueryOptions (condition, sorting, pagination), QueryCondition (field, operator, value).

Entity Model

• Entity interface requires ID, creation/update time read and set capabilities; BaseEntity provides standard fields and constructor.

Database Connection and Configuration

• SQLConfig provides Driver, Host, Port, User, Password, Dbname and other fields, and generates DSN.
• bootstrap.Database uniformly injects SQLDB, Redis, Badger into container.

Error Model

• RepositoryError provides unified repository error encapsulation, contains entity type, operation, ID, message and cause.

Architecture Overview

General SQL repository through "interface + generic + base abstraction" way, provides consistent API surface for different databases. database/sql implementation is suitable for SQLite and other lightweight scenarios; sqlx implementation targets PostgreSQL provides stronger type and scanning capabilities.

Detailed Component Analysis

SqlDBRepository[T]: database/sql-based General Implementation

Design Points

• Through reflection infers table name and entity type, supports soft delete detection (DeletedAt field).
• Unified Save: If exists then updates, otherwise inserts; automatically maintains CreatedAt/UpdatedAt.
• Batch SaveBatch: Loops processing in single transaction, ensures consistency.
• Query Family: Supports by ID, ID list, field value, condition combination, pagination, statistics, random sampling.
• Condition Building: EQ/NEQ/GT/GTE/LT/LTE/LIKE/IN/IS_NULL/IS_NOT_NULL.

SQL Generation and Parameter Binding

• Placeholders: ? (SQLite/MySQL), IN clause dynamically generates placeholders, LIKE automatically wraps wildcards.
• Soft Delete: Query automatically appends deleted_at IS NULL condition.
• Pagination: LIMIT ? OFFSET ?, defaults to created_at DESC sorting.

Transaction and Connection

• Save/SaveBatch/DeleteBatch explicitly start transactions, rollback on exception, commit on success.
• Manages connection pool and lifecycle through *sql.DB.

Error Handling

• Uses unified RepositoryError to wrap operation, entity type, ID and message.
• Special judgment for sql.ErrNoRows, returns "not found" error.

PostgresRepository[T]: sqlx-based PostgreSQL Implementation

Design Points

• Uses *sqlx.DB and QueryxContext/Select, supports richer scanning and return capabilities.
• Supports PostgreSQL-specific placeholders $1/$2 and array, JSONB and other type handling.
• Provides soft delete (update deleted_at) and hard delete (Delete method) two deletion strategies.
• Batch operations execute in transaction, explicit rollback and log recording.

SQL Generation and Parameter Binding

• Placeholders: $1/$2, IN clause dynamically generates $n.
• Field Name Validation: isValidFieldName prevents injection risk.
• Array and JSONB: Handled through custom types and scan functions.

Transaction and Connection

• Uses *sqlx.Tx, supports GetContext/QueryRowContext and other methods.

Error Handling

• Unified RepositoryError; returns "not found" for delete scenarios where RowsAffected is 0.

Conditional Query and Pagination

Condition Assembly

• buildCondition converts QueryCondition to SQL fragment and parameter slice, supports IN, LIKE, NULL, etc.

Pagination and Sorting

• FindWithConditions automatically concatenates WHERE/ORDER BY/LIMIT/OFFSET, defaults to created_at DESC.

Statistics

• CountWithConditions and CountByField/Count uniformly go through WHERE condition and COUNT(*).

Dependency Analysis

Connection Pool and Transaction Management

Connection Pool

• database/sql manages connection pool through *sql.DB; bootstrap registers SQLDB, convenient for global sharing.

Transaction

• Save/SaveBatch/DeleteBatch execute in single transaction, rollback on exception, commit on success.
• PostgreSQL implementation uses BeginTxx/BeginTx, enhances context control.

Graceful Shutdown

• App.Start/Shutdown and CleanUp manage subprocess and resource release; retry goroutine controls exit through cancel.

Error Handling and Retry

Unified Error

• RepositoryError provides entity type, operation, ID, message and cause, convenient for location and log tracing.

Retry Mechanism

• App internally starts retry goroutine, performs exponential backoff retry for startup failed subprocesses (maximum 10 times, upper limit 1 minute).

Test Validation

• SQLite/PostgreSQL tests cover Save, Find, Update, Delete, batch operations, pagination, conditional query, random sampling and other scenarios.

Dependency Relationship

Component Coupling

• Repository implementation depends on usecase interface and entity interface, low coupling high cohesion.
• database/sql and sqlx as external dependencies, separately serve general SQL and PostgreSQL.

External Dependencies

• database/sql, github.com/jmoiron/sqlx, test container testcontainers, etc.

Possible Circular Dependencies

• Current structure is clear, no circular imports found; repository implementation only depends on usecase and entity.

Performance Considerations

SQL Generation and Parameter Binding

• IN clause dynamically generates placeholders by element count, avoids SQL injection caused by string concatenation.
• LIKE condition automatically wraps wildcards, avoids full table scan.

Pagination and Sorting

• Defaults to created_at DESC sorting, recommend building indexes on high-frequency query fields.
• LIMIT/OFFSET suitable for small to medium scale pagination; large scale scenarios recommend using cursor pagination or index-based keyset pagination.

Soft Delete

• Query automatically filters deleted_at IS NULL, reduces useless data scanning; recommend building index for deleted_at.

Connection Pool

• database/sql manages connection pool through *sql.DB; recommend reasonably configuring maximum connections and idle connections combined with business peak.

Batch Operations

• SaveBatch/DeleteBatch execute in single transaction, reduces round trips and lock contention; note single transaction size and timeout settings.

Troubleshooting Guide

Common Error Types

• RepositoryError: Contains entity type, operation, ID, message and cause, convenient for quick location.

Troubleshooting Steps

• Check if entity ID is empty (Save/Update/Delete/Exists all require non-empty ID).
• Check database connection and DSN (Driver/User/Host/Port/Dbname).
• Check table structure and field mapping (DeletedAt, CreatedAt, UpdatedAt, etc.).
• Check transaction status (whether correctly rollback on exception).
• Observe logs and retry behavior (App's retry goroutine will record backoff and maximum retry count).

Test Validation

• Use sqldb_test and postgres_test to validate Save/Find/Update/Delete/batch operations/pagination/conditional query/random sampling and other scenarios.

Conclusion

Sparrow's general SQL repository through "interface + generic + base abstraction", while ensuring database independence, provides complete CRUD, query, pagination, statistics and condition building capabilities. database/sql implementation adapts to SQLite and other lightweight scenarios, sqlx implementation strengthens PostgreSQL's type and scanning capabilities. Combined with unified error model, transaction management and retry mechanism, can provide consistent and reliable repository experience in multi-database environments.

Appendix

Configuration Item Description

• Driver, Host, Port, User, Password, Dbname: Used to generate DSN.
• ESDsn: Event store dedicated database name.

Startup and Connection

• bootstrap.App is responsible for loading configuration, creating logs, registering containers, starting HTTP service and retry goroutine.
• bootstrap.Database injects SQLDB, Redis, Badger into container, convenient for global access.