@ilevkivskyi any idea how to approach this? SQLAlchemy uses this pattern all over the place, where the type depends on the passed arguments (eg. Numeric.as_decimal(), Enum, UUID). I guess a plugin will be needed to handle it.

i also suffer from this issue.

is there a way to explicitly override the type that sqlalchemy-stubs infers for a column? that would at least avoid the false positives, at the (small) cost of putting an explicit type annotation in place.

i tried using annotations like sqlalchemy.Column[uuid.UUID] and sqlalchemy.types.TypeEngine[uuid.UUID] (also in string form) but no luck...

UPDATE: there is a better way

it seems the following does the trick.

at the module level:

import uuid
from typing import cast, TYPE_CHECKING

import sqlalchemy
import sqlalchemy.dialects.postgresql

# https://github.com/dropbox/sqlalchemy-stubs/issues/94
_PostgreSQLUUID = sqlalchemy.dialects.postgresql.UUID(as_uuid=True)
if TYPE_CHECKING:
    PostgreSQLUUID = cast(sqlalchemy.types.TypeEngine[uuid.UUID], _PostgreSQLUUID)
else:
    PostgreSQLUUID = _PostgreSQLUUID

use in orm model classes then looks like this:

class Foo(Base):
    id = sqlalchemy.Column(PostgreSQLUUID, default=uuid.uuid4, primary_key=True)

@wbolster Looks like you answered your own question. I think you can also just use # type: ignore on the definition with the explicit annotation. Also I think your way can be simplified to:

if TYPE_CHECKING:
    PostgreSQLUUID = sqlalchemy.types.TypeEngine[uuid.UUID]
else:
    PostgreSQLUUID = sqlalchemy.dialects.postgresql.UUID(as_uuid=True)

hmm, not sure.

• reassigning to the same name with a different type will trigger another mypy error
• ignoring errors on the line defining the column does not suppress the errors elsewhere when constructing instances

ignoring errors on the line defining the column does not suppress the errors elsewhere when constructing instances

This seems wrong, can you give an example?

if TYPE_CHECKING:
    PostgreSQLUUID = sqlalchemy.types.TypeEngine[uuid.UUID]
else:
    PostgreSQLUUID = sqlalchemy.dialects.postgresql.UUID(as_uuid=True)

Could someone explain this workaround? The runtime value is obvious, but even after reading the source for TypeEngine, I can't tell what TypeEngine[uuid.UUID] is. It works for my codebase, but I'd like to be able to document what this is doing and why it satisfies mypy.

try reveal_type(some_column) on a non-uuid column and observe the mypy output.

the type engine "trick" basically tells the sqlalchemy mypy plugin that this is a column that deals with uuid.UUID objects.

reveal_type is handy, but I mean that I don't understand how sqlalchemy.types.TypeEngine[uuid.UUID] produces a Column[UUID*]. As far as I can tell, TypeEngine doesn't even support subscripting. So is this some magic hint to sqlmypy?

(By the way, thanks for sharing the workaround!)

this is an update to my solution posted earlier. the good news is that things can be simplified. 🥳

the ‘if TYPE_CHECKING’ block and the workarounds using redefinitions can be avoided altogether by using a string literal for the type annotation (since the types are generic in stubs but not at runtime).

here's a full example; the 🧙 trick here is the PostgreSQLUUID definition which can be used as the type for sqlalchemy.Column(...).

import uuid
from typing import cast

import sqlalchemy
import sqlalchemy.dialects.postgresql
import sqlalchemy.ext.declarative

PostgreSQLUUID = cast(
    "sqlalchemy.types.TypeEngine[uuid.UUID]",
    sqlalchemy.dialects.postgresql.UUID(as_uuid=True),
)

metadata = sqlalchemy.MetaData()
Base = sqlalchemy.ext.declarative.declarative_base(metadata=metadata)


class SomeClass(Base):
    id = sqlalchemy.Column(PostgreSQLUUID, default=uuid.uuid4, primary_key=True)
    ... # other columns go here

it can even be made more strict by using a NewType for a class-specific id type that cannot be silently conflated with other uuids (without being explicit about it):

from typing import NewType

FooId = NewType("FooId", uuid.UUID)
PostgreSQLFooId = cast("sqlalchemy.types.TypeEngine[FooId]", PostgreSQLUUID)

class Foo(Base):
    id = sqlalchemy.Column(PostgreSQLFooId, default=uuid.uuid4, primary_key=True)

usage example:

obj = session.query(Foo).one()
reveal_type(obj.id)  # FooID, not uuid.UUID

this means it's possible to have different AddressId and UserId types, even if both are the same underlying type (such as an integer or uuid)!

see https://twitter.com/wbolster/status/1299319516829298689

The most ergonomic way to do this (although not in libraries, strictly in your own project code) is just to monkey-patch classes to add runtime support for Class[] syntax. This is what django-stubs project is doing internally for QuerySet and Manager: https://github.com/typeddjango/django-stubs/blob/master/mypy_django_plugin/django/context.py#L60