Unlocking Any SQL Database with the Hybrid MX Pattern

In enterprise environments, database choice is rarely a developer's preference—it is often a strict organizational mandate. Whether it is a legacy Oracle database, a standardized SQL Server (MSSQL) cluster, or a specialized MySQL configuration, frameworks that lock you into a single database engine are a non-starter.

When we designed Framework M, we focused heavily on providing out-of-the-box support for PostgreSQL and SQLite. For completely different databases (like MongoDB), we introduced the MX Adapter Extension Pattern, allowing developers to completely swap out repository and transaction protocols without forking the framework core.

Today, we are taking this composability a step further. We have refactored our standard SQLAlchemy database adapters to support the Hybrid MX Pattern, allowing developers to connect any official or unofficial SQLAlchemy-supported database engine to Framework M by subclassing standard components and overriding only the dialect-specific parts.

---

The Challenge of Dialect Quirks

Using a generic ORM or query builder like SQLAlchemy gets you 90% of the way toward multi-database compatibility. However, the last 10% is where enterprise databases present unique quirks:

• Naming Sequences: PostgreSQL and SQLite use standard next-value functions or auto-increments. Oracle uses sequence.nextval, and MSSQL uses NEXT VALUE FOR sequence.
• Data Types: Oracle has no native Boolean type prior to 23c (conventionally mapped to NUMBER(1)), while MSSQL handles JSON strings differently than PostgreSQL.
• DDL and Migrations: Synchronizing columns, setting lock timeouts, or checking permissions varies wildly between SQL dialects.

Previously, supporting these meant either writing a custom database adapter from scratch (copying thousands of lines of boilerplate) or adding complex, hacky dialect checks directly into the standard code.

---

Introducing the Hybrid MX Pattern

With the Hybrid MX Pattern, you no longer have to choose between copying code or writing complex workarounds. By refactoring framework-m-standard, we have exposed targeted, overridable hooks inside our standard database components.

Instead of writing a database adapter from scratch, you can now inherit 95% of standard SQLAlchemy logic and override only the specific methods that differ for your target database.

1. Extensible Schema Mapping

The SchemaMapper translates Pydantic schemas into SQLAlchemy tables. We extracted the column-mapping type resolution into an overridable method:

def _get_column_type(
    self,
    _field_name: str,
    python_type: type,
    field_info: FieldInfo,
) -> type[TypeEngine[Any]] | TypeEngine[Any]:
    # Custom mapping logic here (e.g. mapping bool -> NUMBER(1) for certain dialects)
    if python_type is bool:
         return Numeric(precision=1)
    return super()._get_column_type(_field_name, python_type, field_info)

2. Native Sequence Fetching

Document naming sequences are now cleanly decoupled. To resolve custom sequence generation, developers only need to override _fetch_sequence_value:

async def _fetch_sequence_value(
    self, session: AsyncSession, sequence_name: str
) -> int:
    # Custom sequence syntax override (e.g. for Snowflake)
    stmt = select(text(f"SELECT {sequence_name}.NEXTVAL"))
    result = await session.execute(stmt)
    return int(result.scalar())

3. Dialect-Separated Schema Sync

Our runtime declarative sync engine (DeclarativeSyncEngine) now delegates dialect-specific checks (e.g., DDL execution checks, lock timeouts, schema creation, and ALTER TABLE ADD COLUMN statements) to clean helper methods.

For instance, to adapt to custom table alteration syntax:

def _get_add_column_sql(
    self,
    engine: Engine,
    table_name: str,
    column_name: str,
    col_type: str,
    nullable: str,
    default: str,
) -> str:
    return f"ALTER TABLE {table_name} ADD COLUMN {column_name} {col_type} {nullable}{default}"

---

Composing Your Adapter

By packaging these subclassed overrides and registering them via Python entry points in your pyproject.toml using the dialect name (e.g. snowflake) as the entry point name, the framework dynamically binds your customized classes at runtime based on the active database engine:

[project.entry-points."framework_m.adapters.schema_mapper"]
snowflake = "framework_mx_snowflake.schema_mapper:SnowflakeSchemaMapper"

[project.entry-points."framework_m.adapters.naming_service"]
snowflake = "framework_mx_snowflake.naming:SnowflakeNamingService"

[project.entry-points."framework_m.adapters.declarative_sync_engine"]
snowflake = "framework_mx_snowflake.sync_engine:SnowflakeDeclarativeSyncEngine"

Once installed, your database variant matches the active database dialect name (e.g., snowflake) and automatically configures Framework M—with less than 100 lines of custom package code, while retaining the standard GenericRepository completely out-of-the-box.

---

Out-of-the-box Support for the "Big 5"

To make enterprise setups even easier, we have built this support directly into the core framework-m-standard package for the top relational databases. By specifying optional extras, you get lazy-loaded, built-in support for:

• PostgreSQL (framework-m-standard[postgres])
• SQLite (built-in by default)
• MySQL (framework-m-standard[mysql])
• MSSQL (framework-m-standard[mssql])
• Oracle (framework-m-standard[oracle])

Any other database engine supported by SQLAlchemy can be easily integrated by the community using the same entry-point pattern.

Check out our new guide: How to Build a Custom Database Adapter using the Hybrid MX Pattern to start building your own!