PostgreSQL Repository
Table of Contents
- Introduction
- Project Structure
- Core Components
- Architecture Overview
- Detailed Component Analysis
- Dependency Analysis
- Performance Considerations
- Troubleshooting Guide
- Conclusion
- Appendix
Introduction
This document is a comprehensive technical document for Sparrow PostgreSQL repository implementation, targeting database developers, systematically explains sqlx-based PostgreSQL repository design and implementation, covering the following topics:
- Design architecture and responsibility boundaries
- SQL generation strategy and ORM mapping mechanism
- Connection pool and transaction management
- Prepared statement and parameter binding optimization
- Complex query support (JOIN, aggregation, subquery, conditions and sorting)
- Pagination implementation and index optimization strategy
- Data type mapping, NULL value handling and error handling
- Performance monitoring, slow query analysis and connection pool tuning recommendations
Project Structure
Core modules around PostgreSQL repository are as follows:
- Repository Implementation: PostgresRepository[T] (sqlx-based generic implementation)
- Base Repository: BaseRepository[T] (unified interface contract and default behavior)
- Entity Interface: Entity (unified entity capability)
- Error Model: RepositoryError (standardized repository error)
- Configuration and Connection: SQLConfig, bootstrap.Database
- Pagination Model: PaginatedResult[T]
- Testing: postgres_test.go (integration testing and containerized database)
Core Components
PostgresRepository[T]
- sqlx-based PostgreSQL generic repository, provides complete CRUD, batch operations, pagination, conditional query, soft/hard delete, restore and other capabilities.
BaseRepository[T]
- Defines unified interface contract, provides default unimplemented methods, concrete repository responsible for overriding.
Entity Interface
- Defines identification and timestamp capabilities that entities must have.
RepositoryError
- Standardized repository error, contains entity type, operation, ID, message and cause.
SQLConfig
- Database connection configuration and DSN generation.
bootstrap.Database
- Application database client aggregation (SQLDB, Redis, Badger).
PaginatedResult[T]
- Pagination query result encapsulation.
Architecture Overview
PostgreSQL repository adopts "generic + reflection + sqlx" combination:
- Generic constrains entity type, ensures compile-time type safety
- Reflection scans/builds entity fields, maps db tag to database columns
- sqlx provides prepared statements, transactions, batch operations and Rows scanning
- Through BaseRepository[T] unified interface, convenient for extending other storage implementations
Detailed Component Analysis
SQL Generation Strategy and ORM Mapping
- Field Mapping: Maps Go fields to database columns through entity struct's db tag, establishes column name to field name mapping in scanEntity, achieves automatic scanning and assignment.
- Prepared Statements: All write operations use $N placeholders and sqlx's QueryContext/ExecContext, avoids concatenation and injection risks.
- Special Type Handling:
- []string → PostgreSQL array format string
- map[string]interface → JSON string
- Time type: Supports time.Time and *time.Time, parses multiple time formats
- NULL Value Handling: scanEntity sets zero values for empty values, ensures fields do not have illegal values.
Transaction Management
- SaveBatch: Loops to check if entity exists in single transaction, updates if exists, inserts if not exists, finally commits uniformly.
- Transaction Internal Methods: existsInTx, insertInTx, updateInTx, ensures consistency and atomicity.
- Rollback Strategy: Logs possible rollback errors in defer, avoids swallowing real errors.
Conditional Query and Sorting
- Conditional Query: buildWhereClause + buildConditionClause supports EQ, NEQ, GT, GTE, LT, LTE, LIKE, IN, IS_NULL, IS_NOT_NULL.
- Sorting: buildOrderByClause supports multi-field sorting, defaults to created_at DESC.
- Pagination: Limit/OFFSET, FindWithPagination, FindByFieldWithPagination, FindWithConditions.
- Soft Delete: Default queries all append deleted_at IS NULL condition.
Data Type Handling
- String, integer, float, boolean, array, JSON, time, pointer (including time) all have dedicated branch handling.
- Sets field zero value for empty values (nil), avoids dirty data.
- PostgreSQL array and JSON bidirectional conversion is completed by handleSpecialType and scanEntity together.
Soft Delete, Hard Delete and Restore
- Soft Delete: Update sets deleted_at to current time (Find series defaults to filter deleted_at IS NULL).
- Hard Delete: HardDelete directly DELETE.
- Restore: Restore sets deleted_at to empty.
Complex Query Support
- JOIN/Aggregation/Subquery: Current implementation focuses on single table query and condition/sorting/pagination. If JOIN/aggregation/subquery is needed, can implement through custom SQL or extended repository interface in business layer.
- Currently Supported: IN operator (ANY), ILIKE, IS NULL/NOT NULL, etc.
Pagination and Index Optimization
- Pagination: LIMIT/OFFSET, FindWithPagination, FindByFieldWithPagination, FindWithConditions.
- Index Recommendations (General Practice):
- Build indexes on columns commonly used for filtering (such as status, created_at)
- Composite index: WHERE + ORDER BY combination common queries
- LIKE queries recommend using ILIKE and combining with prefix index or GIN/GIST index (depending on data characteristics)
- Soft delete column deleted_at building index can improve filtering performance
Dependency Analysis
Repository Dependencies
- sqlx.DB: Database connection and transaction
- Entity Interface: Entity capability constraint
- BaseRepository[T]: Interface contract
- Logger: Error log recording
Configuration and Container
- SQLConfig: DSN generation
- bootstrap.Database: Aggregates database clients
- App/Container: Dependency injection and lifecycle management
Performance Considerations
Optimization Recommendations
- Prepared Statements and Parameter Binding: All write operations use $N placeholders, reduces SQL parsing and injection risks.
- Batch Operations: SaveBatch batch inserts/updates in single transaction, reduces round trips.
- Reflection Cost: buildInsertData/buildUpdateData/scanEntity use reflection, recommend cautious use in high-frequency paths, introduce cache or generator when necessary.
- Pagination Optimization: OFFSET performance degrades significantly when large, recommend using "cursor pagination" or "index-based positioning pagination".
- Connection Pool: Recommend configuring connection pool parameters during application startup (maximum connections, idle count, lifecycle), and adjust based on stress testing.
- Monitoring and Slow Query: Recommend integrating database slow query logs and metrics collection, combine application instrumentation to locate hot SQL.
Troubleshooting Guide
Common Error Types
- RepositoryError: Contains entity type, operation, ID, message and cause, convenient for quick location.
- sql.ErrNoRows: Expected error when query has no results, need to distinguish "not found" from "exception".
Troubleshooting Steps
- Check if entity ID is empty (validate before save/update/delete)
- Verify db tag matches database column name
- Observe transaction rollback logs (Rollback errors will be recorded)
- Use test cases to validate complex type (JSON, array, time) mapping
Test Reference
- postgres_test.go uses TestContainers to start PostgreSQL, creates test tables, covers Save, Find, Delete, pagination, conditional query and other scenarios.
Conclusion
Sparrow PostgreSQL repository through combination of generics and sqlx, provides type-safe, extensible and easy-to-maintain persistence capability. Its core advantages are:
- Unified interface contract and default behavior (BaseRepository[T])
- Reflection-based ORM mapping and special type handling
- Strict prepared statements and transaction management
- Complete pagination and conditional query capabilities
- Standardized error model and comprehensive test coverage
In production environment, recommend combining index strategy, connection pool configuration and performance monitoring system, continuously optimize query and write performance.