Developers using the Flask microframework will often lean on the popular Flask-SQLAlchemy ORM when working with databases.

Flask-SQLAlchemy is a great alternative API to the original SQLAlchemy, as it handles a lot of potential nastiness that can arise when managing database sessions with multiple concurrent connections as is common in web apps.

Since Flask-SQLAlchemy ties every database session or connection with the request/response cycle directly, it makes it really hard to accidentally leave sessions active in the same worker thread to throw unexpected errors. This makes Flask-SQLAlchemy ideal for working with a database-driven web app.

There's a catch, however: It also makes it difficult to work with your database outside the context of your app.

Thankfully, there are a few ways around this.

Use the app factory pattern and app_context

Let's say you have a Flask app that's driven by a database, but this database also needs to access entries outside of any of your web app's views or API endpoints. A common use would be something like a cronjob that's scheduled to return data from a query or insert data on a regular basis.

You can use Flask's app factory pattern alongside modular blueprints to do this in a separate script outside of your Flask app. The app factory pattern is quite handy as it returns your whole app in a function to use in other scripts.

A basic app factory looks something like this:

/my_app/my_app/__init__.py

def create_app(config_filename):
    app = Flask(__name__)
    app.config.from_pyfile(config_filename)

    from yourapplication.model import db
    db.init_app(app)

    from yourapplication.views.admin import admin
    from yourapplication.views.frontend import frontend
    app.register_blueprint(admin)
    app.register_blueprint(frontend)

    return app

This example comes straight from Flask's documentation - here's what the above code is doing:

1. Defines a function create_app to power our app factory
2. Initializes our app, database, and pulls our config variables (the example uses a config file but these variables could be declared in-line if necessary)
3. Registers our routes and blueprints
4. Returns our entire app as an object

Instead of instantiating your app as a global variable as is common in the minimal application design pattern, an app factory can return as many instances of your app as you want.

This means you can generate standalone "apps" for use in other scripts without actually floating the Flask WSGI:

/my_app/query_users.py

from my_app import create_app, db
from my_app.models import Users

def scheduled_user_query():
    app = create_app()

    with app.app_context():
        user = Users.query.filter_by(my_filter=data_to_query).first()
        db.session.close()

        return user

Voila! We've successfully run a query on the Users table in a standalone file outside of an @app.route() function.

Remember, though! Since Flask-SQLAlchemy automatically closes database sessions after it returns a response, you'll need to do that manually if you interact with your database outside of the request/response cycle. That's why you should end all your interactions with your database manually with db.session.close() when running queries or inserting data with the app_context() method.

If you're new to using Flask's app factory or blueprint design patterns, check out flaskeleton, a bare-bones library to bootstrap any new Flask project with a blueprint-first approach.

Build a protected endpoint

Instead of interacting with your database using separate modules, you can create a protected endpoint as a route within your application to ensure you're always within the context of your app.

This method is admittedly a bit hacky. I wouldn't recommend using it for complex tasks and it introduces security concerns, but it does work.

Here's an example of a simple route protected by an Authorization key in the header of a request:

from flask import request
from models import Users

@app.route("/api/put_stuff_into_database/<data>")
def database_insert(data):
    key = request.headers.get('Authorization')
    check = Users.query.filter_by(key=key).first()

    if key == check.key:
        entry = Data(data_in_table=data)
        db.session.add(entry)
        db.session.commit()
        return 200
    else:
        return 401

In this example, we would still have something like a cronjob scheduled to initiate our task, but instead of cron running a Python script, it would send a request to the /api/put_stuff_into_database endpoint with the proper auth header using a tool like cURL.

There are obvious disadvantages to doing things this way, the foremost being secutity. If this is a mission-critical endeavor it's probably not a good idea to expose this function to the clearnet, even if the endpoint is protected.

But this method isn't without its advantages, either: We don't have to worry at all about conflicting sessions, because we're using Flask-SQLAlchemy as it was designed (i.e. within an @app.route decorated function). This method also doesn't require refactoring for blueprints or an app factory if you're not keen on using them.

So for small projects or instances where data security isn't a huge concern, this will work just fine.

In closing

A common question people ask online once they run into problems with Flask-SQLAlchemy's strict context is

"If using Flask-SQLAlchemy outside of a Flask app is so tricky, why not use the vanilla SQLAlchemy ORM that doesn't have these constraints by default?"

A recent blog post I read advocates just that, but I don't think that's the right approach if you're writing a web application first and foremost.

It just doesn't make sense to rewrite so many of the security and convenience handlers present in Flask-SQLAlchemy just so you can run some queries or insert data outside of your app's traditional context. That's the reason Flask-SQLAlchemy exists in the first place, and there's a reason it's so popular.

If your database is primarily for something other than a web app and Flask is a small part of an otherwise robust codebase, that's a totally different story - like if you're one of those bean counters they call "data scientists" and not a webapp developer ;)

Otherwise, Flask-SQLAlchemy is almost always worth the trouble.

Another common argument I've seen is that Flask-SQLAlchemy and vanilla SQLAlchemy have different APIs. While this is true, they're mostly cross-compatible, and using the handful of methods not present in Flask-SQLAlchemy is as simple to resolve as from sqlalchemy import whatever in most instances. Minuscule API differences isn't a strong enough reason to abandon Flask-SQLAlchemy altogether, imho.

Anyways, I hope this tutorial has been helpful for those trying to do increasingly complex tasks with Flask-SQLAlchemy. It's something I've run into trouble with in the past so I figured I'd share my experiences here, as it seems like it's a common problem for newcomers to Flask.