🗄️ Flask-SQLAlchemy Extension

Why Flask Has No Built-in Database

Almost every real web application needs to remember things: user accounts, blog posts, orders, tasks. That memory lives in a database. Yet Flask, being a microframework, ships with no database layer at all. That is a deliberate design choice, not an omission — Flask hands you the routing and request handling and lets you choose how to store data.

Flask-SQLAlchemy is the official extension that fills that gap. It wraps SQLAlchemy — the most popular database toolkit in the Python world — and adapts it to Flask's application and request lifecycle so it "just works" with sensible defaults.

💡 A useful analogy: Think of Flask-SQLAlchemy as a translator that sits between two people who don't speak the same language. You speak Python (objects, classes, attributes); the database speaks SQL (tables, rows, columns). The extension translates each request faithfully in both directions so you rarely have to write SQL by hand.

What Is an ORM?

SQLAlchemy is an ORM — an Object-Relational Mapper. It maps Python classes to database tables and class instances to table rows. Instead of writing INSERT INTO users (...), you create a User() object and hand it to the session; the ORM writes the SQL for you.

Why developers reach for an ORM

• Work with Python objects — familiar classes and attributes instead of query strings.
• Database-agnostic — the same model code runs on SQLite in development and PostgreSQL in production.
• SQL-injection protection — the ORM parameterizes every value it sends, so user input can't be smuggled into a query.
• Less boilerplate — common create/read/update/delete operations shrink to a line or two.
• Relationships as attributes — user.posts instead of a hand-written JOIN.

Installing & Configuring

Install Flask-SQLAlchemy alongside the driver for whichever database you plan to use. SQLite needs no driver — it is built into Python — which makes it perfect for learning and local development.

# The extension pulls in SQLAlchemy as a dependency
pip install Flask-SQLAlchemy

# A database driver is only needed for non-SQLite engines:
pip install "psycopg[binary]"   # PostgreSQL (modern psycopg 3)
pip install mysqlclient          # MySQL / MariaDB

Here is the smallest possible setup. Notice the modern pieces: we build the extension around a typed DeclarativeBase, and we no longer need to disable SQLALCHEMY_TRACK_MODIFICATIONS — it already defaults to off in Flask-SQLAlchemy 3.x.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase


# A typed declarative base (SQLAlchemy 2.0 style)
class Base(DeclarativeBase):
    pass


# Create the extension object; bind it to an app below
db = SQLAlchemy(model_class=Base)

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

# Attach the extension to this application
db.init_app(app)

if __name__ == "__main__":
    app.run(debug=True)

The Database URI

The SQLALCHEMY_DATABASE_URI is the one setting you truly must provide. Its shape changes with the engine, but the pattern is always dialect+driver://user:password@host:port/database.

import os

app.config["SQLALCHEMY_DATABASE_URI"] = (
    os.environ.get("DATABASE_URL") or "sqlite:///site.db"
)

The Application Factory Pattern

The tiny example above creates app at module import time. That works for a script, but it causes problems as an app grows: you can't easily build a second app with a test configuration, and modules that import app risk circular imports.

The professional solution is the application factory — a function, conventionally create_app(), that builds and returns a fresh Flask instance. The extension is created once at module level unbound, then attached inside the factory with db.init_app(app).

# app/extensions.py — one place for shared extension objects
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass


db = SQLAlchemy(model_class=Base)

# app/__init__.py — the application factory
from flask import Flask
from .extensions import db
from .config import Config


def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(config_class)

    # Bind the extension to THIS application
    db.init_app(app)

    # Import models so their tables are registered, then create them
    with app.app_context():
        from . import models  # noqa: F401
        db.create_all()

    # from .routes import main
    # app.register_blueprint(main)

    return app

# app/config.py — configuration as classes
import os


class Config:
    SQLALCHEMY_DATABASE_URI = os.environ.get("DATABASE_URL") or "sqlite:///site.db"
    SECRET_KEY = os.environ.get("SECRET_KEY") or "dev-key-change-in-production"


class TestConfig(Config):
    SQLALCHEMY_DATABASE_URI = "sqlite:///:memory:"  # fast, disposable
    TESTING = True

Creating the Tables

Defining a model class doesn't create a table by itself — you have to tell SQLAlchemy to issue the CREATE TABLE statements. The method is db.create_all(), and it must run inside an application context so the extension knows which app (and therefore which database) to talk to.

from app import create_app
from app.extensions import db

app = create_app()

with app.app_context():
    db.create_all()   # issues CREATE TABLE for every registered model
    print("Tables created.")

One more modern touch: because two apps might register the same model, always import your models before calling create_all(), exactly as the factory above does.

Engine, Session & Metadata

Flask-SQLAlchemy hides most of SQLAlchemy's machinery, but three concepts are worth knowing because error messages and advanced features reference them constantly.

You interact with the session constantly (db.session.add(...), db.session.commit()), you configure the engine indirectly through the URI, and the metadata is what db.create_all() reads to know which tables to build.

Hands-on Exercise

from datetime import datetime, timezone
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import String, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


db = SQLAlchemy(model_class=Base)


class Task(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    description: Mapped[str | None] = mapped_column(Text)
    done: Mapped[bool] = mapped_column(default=False)
    created_at: Mapped[datetime] = mapped_column(
        default=lambda: datetime.now(timezone.utc)
    )

    def __repr__(self) -> str:
        return f"Task({self.title!r}, done={self.done})"


def create_app():
    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///tasks.db"
    db.init_app(app)
    with app.app_context():
        db.create_all()
    return app


if __name__ == "__main__":
    create_app().run(debug=True)

Summary & Quiz

📚 Further Reading

• Flask-SQLAlchemy 3.1 Documentation
• SQLAlchemy 2.0 ORM Quickstart
• Miguel Grinberg — Flask Mega-Tutorial: Databases

🚀 What's Next?

You have a database wired up but only one throwaway model. Next we go deep on defining database models — column types, constraints, primary keys, and the relationships that connect your tables together.