Blocking database operation in async method

[skewty]

https://github.com/ahmednafies/fastapi_sqlalchemy_alembic/blob/c97cca7438faaf20bddf7451f34561b193fdf57f/main.py#L20

If the database is down or the database call is slow for any reason your website will be down. This is a self inflicted DoS. The async keyword should not be used here.

SQLalchemy doesn't support asycio yet. See Databases or GINO for async wrappers around SQL Alchemy.

Otherwise, thanks for the boilerplate.

[nf1s]

@skewty : thank you very much for explaining this, I am might have mistyped it, but yeah, I am will aware of that fact and actually thought about writing this article for utilizing GINO from the start but I was a bit lazy :) however, I did not know about databases thank you for sharing that, really cool.

[skewty]

I was playing around with encode/Databases today and have, for now, settled on this hybrid approach:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String, JSON, Float, UniqueConstraint, select
from databases import Database
from pydantic import BaseModel
import asyncio

Base = declarative_base()

class FilterModel(Base):
    __tablename__ = "filter"

    id = Column(Integer, primary_key=True, index=True)
    id_label = Column(String(MAX_ID_LABEL_LENGTH), unique=True, nullable=False)
    gui_label = Column(String(MAX_GUI_LABEL_LENGTH), unique=True, nullable=False)
    attributes = Column(JSON, nullable=False, default={})
    user_data_format = Column(JSON, nullable=False, default={})

class FilterSchema(BaseModel):
    id: int
    id_label: str
    gui_label: str

async def do_query():
    await db.connect()
    async with db.transaction():  # not needed for select but shown for illustration
        query = select([FilterModel])
        print("Loading all rows into memory..")
        rows = await db.fetch_all(query)
        for row in rows:
            print(FilterSchema(**row))
        print("Loading only one row into memory at a time")
        async for row in db.iterate(query):
            print(FilterSchema(**row))
    await db.disconnect()

db = Database('postgresql://postgres:pgpass@192.168.51.153/mapfront')
asyncio.get_event_loop().run_until_complete(do_query())

My IDE (PyCharm) has decent support for sqlalchemy when I do queries this way. I can additionally use Alembic and encode/Databases. Seems like wins all around. :)