I'm trying to get an azure sql db working. I am using FastAPI, SQLModel and SQLAlchemy
from sqlmodel import create_engine, SQLModel, Session
def init_db(settings: SqlDBSettings = SqlDBSettings()):
connection_url = URL.create(
settings.DRIVER, username=settings.UID, password=settings.PASSWORD,
host=settings.HOST, port=1433, database=settings.DBNAME,
query={
"driver": "ODBC Driver 17 for SQL Server"
}
)
return create_engine(connection_url, echo=True, connect_args={"check_same_thread": False}, pool_recycle=3600, pool_pre_ping=True)
engine = init_db()
Then i am using sessionmaker with the created engine
session_pool = sessionmaker(
class_=Session, bind=engine, expire_on_commit=False, autocommit=False, autoflush=False
)
Finally the following method is passed as dependency to all my endpoints
def get_db_session():
with session_pool() as session:
yield session
E.g.:
@router.get("/files/{id}", response_model=FileTable)
def get_file_by_id(*, session = Depends(get_db_session), id: int):
file = session.get(FileTable, id)
return file
Everything works until after a couple of hours the database seems to disconnect and I get the following logs
2025-05-08T09:33:33.3775194Z INFO 169.254.129.3:53578 - "GET /api/v1/files/ HTTP/1.1" 500
2025-05-08T09:33:33.4749055Z ERROR Exception in ASGI application
...
2025-05-08T09:33:33.4749533Z raise exc.InvalidatePoolError()
2025-05-08T09:33:33.4749547Z sqlalchemy.exc.InvalidatePoolError: ()
2025-05-08T09:33:33.4750228Z pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Database 'xyz' on server 'xyz.net' is not currently available. Please retry the connection later.
I found that there is an automatic pause option available as i run my database serverless. If i disable this option in azure then the problem does not occur anymore.
(https://www.sqlshack.com/automatic-pause-and-resume-an-azure-sql-database/)
However, this introduces permanent costs of a couple of $s per day which i would like to avoid.
Is the code above using the sessionmaker correct and why does pool_recycle and pre_ping not work as expected? Reading in the documentation i thought it will reestablish a connection before actually executing the query
-Is the code above using the sessionmaker correct
Ensure you are using , Import sessionmaker from sqlalchemy.orm, sqlalchemy.orm.Session for compatibility with sessionmaker.
from sqlmodel import SQLModel, create_engine, Session
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine import URL
def init_db(settings: SqlDBSettings = SqlDBSettings()):
connection_url = URL.create(
settings.DRIVER,
username=settings.UID,
password=settings.PASSWORD,
host=settings.HOST,
port=1433,
database=settings.DBNAME,
query={"driver": "ODBC Driver 17 for SQL Server"}
)
return create_engine(
connection_url,
echo=True,
connect_args={"check_same_thread": False},
pool_recycle=3600,
pool_pre_ping=True,
)
engine = init_db()
Session_pool = sessionmaker(
autocommit=False,
autoflush=False,
bind=engine,
class_=Session # sqlmodel.Session is fine here
)
def get_db_session():
with Session_pool() as session:
yield session
Why does pool_recycle and pre_ping not work as expected?
-pool_recycle=3600: It reconnects if a connection is over an hour old, but this does not help if the database was paused.
-pool_pre_ping=True: It checks if the connection is still alive before using it.
With Azure SQL serverless, pool_pre_ping does not always work well when the database is paused. Resuming can take up to 60 seconds, but the connection check may fail right away. The driver gives an error (HY000) that is not always handled properly, causing SQLAlchemy to drop the whole connection pool.
Follow this document for more details : Dealing with Disconnects
You can try the below recommended steps to fix the issue:
-You can reconnect on faliure with retry logic by wrapping up DB access . This approach retries the operation a few times while Azure SQL starts:
from tenacity import retry, wait_fixed, stop_after_attempt
from sqlalchemy.exc import OperationalError
@retry(wait=wait_fixed(5), stop=stop_after_attempt(3), retry=retry_if_exception_type(OperationalError))
def safe_get_file_by_id(session, id):
return session.get(FileTable, id)
-If your app is not high traffic, then you can use poolclass as NullPool while creating engine as
from sqlalchemy.pool import NullPool
engine = create_engine(connection_url, poolclass=NullPool)
This disables connecting pooling.
Follow this document for more : NullPool
Microsoft Resources : Serverless compute tier for Azure SQL Database