Flask is one of Python's most popular micro web frameworks, and SQLAlchemy is Python's premier database toolkit. Together — via the Flask-SQLAlchemy extension — they form a clean, productive stack for building web applications of any size. Flask-SQLAlchemy ties SQLAlchemy's session lifecycle directly to Flask's request context, so sessions are automatically opened and closed per request with zero manual management.

Why use SQLAlchemy with Flask?

  • Automatic session management per HTTP request
  • Clean model definition using Python classes
  • Built-in support for relationships, migrations, and queries
  • Works with any SQL database — SQLite, PostgreSQL, MySQL, and more
  • Production-tested by thousands of applications worldwide

Advantages of Flask-SQLAlchemy

Flask-SQLAlchemy combines the simplicity of Flask with the power of SQLAlchemy, making it one of the most productive database solutions in the Python ecosystem. The key advantages:

  • Easy setup — Just install flask-sqlalchemy, set the DATABASE_URI, and call db.init_app(app). No complex configuration.
  • Automatic session management — Sessions are tied to the request lifecycle. They open when a request starts and close when it ends, preventing connection leaks.
  • Pythonic model definition — Define tables as Python classes. No raw SQL required for basic operations.
  • Supports all major databases — Works seamlessly with SQLite, PostgreSQL, MySQL, MariaDB, and Oracle by simply changing the connection URL.
  • Full SQLAlchemy power — Access to all of SQLAlchemy's advanced features: relationships, joins, aggregations, and raw SQL when needed.
  • Excellent community & documentation — Widely adopted with thousands of tutorials, Stack Overflow answers, and active maintenance.
  • Works with Alembic / Flask-Migrate — Schema changes are version-controlled and easily deployable to production.

Disadvantages of Flask-SQLAlchemy

Despite its strengths, Flask-SQLAlchemy has limitations worth knowing about before choosing it for a project:

  • Not ideal at very large scale — For extremely high-traffic applications, the ORM abstraction can introduce overhead compared to raw SQL.
  • Learning curve for beginners — Understanding sessions, lazy loading, relationships, and transactions takes time for developers new to ORMs.
  • N+1 query problem — Careless use of relationships can trigger hundreds of queries without the developer realizing it. Requires careful use of joinedload() or selectinload().
  • Tight coupling with Flask — Flask-SQLAlchemy is designed specifically for Flask. Switching to FastAPI or another framework means rewriting the database layer.
  • No built-in async support — For async workloads, you need plain SQLAlchemy 2.0 with an async driver.
  • Migration complexity — Schema changes with Alembic require careful handling in production; autogenerate does not catch all changes (e.g. column type changes on some databases).

app.py — application configuration

What is app.py?

app.py is the main entry point of your Flask application. It is where you create the Flask app instance, configure the database connection, and initialize all extensions like SQLAlchemy. Think of it as the control center — everything starts here.

Why do we use it?

  • App configuration — All settings like the database URL, secret keys, and debug mode live in one place.
  • Extension initialization — SQLAlchemy, Flask-Login, and other extensions are bound to the app here using db.init_app(app).
  • App factory pattern — Using a create_app() function makes it easy to spin up multiple app instances for testing and production without conflicts.
  • Database creation — db.create_all() inside the app context scans all your models and creates the tables if they do not already exist.

Which database do we use?

In this example we use SQLite — a lightweight, file-based database that requires zero configuration. It stores the entire database in a single file (app.db) inside your project folder. SQLite is perfect for development and small projects because you do not need to install a separate database server.

  • SQLite — development. sqlite:///app.db. No server needed, data stored in a local file.
  • PostgreSQL — production, large-scale apps. postgresql://user:pass@localhost/dbname.
  • MySQL — another popular production database. mysql+pymysql://user:pass@localhost/dbname.

Switching databases in SQLAlchemy is as simple as changing the SQLALCHEMY_DATABASE_URI value — your models, queries, and the rest of your code remain exactly the same.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()  # Create extension instance

def create_app():
    app = Flask(__name__)

    # Database configuration
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

    db.init_app(app)  # Bind extension to app

    with app.app_context():
        db.create_all()  # Create tables if they don't exist

    return app

Defining models

What is a model?

A model is a Python class that represents a table in your database. Instead of creating tables by writing SQL manually, you define a Python class and SQLAlchemy automatically creates and manages the corresponding database table for you. Each class = one table. Each attribute (db.Column) = one column.

Why do we use models?

  • No SQL needed — You work with Python objects instead of writing raw SQL. SQLAlchemy translates your Python into SQL automatically.
  • Single source of truth — Your table structure is defined once in the model class. Any change to the class updates the schema through migrations.
  • Validation and constraints — Enforce rules directly on columns: nullable=False, unique=True, default values — all from Python.
  • Easy relationships — Define how tables are connected using db.relationship(), so you can navigate related data through Python attributes.

Purpose of models

The main purpose of a model is to act as a bridge between your Python application and the database. When you create a model object and commit it, SQLAlchemy inserts a row. When you query a model, SQLAlchemy fetches rows and converts them back into Python objects. You never have to think about SQL — just work with Python classes and objects as you normally would.

Models are Python classes that inherit from db.Model. Each class maps to a database table, and each attribute maps to a column. We'll define two model types here: a User model and a Post model.

User model

The User model maps to the users table. Each db.Column() defines a field — id is the auto-incrementing primary key; username and email are required unique fields; created_at auto-saves the registration timestamp; and posts is a virtual relationship attribute that lets you access all posts written by this user via user.posts.

# models.py
from app import db
from datetime import datetime

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

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Relationship to posts
    posts = db.relationship('Post', backref='author', lazy=True)

    def __repr__(self):
        return f'<User {self.username}>'

Post model

The Post model maps to the posts table. id is the primary key, title stores the post heading, body uses db.Text for unlimited-length content, published is a boolean draft/publish flag (defaults to False), and user_id is the foreign key that links each post back to its author in the users table.

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

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    body = db.Column(db.Text, nullable=False)
    published = db.Column(db.Boolean, default=False)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

    def __repr__(self):
        return f'<Post {self.title}>'

CRUD operations

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations for managing data in any database. In Flask-SQLAlchemy, these operations are performed using the db.session object combined with your model classes.

Create — adding records

The Create operation adds a new row to a table. You create a new instance of your model class, add it to the session, and commit. The session acts as a staging area — nothing is written to the database until you call db.session.commit().

Key steps: (1) instantiate the model, (2) add to the session with db.session.add(), (3) commit with db.session.commit(). After commit, the object gets its auto-generated id from the database.

from app import db
from models import User

new_user = User(username='alice', email='alice@example.com')
db.session.add(new_user)
db.session.commit()
print(new_user.id)  # ID is populated after commit

Read — querying records

The Read operation retrieves data. Flask-SQLAlchemy provides the .query property on every model, which gives you a powerful query builder. You can fetch all records, filter by conditions, sort results, paginate, and perform complex joins — all without writing raw SQL.

# Get all users
users = User.query.all()

# Get by primary key
user = User.query.get(1)

# Filter by field
user = User.query.filter_by(username='alice').first()

# Advanced filter with ordering
users = (User.query
            .filter(User.email.like('%@gmail.com'))
            .order_by(User.created_at.desc())
            .limit(10)
            .all())

Update — modifying records

The Update operation modifies existing records. SQLAlchemy uses an identity map — once you fetch an object, it is tracked by the session. Change the object's attributes and call db.session.commit(). SQLAlchemy automatically detects what changed and generates the appropriate SQL UPDATE statement.

user = User.query.get(1)
user.email = 'new_email@example.com'
db.session.commit()  # SQLAlchemy auto-detects the change

Delete — removing records

The Delete operation removes a record permanently. First fetch the object, then pass it to db.session.delete(), and commit. SQLAlchemy handles the DELETE statement and also manages any cascading deletes on related records if cascade='all, delete-orphan' is configured on the relationship.

user = User.query.get(1)
db.session.delete(user)
db.session.commit()

Flask routes with SQLAlchemy

A complete example of REST-style routes using Flask and SQLAlchemy together:

# routes.py
from flask import Blueprint, jsonify, request, abort
from app import db
from models import User

users_bp = Blueprint('users', __name__, url_prefix='/users')

@users_bp.route('/', methods=['GET'])
def list_users():
    users = User.query.order_by(User.username).all()
    return jsonify([{'id': u.id, 'username': u.username} for u in users])

@users_bp.route('/<int:user_id>', methods=['GET'])
def get_user(user_id):
    user = User.query.get_or_404(user_id)
    return jsonify({'id': user.id, 'username': user.username, 'email': user.email})

@users_bp.route('/', methods=['POST'])
def create_user():
    data = request.get_json()
    user = User(username=data['username'], email=data['email'])
    db.session.add(user)
    db.session.commit()
    return jsonify({'id': user.id}), 201

@users_bp.route('/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
    user = User.query.get_or_404(user_id)
    db.session.delete(user)
    db.session.commit()
    return '', 204

Working with relationships

What is a relationship?

A relationship in SQLAlchemy is a way to connect two database tables so that you can access related data through Python objects directly. In a database, tables are linked using foreign keys — a column in one table that points to the primary key of another. SQLAlchemy's relationship() function sits on top of this and lets you navigate those links as simple Python attributes, without writing any SQL JOIN yourself.

Instead of writing SELECT * FROM posts WHERE user_id = 1, you simply write user.posts — SQLAlchemy handles the rest.

Why do we use relationships?

  • Cleaner code — No need to write SQL JOIN queries manually. SQLAlchemy generates them automatically.
  • Easy navigation — Access related data like a Python attribute: user.posts, post.author, student.courses.
  • Less repetition — Define the connection once in the model and use it everywhere.
  • Data integrity — Foreign keys ensure that a Post cannot exist without a valid User.
  • Cascade operations — When a User is deleted, all their Posts can be automatically deleted too using cascade settings.

What problem does it solve?

Without relationships, fetching related data requires writing raw SQL every time. Imagine a users table and a posts table — to get all posts of a user you'd manually write a JOIN, pass the user id, loop through results, and map them to objects. Repetitive, error-prone, and hard to maintain.

SQLAlchemy relationships solve this by letting you define the connection once at the model level. After that, user.posts just works — SQLAlchemy runs the correct query, maps the results to Post objects, and returns them as a Python list.

When do we use relationships?

  • One-to-Many — One record owns many others. Example: one User has many Posts. One Category has many Products.
  • Many-to-Many — Records on both sides can relate to multiple records on the other side. Example: Students and Courses — one student joins many courses, one course has many students.
  • One-to-One — Each record on one side maps to exactly one record on the other. Example: one User has one Profile.

Flask-SQLAlchemy supports three relationship types: One-to-Many, Many-to-Many, and One-to-One. Each maps to a real-world scenario in your application.

1. One-to-Many relationship

A One-to-Many relationship means one record in Table A can be related to many records in Table B. This is the most common relationship type.

Real-world example: one User can write many Posts. One Category can have many Products.

Model definition

The parent model (User) defines the relationship using db.relationship(). The child model (Post) defines the foreign key using db.ForeignKey().

from app import db

class User(db.Model):
    __tablename__ = 'users'
    id         = db.Column(db.Integer, primary_key=True)
    username   = db.Column(db.String(80), unique=True, nullable=False)
    email      = db.Column(db.String(120), nullable=False)

    # One user has MANY posts
    posts = db.relationship('Post', backref='author', lazy=True)

class Post(db.Model):
    __tablename__ = 'posts'
    id      = db.Column(db.Integer, primary_key=True)
    title   = db.Column(db.String(200), nullable=False)
    body    = db.Column(db.Text, nullable=False)

    # Foreign key links Post back to User
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

How it works: db.relationship('Post', backref='author', lazy=True) does two things. First, it adds a posts attribute on the User object so you can do user.posts to get all posts. Second, backref='author' automatically adds an author attribute on every Post object so you can do post.author to get the user who wrote it.

Creating related records

# Step 1: Create a user
user = User(username='alice', email='alice@example.com')
db.session.add(user)
db.session.commit()

# Step 2: Create posts linked to that user
post1 = Post(title='My First Post', body='Hello world!', user_id=user.id)
post2 = Post(title='Flask is Great', body='Here is why...', user_id=user.id)
db.session.add_all([post1, post2])
db.session.commit()

Accessing related records

# Get all posts written by a user
user = User.query.filter_by(username='alice').first()
for post in user.posts:
    print(post.title)
# Output:
# My First Post
# Flask is Great

# Get the author of a post (using backref)
post = Post.query.get(1)
print(post.author.username)   # Output: alice
print(post.author.email)      # Output: alice@example.com

2. Many-to-Many relationship

A Many-to-Many relationship means records in Table A can relate to multiple records in Table B, and vice versa. This requires an intermediate association table to store the connections.

Real-world example: a Student can enroll in many Courses. A Course can have many Students.

Model definition with association table

SQLAlchemy handles the association table automatically. You define it as a db.Table object (not a model class) and reference it in db.relationship().

# Association table (no model class needed)
enrollments = db.Table('enrollments',
    db.Column('student_id', db.Integer, db.ForeignKey('students.id')),
    db.Column('course_id',  db.Integer, db.ForeignKey('courses.id'))
)

class Student(db.Model):
    __tablename__ = 'students'
    id   = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)

    # Many students enrolled in many courses
    courses = db.relationship('Course', secondary=enrollments, backref='students')

class Course(db.Model):
    __tablename__ = 'courses'
    id    = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)

Creating and accessing many-to-many records

# Create students and courses
alice  = Student(name='Alice')
bob    = Student(name='Bob')
python = Course(title='Python 101')
flask  = Course(title='Flask Advanced')
db.session.add_all([alice, bob, python, flask])
db.session.commit()

# Enroll students in courses
alice.courses.append(python)   # Alice enrolled in Python 101
alice.courses.append(flask)    # Alice enrolled in Flask Advanced
bob.courses.append(python)     # Bob enrolled in Python 101
db.session.commit()

# Which courses is Alice enrolled in?
for course in alice.courses:
    print(course.title)
# Output: Python 101 / Flask Advanced

# Which students are in Python 101? (backref)
for student in python.students:
    print(student.name)
# Output: Alice / Bob

Tip: To remove a relationship, use alice.courses.remove(python) and commit. SQLAlchemy deletes the row from the association table automatically.

3. One-to-One relationship

A One-to-One relationship means each record in Table A relates to exactly one record in Table B. It is a special case of One-to-Many where uselist=False is set.

Real-world example: one User has exactly one Profile. One Order has exactly one Invoice.

Model definition

class User(db.Model):
    __tablename__ = 'users'
    id       = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False)

    # uselist=False makes it One-to-One instead of One-to-Many
    profile = db.relationship('Profile', backref='user', uselist=False)

class Profile(db.Model):
    __tablename__ = 'profiles'
    id      = db.Column(db.Integer, primary_key=True)
    bio     = db.Column(db.Text)
    avatar  = db.Column(db.String(200))
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), unique=True)

Creating and accessing one-to-one records

# Create user with a profile
user = User(username='alice')
db.session.add(user)
db.session.commit()

profile = Profile(bio='Python developer', avatar='alice.jpg', user_id=user.id)
db.session.add(profile)
db.session.commit()

# Access profile from user (returns a single object, not a list)
print(user.profile.bio)        # Output: Python developer

# Access user from profile (using backref)
print(profile.user.username)   # Output: alice

4. Cascade delete

By default, if you delete a parent record and it has child records, SQLAlchemy will raise an error or leave orphaned rows. Cascade delete tells SQLAlchemy to automatically delete all related child records when the parent is deleted.

class User(db.Model):
    __tablename__ = 'users'
    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(80))

    # cascade='all, delete-orphan' auto-deletes posts when user is deleted
    posts = db.relationship('Post', backref='author',
                            cascade='all, delete-orphan', lazy=True)

# Now deleting a user also deletes all their posts
user = User.query.get(1)
db.session.delete(user)   # All user.posts are deleted automatically
db.session.commit()

5. Lazy loading options

The lazy parameter controls when SQLAlchemy fetches related records from the database. Choosing the right option is important for performance.

# lazy=True (default) — loads related records only when accessed
posts = db.relationship('Post', backref='author', lazy=True)

# lazy='joined' — loads everything in one SQL JOIN query (eager loading)
posts = db.relationship('Post', backref='author', lazy='joined')

# lazy='dynamic' — returns a query object, good for large collections
posts = db.relationship('Post', backref='author', lazy='dynamic')

# With lazy='dynamic' you can filter before loading
published = user.posts.filter_by(published=True).all()

Tip: Use lazy='joined' when you always need related data (avoids N+1 queries). Use lazy='dynamic' when the collection can be very large and you want to filter before loading.

Conclusion

SQLAlchemy and Flask together offer a clean, powerful, and flexible foundation for Python web development. Flask-SQLAlchemy removes the boilerplate of session management while giving you full access to SQLAlchemy's querying capabilities.

Whether you're building a small personal project or a large production API, this combination scales with your needs and integrates cleanly with the rest of the Python ecosystem.

Frequently asked questions

What is Flask-SQLAlchemy?

Flask-SQLAlchemy is the extension that integrates the SQLAlchemy database toolkit with Flask. It ties SQLAlchemy's session lifecycle to Flask's request context, so sessions open and close automatically per request with no manual management.

Why use SQLAlchemy with Flask?

It provides automatic per-request session management, clean model definitions using Python classes instead of raw SQL, built-in support for relationships and queries, and works with any SQL database — SQLite, PostgreSQL, MySQL, and more — by changing only the connection URL.

How do you define a model in Flask-SQLAlchemy?

A model is a Python class that inherits from db.Model. Each class maps to one database table and each attribute defined with db.Column maps to one column. SQLAlchemy creates and manages the corresponding table for you, so you work with Python objects instead of SQL.

What are the CRUD operations in Flask-SQLAlchemy?

CRUD stands for Create, Read, Update, and Delete. In Flask-SQLAlchemy you perform them with the db.session object and your model classes: add and commit to create, use the .query builder to read, change attributes and commit to update, and db.session.delete then commit to remove records.

What relationship types does SQLAlchemy support?

SQLAlchemy supports one-to-many (one user has many posts), many-to-many (students and courses, via an association table), and one-to-one (one user has one profile, using uselist=False). Relationships let you navigate linked data as Python attributes instead of writing SQL JOINs.

What is the N+1 query problem and how does lazy loading help?

The N+1 problem happens when careless use of relationships triggers a separate query for each related record, causing hundreds of queries. The lazy parameter controls loading: lazy='joined' fetches related data in one JOIN (avoiding N+1), while lazy='dynamic' returns a query you can filter before loading large collections.