flask-sqlalchemy or sqlalchemy

Question

I am new in both flask and sqlalchemy, I just start working on a flask app, and I am using sqlalchemy for now. I was wondering if there is any significant benefit I can get from using flask-sqlalchemy vs sqlalchemy. I could not find enough motivations in http://packages.python.org/Flask-SQLAlchemy/index.html or maybe I did not understand the value!! I would appreciate your clarifications.

Frill

Hmm, there still isn't a satisfactory answer here. Can anyone explain what the actual concrete benefits of flask-sqlalchemy are over plain old sqlalchemy in a Flask app? – Bony

One big disadvantage though is, Flask-SqlAlchemy doesn't provide any way to setup multi-tenancy in app. That is IMO biggest negative. binds provided only is to attach different database to different model, while there is no way to use tenant-specific database with same model. – Westwardly

@RohitJain you just saved me a lot of trouble. I had this same question. I think the benefit of Flask-SQLAlchemy is if you are building databases with models etc. I find SQLAlchemy docs to be a tough read but extremely powerful and flexible. – Ochre

towardsdatascience.com/… explains some disadvantages of flask-sqlalchemy, namely when you want to share your data models across flask and non-flask contexts (e.g. some data pipeline). It suggests to use sqlalchemy, and also suggests a solution to the threading issue which flask-sqlalchemy comes to solve, using sqlalchemy's scoped_session – Cannery

Answer 1

The main feature of the Flask-SQLAlchemy is proper integration with Flask application - it creates and configures engine, connection and session and configures it to work with the Flask app.

This setup is quite complex as we need to create the scoped session and properly handle it according to the Flask application request/response life-cycle.

In the ideal world that would be the only feature of Flask-SQLAlchemy, but actually, it adds few more things. Check out the docs for more info. Or see this blog post with the overview of them: Demystifying Flask-SQLAlchemy (update: the original article is not available at the moment, there is a snapshot on webarchive).

When I first worked with Flask and SQLAlchemy, I didn't like this overhead. I went over and extracted the session management code from the extension. This approach works, although I discovered that it is quite difficult to do this integration properly.

So the easier approach (which is used in another project I am working on) is to just drop the Flask-SQLAlchemy in and don't use any of the additional features it provides. You will have the db.session and you can use it as if it was a pure SQLAlchemy setup.

Overabundance

I remain confused. The linked blog post (and official docs!) list some plain SQLAlchemy features (like the declarative base) as if they are Flask-SQLAlchemy features; it's unclear whether they're taking credit for stuff built into SQLAlchemy or they've reimplemented it (nor why they did so, if it's the latter). Your first paragraph lists two features: convenience wrappers for session management (but won't you need to roll your own anyway if you want to use your SQLAlchemy models outside Flask?) and some unspecified "configuration" to "work with the Flask app". What does that mean? – Ceremony

I am also a bit confused by questions. The linked blog post is quite clear IMO, for example, it says "Custom declarative base model with support for query property and pagination" the key is "custom" and "with support for query property and pagination" is what it adds on top of SQLAlchemy's declarative base. – Overabundance

Regarding the "convenience wrappers" - they are not for convenience, but to make things work properly. To start using SQLAlchemy you need the database connection (eninge / connection / session) objects and you don't want to create these each time you need to make an SQL query, so these should be created globally and avaiaiable across the application code. So you can do something like "from yourapplication import db" and then do "db.session.something()" without thinking how to create and initialize this "db" object properly. – Overabundance

And regarding the unspecified "configuration" to "work with the Flask app" - it is actually specified in the second paragraph: This setup is quite complex as we need to create the scoped session and properly handle it according to the Flask application request/response life-cycle. See more details in the SQLAlchemy docs: When do I construct a Session, when do I commit it, and when do I close it? and Contextual/Thread-local Sessions. – Overabundance

Thanks for the link to: web.archive.org/web/20190901011222/http://derrickgilland.com/… . has very good explanation of the issue and How to get aroind it. – Rhodie

Answer 2

Flask-SQLAlchemy gives you a number of nice extra's you would else end up implementing yourself using SQLAlchemy.

Positive sides on using Flask-SQLAlchemy

---

1. Flask_SQLAlchemy handles session configuration, setup and teardown for you.
2. Gives you declarative base model that makes querying and pagination easier
3. Backend specific settings.Flask-SQLAlchemy scans installed libs for Unicode support and if fails automatically uses SQLAlchemy Unicode.
4. Has a method called apply_driver_hacks that automatically sets sane defaults to thigs like MySQL pool-size
5. Has nice build in methods create_all() and drop_all() for creating and dropping all tables. Useful for testing and in python command line if you did something stupid
6. It gives you get_or_404()instead of get() and find_or_404() instead of find() Code example at > http://flask-sqlalchemy.pocoo.org/2.1/queries/

Automatically set table names. Flask-SQLAlchemy automatically sets your table names converting your ClassName > class_name this can be overridden by setting __tablename__ class List item

Negative sides on using Flask-SQLAlchemy

---

1. Using Flask-SQLAlchemy will make add additional difficulties to for migrating from Flask to let's say Pyramid if you ever need to. This is mainly due to the custom declarative base model on Flask_SQLAchemy.
2. Using Flask-SQLAlchemy you risk using a package with a much smaller community than SQLAlchemy itself, which I cannot easily drop from active development any time soon.
3. Some nice extras Flask-SQLAlchemy has can make you confused if you do not know they are there.

Mariellamarielle

Answer 3

To be honest, I don't see any benefits. IMHO, Flask-SQLAlchemy creates an additional layer you don't really need. In our case we have a fairly complex Flask application with multiple databases/connections (master-slave) using both ORM and Core where, among other things, we need to control our sessions / DB transactions (e.g. dryrun vs commit modes). Flask-SQLAlchemy adds some additional functionality such as automatic destruction of the session assuming some things for you which is very often not what you need.

Repel

Yes, in your use case Flask-SQLAlchemy seems obsolete. But if the OP would have such a scenario he probably won't ask this question. For a new user who don't know anything about session scope Flask-SQLAlchemy is definitely a must! – Zurn

Answer 4

The SQLAlchemy documentation clearly states that you should use Flask-SQLAlchemy (especially if you don't understand its benefits!):

[...] products such as Flask-SQLAlchemy [...] SQLAlchemy strongly recommends that these products be used as available.

This quote and a detailed motivation you can find in the second question of the Session FAQ.

Zurn

Flask-sqlalchemy seems to be unmaintained. The last update was on 1st of August of 2013. Is this advice relevant any more? – Baltoslavic

@Baltoslavic as long as you do not have concrete issues with it I would still recommend it, especially for new users. – Zurn

Seems to be actively maintained as of November 2014. Lots of recent commits. github.com/mitsuhiko/flask-sqlalchemy/commits/master – Bony

Even though OP didn't ask it directly, imo. he wanted to know the benefits of Flask-SQLAlchemy. Your answer is literally "use it even if you don't know what the benefits are" -- yes, what are the benefits? – Operable

This answer does not answer the OP's question. You are recommending flask-sqlalchemy, whithout providing much reasoning behind the recommendation. – Brandwein

Because this is already covered in the link to the SQLAlchemy documentation... – Zurn

Information shouldn't be in links. It should be summarized in the answer itself -- the links should be there for support and those who want to dig deeper. The link will disappear someday and render the answer useless. See your answer is in another castle. – Gabrielegabriell

Answer 5

As @schlamar suggests, Flask-SqlAlchemy is definitely a good thing. I'd just like to add some extra context to the point made there.

Don't feel like you are choosing one over the other. For example, let's say we want to grab all records from a table using a model using Flask-Sqlalchemy. It is as simple as

Model.query.all()

For a lot of the simple cases, Flask-Sqlalchemy is going to be totally fine. The extra point that I would like to make is, if Flask-Sqlalchemy is not going to do what you want, then there's no reason you can't use SqlAlchemy directly.

from myapp.database import db

num_foo = db.session.query(func.count(OtherModel.id)).filter(is_deleted=False).as_scalar()

db.session.query(Model.id, num_foo.label('num_foo')).order_by('num_foo').all()

As you can see, we can easily jump from one to the other with no trouble and in the second example we are in fact using the Flask-Sqlalchemy defined models.

Izmir

"For example lets say we want to grab all records from a table using a model using Flask-Sqlalchemy. It as simple as Model.query.all()" -- this can all be done with just SQLAlchemy, using Flask-SQLAlchemy provides absolutely nothing new here. – Operable

you are awesome! I stumbled upon this post and it solved a problem I was having. after I installed anaconda, for some reason normal updates were not working. changed from Model.query.all() to db.session.query(Model).all() for some reason allowed the sessions to track and updated as normal. – Viyella

"For a lot of the simple cases Flask-Sqlalchemy is gonna be totally fine" -- which simple cases? What's "gonna be totally fine", exactly? "Flask-SqlAlchemy is defo a good thing." -- why? This post raises more questions than it answers, and it doesn't clarify what Flask-SqlAlchemy offers relative to SqlAlchemy. – Gabrielegabriell

Answer 6

Here is an example of a benefit flask-sqlalchemy gives you over plain sqlalchemy.

Suppose you're using flask_user.

flask_user automates creation and authentication of user objects, so it needs to access your database. The class UserManager does this by calling through to something called an "adapter" which abstracts the database calls. You provide an adapter in the UserManager constructor, and the adapter must implement these functions:

class MyAdapter(DBAdapter):
    def get_object(self, ObjectClass, id):
        """ Retrieve one object specified by the primary key 'pk' """
        pass

    def find_all_objects(self, ObjectClass, **kwargs):
         """ Retrieve all objects matching the case sensitive filters in 'kwargs'. """
        pass


    def find_first_object(self, ObjectClass, **kwargs):
        """ Retrieve the first object matching the case sensitive filters in 'kwargs'. """
        pass

    def ifind_first_object(self, ObjectClass, **kwargs):
        """ Retrieve the first object matching the case insensitive filters in 'kwargs'. """
        pass

    def add_object(self, ObjectClass, **kwargs):
        """ Add an object of class 'ObjectClass' with fields and values specified in '**kwargs'. """
        pass

    def update_object(self, object, **kwargs):
        """ Update object 'object' with the fields and values specified in '**kwargs'. """
        pass

    def delete_object(self, object):
        """ Delete object 'object'. """
        pass

    def commit(self):
        pass

If you're using flask-sqlalchemy, you can use the built-in SQLAlchemyAdapter. If you're using sqlalchemy (not-flask-sqlalchemy) you might make different assumptions about the way in which objects are saved to the database (like the names of the tables) so you'll have to write your own adapter class.

Kurland