Is SQLModel naturally async aware ?

[joaopfg]

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

No code available

Description

Can I use SQLModel inside my FastAPI async endpoint implementations without problems ? Apparently it works. But I'm not sure if it's destroying the asynchronicity.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.5

Additional Context

No response

[SteffRainville]

Look at issue #54

You can get an Async enginedirectly from SQLAlchemy

Looking at the SQLmodel code directly the engine is not async

[raulolteanu-sudo]

Just Jumping in on the conversation for the sake of not raising another issue:

Using SQLModel's version of AsyncSession and stumbled by this warning while running some tests on the code:

  /home/user/git_repos/project/venv/lib/python3.9/site-packages/sqlmodel/orm/session.py:60: SAWarning: Class SelectOfScalar will not make use of SQL compilation caching as it does not set the 'inherit_cache' attribute to ``True``.  This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions.  Set this attribute to True if this object can make use of the cache key generated by the superclass.  Alternatively, this attribute may be set to False which will disable this warning. (Background on this error at: https://sqlalche.me/e/14/cprf)
    results = super().execute(

-- Docs: https://docs.pytest.org/en/stable/warnings.html

short version:

Class SelectOfScalar will not make use of SQL compilation caching as it does not set the 'inherit_cache' attribute to ``True``

Has anybody bumped into this ?

[Kaelten]

@raulolteanu-sudo Check out #189, the workaround at the end should help with that.

[andrewmarcus]

https://testdriven.io/blog/fastapi-sqlmodel/

The author is using SQLAlchemy's async engine and session rather than SQLModel's to execute the queries; hence session.execute() rather than session.exec(). Consequently you lose some of the mypy typing benefits that SQLModel brings, but looks like it otherwise works.

On the other hand, it would be nice to bring native support into SQLModel, especially since @tiangolo's other project, FastAPI, is all about asyncing all the things... 😄

[failable]

Any updates?

[monchin]

Any updates?

Currently I'm using

from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlmodel import select

# sqlite for example
db_path = "/path/to/db"
uri = f"sqlite+aiosqlite:///{db_path}"
aengine = create_async_engine(uri, echo=True)

async with AsyncSession(aengine) as session:
    stmt = select(***) # whatever you need to do
    results = await session.exec(stmt)
    data_to_add = YourModel(assign value here)
    session.add(data_to_add)
    data_to_del = results.first()
    await session.delete(data_to_del)
    await session.commit()

I also hope the async tutorial would be finished as soon as possible

[coolbreeze2]

from typing import Optional

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

# sqlite for example
db_path = "database.sqlite"
uri = f"sqlite+aiosqlite:///{db_path}"
engine = create_async_engine(uri, echo=True)

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

@pytest.fixture(autouse=True)
async def init_models():
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.drop_all)
        await conn.run_sync(SQLModel.metadata.create_all)

async def test_async_session():
    async with AsyncSession(engine) as session:
        data_to_add = Hero(name="Deadpond", secret_name="Dive Wilson")
        session.add(data_to_add)

        stmt = select(Hero).where(Hero.name == "Deadpond")  # whatever you need to do
        results = await session.exec(stmt)
        data_to_del = results.first()
        await session.delete(data_to_del)
        await session.commit()