SQLAlchemy¶

Experimental

This functionality is experimental.

Metaxy provides helpers for integrating with SQLAlchemy. These helpers allow to construct sqlalchemy.MetaData objects for user-defined feature tables and for Metaxy system tables.

This integration is convenient for setting up Alembic migrations.

SQLModel Features

Check out our SQLModel integration for metaclass features that combine Metaxy features with SQLModel ORM models. This is the recommended way to use Metaxy with SQLAlchemy.

Alembic Integration¶

Alembic is a database migration toolkit for SQLAlchemy.

The two helper functions: filter_feature_sqla_metadata and get_system_slqa_metadata can be used to retrieve an SQLAlchemy URL and MetaData object for a given IbisMetadataStore.

filter_feature_sqla_metadata returns table metadata for the user-defined tables, while get_system_slqa_metadata returns metadata for Metaxy's system tables.

Call init first

You must call init before using filter_feature_sqla_metadata to ensure all features are loaded into the feature graph.

Here is an example Alembic env.py that uses the Metaxy SQLAlchemy integration:

env.py

from alembic import context
from metaxy import init
from metaxy.ext.sqlalchemy import filter_feature_sqla_metadata

# Alembic Config object
config = context.config

metaxy_cfg = init()
store = metaxy_cfg.get_store("my_store")

# import your SQLAlchemy metadata from somewhere
my_metadata = ...

url, target_metadata = filter_feature_sqla_metadata(my_metadata, store)

# Configure Alembic
config.set_main_option("sqlalchemy.url", url)


# continue with the standard Alembic workflow

Naming Conventions

For consistent, deterministic and readable constraint names in your migrations, configure naming conventions.

Multi-Store Setup¶

You can configure separate metadata stores for different environments:

metaxy.toml

[stores.dev]
type = "metaxy.ext.metadata_stores.duckdb.DuckDBMetadataStore"
config = { database = "dev_metadata.duckdb" }

[stores.prod]
type = "metaxy.ext.metadata_stores.duckdb.DuckDBMetadataStore"
config = { database = "prod_metadata.duckdb" }

Then create multiple Alembic directories and register them with Alembic:

alembic.ini

[dev]
script_location = alembic/dev

[prod]
script_location = alembic/prod

Separate Alembic Version Tables

When using multiple Alembic environments (e.g., system tables vs feature tables), configure separate version tables to avoid conflicts. Set up separate script locations in alembic.ini:

alembic.ini

[dev:metaxy_system]
script_location = alembic/dev/system

[dev:metaxy_features]
script_location = alembic/dev/features

Then pass version_table to context.configure() in each env.py:

alembic/dev/system/env.py

context.configure(
    url=url,
    target_metadata=target_metadata,
    version_table="alembic_version_metaxy_system",
)

alembic/dev/features/env.py

context.configure(
    url=url,
    target_metadata=target_metadata,
    version_table="alembic_version_metaxy_features",
)

Each environment now tracks migrations independently:

alembic_version_metaxy_system for system tables
alembic_version_metaxy_features for feature tables

Create and run migrations separately:

alembic -n dev:metaxy_system revision --autogenerate -m "initialize"
alembic -n dev:metaxy_features revision --autogenerate -m "initialize"
alembic -n dev:metaxy_system upgrade head
alembic -n dev:metaxy_features upgrade head

The two environments now can be managed independently:

devprod

alembic/dev/env.py

from metaxy import init
config = init()
store = config.get_store("dev")
url, target_metadata = filter_feature_sqla_metadata(my_metadata, store)

The -n argument can be used to specify the target Alembic directory:

alembic -n dev upgrade head

alembic/prod/env.py

from metaxy import init
config = init()
store = config.get_store("prod")
url, target_metadata = filter_feature_sqla_metadata(my_metadata, store)

The -n argument can be used to specify the target Alembic directory:

alembic -n prod upgrade head

Alembic + SQLModel¶

To throw SQLModel into the mix, make sure to use the SQLModel integration and pass sqlmodel.SQLModel.metadata into filter_feature_sqla_metadata.

metaxy.ext.sqlalchemy ¶

SQLAlchemy integration for metaxy.

This module provides SQLAlchemy table definitions and helpers for metaxy. These can be used with migration tools like Alembic.

The main functions return tuples of (sqlalchemy_url, metadata) for easy integration with migration tools:

get_system_slqa_metadata: Get URL and system table metadata for a store
filter_feature_sqla_metadata: Get URL and feature table metadata for a store

metaxy.ext.sqlalchemy.filter_feature_sqla_metadata ¶

filter_feature_sqla_metadata(
    store: IbisMetadataStore,
    source_metadata: MetaData,
    project: str | None = None,
    filter_by_project: bool = True,
    inject_primary_key: bool | None = None,
    inject_index: bool | None = None,
    protocol: str | None = None,
    port: int | None = None,
) -> tuple[str, MetaData]

Experimental

This functionality is experimental.

Get SQLAlchemy URL and feature table metadata for a metadata store.

This function filters the source metadata to include only feature tables belonging to the specified project, and returns the connection URL for the store.

This function must be called after init() to ensure features are loaded.

Parameters:

store (IbisMetadataStore) –

IbisMetadataStore instance
source_metadata (MetaData) –

Source SQLAlchemy MetaData to filter.
project (str | None, default: None ) –

Project name to filter by. If None, uses MetaxyConfig.get().project
filter_by_project (bool, default: True ) –

If True, only include features for the specified project. If False, include all features.
inject_primary_key (bool | None, default: None ) –

If True, inject composite primary key constraints. If False, do not inject. If None, uses config default.
inject_index (bool | None, default: None ) –

If True, inject composite index. If False, do not inject. If None, uses config default.
protocol (str | None, default: None ) –

Optional protocol to replace the existing one in the URL. Useful when Ibis uses a different protocol than SQLAlchemy requires.
port (int | None, default: None ) –

Optional port to replace the existing one in the URL. Useful when the SQLAlchemy driver uses a different port than Ibis.

Returns:

tuple[str, MetaData] –

Tuple of (sqlalchemy_url, filtered_metadata)

Raises:

ValueError –

If store's sqlalchemy_url is empty
ImportError –

If source_metadata is None and SQLModel is not installed

Note

Metadata stores do their best at providing the correct sqlalchemy_url, so you typically don't need to modify the output of this function.

Example: Basic Usage

<!-- skip next -->
```py
from metaxy.ext.sqlalchemy import filter_feature_sqla_metadata
from sqlalchemy import MetaData

# Load features first
mx.init()

# Get store instance
config = mx.MetaxyConfig.get()
store = config.get_store("my_store")

my_metadata = MetaData()
# ... define tables in my_metadata ...

# apply the filter function
url, metadata = filter_feature_sqla_metadata(store, source_metadata=my_metadata)
```

Example: With SQLModel

<!-- skip next -->
```py
from sqlmodel import SQLModel

url, metadata = filter_feature_sqla_metadata(store, SQLModel.metadata)
```

Source code in src/metaxy/ext/sqlalchemy/plugin.py

@experimental
@public
def filter_feature_sqla_metadata(
    store: IbisMetadataStore,
    source_metadata: MetaData,
    project: str | None = None,
    filter_by_project: bool = True,
    inject_primary_key: bool | None = None,
    inject_index: bool | None = None,
    protocol: str | None = None,
    port: int | None = None,
) -> tuple[str, MetaData]:
    """Get SQLAlchemy URL and feature table metadata for a metadata store.

    This function filters the source metadata to include only feature tables
    belonging to the specified project, and returns the connection URL for the store.

    This function must be called after init() to ensure features are loaded.

    Args:
        store: IbisMetadataStore instance
        source_metadata: Source SQLAlchemy MetaData to filter.
        project: Project name to filter by. If None, uses MetaxyConfig.get().project
        filter_by_project: If True, only include features for the specified project.
                          If False, include all features.
        inject_primary_key: If True, inject composite primary key constraints.
                           If False, do not inject. If None, uses config default.
        inject_index: If True, inject composite index.
                     If False, do not inject. If None, uses config default.
        protocol: Optional protocol to replace the existing one in the URL.
            Useful when Ibis uses a different protocol than SQLAlchemy requires.
        port: Optional port to replace the existing one in the URL.
            Useful when the SQLAlchemy driver uses a different port than Ibis.

    Returns:
        Tuple of (sqlalchemy_url, filtered_metadata)

    Raises:
        ValueError: If store's sqlalchemy_url is empty
        ImportError: If source_metadata is None and SQLModel is not installed

    Note:
        Metadata stores do their best at providing the correct `sqlalchemy_url`, so you typically don't need to modify the output of this function.

    Example: Basic Usage

        <!-- skip next -->
        ```py
        from metaxy.ext.sqlalchemy import filter_feature_sqla_metadata
        from sqlalchemy import MetaData

        # Load features first
        mx.init()

        # Get store instance
        config = mx.MetaxyConfig.get()
        store = config.get_store("my_store")

        my_metadata = MetaData()
        # ... define tables in my_metadata ...

        # apply the filter function
        url, metadata = filter_feature_sqla_metadata(store, source_metadata=my_metadata)
        ```

    Example: With SQLModel

        <!-- skip next -->
        ```py
        from sqlmodel import SQLModel

        url, metadata = filter_feature_sqla_metadata(store, SQLModel.metadata)
        ```
    """
    url = _get_store_sqlalchemy_url(store, protocol=protocol, port=port)
    metadata = _get_features_metadata(
        source_metadata=source_metadata,
        store=store,
        project=project,
        filter_by_project=filter_by_project,
        inject_primary_key=inject_primary_key,
        inject_index=inject_index,
    )
    return url, metadata

metaxy.ext.sqlalchemy.get_system_slqa_metadata ¶

get_system_slqa_metadata(
    store: IbisMetadataStore,
    protocol: str | None = None,
    port: int | None = None,
) -> tuple[str, MetaData]

Experimental

This functionality is experimental.

Get SQLAlchemy URL and Metaxy system tables metadata for a metadata store.

This function retrieves both the connection URL and system table metadata for a store, with the store's table_prefix automatically applied to table names.

Parameters:

store (IbisMetadataStore) –

IbisMetadataStore instance
protocol (str | None, default: None ) –

Optional protocol (drivername) to replace the existing one in the URL. Useful when Ibis uses a different protocol than SQLAlchemy requires.
port (int | None, default: None ) –

Optional port to replace the existing one in the URL. Useful when the SQLAlchemy driver uses a different port than Ibis.

Returns:

tuple[str, MetaData] –

Tuple of (sqlalchemy_url, system_metadata)

Raises:

ValueError –

If store's sqlalchemy_url is empty

Note

Metadata stores do their best at providing the correct sqlalchemy_url, so you typically don't need to modify the output of this function.

Source code in src/metaxy/ext/sqlalchemy/plugin.py

@public
@experimental
def get_system_slqa_metadata(
    store: IbisMetadataStore,
    protocol: str | None = None,
    port: int | None = None,
) -> tuple[str, MetaData]:
    """Get SQLAlchemy URL and Metaxy system tables metadata for a metadata store.

    This function retrieves both the connection URL and system table metadata
    for a store, with the store's `table_prefix` automatically applied to table names.

    Args:
        store: IbisMetadataStore instance
        protocol: Optional protocol (drivername) to replace the existing one in the URL.
            Useful when Ibis uses a different protocol than SQLAlchemy requires.
        port: Optional port to replace the existing one in the URL.
            Useful when the SQLAlchemy driver uses a different port than Ibis.

    Returns:
        Tuple of (sqlalchemy_url, system_metadata)

    Raises:
        ValueError: If store's sqlalchemy_url is empty

    Note:
        Metadata stores do their best at providing the correct `sqlalchemy_url`, so you typically don't need to modify the output of this function.
    """
    url = _get_store_sqlalchemy_url(store, protocol=protocol, port=port)
    metadata = _get_system_metadata(table_prefix=store._table_prefix)
    return url, metadata

Configuration¶

`enable`¶

Whether to enable the plugin.

Type: bool | Default: False

metaxy.tomlpyproject.tomlEnvironment Variable

[ext.sqlalchemy]
enable = false

[tool.metaxy.ext.sqlalchemy]
enable = false

export METAXY_EXT__SQLALCHEMY_EXT__SQLALCHEMY__ENABLE=false

`inject_primary_key`¶

Automatically inject composite primary key constraints on user-defined feature tables. The key is composed of ID columns, metaxy_created_at, and metaxy_data_version.

Type: bool | Default: False

metaxy.tomlpyproject.tomlEnvironment Variable

[ext.sqlalchemy]
inject_primary_key = false

[tool.metaxy.ext.sqlalchemy]
inject_primary_key = false

export METAXY_EXT__SQLALCHEMY_EXT__SQLALCHEMY__INJECT_PRIMARY_KEY=false

`inject_index`¶

Automatically inject composite index on user-defined feature tables. The index covers ID columns, metaxy_created_at, and metaxy_data_version.

Type: bool | Default: False

metaxy.tomlpyproject.tomlEnvironment Variable

[ext.sqlalchemy]
inject_index = false

[tool.metaxy.ext.sqlalchemy]
inject_index = false

export METAXY_EXT__SQLALCHEMY_EXT__SQLALCHEMY__INJECT_INDEX=false

SQLAlchemy¶

Alembic Integration¶

Multi-Store Setup¶

Alembic + SQLModel¶

metaxy.ext.sqlalchemy ¶

metaxy.ext.sqlalchemy.filter_feature_sqla_metadata ¶

metaxy.ext.sqlalchemy.get_system_slqa_metadata ¶

Configuration¶

enable¶

inject_primary_key¶

inject_index¶

`enable`¶

`inject_primary_key`¶

`inject_index`¶