Please support latest SQLAlchemy or pin it

[samuelcolvin]

Hi @tiangolo, fastapi tests are currently failing and therefore causing pydantic tests to fail.

See pydantic/pydantic#2584, fastapi is not compatible with the v1.4 of SQLAlchemy which was released earlier in March, I've had to pin to SQLAlchemy==1.3.23

Please could we fix fastapi (the incompatibility looks like it might be trivial) or pin the dependency?

Once master of fastapi is fixed, we'll need to remember to also remove the hack from pydantic.

[Kludex]

@samuelcolvin This comes from encode/databases. @PrettyWood created a PR there: encode/databases#299

If this is not solved, @tiangolo will probably fix this in the next weeks.

[PrettyWood]

https://github.com/encode/databases/releases/tag/0.4.3 is out! @samuelcolvin No need for your fix anymore!

[gjask]

Also it seems to me, that SQLAlchemy 1.4 has started to support async for its ORM. It would be great to have it available in fastapi.

https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html#synopsis-orm

[ghost]

Is the use of databases still necessary/desirable? I tried to replace the current async/core-version with pure sqlalchemy, to be more in line with the sqlalchemy documentation, and it kind of works even though I probably understand only 10% of what I'm doing and wouldn't notice if this was horribly bad as long as it won't crash and burn.

meta = MetaData()

t1 = Table(
    "t1", meta, Column("id", Integer, primary_key=True), Column("name", String)
)

engine = create_async_engine(
        "sqlite+aiosqlite:///./sqlite.db",
        echo=True, connect_args={"check_same_thread": False}
    )

app = FastAPI()

@app.on_event("startup")
async def startup():
    async with engine.begin() as conn:
        await conn.run_sync(meta.create_all)
        
@app.on_event("shutdown")
async def shutdown():
    await engine.dispose()
        

@app.get("/")
async def root():
    return {"message": "Hello World!"}
    
@app.get("/read")
async def get_read():
    async with engine.connect() as conn:
        result = await conn.execute(t1.select())
        return result.fetchall()
    
@app.get("/insert")
async def get_insert():
    async with engine.connect() as conn:
        result = await conn.execute(t1.insert(), [{"name": "some name 1"}, {"name": "some name 2"}])
        await conn.commit()
    return {"message": "Done"}

[rinckd]

Michael Kennedy goes over async SQLAlchemy with FastAPI in one of his latest courses: https://github.com/talkpython/web-applications-with-fastapi-course/tree/main/code/ch8-async-databases

It's definitely interesting to play around with, but the SQLAlchemy asyncio extension is still considered beta software until the 2.0 release.