how exactly can I use the commit_after flag and is this a way to deal with Transactions?

[FloareDor]

can you please explain a bit about the commit_after flag? Furthermore, how do I deal with rollbacks in case an exception occurs in the service layer?

    query: Select | Insert | Update,
    connection: AsyncConnection,
    commit_after: bool = False,
) -> CursorResult:
    result = await connection.execute(query)
    if commit_after:
        await connection.commit()

    return result

do the db changes not get committed if I set commit_after to False?

Thank you so much.

[zhanymkanov]

Yes, if commit_after=False, then the transaction won't be automatically committed in DB, unless explicitly commited later.

Manually setting this can be helpful in case of heavy writes, and those writes could be batched within a transaction, instead of creating a separate transaction and commit operation for each of them.

Feel free to change this setting, if your application has a regular number of writes. This part of the code is optional and was required by one of my projects.

[FloareDor]

Thank you so so much for the clarification and your work, man. That makes sense.

Another little doubt:

I see that we can pass around AsyncConnection object around in the database functions.

so, does this mean I can do something like this from the service layer ~ use several service functions for calculations and updations, but only commit everything at the end for Atomicity?

# module/service.py
from src.database import engine

async with engine.connect() as connection:
    user = get_user_by_id(id, connection, commit_after=False)
    new_Score, error = calculate_new_score_based_on_user_params(user)
    if error:
        return (assuming the connection gets closed and the whole transaction gets rolled back)
    user.score = new_Score
    update_user_score(user, new_Score, connection, commit_after=False)
    update_leaderboard(new_Score, user, connection, commit_after=False)

do you think doing something like this makes sense to you, if yes, are there any cons to this?
also, I'm curious why you chose to stick to SQLALCHEMY CORE instead of Sessions.

[zhanymkanov]

Yep, there is a iterator in src/database.py

async def get_db_connection() -> AsyncConnection:
    connection = await engine.connect()
    try:
        yield connection
    finally:
        await connection.close()

which is actually useful only when you call it within routes as a dependency. That way you will have 1 db connection per 1 user request (which might have more than 1 DB calls)

For example

@router.post("/profiles", response_model=CreateProfileResponse, status_code=201)
async def create_profile(
    profile_data: ProfileInit = Depends(valid_profile_init_code),
    db_connection: AsyncConnection = Depends(get_db_connection),
) -> dict[str, Any]:
    created_profile = await service.create_profile(profile_data, db_connection)
    inviter_profile = await service.get_inviter_profile(profile_data.invite_id, db_connection)

    return {
        "profile": created_profile,
        "inviter_profile": inviter_profile,
    }

[zhanymkanov]

I prefer Core, because back in the day there was no async Sqlalchemy ORM, and only the core part could be made async.

That saying, core is more flexible and explicit. ORM stuff is fancy, but I didn't buy it yet.

[zhanymkanov]

Committing later is not a silver bullet, and you shouldn't stick to this solution unless you have to. I had to do this because of the specifics of my previous project, where the writes were so frequent, that COMMIT operation in db quickly became top 1 query.

[FloareDor]

Thank you so much for all this help, man.

It's clear to me now how to approach Transactions.

Thanks for all your work and help.