Clickhouse generation creating invalid python

[coby-astsec]

Things to check first

• I have searched the existing issues and didn't find my bug already reported there

• I have checked that my bug is still present in the latest release

Sqlacodegen version

4.0.2

SQLAlchemy version

2.0.48

RDBMS vendor

Other

What happened?

Invalid python is generated when parsing the given clickhouse DB schema

from typing import Any

from clickhouse_sqlalchemy.types.common import Array, UUID
from sqlalchemy import text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.sql.sqltypes import NullType

class Base(DeclarativeBase):
    pass


class Testtable(Base):
    __tablename__ = 'testtable'

    id: Mapped[str] = mapped_column(UUID, primary_key=True) # This should be uuid.UUID
    col1: Mapped[list] = mapped_column(Array(<class 'clickhouse_sqlalchemy.types.common.String'>), nullable=False)
    col2: Mapped[list] = mapped_column(Array(<class 'clickhouse_sqlalchemy.types.common.String'>), nullable=False, server_default=text('[]'))
    col3: Mapped[Any] = mapped_column(NullType, nullable=False) # NOTE - This is also weird as it should be JSON
    col4: Mapped[list] = mapped_column(Array(<class 'sqlalchemy.sql.sqltypes.NullType'>), nullable=False)
    col5: Mapped[list] = mapped_column(Array(<class 'clickhouse_sqlalchemy.types.common.UUID'>), nullable=False)
    col6: Mapped[list] = mapped_column(Array(<class 'clickhouse_sqlalchemy.types.common.Int32'>), nullable=False)

There are also warnings regarding the JSON type:

/Users/user/.cache/uv/archive-v0/aS1Ku1BPpSOILuRQTOKY0/lib/python3.14/site-packages/clickhouse_sqlalchemy/drivers/base.py:309: SAWarning: Did not recognize type 'JSON' of column 'col3'
  warn("Did not recognize type '%s' of column '%s'" %
/Users/user/.cache/uv/archive-v0/aS1Ku1BPpSOILuRQTOKY0/lib/python3.14/site-packages/clickhouse_sqlalchemy/drivers/base.py:309: SAWarning: Did not recognize type 'JSON' of column 'col4'
  warn("Did not recognize type '%s' of column '%s'" %

Database schema for reproducing the bug

DROP TABLE IF EXISTS testtable;
CREATE TABLE IF NOT EXISTS testtable
(
    id UUID,
    col1 Array(String),
    col2 Array(String) DEFAULT [],
    col3 JSON,
    col4 Array(JSON),
    col5 Array(UUID),
    col6 Array(Int32)

) ENGINE = MergeTree()
ORDER BY (id);

[agronholm]

The JSON type is not available in the latest release of the Clickhouse driver. It is in the master version though.
As for the id column, are you certain the driver accepts uuid.UUID values? Even if it does, this could be a bug in the driver.

[sheinbergon]

@coby-astsec I guess we could re-create using a clickhouse docker image

[coby-astsec]

Yeah I used the docker image to create this example

[coby-astsec]

Regarding the uuid, I will check later and update you

[agronholm]

Regarding the uuid, I will check later and update you

Try with the master version of the driver too.

[coby-astsec]

Is this what you meant by the master version of the driver? Seems like it still doesn't work.

❯ uvx --with git+https://github.com/xzkostyan/clickhouse-sqlalchemy.git@master --with git+https://github.com/mymarilyn/clickhouse-driver.git@master sqlacodegen clickhouse+http://default:default@localhost:18123/default --options noindexes,nojoined

Installed 20 packages in 22ms
/Users/coby/.cache/uv/archive-v0/N31QKACA74yFZJyj2j99s/lib/python3.14/site-packages/clickhouse_sqlalchemy/drivers/base.py:320: SAWarning: Did not recognize type 'JSON' of column 'col2'
  warn("Did not recognize type '%s' of column '%s'" %
from clickhouse_sqlalchemy.types.common import Array
from sqlalchemy import text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass


class Testtable(Base):
    __tablename__ = 'testtable'

    id: Mapped[list] = mapped_column(Array(<class 'clickhouse_sqlalchemy.types.common.String'>), primary_key=True)
    col2: Mapped[list] = mapped_column(Array(<class 'sqlalchemy.sql.sqltypes.NullType'>), nullable=False, server_default=text('[]'))
    col4: Mapped[list] = mapped_column(Array(<class 'clickhouse_sqlalchemy.types.common.Int32'>), nullable=False)

(SQL here was):

DROP TABLE IF EXISTS testtable;                                                                                                                                                              
CREATE TABLE IF NOT EXISTS testtable;                                                                                                                                                        
(                                                                                                                                                                                            
    id Array(Str),                                                                                                                                                                           
    col2 Array(JSON) DEFAULT [],                                                                                                                                                             
    col4 Array(Int32)                                                                                                                                                                        
                                                                                                                                                                                             
) ENGINE = MergeTree()                                                                                                                                                                       
ORDER BY (id);    

[agronholm]

I have no idea what uvx is and what its --with option does. So I can't tell if you really have the master version installed. It's also unclear if their JSON support was finished or not. But until it is, there's no point in claiming this is a bug in sqlacodegen – there's nothing we can do if the driver doesn't reflect the schema properly.

[sheinbergon]

I have no idea what uvx is and what its --with option does. So I can't tell if you really have the master version installed. It's also unclear if their JSON support was finished or not. But until it is, there's no point in claiming this is a bug in sqlacodegen – there's nothing we can do if the driver doesn't reflect the schema properly.

I think the bug is the class <...> type resolution, not the JSON thingy. We should not generate broken bindings

[agronholm]

I have no idea what uvx is and what its --with option does. So I can't tell if you really have the master version installed. It's also unclear if their JSON support was finished or not. But until it is, there's no point in claiming this is a bug in sqlacodegen – there's nothing we can do if the driver doesn't reflect the schema properly.

I think the bug is the class <...> type resolution, not the JSON thingy. We should not generate broken bindings

I'm not sure I follow. For unrecognized column types, SQLAlchemy inserts NullType. What are we supposed to do in that case?

[sheinbergon]

I have no idea what uvx is and what its --with option does. So I can't tell if you really have the master version installed. It's also unclear if their JSON support was finished or not. But until it is, there's no point in claiming this is a bug in sqlacodegen – there's nothing we can do if the driver doesn't reflect the schema properly.

I think the bug is the class <...> type resolution, not the JSON thingy. We should not generate broken bindings

I'm not sure I follow. For unrecognized column types, SQLAlchemy inserts NullType. What are we supposed to do in that case?

I'm not sure what it is. But if we're failing to resolve types and with uninterpretable python, we should consider failing loudly

[agronholm]

I have no idea what uvx is and what its --with option does. So I can't tell if you really have the master version installed. It's also unclear if their JSON support was finished or not. But until it is, there's no point in claiming this is a bug in sqlacodegen – there's nothing we can do if the driver doesn't reflect the schema properly.

I think the bug is the class <...> type resolution, not the JSON thingy. We should not generate broken bindings

I'm not sure I follow. For unrecognized column types, SQLAlchemy inserts NullType. What are we supposed to do in that case?

I'm not sure what it is. But if we're failing to resolve types and with uninterpretable python, we should consider failing loudly

I'm not sure I agree. The generated code can be hand-fixed with relative ease, and some use cases may even involve migrating the schema to another back-end.

[agronholm]

Right, I only focused on the NullType. I didn't immediately see the <class 'clickhouse_sqlalchemy.types.common.String'> et al. I think it could be a dialect glitch too.

[agronholm]

We assume that we get column type instances from reflection, and not plain classes, hence the problem (probably).

[agronholm]

That issue seems to be limited to ARRAY types, so I think we could at least fix that.