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