TL;DR: I opened a PR that fixes this (#58). It is being ignored by maintainers for almost a year.

First Check

[X] I added a very descriptive title to this issue.
[X] I used the GitHub search to find a similar issue and didn't find it.
[X] I searched the SQLModel documentation, with the integrated search.
[X] I already searched in Google "How to X in SQLModel" and didn't find any information.
[X] I already read and followed all the tutorial in the docs and didn't find an answer.
[X] I already checked if it is not related to SQLModel but to Pydantic.
[x] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

[X] I commit to help with one of those options 👆

Example Code

import asyncio
from typing import Optional

from sqlalchemy.ext.asyncio import create_async_engine
from sqlmodel import Field, SQLModel, select
from sqlmodel.ext.asyncio.session import AsyncSession


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


async def main() -> None:
    engine = create_async_engine("sqlite+aiosqlite:///database.db")

    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)

    async with AsyncSession(engine) as session:
        session.add(Hero(name="Spider-Boy", secret_name="Pedro Parqueador"))
        await session.commit()

    async with AsyncSession(engine) as session:
        statement = select(Hero).where(Hero.name == "Spider-Boy")
        h = (await session.exec(statement)).first()
        print(h)  # name='Spider-Boy' id=2 age=None secret_name='Pedro Parqueador'


asyncio.run(main())

Description

First of all, props for yet another amazing library! 🎉❤️

I didn't dive into source code yet to verify that I'm doing the right thing, but in the runtime the snippet behaves as expected. However that's not the case with static analysis:

Mypy gives this error:

$ mypy model.py  # Mypy Version: 0.910                                        
model.py:21: error: Argument 1 to "exec" of "AsyncSession" has incompatible type "SelectOfScalar[Hero]"; expected "Union[Select[<nothing>], Executable[<nothing>]]"
Found 1 error in 1 file (checked 1 source file)

PyCharm fails to derive session variable type: May be related to PY-29891!
Even when session type explicitly stated, PyCharm still struggles to provide same experience as with sync Session

I acknowledge that the issue might be caused Typing/MyPy/PyCharm limitations.

I would be happy to work towards fixing the issue in my free time. Ideally, for that I'd want some clarification and thoughts regarding the issue.

Operating System

macOS 11.4 (20F71)

SQLModel Version

0.0.4

Python Version

Python 3.9.6

Additional Context

Before running code snipped, you need to pip install aiosqlite sqlalchemy[asyncio]
Mypy Version: 0.910
PyCharm Version: PyCharm 2021.1.3 (Professional Edition) Build #PY-211.7628.24, built on June 30, 2021

Aug 28 '21 17:08 Bobronium

Do the type hints work with the vanilla SQLAlchemy AsyncSession? The one here is just a subclass of that one.

Aug 28 '21 20:08 egelja

@MrAwesomeRocks, I don't think they work, no.

However, I expect them to work with the given code snippet since SQLModel positions itself as an IDE friendly SQLAlchemy wrapper.

The sqlmodel.Session provides additional typing definitions for its superclass methods, some of this definitions are apparently missing in sqlmodel.ext.asyncio.session.AsyncSession.

Aug 28 '21 22:08 Bobronium

I asked tiangolo on twitter about AsyncSession

2021-08-30 10 09 31

It isn't tested or documented and he think some things (e.g. autocompletions) might not work perfectly yet... so I would take it that Async isn't quite finished yet.