running migrations on clickhouse

[kar-pev]

Describe the bug

I'm trying to config revisions pipepline for my clickhouse database with alembic 1.7.6 and clickhouse_sqlalchemy 0.3.0.
It's implemented with class structure in my case. When I'm creating first revision, everything seems ok, but when I'm trying to upgrade my database by adding some columns to table, alembic revision autogeneration fails with provided error, caused by sqlalchemy. I think, that my metadata wasn't updated since first revision, but I'm not sure, where especially error is

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.25

DBAPI (i.e. the database driver)

clickhouse

Database Vendor and Major Version

24.1.3

Python Version

3.10.12

Operating system

Ubuntu 22.04.3 LTS

To Reproduce

Model file

from sqlalchemy import Column, create_engine, MetaData
from clickhouse_sqlalchemy import (
    make_session, get_declarative_base, types, engines
)
from datetime import datetime

from alembic import context

url = context.config.get_main_option("sqlalchemy.url")
engine = create_engine(url)
metadata = MetaData()
metadata.create_all(bind=engine)

Base = get_declarative_base(metadata=metadata)


class MetricLogs(Base):
    __tablename__ = "metric_logs"
    ts = Column(types.DateTime, primary_key=True, default=datetime.now)
    run_id = Column(types.Int64, nullable=False)
    wdoc_id = Column(types.Int64, nullable=False)
    metric_name = Column(types.String(100), nullable=False)
    value = Column(types.Float32, nullable=False)

    __table_args__ = (
        engines.MergeTree(order_by=ts),
    )

    def upload_metrics(self):
        session = make_session(engine)
        session.add(self)
        session.commit()
        session.close()


alembic env file:
```python
from alembic.ddl import impl
from alembic import context
from sqlalchemy import pool
from sqlalchemy import engine_from_config
from logging.config import fileConfig
from clickhouse_sqlalchemy import engines
from env import get_var
from clickhouse_sqlalchemy.alembic.dialect import include_object


class ClickhouseImpl(impl.DefaultImpl):
    """
    Implimentation of Alembic migration behavior for Clickhouse DB
    """
    __dialect__ = "clickhouse"
    transactional_ddl = False


# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
config.set_main_option(
    "sqlalchemy.url",
    "clickhouse+http://{user}:{pwd}@{host}:{port}/{db_name}".format(
        user=get_var("CLICKHOUSE_USER"),
        pwd=get_var("CLICKHOUSE_PASSWORD"),
        host=get_var("CLICKHOUSE_HOST"),
        port=get_var("CLICKHOUSE_PORT"),
        db_name=get_var("CLICKHOUSE_DB_NAME")
    )
)


# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)


# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.meeadata

def need():
    from db.models.models import MetricLogs
    return MetricLogs


target_metadata = need().__base__.metadata


def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be availablee

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    print(target_metadata.schema)
    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,
            include_object=include_object
        )

        from sqlalchemy import func, Column, Table, MetaData
        from clickhouse_sqlalchemy import types
        migration_context = context._proxy._migration_context
        migration_context._version = Table(
            context._proxy._migration_context.version_table,
            MetaData(),
            Column("version_num", types.String(), nullable=False),
            Column('dt', types.DateTime, server_default=func.now()),
            engines.ReplacingMergeTree(version='dt', order_by=func.tuple()),
            schema=migration_context.version_table_schema,
        )
        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

### Error

INFO  [alembic.runtime.migration] Context impl ClickhouseImpl.                                                                                                         
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.                                                                                                   
Traceback (most recent call last):                                                                                                                                     
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4295, in _col_expressions                                                       
    return [                                                                                                                                                           
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4296, in <listcomp>                                                             
    parent.c[col] if isinstance(col, str) else col                                                                                                                     
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 1624, in __getitem__                                                              
    return self._index[key][1]                                                                                                                                         
KeyError: 'ts'                                                                                                                                                         
                                                                                                                                                                       
The above exception was the direct cause of the following exception:                                                                                                   
                                                                                                                                                                       
Traceback (most recent call last):                                                                                                                                     
  File "/usr/bin/alembic", line 33, in <module>                                                                                                                        
    sys.exit(load_entry_point('alembic==1.7.6.dev0', 'console_scripts', 'alembic')())                                                                                  
  File "/usr/lib/python3/dist-packages/alembic/config.py", line 588, in main                                                                                           
    CommandLine(prog=prog).main(argv=argv)                                                                                                                             
  File "/usr/lib/python3/dist-packages/alembic/config.py", line 582, in main                                                                                           
    self.run_cmd(cfg, options)                                                                                                                                         
  File "/usr/lib/python3/dist-packages/alembic/config.py", line 559, in run_cmd                                                                                        
    fn(                                                                                                                                                                
  File "/usr/lib/python3/dist-packages/alembic/command.py", line 227, in revision                                                                                      
    script_directory.run_env()                                                                                                                                         
  File "/usr/lib/python3/dist-packages/alembic/script/base.py", line 563, in run_env                                                                                   
    util.load_python_file(self.dir, "env.py")                                                                                                                          
  File "/usr/lib/python3/dist-packages/alembic/util/pyfiles.py", line 92, in load_python_file                                                                          
    module = load_module_py(module_id, path)                                                                                                                           
  File "/usr/lib/python3/dist-packages/alembic/util/pyfiles.py", line 108, in load_module_py                                                                           
    spec.loader.exec_module(module)  # type: ignore                                                                                                                    
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/home/ivan/ai-postprocessing/db/alembic/env.py", line 116, in <module>
    run_migrations_online()
  File "/home/ivan/ai-postprocessing/db/alembic/env.py", line 110, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/lib/python3/dist-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/lib/python3/dist-packages/alembic/runtime/migration.py", line 608, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/usr/lib/python3/dist-packages/alembic/command.py", line 203, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/api.py", line 525, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/api.py", line 572, in _run_environment
    compare._populate_migration_script(
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/compare.py", line 53, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/compare.py", line 87, in _produce_net_changes
    comparators.dispatch("schema", autogen_context.dialect.name)(
  File "/usr/lib/python3/dist-packages/alembic/util/langhelpers.py", line 265, in go
    fn(*arg, **kw)
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/compare.py", line 126, in _autogen_for_tables
    _compare_tables(
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/compare.py", line 243, in _compare_tables
    sqla_compat._reflect_table(inspector, t, None)
  File "/usr/lib/python3/dist-packages/alembic/util/sqla_compat.py", line 248, in _reflect_table
    return inspector.reflect_table(table, None)
  File "/home/ivan/.local/lib/python3.10/site-packages/clickhouse_sqlalchemy/drivers/reflection.py", line 25, in reflect_table
    self._reflect_engine(ch_table.name, schema, ch_table)
  File "/home/ivan/.local/lib/python3.10/site-packages/clickhouse_sqlalchemy/drivers/reflection.py", line 44, in _reflect_engine
    engine._set_parent(table)
  File "/home/ivan/.local/lib/python3.10/site-packages/clickhouse_sqlalchemy/engines/mergetree.py", line 49, in _set_parent
    self.order_by._set_parent(table, **kwargs)
  File "/home/ivan/.local/lib/python3.10/site-packages/clickhouse_sqlalchemy/engines/base.py", line 53, in _set_parent
    ColumnCollectionMixin._set_parent(self, table)
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4309, in _set_parent
   for col in self._col_expressions(parent):
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4300, in _col_expressions
    raise exc.ConstraintColumnNotFoundError(
sqlalchemy.exc.ConstraintColumnNotFoundError: Can't create KeysExpressionOrColumn on table 'metric_logs': no column named 'ts' is present.


### Additional context

_No response_

[zzzeek]

hi -

have you gotten support for the clickhouse dialect first? this error is likely to be part of that dialect's implementation

[kar-pev]

No, I haven't, cause issue was from original sqlalchemy. I thought, that this problem appeared from bad metadata providing or smth likely

[zzzeek]

this looks like you are doing various custom things and I dont actually understand what the code is supposed to do. it looks like the errors are generated by something called engines.ReplacingMergeTree(version='dt', order_by=func.tuple()), that's not in SQLAlchemy or Alembic and seems to be clickhouse specific, but then also I dont really know what this is:

migration_context._version = Table(
            context._proxy._migration_context.version_table,
            MetaData(),
            Column("version_num", types.String(), nullable=False),
            Column('dt', types.DateTime, server_default=func.now()),
            engines.ReplacingMergeTree(version='dt', order_by=func.tuple()),
            schema=migration_context.version_table_schema,
        )

that's not any kind of public API at all, then also there are no migration files supplied here.

No, I haven't, cause issue was from original sqlalchemy. I thought, that this problem appeared from bad metadata providing or smth likely

if you send invalid structures into SQLAlchemy it will raise errors, that's not a bug. the issue here is that there is some kind of non-sqlalchemy construct called "KeysExpressionOrColumn" coming up and it expects some column named "ts" to exist, which it doesnt. I would assume this is related to engines.ReplacingMergeTree(version='dt', order_by=func.tuple()), or some other clickhouse thing, we dont support that here.

[kar-pev]

Unfortunately, the only solution I've found is to delete all migrations from db and migrate them from alembic with default functionality

[kar-pev]

Maybe write issue into clickhouse-sqlalchemy is the best solution. Please provide url to it, if you do it

[piroinno]

Sure. I think the problem is that the library is not able to handle contraints.

[kar-pev]

I thought, that problem is with pulling of metadata, but I'm also not sure

[piroinno]

Update. Looks like the issue has been fixed in the the latest version (0.3.1) of the clickhouse-sqlalchemy package: https://github.com/xzkostyan/clickhouse-sqlalchemy/blob/master/CHANGELOG.md