[mrkaye97]

+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-...) by suggesting using dependency injection to manage database connections, only to start seeing connection pool exhausted errors as soon as the number of concurrent requests exceeds the number of allowed connections.

[oleg2025]

FastAPI pattern works very well with Pgbouncer, when it is in transaction pool mode.

Your Python application maintains a connection to Pgbouncer during the lifecycle of the request, but the physical Postgres connection is allocated only during the DB transaction. You will need open/close transactions in your code though.

[frollogaston]

This is why I said PgBouncer is a sign of something being wrong. Devs aren't managing connections right, they try to paper over it with PgBouncer, it's not really easier cause they now need to be conscious of xacts instead, and now there's an extra moving part in the DB that most of the team doesn't really understand. PgBouncer has its other uses, but I really don't like this one.

I also get it, xact should be 1:1 with connection in a lot of these backend applications. Sometimes I have a few little helpers for that, like pool.sql() will take conn, open xact, execute, close xact, return conn. If the DB driver doesn't already have that.

[frollogaston]

Oh wow. Dep injection for DB connections is nasty.

[alfons_foobar]

I might be outing myself as a noob here, but... what is the (better) alternative?

[0x696C6961]

You inject the pool itself.

[alfons_foobar]

Sorry, I am being dense... how does that solve the problem?

I still have to get a connection from the pool, I just do it inside the function body now, right?

So this

    @app.get("/users")
    def get_users(conn = Depends[get_db_conn]):
        users = conn.execute("SELECT * FROM users")
        return users

would become that instead:

    @app.get("/users")
    def get_users(pool = Depends[get_db_pool]):
        with pool.get_conn() as conn:
            users = conn.execute("SELECT * FROM users")
        return users

But I still need enough connections in the pool to handle all concurrent requests, no?

[frollogaston]

The idea is you only take a connection from the pool when you need to touch the DB, then you give it back immediately. It's very possible that's only a small fraction of the time spent in some handlers. If you inject the connection, you always hold it through the entire request.

[renegade-otter]

No - you do not always give it back immediately in many cases as you have a transaction, which cannot "change hands". If a write connection makes consecutive updates to the DB, you must see it through before closing.

[alfons_foobar]

ahh, gotcha! thanks!