DEV Community

Flask and SQLAlchemy without the Flask-SQLAlchemy Extension

Nested Software on June 11, 2018

When using SQLAlchemy with Flask, the standard approach is to use the Flask-SQLAlchemy extension. However, this extension has some issues. In par...

Read full post

arzachel23 • Jan 14 '19

This is very helpful. I come from a database orientation, & I really have no need to have Flask generate my database for me, & then Alembic migrate every time I need to make a change. Strongly prefer using reflected tables in SQLAlchemy, & making database changes w/software that's dedicated to that purpose!

Nguyen Kim Son • Sep 25 '19

Very helpful article! I usually add engine.dispose() as well to make sure there's no stale connection to the database.

Nested Software • Sep 26 '19

Thank you for the kind comment! It's possible I am mistaken, but I believe using engine.dispose() in this way isn't a good idea. It appears to completely reset the entire connection pool that SQLAlchemy is using behind the scenes.

From the documentation:

The Engine is intended to normally be a permanent fixture established up-front and maintained throughout the lifespan of an application. It is not intended to be created and disposed on a per-connection basis

Nguyen Kim Son • Sep 26 '19

Thanks for the information! We used to ran into the problem of the connection being killed before (I suspected this is the database killing inactive connections but this should be already handled by the connection pool) and the workaround is to create a new connection every time, even if it adds overhead.

marinkreso95 • May 22 '19

Hi @nestedsoftware .

Why do we need to remove session on teardown. What would happen if we don't release the resources used by a session after each request?

Nested Software • May 22 '19 • Edited

That's a good question @marinkreso95 ! It's important to make sure to release any database/transactional resources that are no longer needed by the current thread. For example, if a connection to the db used by Session is not released back to its connection pool, it won't be available for use by another thread. Any external resources like this should be cleaned up.

The documentation says that these resources will be released when the current thread ends:

The scoped_session.remove() method, as always, removes the current Session associated with the thread, if any. However, one advantage of the threading.local() object is that if the application thread itself ends, the “storage” for that thread is also garbage collected. So it is in fact “safe” to use thread local scope with an application that spawns and tears down threads, without the need to call scoped_session.remove(). However, the scope of transactions themselves, i.e. ending them via Session.commit() or Session.rollback(), will usually still be something that must be explicitly arranged for at the appropriate time, unless the application actually ties the lifespan of a thread to the lifespan of a transaction.

So, depending on how Flask handles threads, we may not need to do this ourselves. I think it's still better to call Session.remove explicitly when the request is done, just to be certain.

marinkreso95 • May 22 '19

Thank you very much for quick response, and great article @nestedsoftware

Marco Grubert • Oct 11 '18

Excellent introduction!
I am seeing an error message when trying to invoke Session.query() that method is not defined.

Nested Software • Oct 11 '18 • Edited

Hi @grubertm , my first thought is that maybe the sqlalchemy classes are not being imported properly. How did you install sqlalchemy? I did try to provide instructions in the readme file of the linked github project (the article itself only includes the code that needs to be added for sqlalchemy to work with flask - but it assumes everything else has already been done). Did you follow the readme? In that case maybe the readme has a mistake. It could also be an environment thing. I used ubuntu 18.04 with python 3.6 to set this up. If your environment is different, that could also be a possibility.

Fathey • Jun 29 '20

Great !!
but i wonder How to Achive to sperate data in multi databases like in flask_sqlalchmey
bind_key in sqlalchemy

Nested Software • Jun 30 '20 • Edited

I don't know how this works in flask-sqlalchemy, but you can create a separate session for each db.

Pranav • Jul 6 '18

I do use flask for backend api development. Great article. I also had the same concerns and ditched flask-sqlalchemy extension for sqlalchemy stand alone.