Flask SQLAlchemy Database

Flask does not include a built-in ORM. The community standard is Flask-SQLAlchemy, which wraps SQLAlchemy and provides a clean integration with the Flask application context. This article covers ORM concepts, model definitions, CRUD operations, query filtering, relationships, database migrations with Flask-Migrate, server-side sessions with Flask-Session, and application modularization with Blueprints.

ORM Overview

ORM (Object-Relational Mapping) maps Python class instances to rows in a relational database table.

Advantages:

Write Python, not SQL — database operations become attribute and method calls on model objects.
Database abstraction — switching from MySQL to PostgreSQL requires only a config change, not code changes.

Disadvantages:

Some performance overhead compared to raw SQL.
Complex queries can become verbose or less efficient.

Setting Up Flask-SQLAlchemy

# Install Flask-SQLAlchemy
pip install flask-sqlalchemy

# MySQL driver (if using MySQL)
pip install flask-mysqldb
# If flask-mysqldb fails on Linux: sudo apt-get install libmysqlclient-dev python3-dev

Database Connection Configuration

# config.py
class Config(object):
    DEBUG = True
    SECRET_KEY = "your-secret-key"
    # Connection string: dialect://user:password@host:port/dbname?charset=encoding
    SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4"
    SQLALCHEMY_TRACK_MODIFICATIONS = False  # suppress modification tracking warning
    SQLALCHEMY_ECHO = True                  # print SQL statements to console

Create the database in MySQL first:

mysql -uroot -p123
mysql> CREATE DATABASE students CHARSET=utf8mb4;

Common SQLAlchemy Field Types

Field Type	Python Type	Description
`Integer`	`int`	32-bit integer
`SmallInteger`	`int`	16-bit integer
`BigInteger`	`int`	Unlimited-precision integer
`Float`	`float`	Floating-point number
`Numeric(p,s)`	`Decimal`	Fixed-precision decimal
`String(n)`	`str`	Variable-length string, max n characters
`Text`	`str`	Long text
`Boolean`	`bool`	True/False
`Date`	`datetime.date`	Date only
`Time`	`datetime.time`	Time only
`DateTime`	`datetime.datetime`	Date and time
`LargeBinary`	`bytes`	Binary data

Common Column Constraints

Option	Description
`primary_key=True`	Mark as primary key
`unique=True`	No duplicate values allowed
`index=True`	Create a database index for faster queries
`nullable=True/False`	Allow or disallow NULL values
`default=value`	Default value when none is provided

Defining Models

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(import_name=__name__)

class Config():
    DEBUG = True
    SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8"
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)

db = SQLAlchemy()
db.init_app(app)   # connect db to the app

class Student(db.Model):
    __tablename__ = "tb_student"

    id    = db.Column(db.Integer, primary_key=True, comment="Primary key")
    name  = db.Column(db.String(64), index=True, comment="Name")
    sex   = db.Column(db.Boolean, default=True, comment="Sex")
    age   = db.Column(db.SmallInteger, nullable=True, comment="Age")
    email = db.Column(db.String(128), unique=True, comment="Email")
    money = db.Column(db.Numeric(8, 2), default=0, comment="Wallet balance")

    def __repr__(self):
        return 'Student:%s' % self.name

class Teacher(db.Model):
    __tablename__ = 'tb_teacher'
    id     = db.Column(db.Integer, primary_key=True)
    name   = db.Column(db.String(64), unique=True)
    option = db.Column(db.Enum("Lecturer", "TA", "Homeroom"), default="Lecturer")

    def __repr__(self):
        return 'Teacher:%s' % self.name

class Course(db.Model):
    __tablename__ = 'tb_course'
    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(64), unique=True)
    price = db.Column(db.Numeric(6, 2))

    def __repr__(self):
        return 'Course:%s' % self.name

@app.route('/')
def index():
    return "ok"

if __name__ == '__main__':
    with app.app_context():
        # db.drop_all()   # drop all tables
        db.create_all() # create all tables (run once, then remove)
    app.run(debug=True)

CRUD Operations

All write operations go through db.session:

Create

# Insert one record
student1 = Student(name="Alice", sex=True, age=17, email="alice@example.com", money=100)
db.session.add(student1)
db.session.commit()

# Insert multiple records at once
students = [
    Student(name='wang', email='wang@163.com', age=22),
    Student(name='zhang', email='zhang@189.com', age=22),
    Student(name='chen', email='chen@126.com', age=22),
]
db.session.add_all(students)
db.session.commit()

Delete

# Method 1: fetch then delete
student = Student.query.first()
db.session.delete(student)
db.session.commit()

# Method 2: filter then delete (returns count of deleted rows)
Student.query.filter(Student.name == 'sun').delete()
db.session.commit()

Update

# Method 1: fetch, modify, commit
student = Student.query.first()
student.name = 'dong'
db.session.commit()

# Method 2: filter then update (returns count of updated rows)
Student.query.filter(Student.name == 'liu').update({'money': 1000})
db.session.commit()

# Method 3: relative update (like Django's F() expression)
Student.query.filter(Student.age == 22).update({Student.money: Student.money + 200})
db.session.commit()

# Bulk update via mappings
db.session.bulk_update_mappings(Student, [{'id': 1, 'money': 500}, {'id': 2, 'money': 300}])
db.session.commit()

Querying

Common Query Filters

Filter	Description
`filter()`	Add flexible filter conditions (supports operators)
`filter_by()`	Add equality filters using keyword arguments
`limit(n)`	Limit results to n rows
`offset(n)`	Skip the first n rows
`order_by()`	Sort results
`group_by()`	Group results

Common Result Methods

Method	Returns
`all()`	List of all matching objects
`first()`	First matching object, or `None`
`first_or_404()`	First matching object, or 404 error
`get(pk)`	Object with this primary key, or `None`
`get_or_404(pk)`	Object with this primary key, or 404 error
`count()`	Number of matching rows
`paginate()`	A `Pagination` object for paging through results

Basic Query Examples

# Get by primary key
Student.query.get(4)

# Get all rows
Student.query.all()

# Get first row
Student.query.first()

# filter() — flexible conditions
Student.query.filter(Student.name.endswith("g")).all()
Student.query.filter(Student.name.contains("u")).all()
Student.query.filter(Student.name.startswith("w")).all()
Student.query.filter(Student.age == 22).all()
Student.query.filter(Student.age > 18).all()

# filter_by() — equality only, keyword arguments
Student.query.filter_by(name="wang").first()
Student.query.filter_by(age=22).all()

# Alternative using db.session.query()
db.session.query(Student).filter(Student.age == 22).all()

Logical Operators

from sqlalchemy import and_, or_, not_

# NOT
Student.query.filter(Student.name != 'wang').all()
Student.query.filter(not_(Student.name == 'wang')).all()

# AND
Student.query.filter(and_(Student.name != 'wang', Student.email.endswith('163.com'))).all()
# Multiple filter() calls also combine with AND:
Student.query.filter(Student.name.startswith("li"), Student.email.startswith("li")).all()

# OR
Student.query.filter(or_(Student.age == 18, Student.email.endswith("163.com"))).all()

# IN
Student.query.filter(Student.id.in_([1, 3, 5, 7, 9])).all()

Ordering, Limiting, Offsetting

# Sort by age descending
Student.query.order_by(Student.age.desc()).all()

# Multi-column sort
Student.query.order_by(Student.age.desc(), Student.id.desc()).all()

# Top 3 oldest students
Student.query.order_by(Student.age.desc()).limit(3).all()

# 4th through 7th oldest students
Student.query.order_by(Student.age.desc()).offset(3).limit(4).all()

# Count
Student.query.filter(Student.age >= 19, Student.sex == True).count()

Aggregate Functions and GROUP BY

from sqlalchemy import func

# Count students by gender
db.session.query(Student.sex, func.count(Student.id)).group_by(Student.sex).all()

# Count students by age, only ages > 19
db.session.query(Student.age, func.count(Student.id)).group_by(Student.age).having(Student.age > 19).all()

# Youngest student by gender
db.session.query(Student.sex, func.min(Student.age)).group_by(Student.sex).all()

Available aggregate functions: func.count, func.avg, func.min, func.max, func.sum.

Pagination

@app.route("/list")
def list_view():
    # paginate(page=1, per_page=20) — defaults to page 1 from ?page= query param
    pagination = Student.query.paginate(per_page=3)
    return render_template("list.html", pagination=pagination)

Template:

{% for student in pagination.items %}
<tr>
    <td>{{ student.id }}</td>
    <td>{{ student.name }}</td>
    <td>{{ student.age }}</td>
</tr>
{% endfor %}

<div class="page">
    {% if pagination.has_prev %}
        <a href="?page=1">First</a>
        <a href="?page={{ pagination.page - 1 }}">Previous</a>
    {% endif %}
    <span>{{ pagination.page }}</span>
    {% if pagination.has_next %}
        <a href="?page={{ pagination.page + 1 }}">Next</a>
        <a href="?page={{ pagination.pages }}">Last</a>
    {% endif %}
</div>

Raw SQL

# SELECT multiple rows
ret = db.session.execute("SELECT * FROM tb_student").fetchall()
# SELECT one row
ret = db.session.execute("SELECT * FROM tb_student").fetchone()
# UPDATE/INSERT/DELETE
db.session.execute("UPDATE tb_student SET money = money + 200 WHERE age = 22")
db.session.commit()

Model Relationships

Common Relationship Options

Option	Description
`backref`	Adds a reverse reference attribute on the related model
`lazy`	Controls when related data is loaded: `'select'` (load on access), `'subquery'` (load immediately via subquery), `'dynamic'` (return query object)
`uselist`	`False` for one-to-one (returns a scalar instead of a list)
`secondary`	Specifies the association table for many-to-many relationships
`primaryjoin`	Explicitly specifies the join condition between the two models
`secondaryjoin`	Specifies the secondary join condition for many-to-many relationships, when SQLAlchemy cannot determine it automatically

One-to-One

class Student(db.Model):
    __tablename__ = "tb_student"
    id   = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    # Relationship attribute — not stored in the DB table
    # uselist=False → one-to-one (returns a single object, not a list)
    info = db.relationship("StudentInfo", uselist=False, backref="own")

class StudentInfo(db.Model):
    __tablename__ = "tb_student_info"
    id      = db.Column(db.Integer, primary_key=True)
    address = db.Column(db.String(299))
    edu     = db.Column(db.Enum("High School", "Associate", "Bachelor", "Master", "PhD"))
    uid     = db.Column(db.Integer, db.ForeignKey(Student.id), comment="Foreign key")
    # In one-to-one: the FK goes on the subsidiary table
    # In one-to-many: the FK goes on the "many" side

Usage:

# Read from the primary side
student = Student.query.get(3)
print(student.info.address)

# Read from the subsidiary side (via backref)
info = StudentInfo.query.filter_by(address="Beijing").first()
print(info.own.name)

# Create with related data in one go
student = Student(name="liu", sex=True, age=22, email="liu@example.com", money=100)
student.info = StudentInfo(address="Shenzhen", edu="Bachelor")
db.session.add(student)
db.session.commit()

One-to-Many

class Teacher(db.Model):
    __tablename__ = 'tb_teacher'
    id     = db.Column(db.Integer, primary_key=True)
    name   = db.Column(db.String(64), unique=True)
    # Relationship on the "one" side
    course = db.relationship("Course", uselist=True, backref="teacher", lazy='dynamic')

class Course(db.Model):
    __tablename__ = 'tb_course'
    id         = db.Column(db.Integer, primary_key=True)
    name       = db.Column(db.String(64), unique=True)
    price      = db.Column(db.Numeric(6, 2))
    # FK on the "many" side
    teacher_id = db.Column(db.Integer, db.ForeignKey(Teacher.id))

Usage:

# "One" side → access "many"
teacher = Teacher.query.get(1)
for course in teacher.course:
    print(course.name, course.price)

# "Many" side → access "one" (via backref)
course = Course.query.get(1)
print(course.teacher.name)

# Create teacher with courses
teacher = Teacher(name="Ms. Wang", option="Lecturer")
teacher.course = [
    Course(name="Intro to Drawing", price=199.00),
    Course(name="Intro to Illustration", price=129.00),
]
db.session.add(teacher)
db.session.commit()

lazy loading strategies:

'select' (default) — related data is loaded on first access (lazy load, executes SQL only when needed)
'subquery' — loads related data immediately using a subquery
'dynamic' — returns a query object; SQL is only executed when you call .all() or iterate

Many-to-Many

Requires an association table:

# Association table (not a full model)
achievement = db.Table('tb_achievement',
    db.Column('student_id', db.Integer, db.ForeignKey('tb_student.id')),
    db.Column('course_id',  db.Integer, db.ForeignKey('tb_course.id')),
)

class Course(db.Model):
    __tablename__ = 'tb_course'
    id       = db.Column(db.Integer, primary_key=True)
    name     = db.Column(db.String(64), unique=True)
    price    = db.Column(db.Numeric(6, 2))
    students = db.relationship('Student', secondary=achievement, backref='courses', lazy='dynamic')

class Student(db.Model):
    __tablename__ = 'tb_student'
    id   = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    # 'courses' backref is automatically added by the relationship above

Database Migrations

Instead of dropping and recreating tables (which loses data), use Flask-Migrate to track and apply incremental schema changes:

pip install flask-migrate

from flask_migrate import Migrate, MigrateCommand
from flask_script import Manager

app = Flask(__name__)
db = SQLAlchemy(app)
migrate = Migrate(app, db)

manage = Manager(app)
manage.add_command('db', MigrateCommand)  # adds the 'db' command to manage.py

Migration workflow:

# 1. Initialize migrations (run once — creates the migrations/ folder)
python main.py db init

# 2. Generate a migration file based on model changes
python main.py db migrate -m "initial migration"
# Equivalent to Django's makemigrations

# 3. Apply the migration to the database
python main.py db upgrade

# 4. Roll back to the previous version
python main.py db downgrade

# View migration history
python manage.py db history
# Roll back to a specific version
python manage.py db downgrade <version>

Flask-Session (Server-Side Sessions)

Flask’s default session stores data client-side in a signed cookie (limited to ~4 KB). Flask-Session lets you store sessions server-side in Redis, a database, or other backends.

pip install flask-session

Redis-Backed Sessions

import redis

class Config(object):
    SECRET_KEY = "your-secret-key"
    SESSION_TYPE = "redis"
    SESSION_PERMANENT = False             # expire when browser closes
    SESSION_USE_SIGNER = False            # do not encrypt the session cookie value
    SESSION_KEY_PREFIX = "session:"       # Redis key prefix
    SESSION_REDIS = redis.Redis(host='127.0.0.1', port=6379)

from flask_session import Session

app.config.from_object(Config)
Session(app)

@app.route("/set_session")
def set_session():
    session["username"] = "Alice"
    return "ok"

Database-Backed Sessions (SQLAlchemy)

app.config['SESSION_TYPE'] = 'sqlalchemy'
app.config['SESSION_SQLALCHEMY'] = db
app.config['SESSION_SQLALCHEMY_TABLE'] = 'sessions'
app.config['SESSION_PERMANENT'] = True
app.config['SESSION_USE_SIGNER'] = False
app.config['SESSION_KEY_PREFIX'] = 'session:'

Session(app)

Blueprints (Modular Applications)

As a Flask application grows, you want to split views and routes into separate modules. Blueprints are Flask’s mechanism for modularization.

A Blueprint is a container for routes, templates, and static files that can be registered on the main application at a URL prefix.

Creating a Blueprint

Step 1 — Create a package for the blueprint:

# users/__init__.py
from flask import Blueprint

users = Blueprint('users', __name__)

from .views import *   # import all views so routes are registered

Step 2 — Define views inside the blueprint:

# users/views.py
from . import users

@users.route('/')
def home():
    return 'users.home'

Step 3 — Register the blueprint on the main app:

# main.py
from flask import Flask
from users import users

app = Flask(__name__)
app.register_blueprint(users, url_prefix='/users')

Now /users/ routes to the home view function inside the blueprint.

How Blueprint Registration Works

A blueprint stores a set of operations to be applied to the application object later — registering a route is one such operation.

When you call the route decorator directly on the app object, it immediately updates the app’s url_map.
A blueprint object has no url_map of its own. When you call route on a blueprint, it simply appends an entry to an internal list of deferred operations (deferred_functions).
When register_blueprint() runs, the application pulls each entry out of the blueprint’s deferred_functions list and executes it with itself as the argument — effectively calling add_url_rule() on the app. This is what actually updates the app’s url_map.

Blueprint URL Generation

Use the blueprint name as a prefix with url_for:

url_for('users.home')   # generates '/users/'

Blueprint Static Files

By default, blueprints do not register a static file directory. Specify static_folder when creating the blueprint:

user_blu = Blueprint("users", __name__, static_folder='static_users')
# Files in users/static_users/ are served at /users/static_users/<filename>

To customize the URL path for static files:

admin = Blueprint("admin", __name__, static_folder='static_admin', static_url_path='/lib')
app.register_blueprint(admin, url_prefix='/admin')
# Files served at /admin/lib/<filename>

Blueprint Templates

By default, blueprints look for templates in the application’s global templates/ folder. To use a blueprint-local template directory:

admin = Blueprint('admin', __name__, template_folder='templates_admin')

If a template with the same name exists in both the global templates/ and the blueprint’s template_folder, the global one takes precedence. Name your blueprint templates carefully to avoid collisions.

Decoupling Configuration from the App Object

Flask-SQLAlchemy — and Flask extensions in general — don’t require the app object to be passed in at construction time. They support an init_app() method instead, a pattern every well-behaved Flask extension implements: init_app() registers the extension’s default configuration and attaches the extension instance to the app’s extensions dict, without needing app up front.

This makes it possible to create shared objects like db in a standalone config module and import them from anywhere in the project, avoiding circular imports between your models and your app entry point:

# config.py
import redis
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()   # created without an app — bound later via init_app()

class Config(object):
    DEBUG = True
    SECRET_KEY = "your-secret-key"
    SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/flask_students"
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    SQLALCHEMY_ECHO = True

    # Store sessions via SQLAlchemy, reusing the same db object
    SESSION_TYPE = 'sqlalchemy'
    SESSION_SQLALCHEMY = db
    SESSION_SQLALCHEMY_TABLE = 'sessions'
    SESSION_PERMANENT = True
    SESSION_USE_SIGNER = False
    SESSION_KEY_PREFIX = 'session:'

# main.py
from flask import Flask, session
from config import Config, db
from flask_session import Session

app = Flask(__name__, template_folder='templates')
app.config.from_object(Config)

db.init_app(app)   # bind db to this app
Session(app)

@app.route("/set_session")
def set_session():
    session["username"] = "Alice"
    return "ok"

if __name__ == '__main__':
    app.run()

Last updated on 2026-06-23

Flask Jinja2 Templates Flask Project Deployment