Why should we use SQLAlchemy with Python Async

As we all know, SQL operation is usually considered as the most time-consuming work in a whole project. To alleviate and reduce the impact to the overall performance caused by SQL operation, we should consider using an async way to operate SQL database.

Also, async API could help us build an concurrent database operation, that's means the program wouldn't stuck by a single time-consuming database operation. The database operation could be executed with other program features at the same time.

Next, we are going to discuss how to create an async engine, and use the engine to connect MySQL database and operate the database.

Async Engine & sessionmaker

What is engine

In SQLAlchemy, all SQL application is start from an SQL database engine, which could be created using create_engine or its async version create_async_engine(). Typically, engine is created for a single specified SQL database.

What does engine do

The engine act as a factory to produce connection which is used to operate the database. Also, the engine has a holding space for created connections called connection pool, which helps to reuse the created connections and improve performance.

Create Engine

Below is code example to create a new async engine.

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

# async database engine, and use aiomysql as operation core
# 
# Notice:
# If you want to use async with sqlalchemy,
# then the operation core must be async-competible
engine = create_async_engine(
    f"mysql+aiomysql://"
    f"{configs.sql.username}:{configs.sql.password}"
    f"@{configs.sql.host}/{configs.sql.dbname}"
)

# async version sessionmaker
session_maker = async_sessionmaker(_engine_instance)

Here is some description of code above.

URL-like string param

Notice that when we create engine, the primary param is a URL-like string, which contains the necessary info of the database we want to connect. It consists of several part.

• dialect Type of the database, could be mysql, sqllite etc.
• DBAPI The operation core of the engine, all operation will be execute through this DBAPI, for example, could be aiomysql.
• Database Location Provide database info like host, username, dbname, password and port. port is usually to be 3306 for MySQL.

echo param

If you want to add a logger to the SQL engine, you can add echo = True param in create_async_engine() method. Then all the operation executed will be output to stdout.

Lazy Initialization

SQLAlchemy will NOT immediately create an engine and connect to database when you call create_async_engine() method, the connection will be established when the first time you operate the database using this engine.

To learn more about creating engine, please check out SQLAlchemy Docs - Create Engine

Session Management

It's highly recommended to use ContextManager pattern with SQLAlchemy Session object.

Following is an simple example:

async def get_session():
    """Get a new session using session maker

    Usage With FastAPI:

    This function could be used as FastAPI dependency.
    Once you get the returned `Session` object, it must be used in Context Manager pattern like below:

        async with get_session() as session:
            session.execute(...)

    Otherwise, the Session may never be closed properly.
    
    Use Independently:
    
    When using this function without FastAPI, you should use another util function to convert 
    this function into context manager (which actually automatically done in FastAPI)
    
        from contextlib import asynccontextmanager
        
        session_mgr = asynccontextmanager(get_session())
        async with session_mgr() as session:
            session.execute(...)
    """
    maker = init_session_maker() # show return async session maker
    async with maker() as session:
        # logger.debug(f'Before yield session')
        yield session
    logger.debug("Session closed")