Using CompositeType breaks multiple database applications

[cooperellidge]

I have an application that manages separate databases, Postgres and SQLite. My Postgres models use CompositeType which causes a before_create DDL event to be registered with a global registered_composite for this column. When SQLAlchemy is creating the SQLite database, it attempts to run this composite but fails as SQLite dialect does not support them.

Fundamentally, this seems to be because SQLALchemy-Utils treats registration as global, not per connection. This seems to be agreed by an ominous TODO...

sqlalchemy-utils/sqlalchemy_utils/types/pg_composite.py

Line 158 in e2cbc08

# TODO: Make the registration work on connection level instead of global level

Git blame shows this hasn't been touched since it was added 10 years ago. I couldn't find a corresponding issue for this TODO.

Can this be fixed? Or, is there a better way to handle Postgres composite types outside of SQLAlchemy-Utils?

[cooperellidge]

Minimal reproduction with Python 3.12

uv pip list
> greenlet          3.2.2
> psycopg           3.2.9
> psycopg2-binary   2.9.10
> sqlalchemy        2.0.41
> sqlalchemy-utils  0.41.2
> typing-extensions 4.13.2

from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped
from sqlalchemy_utils.types import CompositeType


class PostgreSQLBase(DeclarativeBase):
    ...

some_composite = CompositeType(
    "some_composite",
    [
        Column("x", Integer),
        Column("y", Integer)
    ]
)

class PostgresTable(PostgreSQLBase):
    __tablename__ = "postgres_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    composite: Mapped[str] = mapped_column(some_composite)

class SQLiteBase(DeclarativeBase):
    ...


class SQLiteTable(SQLiteBase):
    __tablename__ = "sqlite_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    value: Mapped[str] = mapped_column()

if __name__=="__main__":
    sqlite_engine = create_engine("sqlite:///:memory:")

    SQLiteBase.metadata.create_all(sqlite_engine)

Traceback (most recent call last):
  File "/main.py", line 36, in <module>
    SQLiteBase.metadata.create_all(sqlite_engine)
  File "/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 5924, in create_all
    bind._run_ddl_visitor(
  File "/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3248, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2455, in _run_ddl_visitor
    ).traverse_single(element)
      ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.12/site-packages/sqlalchemy/sql/visitors.py", line 664, in traverse_single
    return meth(obj, **kw)
           ^^^^^^^^^^^^^^^
  File "/.venv/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 972, in visit_metadata
    with self.with_ddl_events(
         ^^^^^^^^^^^^^^^^^^^^^
  File "/.local/share/uv/python/cpython-3.12.9-linux-x86_64-gnu/lib/python3.12/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File ".venv/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 886, in with_ddl_events
    target.dispatch.before_create(
  File ".venv/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 378, in __call__
    fn(*args, **kw)
  File ".venv/lib/python3.12/site-packages/sqlalchemy_utils/types/pg_composite.py", line 319, in before_create
    composite.create(connection, checkfirst=True)
  File ".venv/lib/python3.12/site-packages/sqlalchemy_utils/types/pg_composite.py", line 258, in create
    not bind.dialect.has_type(bind, self.name, schema=self.schema)
        ^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'SQLiteDialect_pysqlite' object has no attribute 'has_type'. Did you mean: 'has_table'?

[danielbitzer]

I'm facing this issue also