Pitfalls to using synchronous and async engine in same app? #10344
-
|
Hi, I'm working on an application that makes use of synchronous SQLAlchemy and has many different jobs running. I would like to introduce SQLAlchemy asyncio into some of the jobs while leaving the synchronous code in place for the vast majority of jobs that would be unchanged. I am aware of features like: run_sync for running otherwise synchronous code in an asyncio application. My question is if it's okay to instantiate and use two engines of different drivers in the same app: sync_db_url = "postgres://user:pass@localhost/test" # psycopg2
async_db_url = "postgresql+asyncpg://user:pass@localhost/test"
sync_engine = create_engine(sync_db_url)
async_engine = create_async_engine(async_db_url)Is this possible or ill-advised? I can't seem to find anywhere this is talked about explicitly in any detail. Cheers, SQLAlchemy Version in Use2.0.20 DBAPIpsycopg2 Database Vendor and Major VersionPostgreSQL 12+ Python Version3.10 Operating systemLinux |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 4 replies
-
|
yes. it works for me. I use both async and sync in the same fastapi project with two diffrent session depends |
Beta Was this translation helpful? Give feedback.
-
|
Hi, The main drawback is probably that you cannot share a transaction between the two. If you use psycopg version 3 you can also use the same dbapi on both engines.
Please note that you can run synchronous sqlalchemy code only. If you try to use other blocking features, like time.sleep() or request, you will block the asyncio loop |
Beta Was this translation helpful? Give feedback.
-
|
Thank you! |
Beta Was this translation helpful? Give feedback.
-
|
How were you able to inherit both AsyncBase and SyncBase in the same model? |
Beta Was this translation helpful? Give feedback.
-
|
I'm not sure what those are. They are not sqlalchemy classes |
Beta Was this translation helpful? Give feedback.
-
|
AsyncBase = declarative_base(cls=AsyncAttrs) class Base(AsyncBase, SyncBase): |
Beta Was this translation helpful? Give feedback.
-
|
You only need one of those, don't create two bases |
Beta Was this translation helpful? Give feedback.
Hi,
The main drawback is probably that you cannot share a transaction between the two.
If that's not a problem then I don't see major things. Just consider that the two engine are independent, so the number of connections to postgresql can be higher with the default configuration, since each engine has its own pool.
If you use psycopg version 3 you can also use the same dbapi on both engines.
Please note that you can run synchronous sqlalchemy code only. If you try to use other blocking features, like time.sleep() or request, you will block the asyncio loop