Building DAPS: A Centralised Data Access Proxy Service for Scalable, Multi-Tenant Microservices

In modern microservice architectures, managing data access can become a significant challenge, especially in multi-tenant environments where each tenant might have distinct database configurations or even instances. Repeating data access logic, handling diverse database credentials securely, and ensuring consistent type handling across numerous services can lead to boilerplate, operational overhead, and potential inconsistencies.

To address these challenges, I developed DAPS (Data Access Proxy Service), a system designed to centralise and streamline database interactions. This article provides a technical overview of its architecture, the problems it solves, and some key implementation details.

The Core Problem DAPS Solves

Imagine a fintech platform rapidly onboarding new institutional clients (Banks, MFBs). Each client requires its own isolated data environment (test and live databases). If every microservice (e.g., Onboarding, Transactions, Reporting) had to manage connection details and data access logic for every tenant:

1. Configuration Nightmare: Each service would need an ever-growing list of tenant DB credentials.

2. Deployment Bottlenecks: Adding a new tenant or updating credentials would necessitate redeploying multiple services.

3. Boilerplate Overload: Developers would repeatedly write code for constructing queries, setting parameters, handling JDBC intricacies, and mapping results.

4. Inconsistent Data Handling: Risk of different services handling types (especially nulls, dates, and collections for IN clauses) inconsistently when talking to the database.

DAPS was conceived to abstract and centralise these concerns.

The DAPS Ecosystem: Two Key Components

DAPS ecosystem comprises a client-side library with a code generator and a server-side proxy service:

1. dataaccessproxy-client (The Developer's Toolkit)

This Java library is embedded in each microservice that needs to access data. Its main features are:

• @GenerateDapsFacade Annotation & Processor: Developers define simple Java interfaces for their data access needs, similar to Spring Data repositories (e.g., UserRepository). Annotating such an interface with @GenerateDapsFacade triggers a compile-time annotation processor (DapsFacadeGeneratorProcessor).

  • This processor generates a Facade Interface (e.g., UserDapsFacade) and a Facade Implementation (e.g., UserDapsFacadeImpl).

  • The generated facade handles all the "plumbing" to communicate with the DAPS service.

• Typed Parameter Handling (ParameterDescriptor): A crucial innovation was introducing a ParameterDescriptor(Object value, int sqlJdbcType) DTO.

  • The facade generator analyzes repository method parameters (e.g., String email, LocalDateTime transactionDate, List<Long> ids) and determines their corresponding java.sql.Types constant.

  • It generates code to wrap each parameter value (and its SQL type hint) into a ParameterDescriptor. For null values, this explicit type hint is vital for the downstream JDBC layer.

  • java.time.* types are converted to their java.sql.* counterparts (e.g., LocalDateTime -> java.sql.Timestamp) by the generated code before creating the ParameterDescriptor.

• QueryRequest DTO: This DTO, now containing List<ParameterDescriptor> or Map<String, ParameterDescriptor>, is sent to the DAPS service.

• EntitySqlUtil (for save methods): For repository methods like User save(User user); (which developers must explicitly override with the concrete entity type), this runtime utility uses reflection and persistence annotations (@Id, @Table, @Column) on the entity to:

  • Dynamically generate INSERT ... RETURNING id (for PostgreSQL) or UPDATE ... WHERE id = ? SQL statements.

  • Prepare a Map<String, ParameterDescriptor> for the SQL, using convertToJdbcCompliantType and getJdbcTypeForJavaType helpers.

  • Cache entity metadata ( table/column names, ID field) for performance.

• NamedParameterSqlUtil: This utility (used by both the generator for paginated named queries and the DAPS service) was updated to accept Map<String, ParameterDescriptor> and output an ordinal SQL string along with an ordered List<ParameterDescriptor>, preserving the crucial sqlDataType hints.

• HttpDataAccessProxyClient: The runtime client that makes HTTP calls to the DAPS service. It now constructs QueryRequest DTOs using ParameterDescriptors and includes intelligent result mapping (differentiating scalar results from complex POJO/entity mapping for List<T> and Optional<T>).

2. dataaccessproxy-svc (The Central Service)

This Spring Boot microservice is the single gateway for database operations.

• TenantDataSourceManager: Manages a cache of per-tenant, per-mode (live/test) DataSource objects (HikariCP connection pools). It fetches encrypted DB credentials from a central TenantConfigService (which itself caches decrypted details) and creates/configures pools on demand. This was tuned by adjusting default pool sizes (maximumPoolSize, minimumIdle) to prevent exhausting database server connections.

• QueryExecutionService.executeQuery(QueryRequest request): The core workhorse.

  • Parameter Style Handling: If request.parameterStyle is NAMED, it uses the NamedParameterSqlUtil to convert the SQL and Map<String, ParameterDescriptor> to an ordinal SQL string and List<ParameterDescriptor>.

  • IN (?) Clause Expansion: If a ParameterDescriptor's value is a Collection, the SQL string is dynamically rewritten to expand IN (?) to IN (?, ?, ...) and the collection's items are flattened into the finalParamDescriptors list (with guessed SQL types for the items). Empty collections are handled by generating IN (?) and passing a ParameterDescriptor(null, Types.NULL).

  • JDBC Parameter Setting (Key Fixes):

    • It iterates through the finalParamDescriptors.

    • For null values: It uses preparedStatement.setNull(index, parameterDescriptor.sqlDataType()). This explicit typing of nulls was critical to resolve PostgreSQL errors like "could not determine data type of parameter" and "operator does not exist: timestamp >= character varying".

    • For non-null values: It uses specific JDBC setters (setTimestamp, setDate, setString, setBigDecimal, etc.) based on instanceof checks of the parameterDescriptor.value(). It also includes heuristic parsing for strings that might represent dates, guided by the sqlDataType hint.

  • Result Processing: Returns List<Map<String, Object>> (with lowercased column labels) or an updateCount.

  • Error Reporting: Standardised ApiError DTO with specific errorCode and message for clear feedback to clients.

Benefits Achieved:

• Drastic Reduction in Developer Boilerplate: Engineers define simple interfaces; the generator handles DAPS interaction complexities.

• Improved Type Safety: Explicit type handling with ParameterDescriptor from client to DAPS service to JDBC resolved numerous subtle bugs related to null parameters and date/timestamp conversions.

• Centralised DB Connection Management: Solved the nightmare of managing hundreds of tenant DB credentials across multiple microservices and eliminated redeployments for new tenant onboarding.

• Enhanced Scalability & Maintainability: The DAPS service can be scaled independently, and connection pooling is managed centrally. Changes to DB interaction logic are localised.

• Faster Tenant Onboarding: Combined with AWS Step Functions for workflow orchestration (automating DNS validation, reverse proxy setup, and DAPS configuration for new tenants), this system enables rapid onboarding of new institutional clients.

Technical Challenges & Solutions Highlights:

• null Parameters in JDBC: PostgreSQL's strictness with untyped nulls in certain SQL expressions was a major hurdle. Propagating type hints via ParameterDescriptor and using PreparedStatement.setNull(index, sqlType) in the DAPS service was the key.

• Generic IN Clause Expansion: Handling List<T> parameters for SQL IN clauses requires dynamic SQL modification and careful parameter list flattening.

• Date/Time Type Consistency: Ensuring java.time.* types are consistently converted to java.sql.* types (or ISO strings) for JDBC/JSON, and then correctly handled/parsed on the receiving end.

• SELECT with JOINs: Educating developers to use explicit column selection and aliasing is crucial to avoid ambiguous column names when mapping results to entities. The DAPS service, by default, returns all columns with their labels lowercased. (Future work could involve the DAPS service creating more unique map keys from SELECT results, e.g., table_name.column_name).

Conclusion:

The DAPS ecosystem, with its facade generator and robust service-side processing, has significantly improved our multi-tenant architecture's data access layer. By tackling type handling intricacies head-on and automating facade creation, we've enhanced both developer productivity and system reliability, paving the way for smoother scaling and faster delivery of features for our institutional clients.