AsyncSession does not play well with typing and autocompletion

[Bobronium]

TL;DR: Fix is available here: #58, pls merge.

First Check

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

Commit to Help

• 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

[egelja]

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

[Bobronium]

@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.

[obassett]

I asked tiangolo on twitter about AsyncSession

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.

[Bobronium]

@obassett, acknowledged! Well, I'm happy to help anyway :)

[SteffRainville]

The samples uses a "create_async_engine" directly from SQLAlchemy but uses the AsynSession from SQLmodel. Is this the way to go?

[Bobronium]

Is this the way to go?

I'm not sure if it's the way, but it's certainly a way :)

Are there any other ways to use it you aware of?

[lucasteles]

There is any workaround to have the right type check?

[bvsn]

@lucasteles just check the attached PR. For now, you can manually patch (or ignore) AsyncSession.exec typing for the statement argument.

[unkindypie]

Any updates?

[Bobronium]

@tiangolo, fix this, please.