SQLAlchemy 2.0 Integration

Relevant source files

The following files were used as context for generating this wiki page:

• examples/07_sqlalchemy2.py
• src/flext_db_oracle/services/sql_builder.py
• src/flext_db_oracle/api.py (referenced via Module Organization)
• tests/unit/test_api.py
• tests/unit/test_services.py
• pyproject.toml

The SQLAlchemy 2.0 integration within the FLEXT DB Oracle library serves as the underlying engine for robust, enterprise-grade Oracle database operations. It leverages SQLAlchemy Core for platform-agnostic SQL expression building while applying Oracle-specific dialects and custom types to ensure compatibility with enterprise Oracle environments.

This integration is primarily managed through the sql_builder service, which translates high-level domain requests into optimized SQLAlchemy 2.0 statements. It supports advanced features such as connection URL formatting, DDL/DML statement generation, and strict type handling for Oracle-specific data structures.

Sources: examples/07_sqlalchemy2.py:1-12, src/flext_db_oracle/services/sql_builder.py:1-15, src/README.md:10-25

Architecture and Components

The integration follows a layered approach where SQLAlchemy 2.0 acts as the infrastructure layer for SQL generation. The FlextDbOracleServiceSqlBuilder mixin provides the logic required to compile SQLAlchemy ClauseElement objects into Oracle-dialect SQL strings.

Key Components

Component	Description
FlextDbOracleSettings	Pydantic-based configuration that generates SQLAlchemy-compatible connection parameters.
FlextDbOracleServiceSqlBuilder	A mixin class that utilizes SQLAlchemy Core (select, insert, update, delete, CreateTable) for statement construction.
OracleRawType	A custom UserDefinedType used to preserve exact Oracle type strings (e.g., VARCHAR2, NUMBER) during DDL compilation.
oracle_dialect	The specific SQLAlchemy dialect utilized for all compilation processes to ensure Oracle-compliant syntax.

Sources: src/flext_db_oracle/services/sql_builder.py:38-60, examples/07_sqlalchemy2.py:17-35, src/README.md:30-45

SQL Generation Flow

The following diagram illustrates how the library transforms high-level API calls into compiled Oracle SQL using SQLAlchemy 2.0.

The diagram shows the transition from the high-level API through the services layer into the SQLAlchemy 2.0 compilation engine.
Sources: src/flext_db_oracle/services/sql_builder.py:65-100, tests/unit/test_services.py:45-70

DML Statement Construction

The system uses SQLAlchemy 2.0's functional approach to build DML (Data Manipulation Language) statements. This ensures that identifiers are properly quoted and bind parameters are safely handled.

Parameterized Queries

The library employs bindparam for safe execution, preventing SQL injection. It also features a unique normalization process where SQLAlchemy's internal bind names are mapped back to public-facing bind names to preserve identifier clarity in Oracle.

# Internal logic for compiling statements with bind parameters
def _compile_statement_with_binds(
    cls,
    statement: ClauseElement,
    bind_names: dict[str, str],
) -> str:
    sql = cls._compile_statement(statement)
    for column_name, bind_name in bind_names.items():
        sql = sql.replace(f":{bind_name}", f":{column_name}")
    return sql

Sources: src/flext_db_oracle/services/sql_builder.py:73-82

SELECT and JOIN Operations

Complex queries are built using SQLAlchemy table and column abstractions. This allows for automatic schema prefixing and case-sensitive identifier handling through the _normalize_identifier method, which utilizes SQLAlchemy's quoted_name.

Sources: src/flext_db_oracle/services/sql_builder.py:175-215, tests/unit/test_api.py:758-798

DDL Generation and Type Mapping

DDL (Data Definition Language) operations such as CREATE TABLE and CREATE INDEX are supported through SQLAlchemy's schema constructs.

Custom Oracle Types

Because standard SQLAlchemy types might not capture specific Oracle precision or custom keywords (like VARCHAR2 vs standard VARCHAR), the project implements OracleRawType. This class inherits from SQLAlchemy's UserDefinedType to pass raw type specifications directly to the compiler.

Table and Index Configuration

The integration enables the generation of Oracle-specific DDL options that are not standard in SQL, such as TABLESPACE and PARALLEL execution clauses.

Operation	SQLAlchemy Construct	Oracle Specifics
Create Table	CreateTable	Quoted identifiers, OracleRawType specs
Create Index	CreateIndex	TABLESPACE, PARALLEL clauses
Drop Table	DropTable	Schema-aware drops

Sources: src/flext_db_oracle/services/sql_builder.py:90-130, src/flext_db_oracle/services/sql_builder.py:270-320

Implementation Example: SQLAlchemy 2.0 Setup

The integration allows users to quickly generate connection strings compatible with SQLAlchemy 2.0 create_engine requirements.

def demonstrate_sqlalchemy_setup() -> None:
    """Example of SQLAlchemy 2.0 configuration setup."""
    settings = create_oracle_config()
    # Connection URL format configured for SQLAlchemy
    logger.info(f"📍 Host: {settings.host}:{settings.port}")
    logger.info(f"📚 Service: {settings.service_name}")
    logger.info("📚 Ready for SQLAlchemy 2.0 integration")

Sources: examples/07_sqlalchemy2.py:38-50

Summary of Configuration Requirements

The integration relies on specific dependencies defined in the project configuration to ensure full SQLAlchemy 2.0 compatibility.

• Python Version: 3.13+
• Primary Driver: oracledb>=3.4.2
• Validation: pydantic>=2.13.3
• Core Logic: flext-core

Sources: pyproject.toml:25-35, README.md:1-10

The SQLAlchemy 2.0 integration provides a bridge between the FLEXT DB Oracle domain layer and the raw database driver. By utilizing SQLAlchemy Core for expression building and DDL generation, the library ensures that all Oracle interactions are syntactically correct, safe from injection, and performant through the use of Oracle-specific compiler dialects.