3 ms·
+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-a-her
by mrkaye97 2mo ago
+1 to this - I've griped pretty often that FastAPI's documentation implicitly recommends this (https://fastapi.tiangolo.com/tutorial/sql-databases/#create-a-hero 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.
- frollogaston 2mo agoOh wow. Dep injection for DB connections is nasty.
- alfons_foobar 2mo agoI might be outing myself as a noob here, but... what is the (better) alternative?
- 0x696C6961 2mo agoYou inject the pool itself.
- alfons_foobar 2mo agoSorry, 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 2mo agoThe 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 2mo agoNo - 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.
- frollogaston 2mo agoI meant you give it back immediately when you're done with it. So usually after you commit, unless you want to hold it longer for some special reasons.
- alfons_foobar 2mo agoahh, gotcha! thanks!
- oleg2025 2mo agoFastAPI 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.
- deleted 2mo ago[deleted]
- frollogaston 2mo agoThis 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.