Synchronous path operation functions are not compatible when using SQLite.

Exception

I am developing an embedded program based on SQLite and have encountered the following exception:

Traceback (most recent call last):
  File "/Users/mark/Library/Caches/pypoetry/virtualenvs/temp-1pPir6m0-py3.11/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 260, in _close_connection
    self._dialect.do_terminate(connection)
  File "/Users/mark/Library/Caches/pypoetry/virtualenvs/temp-1pPir6m0-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 689, in do_terminate
    self.do_close(dbapi_connection)
  File "/Users/mark/Library/Caches/pypoetry/virtualenvs/temp-1pPir6m0-py3.11/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 692, in do_close
    dbapi_connection.close()
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 6206205952 and this is thread id 8700050048.

Reproduce

import uvicorn
from fastapi import FastAPI
from fastapi_sqlalchemy import DBSessionMiddleware, db

app = FastAPI()
app.add_middleware(DBSessionMiddleware, db_url="sqlite:///db.db", commit_on_exit=True)

# Error
@app.get("/")
def main():
    db.session.execute(
        "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))"
    )
    return {"Hello": "World"}

# Ok
@app.get("/async")
async def async_main():
    db.session.execute(
        "CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))"
    )
    return {"Hello": "World"}

if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000)

Reason

By ChatGPT.

This error message indicates that you are trying to use an SQLite object (such as a connection or cursor) in a thread different from the one where it was created. SQLite objects are not thread-safe, meaning they can only be used in the thread where they were created.

To resolve this error, you should ensure that all SQLite objects are created and used within the same thread. If you need to use an SQLite object in multiple threads, you should create a new object for each thread.

https://github.com/mfreeborn/fastapi-sqlalchemy/blob/3d182f202cb673a09c52b07fd2d52cb57ce382c5/fastapi_sqlalchemy/middleware.py#L43-L45

When calling with db() in the main thread, and since the path operation function is a normal function, it will be called and executed in a sub-thread. The same SQLite object appears in different threads, resulting in the above exception being thrown.

The ultimate reason is that fastapi-sqlalchemy does not support synchronous path operation functions, so will this feature be considered for implementation?

Fixed in #49 / #47 , will require a small change in the code however:

import uvicorn
from fastapi import FastAPI
from fastapi_sqlalchemy import DBSessionMiddleware, db
from sqlalchemy import text
app = FastAPI()

app.add_middleware(DBSessionMiddleware, db_url="sqlite:///db.db", commit_on_exit=True)

# Error
@app.get("/")
def main():
    db.session.execute(
        text("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))")
    )
    return {"Hello": "World"}

# Ok
@app.get("/async")
async def async_main():
    db.session.execute(
       text("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255))")
    )
    return {"Hello": "World"}

if __name__ == "__main__":
    uvicorn.run(app, host="127.0.0.1", port=8000)

mfreeborn / fastapi-sqlalchemy