Connection pooling in Flask - Step-by-Step Execution

Concept Flow - Connection pooling

App starts

↓

Create pool of DB connections

↓

Request comes in

↓

Get connection from pool

↓

Use connection for query

↓

Return connection to pool

↓

Next request uses pooled connection

↓

App stops, close all connections

This flow shows how a Flask app creates a pool of database connections to reuse them efficiently for incoming requests.

Execution Sample

Flask

from flask import Flask
from sqlalchemy import create_engine

app = Flask(__name__)
engine = create_engine('sqlite:///mydb.sqlite', pool_size=2)

@app.route('/')
def index():
    with engine.connect() as conn:
        result = conn.execute('SELECT 1')
        return str(result.scalar())

This Flask app uses SQLAlchemy to create a connection pool of size 2 and reuses connections for each request.

Execution Table

Step	Action	Pool State (connections in use / available)	Result
1	App starts and creates pool with 2 connections	0 / 2	Pool ready
2	First request arrives	0 / 2	Get connection from pool
3	Assign connection #1 to request	1 / 1	Connection #1 in use
4	Execute query using connection #1	1 / 1	Query result: 1
5	Return connection #1 to pool	0 / 2	Connection #1 available
6	Second request arrives	0 / 2	Get connection from pool
7	Assign connection #2 to request	1 / 1	Connection #2 in use
8	Execute query using connection #2	1 / 1	Query result: 1
9	Return connection #2 to pool	0 / 2	Connection #2 available
10	Third request arrives	0 / 2	Get connection from pool
11	Assign connection #1 to request (reused)	1 / 1	Connection #1 in use
12	Execute query using connection #1	1 / 1	Query result: 1
13	Return connection #1 to pool	0 / 2	Connection #1 available
14	App stops, close all connections	0 / 0	Pool closed

💡 App stops or no more requests; all connections closed

Variable Tracker

Variable	Start	After Step 3	After Step 5	After Step 7	After Step 9	After Step 11	After Step 13	Final
connections_in_use	0	1	0	1	0	1	0	0
connections_available	2	1	2	1	2	1	2	0

Key Moments - 3 Insights

Why does the pool have connections available even after multiple requests?

Because connections are returned to the pool after use (see steps 5, 9, 13), so they can be reused for new requests.

What happens if two requests come at the same time and pool size is 2?

Both requests get assigned one connection each (steps 3 and 7), using all available connections until returned.

Why is connection #1 reused in step 11 instead of creating a new one?

Because connection #1 was returned to the pool and is available, so the pool reuses existing connections to save resources.

Visual Quiz - 3 Questions

Look at the execution table, what is the pool state after step 7?

A 2 / 0

B 0 / 2

C 1 / 1

D 0 / 1

💡 Hint

Check the 'Pool State' column at step 7 in the execution table.

At which step does the connection #1 get returned to the pool after first use?

A Step 3

B Step 5

C Step 7

D Step 9

💡 Hint

Look for when 'Return connection #1 to pool' happens in the execution table.

If the pool size was 1, what would happen at step 7 when the second request arrives?

A It would wait until the connection is returned

B It would create a new connection beyond the pool size

C It would fail immediately

D It would reuse connection #1 immediately

💡 Hint

Think about how connection pools limit simultaneous connections; see steps 3 and 7.

Concept Snapshot

Connection pooling in Flask with SQLAlchemy:
- Create engine with pool_size (e.g., 2)
- Pool holds open DB connections
- Requests get connections from pool
- Connections returned after use
- Reuse saves time and resources
- Limits max simultaneous DB connections

Full Transcript

Connection pooling means keeping a set of database connections open and ready to use in a Flask app. When the app starts, it creates a pool with a fixed number of connections. Each incoming request takes one connection from the pool, uses it to run queries, then returns it back. This way, connections are reused instead of opening and closing each time, which is faster and more efficient. The execution table shows how connections are assigned and returned step-by-step. If the pool size is 2, two requests can use connections simultaneously. When a connection is returned, it becomes available for the next request. If more requests come than the pool size, they wait until a connection is free. This helps manage resources and improves app performance.

Practice

1. What is the main benefit of using connection pooling in a Flask application?

easy

A. It reuses database connections to improve performance.

B. It automatically creates new database tables.

C. It encrypts all database queries for security.

D. It reduces the size of the Flask application code.

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 Flask apps

   This reuse reduces connection overhead and speeds up database access, improving app performance.

3. Final Answer:

   It reuses database connections to improve performance. -> Option A

4. Quick Check:

   Connection pooling = reuse connections = better speed [OK]

Hint: Pooling means reusing connections to speed up queries [OK]

Common Mistakes:

• Thinking pooling creates tables automatically
• Confusing pooling with encryption
• Believing pooling reduces app code size

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

easy

A. app.config['SQL_POOL_LIMIT'] = 10

B. app.config['POOL_MAX_SIZE'] = 10

C. app.config['SQLALCHEMY_POOL_SIZE'] = 10

D. app.config['MAX_POOL'] = 10

Solution

1. Step 1: Recall Flask-SQLAlchemy config keys

   The correct key to set pool size is 'SQLALCHEMY_POOL_SIZE'.

2. Step 2: Match the option with correct key

   app.config['SQLALCHEMY_POOL_SIZE'] = 10 uses 'SQLALCHEMY_POOL_SIZE' correctly with value 10.

3. Final Answer:

   app.config['SQLALCHEMY_POOL_SIZE'] = 10 -> Option C

4. Quick Check:

   Pool size config = SQLALCHEMY_POOL_SIZE [OK]

Hint: Look for 'SQLALCHEMY_POOL_SIZE' to set pool size [OK]

Common Mistakes:

• Using incorrect config keys like POOL_MAX_SIZE
• Confusing pool size with max connections
• Misspelling config keys

3. Given this Flask-SQLAlchemy setup:

app.config['SQLALCHEMY_POOL_SIZE'] = 5
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 10

What is the maximum number of simultaneous database connections allowed?

medium

A. 15

B. 10

C. 5

D. 20

Solution

1. Step 1: Understand pool size and max overflow

   Pool size is 5 connections kept open. Max overflow allows 10 extra temporary connections.

2. Step 2: Calculate total max connections

   Total max = pool size (5) + max overflow (10) = 15 connections.

3. Final Answer:

   15 -> Option A

4. Quick Check:

   Max connections = pool size + overflow = 15 [OK]

Hint: Add pool size and max overflow for max connections [OK]

Common Mistakes:

• Ignoring max overflow and choosing pool size only
• Adding incorrectly or mixing values
• Confusing max overflow as total limit

4. You set app.config['SQLALCHEMY_POOL_SIZE'] = 'five' in your Flask app. What will happen when you run the app?

medium

A. The app will run normally with pool size 5.

B. A TypeError or ValueError will occur due to wrong type.

C. The pool size will default to 10 silently.

D. The app will ignore the setting and use no pool.

Solution

1. Step 1: Check the type of pool size value

   Pool size must be an integer. Setting it as string 'five' is invalid.

2. Step 2: Identify error on invalid type

   Flask-SQLAlchemy or underlying SQLAlchemy will raise a TypeError or ValueError when parsing this config.

3. Final Answer:

   A TypeError or ValueError will occur due to wrong type. -> Option B

4. Quick Check:

   Wrong type config causes error, not silent fallback [OK]

Hint: Pool size must be integer, strings cause errors [OK]

Common Mistakes:

• Assuming string numbers work fine
• Expecting silent default fallback
• Ignoring error messages on startup

5. You want to optimize a Flask app for many users by adjusting connection pooling. Which combination of settings is best to handle 50 simultaneous users smoothly?

hard

A. SQLALCHEMY_POOL_SIZE=50, SQLALCHEMY_MAX_OVERFLOW=0

B. SQLALCHEMY_POOL_SIZE=20, SQLALCHEMY_MAX_OVERFLOW=10

C. SQLALCHEMY_POOL_SIZE=5, SQLALCHEMY_MAX_OVERFLOW=5

D. SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40

Solution

1. Step 1: Calculate total connections needed

   To handle 50 users, total connections should be at least 50.

2. Step 2: Evaluate each option's total connections

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40: 10 + 40 = 50 (meets requirement)
   SQLALCHEMY_POOL_SIZE=50, SQLALCHEMY_MAX_OVERFLOW=0: 50 + 0 = 50 (meets but no overflow flexibility)
   SQLALCHEMY_POOL_SIZE=5, SQLALCHEMY_MAX_OVERFLOW=5: 5 + 5 = 10 (too low)
   SQLALCHEMY_POOL_SIZE=20, SQLALCHEMY_MAX_OVERFLOW=10: 20 + 10 = 30 (too low)

3. Step 3: Choose best option for flexibility

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40 allows a smaller base pool with high overflow, balancing resource use and capacity.

4. Final Answer:

   SQLALCHEMY_POOL_SIZE=10, SQLALCHEMY_MAX_OVERFLOW=40 -> Option D

5. Quick Check:

   Pool + overflow ≥ users; flexible overflow is best [OK]

Hint: Sum pool size and overflow to cover all users [OK]

Common Mistakes:

• Choosing too small total connections
• Ignoring overflow flexibility
• Setting pool size too high without overflow