DEV Community

Cover image for Flask and SQLAlchemy without the Flask-SQLAlchemy Extension
Nested Software
Nested Software

Posted on • Updated on • Originally published at nestedsoftware.com

Flask and SQLAlchemy without the Flask-SQLAlchemy Extension

When using SQLAlchemy with Flask, the standard approach is to use the Flask-SQLAlchemy extension.

However, this extension has some issues. In particular, we have to use a base class for our SQLAlchemy models that creates a dependency on flask (via flask_sqlalchemy.SQLAlchemy.db.Model). Also, an application may not require the additional functionality that the extension provides, such as pagination support.

Let's see if we can find a way to use plain SQLAlchemy in our Flask applications without relying on this extension.

This article focuses specifically on connecting a Flask application to SQLAlchemy directly, without using any plugins or extensions. It doesn't address how to get a Flask application working on its own, or how SQLAlchemy works. It may be a good idea to get these parts working separately first.

Below is the code that sets up the SQLAlchemy session (db.py):

import os

from sqlalchemy import create_engine

from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker

engine = create_engine(os.environ['SQLALCHEMY_URL'])

Session = scoped_session(sessionmaker(bind=engine))
Enter fullscreen mode Exit fullscreen mode

The key here is scoped_session: Now when we use Session, SQLAlchemy will check to see if a thread-local session exists. If it already exists, then it will use it, otherwise it will create one first.

The following code bootstraps the Flask application (__init__.py):

from flask import Flask

from .db import Session

from .hello import hello_blueprint

app = Flask(__name__)
app.register_blueprint(hello_blueprint)

@app.teardown_appcontext
def cleanup(resp_or_exc):
    Session.remove()
Enter fullscreen mode Exit fullscreen mode

The @app.teardown_appcontext decorator will cause the supplied callback, cleanup, to be executed when the current application context is torn down. This happens after each request. That way we make sure to release the resources used by a session after each request.

In our Flask application, we can now use Session to interact with our database. For example (hello.py):

import json

from flask import Blueprint

from .db import Session

from .models import Message

hello_blueprint = Blueprint('hello', __name__)

@hello_blueprint.route('/messages')
def messages():
    values = Session.query(Message).all()

    results = [{ 'message': value.message } for value in values]

    return (json.dumps(results), 200, { 'content_type': 'application/json' })
Enter fullscreen mode Exit fullscreen mode

This should be sufficient for integrating SQLAlchemy into a Flask application.

For a more detailed overview of the features Flask-SQLAlchemy provides, see Derrick Gilland's article, Demystifying Flask-SQLAlchemy

We also get the benefit of not having to create a dependency on Flask for our SQLAlchemy models. Below we're just using the standard sqlalchemy.ext.declarative.declarative_base (models.py):

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String

Base = declarative_base()

class Message(Base):
    __tablename__ = 'messages'
    id = Column(Integer, primary_key=True)
    message = Column(String)

    def __repr__(self):
        return "<Message(message='%s')>" % (self.message)
Enter fullscreen mode Exit fullscreen mode

I could be wrong, but I would prefer to start a project with this approach initially, and only to incorporate the Flask-SQLAlchemy extension later if it turns out to be demonstrably useful.

This code is available on github: https://github.com/nestedsoftware/flask_sqlalchemy_starter

Top comments (12)

Collapse
 
arzachel23 profile image
arzachel23

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!

Collapse
 
sonnk profile image
Nguyen Kim Son

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

Collapse
 
nestedsoftware profile image
Nested Software

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

Collapse
 
sonnk profile image
Nguyen Kim Son

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.

Collapse
 
marinkreso95 profile image
marinkreso95

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?

Collapse
 
nestedsoftware profile image
Nested Software • 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.

Collapse
 
marinkreso95 profile image
marinkreso95

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

Collapse
 
grubertm profile image
Marco Grubert

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

Collapse
 
nestedsoftware profile image
Nested Software • 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.

Collapse
 
fatheyabdelslam profile image
Fathey

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

Collapse
 
nestedsoftware profile image
Nested Software • Edited

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

Collapse
 
pranav93 profile image
Pranav

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.