SQLAlchemy integration in Flask

Hey folks! I'd like to open a can of worms: SQLAlchemy integration in Flask. It's a long read but I hope you'll like it. I try not to be on the ranty side. First, some context: we have been plagued by tech debt for a very long time, maybe more than other development projects because web tech has evolved very quickly in the past 15 years (after the big flatline of "I need to be IE6-compatible", but I digress), and also because we're a small team maintaining a ton of apps. In the past, we've had to switch web frameworks a couple of times. Turbogears 1, then Turbogears 2, then Flask, but we also have apps in CherryPy (to this day!) and Pyramid (and somewhat Django although we're not maintaining them). As a result we've grown very suspicious of framework integration because that means more work when we (inevitably) have to migrate to a different framework. I think it's partly why we chose Flask at the time: it's a minimalist framework, therefore there will be less integration bits to migrate away from. I don't agree entirely with this point of view but I think the standardization has done us a lot of good. We've also standardized on SQLAlchemy for our DB, which is great. But we haven't standardized on how to integrate the two. There's an integration library called Flask-SQLAlchemy, but our apps don't use it (to my knowledge). I think that the reason is that Flask-SQLAlchmemy makes your models unusable without Flask, and that triggers our "what if the framework goes away" PTSD. Here's an example of what models look like with Flask-SQLAlchemy: from flask import Flaskfrom flask_sqlalchemy import SQLAlchemy app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db' db = SQLAlchemy(app) class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(80), unique=True, nullable=False As you can see, the User class inherits from db.Model, something that the Flask-SQLAlchemy extension provides. Same for the Column and the DB types. I do understand why it would make us flinch to "tie" our models to the web framework, even indirectly. (that said, if we want to migrate those models from Flask-SQLAlchemy to native SQLAlchemy, it would be so trivial that plain sed can handle it) So we've tried to do the SQLAlchemy integration in Flask ourselves. This has two downsides: - it's not as easy as it looks, especially when migrations and Alembic come into play - we end up with many slightly different integrations, written by different people or even the same person at different points in time. Ironically, our attempt at avoiding tech debt has caused us more tech debt. Flask-SQLAlchemy has however some pretty strong points: - it's widely used and of good quality - it's maintained by the same people who maintain Flask (although the same cannot be said of Flask-Migrate, the Alembic integration) - it gives us some shortcuts very common in web apps, like get_or_404 or pagination support, that we also had to write ourselves with differing implementations. But if we don't want to go this route, I've extracted a lightweight way of integrating SQLAlchemy in Flask that leaves the models usable without Flask. I think it's a reasonable concern to separate entirely the models from the web app framework, so I'm not advocating for Flask-SQLAlchemy here. I'm advocating against each app having its own integration. On top of that, while I don't see us moving away from Flask anytime soon, I do see a reason why we would want to look at other web frameworks in the near future, and that reason is async support. Flask 2.x has grown support for async views, but it's not as good <https://flask.palletsprojects.com/en/2.0.x/async-await/#when-to-use-quart... as other async-native frameworks such as FastAPI or Starlette, and extensions are unlikely to support it. That said, using async with SQLAlchemy requires doing away with quite a few convenience features of the ORM, such as implicit queries on model attributes, so moving to an async framework would require us to rewrite all the database access layer anyway. Maybe this is not a good example after all. Anyway, this long email is about finding a common ground for SQLAlchemy integration in Flask, while taking into account our difficult experiences with webframewoks in the past, but not being locked in them. Is there something that I misrepresented here? Do you have opinions? Preferences? Thanks! Aurélien