Why we use yield to get Sessionlocal in Fastapi with sqlalchemy?

Question

def get_db():
    db = SessionLocal()
    try:
        return db
    finally:
        db.close()

I got this code snipped to get Sessionlocal in fastapi with Sqlalchemy. Well, when I used return instead of Yield. My code still works. Then, I do not understand the reason of using Yield. Can someone help me?

Answer 1

Well, when I used return instead of Yield. My code still works. Then, I do not understand the reason of using Yield.

It's a great question, the answer is, yes there's a reason to use yield instead of return.

SQLAlchemy has a Connection Pooling mechanism by default. That means with yield you are creating a single session for each request. When you use return you are using a single database connection for all your app.

Isn't it clear? Let's visualize it to make things more interesting.

So what do we have in this example?

• A connection pool that holds 5 different connections.
• 2 endpoint
• 3 incoming requests.

When you use yield it would look something like this underneath because it goes to an endpoint, asks something to the database and yield creates a new Session object every time. It provides a transactional scope around a series of operations, but using return there instead of yield will just return that session object.

Answer 2

There is a fundamental difference, when you use a return, the closing is performed before the function returns the db object, in fact you are returning a closed db object. Because

When return passes control out of a try statement with a finally clause, that finally clause is executed before really leaving the function.

Otherwise, when the yield is used, the finally code block is executed after the request has been processed and the response has been sent. You can read more about dependencies with yield here.

Why your code continues to work, I cannot tell without seeing the whole code.