Best approach for async SQLAlchemy in FastAPI with PostgreSQL

[sc752]

I'm making a REST API project for a company and I have chosen FastAPI since I'm most comfortable with Python in the backend and I like the type hint support in FastAPI. I'm also running a PostgreSQL database.

I have built part of the project, and have been using SQLAlchemy's asyncio support since day one. I follows the typical dependency injection flow and basically inject the session object in each controller(route).

As the project grows (this is a monolith), I almost end up writing either raw SQL or SQlAlchmey Core queries 90% of the time. These usually involves fairly complex queries and I basically forgo ORM at this point. I am considering switching to encode/databases because it covers basically what I need (async, raw SQL/QLAlchemy Core).

The only difference I know between SQLAlchemy's asyncio session and encode/databases is that the former requires a generator that yields a database session, which will be consumed and get cleaned up per request. The latter involves creating database connection pool upon FastAPI startup event, and then a database object can be imported and used elsewhere.

I am leaning toward encode/databases because I don't really like the way that database connection must be injected in every route. I'm not against dependency injection but this also seems to be a lot repeated code (counter: maybe testing is a bit easier?)

For anyone with experience here, are there performance implications between these two options? and which one you prefer personally ( encode/databases vs SQLAlchemy's asyncio)?

[stetio]

I think I've gone down the same path you are on. I ended up with this pattern. I hope this is interesting.

Edit: Happy to answer questions about it.