Flask-SQLAlchemy

Database models, queries, and relationships with Flask-SQLAlchemy

Knowledge0%

Learn & Drill

Fluency0%

Drill & Speed

Retention0%

Mastery & Review

Confidence0%

All modes

Practice

Free tier: read the explanation here. Upgrade to Pro for Drills, Speed challenges & Mastery badges.

Upgrade

Knowledge

Fluency

Retention

Knowledge Debt detected

You can study this freely — but your score may plateau if these foundations have gaps. The Mastery badge requires them to be solid.

Removing Duplicates with DISTINCT0%

SQL JOINs0%

Blueprints0%

Explanation

Flask-SQLAlchemy integrates SQLAlchemy's into Flask, giving you Python classes that to database tables.

Setup:

python from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db" db = SQLAlchemy(app)

Defining models:

python from datetime import datetime class User(db.Model): __tablename__ = "users" id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(255), unique=True, nullable=False) name = db.Column(db.String(100), nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow) active = db.Column(db.Boolean, default=True) posts = db.relationship("Post", back_populates="author", lazy="dynamic") def to_dict(self): return {"id": self.id, "email": self.email, "name": self.name}

CRUD operations:

python # Create user = User(email="alice@example.com", name="Alice") db.session.add(user) db.session.commit() # Read user = User.query.get(42) # by primary key users = User.query.filter_by(active=True).all() user = User.query.filter(User.email == "alice@example.com").first() # Update user.name = "Alice Smith" db.session.commit() # Delete db.session.delete(user) db.session.commit()

Query patterns:

python # Pagination page = User.query.paginate(page=1, per_page=10) users = page.items total = page.total # Ordering, filtering users = (User.query .filter(User.active == True) .order_by(User.created_at.desc()) .limit(10) .all())

Examples

One-to-many relationship

back_populates creates the reverse relationship — access books from author and author from book

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Author(db.Model):
    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(100), nullable=False)
    books = db.relationship("Book", back_populates="author", lazy="dynamic")

class Book(db.Model):
    id        = db.Column(db.Integer, primary_key=True)
    title     = db.Column(db.String(200), nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey("author.id"), nullable=False)
    author    = db.relationship("Author", back_populates="books")

# Usage
author = Author(name="George Orwell")
book   = Book(title="1984", author=author)
db.session.add_all([author, book])
db.session.commit()

orwell_books = author.books.all()

REST endpoint with SQLAlchemy

get_or_404 returns the object or raises a 404 automatically — clean and concise

from flask import Blueprint, jsonify, request
from .models import db, User

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

@users_bp.route("/")
def list_users():
    page  = request.args.get("page", 1, type=int)
    paged = User.query.filter_by(active=True).paginate(page=page, per_page=20)
    return jsonify({
        "data":  [u.to_dict() for u in paged.items],
        "total": paged.total,
        "pages": paged.pages,
    })

@users_bp.route("/<int:user_id>")
def get_user(user_id):
    user = User.query.get_or_404(user_id)
    return jsonify(user.to_dict())

How well did you understand this?

Next in Flask

JWT Authentication

Continue

Unlocks

JWT Authentication