Connection Manager
Relevant source files
The following files were used as context for generating this wiki page:
The Connection Manager system in flext-dbt-oracle provides a robust, typed infrastructure for managing Oracle database connectivity within dbt workflows. It centralizes credential handling, connection pooling configuration, and Oracle-specific connection string (DSN) generation. By leveraging Pydantic-based settings and models, it ensures that all database interactions are governed by validated configurations that support both Service Names and SIDs.
The system is designed to work as a modern dbt adapter component, integrating with flext-db-oracle for low-level database interactions while providing dbt-specific abstractions. It handles various connection protocols (TCP/TCPS), SSL validation, and performance tuning parameters such as batch sizes and timeouts.
Sources: rules.json:11-20, src/flext_dbt_oracle/settings.py:15-30, src/flext_dbt_oracle/models.py:82-95
Architecture and Components
The connection management architecture is divided into three primary layers: Configuration (Settings), Data Models, and Utility Clients.
Logical Component Flow
This diagram illustrates how raw environment settings are transformed into validated connection objects and used by the client.
Key Classes
| Class | Responsibility | Source File |
|---|---|---|
FlextDbtOracleSettings | Primary configuration object that reads from environment variables (FLEXT_DBT_ORACLE_). | src/flext_dbt_oracle/settings.py:15 |
OracleConnectionConfig | A structured domain model for Oracle connection parameters. | src/flext_dbt_oracle/models.py:82 |
DbtOracle.Client | Facade for executing database operations and connectivity health checks. | src/flext_dbt_oracle/utilities.py:18 |
Configuration and Settings
Connectivity is primarily configured through FlextDbtOracleSettings. This class uses an environment prefix FLEXT_DBT_ORACLE_ and supports advanced Oracle features like NLS settings and SSL DN matching.
Sources: src/flext_dbt_oracle/settings.py:18-23
Connection Parameters
The following table summarizes the core connection fields used to establish a session.
| Field | Type | Default | Description |
|---|---|---|---|
oracle_host | str | localhost | Database host address. |
oracle_port | int | 1521 | TNS listener port. |
oracle_username | str | oracle | Database user. |
oracle_service_name | str | XEPDB1 | Oracle Service Name. |
sid | str | None | None | Oracle System ID (Alternative to Service Name). |
protocol | Literal['tcp', 'tcps'] | tcp | Network protocol. |
ssl_server_dn_match | bool | False | Validates server certificate DN. |
| Sources: src/flext_dbt_oracle/settings.py:27-58, tests/unit/test_connections.py:21-30 |
DSN Generation Logic
The system dynamically generates Data Source Names (DSNs) based on whether a sid or service_name is provided. If sid is present, the separator : is used; otherwise, / is used for service names.
# Logic for DSN construction in models.py
if self.sid:
return f"{self.protocol}://{self.username}:***@{self.host}:{self.port}:{self.sid}"
return f"{self.protocol}://{self.username}:***@{self.host}:{self.port}/{self.service_name}"
Sources: src/flext_dbt_oracle/models.py:122-132, tests/unit/_config_parts/connection.py:16-30
Performance and Pooling
The Connection Manager exposes fine-grained control over Oracle's connection pooling and query performance. These are accessible via the performance_settings property on the settings object.
Sources: src/flext_dbt_oracle/settings.py:180-195
Performance Configuration Options
| Parameter | Type | Default | Description |
|---|---|---|---|
pool_min_size | PositiveInt | 1 | Minimum number of sessions in the pool. |
pool_max_size | PositiveInt | 10 | Maximum capacity of the session pool. |
query_timeout | PositiveInt | 300 | Seconds before a query is aborted. |
fetch_size | PositiveInt | 1000 | Number of rows to fetch in a single round-trip. |
retry_attempts | NonNegativeInt | 3 | Number of reconnection attempts on failure. |
| Sources: src/flext_dbt_oracle/settings.py:80-105, rules.json:96-102 |
Pool Validation
The system enforces that pool_max_size must always be greater than or equal to pool_min_size during the configuration validation phase.
Sources: src/flext_dbt_oracle/settings.py:113-119, tests/unit/_config_parts/validation.py:65-75
Integration with dbt
Connection settings are mapped into dbt-compatible profiles. The dbt_settings property provides a dictionary containing the database (derived from service name), schema (derived from the effective schema), and the materialization strategy.
Sources: src/flext_dbt_oracle/settings.py:198-206
Effective Schema Resolution
The Connection Manager determines the effective_schema by checking if a specific schema_name is provided; if not, it defaults to the oracle_username.
Sources: src/flext_dbt_oracle/settings.py:136-139, tests/unit/_config_parts/connection.py:42-50
Transaction and SQL Features
As defined in the project rules, the connection management follows these constraints:
- Auto-commit: Disabled (
false). - Transaction Support: Enabled.
- Pooling: Native Oracle connection pooling enabled.
- Dialect: Oracle-specific quoting policies (identifiers and schemas are not quoted by default).
Sources: rules.json:37-45, rules.json:80-86
Conclusion
The Connection Manager provides a centralized and validated approach to Oracle connectivity. By encapsulating complex DSN logic and performance tuning into typed models, it ensures that dbt pipelines can reliably connect to Oracle environments using modern best practices for session pooling and security.