Repository navigation
Session connections not properly released when using FastAPI Depends, causing connection pool exhaustion #1417
First Check
Commit to Help
Example Codefrom fastapi import FastAPI, Depends
from sqlmodel import SQLModel, Field, create_engine, Session, select
from typing import Optional, List
# Database model
class User(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
email: str
age: int
# Database setup
DATABASE_URL = "sqlite:///./test.db"
engine = create_engine(
DATABASE_URL,
echo_pool="debug",
)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def get_session():
with Session(engine) as session:
yield session
# FastAPI app
app = FastAPI()
@app.get("/users", response_model=List[User])
async def get_users(session: Session = Depends(get_session)):
users = session.exec(select(User)).all()
return users
@app.get("/users-session-begin", response_model=List[User])
async def get_users(session: Session = Depends(get_session)):
with session.begin():
users = session.exec(select(User)).all()
return users
if __name__ == "__main__":
import uvicorn
uvicorn.run(app, host="0.0.0.0", port=8000)
@app.on_event("startup")
def on_startup():
create_db_and_tables()
seed_database()
def seed_database():
with Session(engine) as session:
# Check if data already exists
existing_users = session.exec(select(User)).first()
if existing_users:
return
# Seed data
users = [
User(name="John Doe", email="john@example.com", age=30),
User(name="Jane Smith", email="jane@example.com", age=25),
User(name="Bob Johnson", email="bob@example.com", age=35),
User(name="Alice Brown", email="alice@example.com", age=28),
User(name="Charlie Wilson", email="charlie@example.com", age=32),
User(name="Diana Davis", email="diana@example.com", age=27),
User(name="Eve Miller", email="eve@example.com", age=29),
User(name="Frank Garcia", email="frank@example.com", age=31),
User(name="Grace Martinez", email="grace@example.com", age=26),
User(name="Henry Rodriguez", email="henry@example.com", age=33),
]
for user in users:
session.add(user)
session.commit()DescriptionBug DescriptionWhen using SQLModel Session with FastAPI's Depends() dependency injection, database connections are not properly released back to the connection pool after request completion. This leads to connection pool exhaustion and application blocking when the pool limit is reached. EnvironmentSQLModel version: 0.0.24 Expected BehaviorDatabase connections should be automatically released back to the connection pool after each request completes, allowing the application to handle concurrent requests without exhausting the pool. Actual BehaviorConnections remain checked out from the pool and are not released, causing:
Steps to Reproduce
If you change the endpoint to hit the ##Questions
Operating SystemLinux Operating System DetailsPRETTY_NAME="Ubuntu 24.04.2 LTS" SQLModel Version0.024 Python Version3.12.5 Additional Contextsample k6 script to load test import http from 'k6/http';
import { check } from 'k6';
import { Rate } from 'k6/metrics';
// Custom metrics
export let errorRate = new Rate('errors');
export let options = {
iterations: 5000,
vus: 100, // 100 virtual users hitting as fast as possible
thresholds: {
http_req_duration: ['p(95)<10000'], // 95% of requests must complete within 10s
http_req_failed: ['rate<0.8'], // Allow higher error rate for stress test
},
};
const BASE_URL = 'http://localhost:8000';
export default function () {
let response = http.get(`${BASE_URL}/users`);
let result = check(response, {
'status is 200': (r) => r.status === 200,
'response time < 10000ms': (r) => r.timings.duration < 10000,
'response has content': (r) => r.body.length > 0,
});
errorRate.add(!result);
// No sleep - hit as fast as possible
}
export function handleSummary(data) {
return {
'load-test-results.json': JSON.stringify(data, null, 2),
stdout: `
=====================================
LOAD TEST SUMMARY
=====================================
Total Requests: ${data.metrics.http_reqs.count}
Failed Requests: ${data.metrics.http_req_failed.count}
Average Response Time: ${data.metrics.http_req_duration.avg.toFixed(2)}ms
95th Percentile: ${data.metrics.http_req_duration['p(95)'].toFixed(2)}ms
Max Response Time: ${data.metrics.http_req_duration.max.toFixed(2)}ms
Queue Exhaustion Indicators:
- High response times (>2000ms): ${data.metrics.http_req_duration['p(95)'] > 2000 ? 'YES' : 'NO'}
- Failed requests: ${data.metrics.http_req_failed.count > 0 ? 'YES' : 'NO'}
- Timeouts: Check for 5xx errors or timeouts above
=====================================
`,
};
} |
Replies: 2 comments 2 replies
|
I'm facing a similar issue, but when using SQLModel with Celery workers. When I process a lot of tasks really quickly, the connection pool gets exhausted |
|
This issue comes from FastAPI, not SQLModel. If I'm right, it will only happen when you specified the response model. Try removing response model from your endpoint (or, return UPD: in the initial code example there is even more obvious problem - blocking database IO is used inside |
This issue comes from FastAPI, not SQLModel.
fastapi/fastapi#12066
If I'm right, it will only happen when you specified the response model. Try removing response model from your endpoint (or, return
JSONResponsedirectly to bypass the response validation) and this should work without errors.UPD: in the initial code example there is even more obvious problem - blocking database IO is used inside
asyncednpoint functions. So, it should be either refactored to usingasyncDB interactions, or endpoint functions should be converted to sync (justdef, notasync def)