Connection pooling in FastAPI - Mini Project: Build & Apply

Connection Pooling with FastAPI and Databases

📖 Scenario: You are building a simple FastAPI 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: Create a FastAPI app that uses a connection pool to connect to a SQLite database. You will set up the database URL, configure the connection pool size, write a function to get a connection from the pool, and add a route that uses the connection to fetch data.

📋 What You'll Learn

Create a variable DATABASE_URL with the exact value sqlite+aiosqlite:///./test.db

Create a variable POOL_SIZE and set it to 5

Create an AsyncEngine using create_async_engine with DATABASE_URL and pool_size=POOL_SIZE

Create an async function get_connection that returns a connection from the engine

Create a FastAPI app instance called app

Add a GET route /items that uses get_connection to fetch all rows from a table items and returns them as a list

💡 Why This Matters

🌍 Real World

Connection pooling is used in real web apps to improve speed and handle many users by reusing database connections instead of opening new ones each time.

💼 Career

Understanding connection pooling is important for backend developers working with databases and web frameworks like FastAPI to build scalable and efficient applications.

1

Set up the database URL

Create a variable called DATABASE_URL and set it to the string "sqlite+aiosqlite:///./test.db".

FastAPI

# Create the database URL
# Your code here

Hint

Use a string exactly like "sqlite+aiosqlite:///./test.db" for the database URL.

2

Configure the connection pool size

Create a variable called POOL_SIZE and set it to the number 5.

FastAPI

DATABASE_URL = "sqlite+aiosqlite:///./test.db"
# Set the pool size
# Your code here

Hint

Use a simple integer assignment like POOL_SIZE = 5.

3

Create the async engine with connection pooling

Import create_async_engine from sqlalchemy.ext.asyncio. Then create a variable called engine by calling create_async_engine with DATABASE_URL and pool_size=POOL_SIZE.

FastAPI

DATABASE_URL = "sqlite+aiosqlite:///./test.db"
POOL_SIZE = 5

# Import create_async_engine and create engine with pool size
# Your code here

Hint

Remember to import create_async_engine before using it.

4

Create FastAPI app and route using connection pool

Import FastAPI from fastapi. Create a FastAPI app instance called app. Then create an async function called get_connection that uses async with engine.connect() as conn and returns conn. Finally, add a GET route /items to app that uses get_connection to execute SELECT * FROM items and returns the fetched rows as a list.

FastAPI

from sqlalchemy.ext.asyncio import create_async_engine

DATABASE_URL = "sqlite+aiosqlite:///./test.db"
POOL_SIZE = 5

engine = create_async_engine(DATABASE_URL, pool_size=POOL_SIZE)

# Import FastAPI and create app
# Create async get_connection function
# Add GET /items route
# Your code here

Hint

Use async with engine.connect() as conn to get a connection. Use await conn.execute() to run the query. Return the rows as a list of dictionaries.

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