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.


class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String, unique=True, nullable=False)
    email = db.Column(db.String, unique=True, nullable=False)
    image_file = db.Column(db.String(20), default='default.jpg', nullable=False)
    password = db.Column(db.String(60), nullable=False)

    def __repr__(self):
        return f'User("{self.username}", "{self.email}", "{self.image_file}"'

class Post(db.Model):
    __tablename__ = 'post'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    author = db.relationship('User', backref='posts', lazy=True)

    def __repr__(self):
        return f'Post("{self.title}", "{self.date_posted}"'

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

>>> from flask_bcrypt import Bcrypt
>>> bcrypt = Bcrypt()
>>> bcrypt.generate_password_hash('my_password')
>>> # above, b' indicates that hash is in bytes
>>> # to generate hash in string, use .decode('utf-8')
>>> bcrypt.generate_password_hash('my_password').decode('utf-8')
>>> # the password hash is salted. So, each subsequent try of same 
>>> # password results in a different hash
>>> bcrypt.generate_password_hash('my_password').decode('utf-8')
>>> # here is how you check the password against the hash
>>> # my_password is the correct password.
>>> hashed_password = bcrypt.generate_password_hash('my_password').decode('utf-8')
>>> bcrypt.check_password_hash(hashed_password,'my_password')
>>> bcrypt.check_password_hash(hashed_password,'not_my_password')


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:

class User(db.Model, UserMixin):
    __tablename__ = 'users'

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:

@app.route('/register', methods=['GET', 'POST'])
def register():
    if current_user.is_authenticated:
        return redirect(url_for('site_home'))
    form = RegistrationForm()
    if form.validate_on_submit():
        hashed_password = bcrypt.generate_password_hash(form.password.data).decode('utf-8')
        new_user = User(
            username = form.username.data,
            email = form.email.data,
            password = hashed_password
        flash(f'Your account has been created. Please login.', category='success')
        return redirect(url_for('login'))
    return render_template('register.html', title='Register', form=form)

@app.route('/login', methods=['GET', 'POST'])
def login():
    if current_user.is_authenticated:
        return redirect(url_for('site_home'))
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter(User.email==form.email.data).first()
        if user and bcrypt.check_password_hash(user.password, form.password.data):
            login_user(user, remember=form.remember.data)
            return redirect(url_for('site_home'))
            flash(f'Login unsuccessful{form.email.data}!', category='danger')
    return render_template('login.html', title='Login', form=form)

def logout():
    return redirect(url_for('site_home'))

post/redirect/get pattern

@app.route('/account', methods=['GET', 'POST'])
def account():
    form = UpdateAccountForm()
    if form.validate_on_submit():
        current_user.username = form.username.data
        current_user.email = form.email.data
        flash('Your account has been updated', 'success')
        return redirect(url_for('account'))
    # Important! we redirected to account page above instead of letting it goto account url
    # through render_template. This forces the client to send a get request message to the server.
    # this is called post-redirect-get pattern
    # this way, the browser does not get a warning "you are about to resubmit.." which
    # happens when you reload a browser right after submitting a form
    return render_template('account.html', title='Account', form=form)

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:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_bcrypt import Bcrypt
from flask_login import LoginManager
from flaskblog.config import Config
from flask_migrate import Migrate

db = SQLAlchemy()
bcrypt = Bcrypt()
migrate = Migrate()
login_manager = LoginManager()
login_manager.login_view = "users.login"
login_manager.login_message_category = "info"

# import routes here. Since this file is also imported into routes.py, this will avoid
# circular dependency as app has been already been defined so when
# from flaskblog import app statement is hit in routes.py, app is already resolved.

def create_app(config_class=Config):
    app = Flask(__name__)
    migrate.init_app(app, db)
    from flaskblog.users.routes import users
    from flaskblog.main.routes import main
    from flaskblog.posts.routes import posts
    from flaskblog.errors.handlers import errors

    return app

now you will be able to see flask db commands

(i)$ flask db
Usage: flask db [OPTIONS] COMMAND [ARGS]...

  Perform database migrations.

  --help  Show this message and exit.

  branches   Show current branch points
  current    Display the current revision for each database.
  downgrade  Revert to a previous version
  edit       Edit a revision file
  heads      Show current available heads in the script directory
  history    List changeset scripts in chronological order.
  init       Creates a new migration repository.
  merge      Merge two revisions together, creating a new revision file
  migrate    Autogenerate a new revision file (Alias for 'revision...
  revision   Create a new revision file.
  show       Show the revision denoted by the given symbol.
  stamp      'stamp' the revision table with the given revision; don't run...
  upgrade    Upgrade to a later version

Step 2

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

(i)$ flask db init
/home/helios/Python/projects/Flask/flask_tut/venv/lib/python3.7/site-packages/flask_sqlalchemy/__init__.py:835: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
  Creating directory /home/helios/Python/projects/Flask/flask_tut/migrations ...  done
  Creating directory /home/helios/Python/projects/Flask/flask_tut/migrations/versions ...  done
  Generating /home/helios/Python/projects/Flask/flask_tut/migrations/script.py.mako ...  done
  Generating /home/helios/Python/projects/Flask/flask_tut/migrations/env.py ...  done
  Generating /home/helios/Python/projects/Flask/flask_tut/migrations/README ...  done
  Generating /home/helios/Python/projects/Flask/flask_tut/migrations/alembic.ini ...  done
  Please edit configuration/connection/logging settings in '/home/helios/Python/projects/Flask/flask_tut/migrations/alembic.ini' before proceeding.

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 *