Flask SQLAlchemy Basics

Flask-SQLAlchemy wires SQLAlchemy into a Flask app. Define models as classes, run migrations with Flask-Migrate (Alembic), then query through db.session in your views.

Flask-SQLAlchemy integrates SQLAlchemy sessions with Flask app context: models subclass db.Model, queries run in views via Model.query or session.execute(select(...)), commits persist changes. SQLite suits development; Postgres in production with connection pooling.

Migrations via Flask-Migrate (Alembic) version schema—avoid db.create_all() only workflows once teams collaborate.

Production code combines this topic with logging, tests, and clear module boundaries so refactors stay safe when requirements grow.

db.Column types map to SQL; db.relationship links tables with foreign keys.

db.session.add/commit/rollback follow unit-of-work pattern.

Application context required for db access outside request—app.app_context().

Lazy loading relationships can N+1 query—use joinedload in selects.

JSON responses jsonify model dicts built in view or schema layer.

Configuration SQLALCHEMY_DATABASE_URI and track modifications flag.

Practice explaining flask sqlalchemy basics aloud with a concrete example from your current project so the abstraction sticks beyond copy-paste exercises.

Separate models package imported after db init to avoid circular imports. Use UUID primary keys for public APIs; autoincrement ints for internal tools.

Session scope: one commit per request teardown hook vs explicit commit in view—pick one style per app.

Migrate to session.scalars(select(Model)).all() with SQLAlchemy 2.0; legacy Query interface is deprecated in Flask-SQLAlchemy 3.x tutorials.

Prefer session.scalars(select(Model)) in SQLAlchemy 2.0 code paths instead of deprecated Query APIs.

Read the parent tutorial on pythondeck.com for runnable snippets, then reproduce them locally in a virtual environment with pinned dependency versions matching your deployment target.

When pairing with teammates, agree on one idiomatic pattern per concern—mixed styles in one repo slow reviews and invite subtle integration bugs during merges.

Committing inside GET requests causing accidental writes.

Sharing global session across threads without scoped_session.

Storing secrets in database URI in repo—use env vars.

Returning ORM objects directly to jsonify without serialization schema.

Initialize Flask-Migrate early; never edit production DB by hand.

Use select() style 2.0 queries in new code for clarity.

Add indexes on filter columns used in list endpoints.

Test rollback behavior when commit raises IntegrityError.

Re-read the examples below with these ideas in mind; change variable names and inputs to match your own project.

# Example: Model + view
# Run in the REPL or save as a .py file and execute with python.
from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///shop.db"
db = SQLAlchemy(app)

class Product(db.Model):
    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(120), nullable=False)
    price = db.Column(db.Float,   nullable=False)

@app.get("/products")
def list_products():
    rows = Product.query.order_by(Product.name).all()
    return jsonify([{"id": p.id, "name": p.name, "price": p.price} for p in rows])

with app.app_context():
    db.create_all()
    if not Product.query.first():
        db.session.add_all([Product(name="Pen", price=1.5),
                            Product(name="Book", price=10.0)])
        db.session.commit()

# Flask-SQLAlchemy integrates ORM sessions with Flask apps
from flask import Flask, jsonify  # web
from flask_sqlalchemy import SQLAlchemy  # ORM extension
app = Flask(__name__)  # app
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///demo.db"  # file DB
db = SQLAlchemy(app)  # extension

class Item(db.Model):  # table
    id = db.Column(db.Integer, primary_key=True)  # PK
    name = db.Column(db.String(80), nullable=False)  # name column

with app.app_context():  # need context for db ops
    db.create_all()  # create tables
    print(Item.query.count())  # row count

# db.session tracks a transaction; commit persists
from flask import Flask  # flask
from flask_sqlalchemy import SQLAlchemy  # ORM
app = Flask(__name__); app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///demo.db"
db = SQLAlchemy(app)  # db

class Item(db.Model):  # model
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))

with app.app_context():  # context
    db.create_all()  # schema
    if Item.query.count() == 0:  # seed once
        db.session.add(Item(name="Pen"))  # insert row
        db.session.commit()  # persist
    print(Item.query.first().name)  # Pen