`alembic_version` table can no longer be created with `order by tuple` since 25.12

[xoelop]

Describe the bug
Before 25.12 it was allowed to create ReplacingMergeTree tables with an empty order by clause. Since 25.12, it's no longer allowed (link)

It is now forbidden to create special MergeTree tables (such as ReplacingMergeTree, CollapsingMergeTree, etc.) with an empty ORDER BY key, since merge behavior in these tables is undefined. If you still need to create such a table, enable the allow_suspicious_primary_key setting. #91569 (Anton Popov).

To Reproduce
Here’s a minimal repro you can paste into a GitHub issue. It uses ClickHouse 25.12.1, clickhouse-sqlalchemy, and Alembic. Running alembic init then alembic upgrade head triggers the failure when alembic_version is created.

docker-compose.yml

services:
  clickhouse:
    image: clickhouse/clickhouse-server:25.12.1
    ports:
      - "8123:8123"
      - "9000:9000"
    environment:
      - CLICKHOUSE_DB=default
      - CLICKHOUSE_USER=default
      - CLICKHOUSE_PASSWORD=

requirements.txt

alembic
clickhouse-sqlalchemy
sqlalchemy

alembic.ini

[alembic]
script_location = alembic

[alembic:runtime]
sqlalchemy.url = clickhouse+native://default:@localhost:9000/default

alembic/env.py

from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context
from clickhouse_sqlalchemy.alembic.dialect import patch_alembic_version

config = context.config
if config.config_file_name:
    fileConfig(config.config_file_name)

target_metadata = None

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            patch_alembic_version(context)  # <-- creates ReplacingMergeTree ORDER BY tuple()
            context.run_migrations()

run_migrations_online()

Steps

docker compose up -d
python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
alembic init alembic
# replace alembic/env.py with the snippet above
alembic upgrade head

Expected error

DB::Exception: Sorting key cannot be empty for MergeTree table with Replacing merging mode. (BAD_ARGUMENTS)

This shows patch_alembic_version currently uses ReplacingMergeTree(order_by=tuple()), which is rejected by ClickHouse 25.12.1.

Expected behavior
No errors like

  File "/Users/xoel/CODE/theirstack/back-theirstack/.venv/lib/python3.12/site-packages/clickhouse_sqlalchemy/drivers/http/transport.py", line 179, in _send
    raise DatabaseException(orig)
clickhouse_sqlalchemy.exceptions.DatabaseException: Orig exception: Code: 36. DB::Exception: Sorting key cannot be empty for MergeTree table with Replacing merging mode. (BAD_ARGUMENTS) (version 25.12.1.649 (official build))

Command failed with exit code 1: /Users/xoel/.local/bin/uv run alembic --name=ch upgrade head

Versions

• clickhouse-sqlalchemy: 0.3.2.
• python 3.12.10
• clickhouse 25.12.1

Possible solution

Replacing patch_alembic_version by this

def patch_alembic_version_with_order(context: Any, **kwargs: Any) -> None:
    """Patch alembic_version to use a non-empty ORDER BY for MergeTree engines."""
    migration_context: Any = context._proxy._migration_context
    version: Any = migration_context._version

    dt = sa.Column(
        "dt",
        ch_types.DateTime,
        server_default=sa.func.now(),  # pylint: disable=not-callable
    )
    version_num = sa.Column("version_num", sa.String, primary_key=True)
    version.append_column(dt)
    version.append_column(version_num, replace_existing=True)

    order_by: Any = version.c.dt
    if "cluster" in kwargs:
        cluster = kwargs["cluster"]
        version.engine = engines.ReplicatedReplacingMergeTree(
            kwargs["table_path"],
            kwargs["replica_name"],
            version=dt,
            order_by=order_by,
        )
        version.kwargs["clickhouse_cluster"] = cluster
    else:
        version.engine = engines.ReplacingMergeTree(version=dt, order_by=order_by)