Fastapi: [Question] SQLAlchemy QueuePool limits

[alexiri]

I'm trying to follow the SQLAlchemy part of the tutorial but I'm having problems because after several requests I'm running out of sessions. Clearly I should be closing them at some point, but I don't quite know how or when. This is also my first time using SQLAlchemy, so that doesn't help. :)

Should I be using the Starlette database middleware instead? If so, can I still integrate it with the nice SQLAlchemy models?

[tiangolo]

The documentation/tutorial for SQLAlchemy was broken, sorry for that :disappointed_relieved:

It is fixed now, and the code of the tutorial itself is now part of the tests, so it is now continuously ensured that that code works as it should.

The new tutorial doesn't require a full PostgreSQL server, as it uses a SQLite database (a single file), so, you should be able to just copy the code as is, and run it locally, and there you would have a full FastAPI (micro) application, serving from a local database (in a file tests.db).

Please try the new tutorial an let me know how it goes: https://fastapi.tiangolo.com/tutorial/sql-databases/

[alexiri]

Hola @tiangolo,

I must still be doing something wrong, I'm still exhausting the connection pool.

My app.models __init__.py looks like this:

...
engine = create_engine(DATABASE_URI,
    convert_unicode=True,
    echo=False, echo_pool=True,
    pool_size=5,
    max_overflow=0,
    pool_recycle=1)

db_session = scoped_session(
    sessionmaker(autocommit=False, autoflush=False, bind=engine)
)

class CustomBase:
    # Generate __tablename__ automatically
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

Base = declarative_base(cls=CustomBase)
Base.metadata.create_all(bind=engine)

Then, from each place I need a DB connection (like app.crud.user) I do a

from app.models import db_session
...
def get_user_by_id(userid):
    return db_session.query(User).get(userid)

I guess this isn't the right way of doing it, as it seems each import opens a new connection and it never gets released. What should I be doing instead?

[euri10]

Hi Alex, best way to proceed imho would be for you to fork the repo,
checkout on a new branch and write your failing test case so that it's fast
and efficient to get a sense of what you're doing, or at least write an
entire reproducable piece of code others may use instead of pieces.

I'd gladly help but for instance here I don't know what db you're using,
can't infer it from the DATABASE_URI, I have no idea of the error message
you may face, I get a sense of what "exhausting the connection pool" means
but frankly without more details it's rather hard to figure things out.

Le mer. 13 févr. 2019 à 11:45 AM, Alex Iribarren [e-mail]
a écrit :

Hola @tiangolo https://github.com/tiangolo,

I must still be doing something wrong, I'm still exhausting the connection
pool.

My app.models __init__.py looks like this:

...
engine = create_engine(DATABASE_URI,
convert_unicode=True,
echo=False, echo_pool=True,
pool_size=5,
max_overflow=0,
pool_recycle=1)

db_session = scoped_session(
sessionmaker(autocommit=False, autoflush=False, bind=engine)
)
class CustomBase:
# Generate __tablename__ automatically
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()

Base = declarative_base(cls=CustomBase)
Base.metadata.create_all(bind=engine)

Then, from each place I need a DB connection (like app.crud.user) I do a

from app.models import db_session...def get_user_by_id(userid):
return db_session.query(User).get(userid)

I guess this isn't the right way of doing it, as it seems each import
opens a new connection and it never gets released. What should I be doing
instead?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/tiangolo/fastapi/issues/27#issuecomment-463150889,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABDZPjbJsWwoHBqYgUL7baWVkpECsap-ks5vM-yzgaJpZM4a0vji
.

[alexiri]

I'm using Postgres and the error I get is:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 0 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)

This happens after 5 queries to the DB, which leads me to believe I'm not liberating connections as I should.

Still, you're right, the easiest way forward is for me to produce a test case. I'll work on that when I have some time.

[euri10]

You might want to take a look at this explanation

I also wrote a sample test case with a postgresql db and several engine configs, looping from 6 to 100 times the same route, facing no timeouts, hope it helps

notice that without https://github.com/euri10/fastapi/blob/58628e81efe027afb2e9ec5ae19113f13a35a7a1/tests/test_psql_pool.py#L51 the timeout will occur as expected when the max_overflow + current connections is reached, hence the read above that explains all that better than I could :)

[alexiri]

I was just putting this together to show the issue I'm having: https://github.com/alexiri/fastapi_hang. I see now that the problem seems to be how I'm using dependencies that also query the database. The reason I'm trying to do that is to create dependencies that verify a user's role to see if they're allowed to perform a particular action, imitating get_current_user in the Security examples.

The issue does go away if I put a db_session.close() after both queries, but then I guess one request opens and closes two DB sessions... I guess I'm going about this all wrong?

[tiangolo]

@euri10 as always, thanks for the help here!

@alexiri I'm creating a full-stack-fasapi-postgresql project generator and I'm being able to reproduce the issue.

---

The abstract steps to solve it are:

• Don't use a scoped_session but a standard Session.
• Create a session per-request.
• Close the session after the request is done.

---

To finish implementing it in FastAPI in the simplest and more convenient way, we depend on a feature of Starlette that will be available soon: https://github.com/encode/starlette/issues/379

Meanwhile, a "hack" to work around it, that is the closest to what we want is described in the updated docs: https://fastapi.tiangolo.com/tutorial/sql-databases/

---

In short, the situation happens because FastAPI will call the non-async functions in a separate thread, and the scoped_session will create a separate session for each sub-thread, but then it is not being closed properly.

If you don't want to use the technique described in the docs, you can just create a new Session with db_session = Session() at the beginning of each path operation function and call db_session.close() at the end (as you are doing).

For more information, check the comments here: https://github.com/encode/starlette/issues/370#issuecomment-463715970

---

This is just an update on the state of it, so you know how to proceed meanwhile, but once we have request.state I'll update the docs accordingly.

[alexiri]

Hola @tiangolo, thanks a lot for this great update, I understand the problem now. I've implemented the hack for now until the final solution is available.

[tiangolo]

So, request.state in Starlette was implemented today: https://github.com/encode/starlette/pull/404

I just updated FastAPI to upgrade the Starlette version (and the compatibility changes).

And I updated the tutorial for SQL with SQLAlchemy using the new request.state: https://fastapi.tiangolo.com/tutorial/sql-databases/

You should now be able to use the new, proper technique, with FastAPI version 0.6.0 :tada:

[alexiri]

Excellent, thanks! And thanks a lot for the really fast turn-around! You rock!!

[tiangolo]

Thanks! :smile: