flush vs commit in async SQLAlchemy

The difference between flush and commit in SQLAlchemy (and by extension in AsyncSession) is significant, as they serve different purposes in the lifecycle of a transaction:

flush

- Purpose: flush sends the current state of the session's changes (like inserts, updates, and deletes) to the database but does not commit the transaction. It ensures that the database reflects the current state of the session's changes.

- Scope: Only affects the database's in-memory state but does not make any changes permanent.

- Use Case: flush is used when you need to ensure that pending changes are sent to the database before performing further operations that depend on these changes.

commit

- Purpose: commit makes all the changes in the current transaction permanent. It finalizes the transaction and ensures that all pending changes are saved to the database.

- Scope: Commits the transaction, making all changes permanent and visible to other transactions.

- Use Case: commit is used when you are certain that all operations in the current transaction are complete and should be permanently saved to the database.

Example:

Consider a scenario where you are adding a new user to the database and want to perform some subsequent operations that depend on this user being added.

Using flush:

async with AsyncSession(engine) as session:
    async with session.begin():
        session.add(user)
        await session.flush()  # Sends the new user to the database
        # Perform some operations that depend on user being in the database
        # ...
        user_id = user.id
        new_order = Order(user_id=user_id, item='Book')
        session.add(new_order)
    await session.commit()  # Commits both the user and the order        

In this example, flush ensures that the user is sent to the database and gets an id before the Order object is created with the user_id.

Using commit:

async with AsyncSession(engine) as session:
    async with session.begin():
        session.add(user)
        # Perform some operations (user is not yet in the database)
        # ...
    await session.commit()  # Commits the user to the database
# At this point, the user is permanently saved in the database.        

Here, commit makes the addition of user permanent in the database. Any operations that depend on user being in the database should occur after the commit.

Diagram

[ Document ] -------> [ Database ]

(Flush) (Temporary state)

↓

[ Database ] -------> [ Document ]

(Commit) (Permanent state)

Conclusion:

- flush: Synchronizes the session's state with the database without committing the transaction.

- commit: Makes all changes in the current transaction permanent.

You would use flush when you need the database to be aware of changes without making them permanent (e.g., to get an auto-generated ID). You would use commit to finalize and make all changes in the transaction permanent.