How to Use Async SQLAlchemy in FastAPI

In this video, I'll demonstrate how to use async Sqlalchemy in FastAPI by converting an existing sync version of Sqlalchemy to Async.
Need one-on-one help with your project? I can help through my coaching program. Learn more here: prettyprinted....
Get the code I wrote in this video here: prettyprinted....
Twitter: / pretty_printed
Github: github.com/pre...

[@gerardorosiles8918]

Thanks for the explanation. Illustrating a more complex query and a full CRUD example would be great.

[@cusematt23]

This helped me a ton ... great stuff👍

[@Iamjafex]

Good overview! I agree with your distaste of the new sql alchemy approach. I've used pre 2.0 for a few years now and have found it very easy and comfortable, but I do see the benefit of this approach in verbosity. Hopefully it'll make stuff like joins and other weird query gimmicks easier than the old approach

[@prettyprinted]

Yeah I need to use it in a bigger project to get a good feel for how it works. Thanks for watching the video!

[@SsaliJonathan]

Nice video Anthony

[@prettyprinted]

Thanks for watching!

[@sinancetinkaya]

Yesterday I was looking for this :)

[@prettyprinted]

Glad I could help. Thanks for watching.

[@night23412]

instead of doing
results = await db.execute(select(User))
users = results.scalars().all()
you can also do this if it makes it look any better
users = await db.scalars(select(User))
users = users.all()

[@jijojoseph8721]

can you help in how we can integrate alembic into this

[@ineps7603]

in this approach will be one Session per request?

[@Learn_with_cosmos]

Nice one. I am coming back to python after years of NodeJS and others lol!

[@pococonut609]

Thanks

[@mauricioyepes3394]

Great video, thank you. What do you think about using asyncio?
result = await asyncio.gather(*tasks)
And tasks could be like:
tasks = [get_users()]
In that way you don't need to change the logic to access the DB, the responsibility for converting the get_users to async will be for asyncio. This may work for projects that already have a lot of logic using sync access to the DB so they don't need to refactor all the logic they have to access the DB.

[@prettyprinted]

At some point you'll need to have async access to the database otherwise you won't get all the benefits of async code. But if your DB access code where contained behind helper functions, it would be easier to convert the helper functions to async.

[@default_youtube_profile]

aiomysql extension for sqlalchemy does not have async_sessionmaker! what should I use?

[@salmanmushtaq7633]

Can we use same for production? Also what will be the driver for postgresql?

[@qqyyabs]

psycopg3 works pretty well

[@sampri22]

Is there one session per request in this example?

[@TheAremoh]

Nice video.

[@prettyprinted]

Thanks for watching!

[@ReRubis]

Is putting base.metadata.create_all method in function returning session a good approach?
Isn't it gonna run everytime you init session, which occurs on every endpoint?
And it's run_sync. Doesn't seem efficient.
I think it should be separated into a separate function and ran once on the app startup, no?

[@tainebambrough6029]

Thank you for this !

[@prettyprinted]

You're welcome! Thanks for watching.

[@CodeTesting-pd1td]

Would you happen to know if using the @asynccontextmanager decorator from the built-in contextlib is of any use when creating AsyncSession objects?
According to the SQLAlchemy documentation, it seems that the async_sessionmaker is already handling the teardown.
I am having trouble deciding if I should use the asynccontextmanager or not.

[@danielfajt8090]

Does this solution cover a rollback? get_db() -> AsyncSession, on which you will use transaction eventually. If you call flush() somewhere in the call stack and an exception is raised, this won't call a rollback, right? Thanks.

[@moneeshkumar1838]

Is it safe to use Aync SQLAlchemy, do we run into race conditions and Is it production safe?
Kindly reply

[@aflous]

It is as safe as it can get, "we are all consenting adults here"

[@user-uc2lm7pw9x]

Great video,
Actually, I have an question.
when I use AsyncSession and call await db.execute(""" some procedures """) and that result of the procedure is multiple result sets.
so how to get several sets?

[@qqyyabs]

use limit in statement

[@joaovictor-dl6ve]

What's the difference using async db and sync db? like, if I want I can use SYNC sqlalchemy with ASYNC fastapi , right?

[@prettyprinted]

Yes, you always have the option of using sync code in async code. But the other way around requires that you set up your app to use async. That comes for free in FastAPI, but if you were writing something like a script, you'd need to set it up to handle async. Why async? So you don't have to wait for input/output and do other things instead.

[@Useroftherisingsun]

I understand that nobody's probably gonna answer me soon here, but I don't understand why metadata.create_all() is placed into get_db() function. Like, isn't create_all() method is should only be used once while creating database, not when accesing it?

[@prettyprinted]

I did that for convenience so I wouldn't have to create all the tables separately. It doesn't hurt to run create_all multiple times because it doesn't have any effect if the tables already exists. But yes, in a real app, you wouldn't need it in get_db.

[@Useroftherisingsun]

@@prettyprinted does it also have no effect on the execution time?

[@prettyprinted]

@@Useroftherisingsun There is definitely a small cost to using it, yes, so there's no need to use it in a production app. In a development environment, your database is often running on the same machine as your code, so it only adds a few milliseconds.

[@Useroftherisingsun]

@@prettyprinted Got it. Thanks for your fast reply! Have a great day :)

[@random_act]

cool

[@wattsfield1889]

That's weird, my AsyncSession does not have the .query()

[@joaovictor-dl6ve]

Can I use migrations with sqlalchemy assincronous?

[@prettyprinted]

Yeah, migrations can still be used.

[@fumano2679]

Ill just stick to synchronous computing, if i wanted efficiency i would use rust and not python :)

[@heroe1486]

Then continue with your logic and use Django/DRF, which is superior than other python options in most aspects outside of perfs.