Flask + SQLAlchemy tips

First and foremost: this : https://flask-sqlalchemy.palletsprojects.com/en/2.x/

This is the old Flask website built in flask. Some patterns are outdated. But still pretty nice to gloss over with a cup of tea and some buiscuits. 🙂 https://github.com/pallets/flask-website/tree/master/flask_website

scoped_session(sessionmaker()) or plain sessionmaker() ?

Well, the recommended pattern is to use scoped_session as it is considered thread safe. You create a global session object that is thread safe and use it in all of your methods. Here is where flask-sqlalchemy bindings help us. They give us this scoped session out of the box.

So when you do:
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
db = SQLAlchemy(app)

#create some transient objects inside some method
db.session.add(some_db_object) -> here the .session object is thread safe.

Some really nice stuff from the official docs here: https://flask-sqlalchemy.palletsprojects.com/en/2.x/quickstart/#road-to-enlightenment

Road to Enlightenment

The only things you need to know compared to plain SQLAlchemy are:

  1. SQLAlchemy gives you access to the following things:
    • all the functions and classes from sqlalchemy and sqlalchemy.orm
    • a preconfigured scoped session called session
    • the metadata
    • the engine
    • a SQLAlchemy.create_all() and SQLAlchemy.drop_all() methods to create and drop tables according to the models.
    • a Model baseclass that is a configured declarative base.
  2. The Model declarative base class behaves like a regular Python class but has a query attribute attached that can be used to query the model. (Model and BaseQuery)
  3. You have to commit the session, but you don’t have to remove it at the end of the request, Flask-SQLAlchemy does that for you.


Here, there will be no actual column created on user table with name ‘posts’. Instead doing user.posts will actually emit sql query that will fetch all the posts by that user.
Also, notice that in author = db.relationship('User', backref='posts', lazy=True), we have used the ‘User’ class. While in the user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False), we used ‘user’ or the User table in the database.

Similarly, there will be no actual column with name “author” on the post table. If we do post_instance.author, it will actually emot an sql query and fetch the user associated with the post via the foreign key relationship

Create database tables

After we have specified the models (see above section for User and Post models), we can create them in the database like so:

  1. source your env.sh file that has the database connection string
  2. enter python console
  3. >>>from app.py import db (you will do this in the python console.Assuming that app.py is the file where Flask app is instantiated and has the line db = SQLAlchemy(app)
  4. >>>db.create_all()

That’s it. All tables will be created.

You can query for all Users like so: User.query.all() and for all posts like so Post.query.all()

Hashing Password

pip install flask-bcrypt


This library only deals with user authentication. Almost exactly same as Django auth library. Like auth library mainly deals with the User model provided by Django, similarly, this library also mainly deals with User model in your app. The way to wire it in is to use UserMixin in your app’s User model.

pip install flask-login

Then wire it up to the app instance like so (generally done in __init__.py file);

from flask_login import LoginManager

login_manager = LoginManager(app)

login_manager.login_view = 'login'
login_manager.login_message_category = 'info'

Here ‘login’ is the method name associated with login route. This line will cause redirections from protected pages to login page, if an anonymous user tries to access them.

login_message_category is used to provide Bootstap css class to style the flash message.

Next, you will need to add default methods (4 methods.. is_authenticated, is_active, is_anonymous, get_id()) on your app’s User model.

Since, this is such a common thing, use UserMixin provided by flask_login in your User model:

That’s it. Now you can use the login_user, is_authenticated and current_user like so:

Note: current_user is a convenience method given by flask_login. It returns the curent logged user. Its very useful in redirecting logged-in users when the try to visit the login or registration pages.

Note: login_user() is like makes a session ID for the authenticated user and sets the is_authenticated flag to True. logout_user() does the opposite.

in routes.py:
from flask_login import login_user, logout_user, current_user, login_required

and then:

post/redirect/get pattern

Above, we have some AccountUpdateForm with fields like email, username that a user can use to update their account information. What I want to point out are the
return redirect(url_for('account')) and return render_template('account.html', title='Account', form=form) lines.

If you see, they both load the account.html. But we implement a pattern called post/redirect/get pattern, so that reloading a form right after submission does not have side-effects. This is because if you run render_template, right after form submission, your browser will pop-up a warning message.. like you are about to reload form.. which is browser’s way of telling you that you are doing something that will lead to a post request to the server. Explicitly redirecting forces browser to send a get request and then you don’t get that warning.


Step 1

pip install flask-migrate

then in the __init__.py of the main application package, add the flask_migrate extension to the app like so:

now you will be able to see flask db commands

Step 2

Now create a migration repository. This is done using a flask db init command

As you see in the console output, running the above command creates a migrations directory in your project and then puts some files inside it. Basically, this is an empty database migration repository. Each time we do a database migration, a migration script will be added to this migrations directory, specifically in the migrations/versions directory.

Keep in mind that the entire migrations directory, with all of its contents needs to be committed to git. In other words, you should treat this directory and all of its contents as source code.

Step 3

Generate migrations script.

In this stage, we have two scenarios. New projects and Existing projects.

New Projects

flask db migrate

flask db upgrade

Existing Projects with existing database, that were never put under mogrations

Here running flask db migrate will not generate any schema in the migrations/versions directory. This is because the database is up-to-date. The only way to generate the initial database migration in the migrations/versions directory is to change the SQLALCHEMY_DATABASE_URI variable in the config.py to point to a new and empty database. This way we trick flask-migrate into thinking that this is a from scratch migration. I generally don’t change the SQLALCHEMY_DATABASE_URI directly and instead keep my connection strings inside an env.sh file. So, I instead change the connection string in this file to point to an empty database. This step is only needed to generate this initial schema.

flask db migrate

Now you can change the connection string back to your database.

The next step according to the documentation is to run the upgrade command, which executes the migration script and applies the changes in it to your database. Obviously this is also going to fail, because the database does not need updating. Instead you have to tell Flask-Migrate and Alembic that the database is up to date. You can do this with the stamp command.

flask db stamp head

This command will add a alembic_version table to your database, and will store the initial migration generated above as the current migration. The head alias always points to the most recent migration, which in our case is the first and only one currently in the repository.

No Comments

You can leave the first : )

Leave a Reply

Your email address will not be published. Required fields are marked *