Setting up Flask-SQLAlchemy

Install

bash

pip install Flask-SQLAlchemy

Minimal setup example

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
 
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///app.db"
 
# recommended: disable noisy signaling
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
 
db = SQLAlchemy(app)

This creates a db object that provides:

• db.Model base class for models
• db.session for transactions
• typed columns like db.Column, db.String, db.Integer

Where should db live in larger apps?

For scalable structure, you’ll often do:

• db = SQLAlchemy() (no app yet)
• later: db.init_app(app) inside an app factory

We’ll go deeper in the architecture phase (Blueprints + factory).

🧪 Try It Yourself

Exercise 1 – Create a Flask App

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Dynamic Route

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Return JSON

pch.exerciseTag pch.exerciseTitle

What the extension adds

SQLAlchemy(app) wires three things together: an engine per app, a scoped session tied to the request, and a Model base your classes inherit from.

diagram Diagram mermaid

  flowchart TD
  E["SQLAlchemy(app, model_class=Base)"] --> EN["engine
one per app, built from the URI"]
  E --> SE["db.session
scoped to the app context"]
  E --> MO["db.Model
the declarative base your models use"]
  EN --> C["connection pool"]
  SE --> T["a transaction per request,
removed when the context ends"]

app.py

import sqlalchemy as sa
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
 
class Base(DeclarativeBase):
    pass
 
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///demo.db"
db = SQLAlchemy(app, model_class=Base)
 
class User(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(sa.String(20), unique=True)
    age: Mapped[int] = mapped_column(default=0)

The table name is derived from the class name, lowercased — User became user, measured. Set __tablename__ explicitly when you want something else, and note that some names (user in PostgreSQL) are reserved words worth avoiding.

Almost everything needs an application context

context.py

db.engine
# RuntimeError: Working outside of application context.

Measured exactly that. The engine is resolved from current_app, so it only exists while a context is active. Inside a view there is always one; in a script, a shell or a test you push it yourself:

script.py

with app.app_context():
    db.create_all()
    db.session.add(User(name="ada"))
    db.session.commit()

flask shell pushes a context for you, which is why the same lines work there without the with.

The session is per-context, not global

db.session is a scoped session: each application context gets its own, and it is removed when the context ends. That is what stops one request’s uncommitted work from leaking into another, and why background threads need their own context.

See it move

sketch What SQLAlchemy(app) gives you p5.js Pause Restart

An engine per app, a session scoped to the application context, and the declarative base your models inherit from.

Check yourself

pch.quizTag pch.quizDefaultTitle

1. Accessing db.engine at module level raises RuntimeError: Working outside of application context. Why?

   A the database file does not exist yet B the engine is resolved from current_app, which only exists while an application context is active C SQLAlchemy must be initialised after the first request D the URI was not configured
   pch.quizShowAnswer

   B — the engine is resolved from current_app, which only exists while an application context is active — Views always run inside a context. In a script or test you push one yourself with app.app_context(); flask shell pushes one for you.

2. A model class is named User and no __tablename__ is set. What is the table called?

   A User B user C users D tbl_user
   pch.quizShowAnswer

   B — user — Measured: the table was created as user, the class name lowercased. Set __tablename__ explicitly when you want a different name — note that user is a reserved word in PostgreSQL.

3. What does it mean that db.session is scoped to the application context?

   A there is one global session shared by every request B each application context gets its own session, removed when the context ends, so requests cannot see each other's uncommitted work C the session is recreated for every query D sessions are shared between threads automatically
   pch.quizShowAnswer

   B — each application context gets its own session, removed when the context ends, so requests cannot see each other's uncommitted work — That isolation is why one request's rollback cannot affect another, and why a background thread needs to push its own context to get a session.