Connection pooling in FastAPI - Step-by-Step Execution

Concept Flow - Connection pooling

App starts

↓

Create connection pool

↓

Request comes in

↓

Get connection from pool

↓

Use connection for query

↓

Return connection to pool

↓

Request ends

↓

Repeat for next request

↓

App stops -> close pool

The app creates a pool of reusable connections. Each request borrows a connection, uses it, then returns it for reuse.

Execution Sample

FastAPI

from fastapi import FastAPI
from databases import Database

app = FastAPI()
db = Database('sqlite:///test.db')

@app.on_event('startup')
async def startup():
    await db.connect()

@app.on_event('shutdown')
async def shutdown():
    await db.disconnect()

@app.get('/')
async def read_root():
    query = 'SELECT 1'
    return await db.fetch_one(query)

This FastAPI app uses a database connection pool to handle queries efficiently on each request.

Execution Table

Step	Event	Pool State	Connection Action	Result
1	App starts	Pool created, empty	No connection used yet	Ready to accept requests
2	Startup event	Pool initialized with connections	Connections opened and pooled	Pool ready with open connections
3	Request 1 arrives	Pool has free connections	Connection borrowed from pool	Connection assigned to request
4	Request 1 query	Pool minus 1 free connection	Connection used to run query	Query executed, result ready
5	Request 1 ends	Connection returned to pool	Connection marked free	Pool restored to previous state
6	Request 2 arrives	Pool has free connections	Connection borrowed from pool	Connection assigned to request
7	Request 2 query	Pool minus 1 free connection	Connection used to run query	Query executed, result ready
8	Request 2 ends	Connection returned to pool	Connection marked free	Pool restored to previous state
9	App shutdown	Pool closing	All connections closed	Pool destroyed, app stops

💡 App stops and closes all connections, ending the pool lifecycle.

Variable Tracker

Variable	Start	After Step 3	After Step 5	After Step 6	After Step 8	Final
Pool free connections	0 (before startup)	N-1 (connection borrowed)	N (connection returned)	N-1 (connection borrowed)	N (connection returned)	0 (after shutdown)
Connection borrowed	None	1 connection	None	1 connection	None	None

Key Moments - 3 Insights

Why does the pool have fewer free connections during a request?

Because a connection is borrowed from the pool to handle the request, reducing the free count until it is returned (see execution_table steps 3 and 4).

What happens to connections when the app shuts down?

All connections in the pool are closed and the pool is destroyed to free resources (see execution_table step 9).

Why reuse connections instead of opening a new one each request?

Opening connections is slow and costly. Reusing from the pool saves time and resources, improving performance (concept_flow explanation).

Visual Quiz - 3 Questions

Look at the execution_table, what is the pool state after step 5?

A Pool is empty

B Pool has one connection borrowed

C Pool has all connections free

D Pool is closed

💡 Hint

Check the 'Pool State' column at step 5 in the execution_table.

At which step does the app close all connections?

A Step 5

B Step 9

C Step 7

D Step 3

💡 Hint

Look for the 'App shutdown' event in the execution_table.

If a request borrows a connection but never returns it, what happens to the pool free connections?

A They decrease and stay low

B They stay the same

C They increase

D They reset automatically

💡 Hint

Refer to variable_tracker for 'Pool free connections' during borrowing and returning.

Concept Snapshot

Connection Pooling in FastAPI:
- Create a pool of reusable DB connections at startup
- Borrow a connection per request
- Use connection for queries
- Return connection to pool after use
- Close all connections on shutdown
Improves performance by avoiding repeated connection setup.

Full Transcript

Connection pooling in FastAPI means the app creates a set of database connections at startup. When a request comes, it borrows one connection from this pool to run queries. After the request finishes, the connection is returned to the pool for reuse. This saves time and resources compared to opening a new connection each time. When the app stops, all connections are closed properly. The execution table shows each step: app start, pool creation, requests borrowing and returning connections, and app shutdown closing the pool. The variable tracker follows how many connections are free or borrowed over time. Key moments include understanding why connections are borrowed and returned, and what happens on shutdown. The visual quiz tests understanding of pool state changes and connection lifecycle. This approach helps FastAPI apps handle many requests efficiently by reusing database connections.

Practice

1. What is the main benefit of using connection pooling in FastAPI applications?

easy

A. It caches API responses for faster delivery

B. It reuses database connections to improve performance

C. It automatically creates database tables

D. It encrypts data sent to the database

Solution

1. Step 1: Understand connection pooling purpose

   Connection pooling keeps database connections open and reuses them instead of opening new ones each time.

2. Step 2: Identify the benefit in FastAPI context

   This reuse reduces the time spent opening connections, speeding up database access in FastAPI apps.

3. Final Answer:

   It reuses database connections to improve performance -> Option B

4. Quick Check:

   Connection pooling = reuse connections = better speed [OK]

Hint: Pooling means reusing connections, not creating or encrypting [OK]

Common Mistakes:

• Confusing pooling with encryption
• Thinking pooling creates tables automatically
• Mixing pooling with API response caching

2. Which of the following is the correct way to set the maximum pool size using SQLAlchemy in FastAPI?

easy

A. engine = create_engine(DB_URL, pool_size=10)

B. engine = create_engine(DB_URL, max_connections=10)

C. engine = create_engine(DB_URL, max_pool=10)

D. engine = create_engine(DB_URL, connection_limit=10)

Solution

1. Step 1: Recall SQLAlchemy pool size parameter

   The correct parameter to set max pool size is pool_size.

2. Step 2: Match parameter with options

   Only engine = create_engine(DB_URL, pool_size=10) uses pool_size=10, which is valid syntax.

3. Final Answer:

   engine = create_engine(DB_URL, pool_size=10) -> Option A

4. Quick Check:

   pool_size sets max connections in SQLAlchemy [OK]

Hint: Use pool_size to set max connections in create_engine [OK]

Common Mistakes:

• Using incorrect parameter names like max_connections
• Confusing pool_size with connection_limit
• Trying to set max_pool which is invalid

3. Given this FastAPI code snippet using SQLAlchemy, what will be the output when the app handles multiple requests?

from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.db', pool_size=5, max_overflow=0)

# Each request uses engine.connect()

medium

A. Only one connection is used for all requests

B. Each request creates a new connection ignoring pool size

C. Up to 5 connections are reused; new requests wait if all are busy

D. The app crashes due to too many connections

Solution

1. Step 1: Understand pool_size effect

   Setting pool_size=5 means the engine keeps up to 5 connections open for reuse.

2. Step 2: Behavior on multiple requests

   When more than 5 requests come, new ones wait until a connection is free; no new connections beyond 5 are created.

3. Final Answer:

   Up to 5 connections are reused; new requests wait if all are busy -> Option C

4. Quick Check:

   pool_size=5 means max 5 reusable connections [OK]

Hint: pool_size limits max open connections reused [OK]

Common Mistakes:

• Assuming each request creates a new connection
• Thinking only one connection is shared
• Believing the app crashes when pool is full

4. You wrote this FastAPI code with SQLAlchemy connection pooling:

engine = create_engine('postgresql://user:pass@localhost/db', pool_size=5, max_overflow=0)
connection = engine.connect()
# forgot to close connection after use

What problem will this cause?

medium

A. No problem; connections close automatically

B. The database will reject all connections immediately

C. The app will automatically close connections after timeout

D. Connections will be exhausted causing new requests to hang

Solution

1. Step 1: Identify missing connection close

   Not closing connections means they stay checked out and unavailable for reuse.

2. Step 2: Effect on connection pool

   Since pool_size=5, after 5 connections are checked out and not closed, no free connections remain, causing new requests to wait indefinitely.

3. Final Answer:

   Connections will be exhausted causing new requests to hang -> Option D

4. Quick Check:

   Not closing connections exhausts pool causing hangs [OK]

Hint: Always close connections to free pool slots [OK]

Common Mistakes:

• Assuming connections close automatically without code
• Thinking database rejects connections immediately
• Believing app auto-closes connections after timeout

5. You want to optimize a FastAPI app using SQLAlchemy with a PostgreSQL database. The app has many short requests. Which pooling configuration best balances performance and resource use?

Options:
A) pool_size=20, max_overflow=10
B) pool_size=1, max_overflow=50
C) pool_size=5, max_overflow=0
D) pool_size=0, max_overflow=0

hard

A. pool_size=5, max_overflow=0

B. pool_size=1, max_overflow=50

C. pool_size=20, max_overflow=10

D. pool_size=0, max_overflow=0

Solution

1. Step 1: Understand pool_size and max_overflow roles

   pool_size sets fixed connections; max_overflow allows extra temporary connections beyond pool_size.

2. Step 2: Analyze options for many short requests

   Too large pool_size wastes resources; too small with high overflow risks overhead. pool_size=5, max_overflow=0 with moderate pool_size=5 and no overflow balances reuse and resource use well.

3. Final Answer:

   pool_size=5, max_overflow=0 -> Option A

4. Quick Check:

   Moderate pool_size with no overflow balances performance and resources [OK]

Hint: Moderate pool_size with zero overflow balances well [OK]

Common Mistakes:

• Choosing very high pool_size wasting resources
• Using zero pool_size disables pooling
• Setting high max_overflow causes many temporary connections