Connection pooling in Flask - Mini Project: Build & Apply

Connection Pooling in Flask

📖 Scenario: You are building a simple Flask web app that connects to a database. To make your app faster and handle many users, you want to use connection pooling. Connection pooling means reusing database connections instead of opening a new one every time.

🎯 Goal: Build a Flask app that sets up a connection pool to a SQLite database and uses it to get data efficiently.

📋 What You'll Learn

Create a Flask app with a database connection pool

Use SQLAlchemy's connection pooling feature

Write a route that queries the database using the pool

Close connections properly after use

💡 Why This Matters

🌍 Real World

Connection pooling is used in web apps to improve performance by reusing database connections instead of opening new ones for every request.

💼 Career

Understanding connection pooling is important for backend developers to build scalable and efficient web applications.

1

Set up Flask app and database URL

Create a Flask app by importing Flask and initializing it as app. Also, create a variable called DATABASE_URL and set it to "sqlite:///example.db".

Flask

# Your code here

Hint

Use Flask(__name__) to create the app. The database URL for SQLite starts with sqlite:///.

2

Configure SQLAlchemy engine with connection pool

Import create_engine from sqlalchemy. Create a variable called engine by calling create_engine with DATABASE_URL and set pool_size=5 and max_overflow=10 to configure the connection pool.

Flask

from flask import Flask

app = Flask(__name__)

DATABASE_URL = "sqlite:///example.db"
# Your code here

Hint

Use create_engine with pool_size and max_overflow to set up pooling.

3

Write a route that queries the database using the pool

Create a Flask route for "/users" using @app.route. Inside the function get_users, use engine.connect() as conn in a with block. Execute the SQL query "SELECT name FROM users" using conn.execute() and fetch all results. Return the list of user names as a string joined by commas.

Flask

from flask import Flask
from sqlalchemy import create_engine

app = Flask(__name__)

DATABASE_URL = "sqlite:///example.db"

engine = create_engine(DATABASE_URL, pool_size=5, max_overflow=10)

# Your code here

Hint

Use with engine.connect() as conn: to get a connection from the pool. Then run the query and collect names.

4

Add app run block for local testing

Add the standard Flask app run block: if __name__ == "__main__": and inside it call app.run(debug=True) to start the app in debug mode.

Flask

from flask import Flask
from sqlalchemy import create_engine

app = Flask(__name__)

DATABASE_URL = "sqlite:///example.db"

engine = create_engine(DATABASE_URL, pool_size=5, max_overflow=10)

@app.route("/users")
def get_users():
    with engine.connect() as conn:
        result = conn.execute("SELECT name FROM users")
        users = [row[0] for row in result]
    return ", ".join(users)

# Your code here

Hint

This block lets you run the Flask app locally with debugging on.

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