Flask-SQLAlchemy and Models

Introduction

Web apps persist data in databases. Flask-SQLAlchemy connects Flask to SQLAlchemy ORM so you define Python models instead of hand-writing SQL for every query. This chapter configures the database URI, defines models and relationships, and performs CRUD with db.session.

Prerequisites

Install and extensions.py

bash
pip install Flask-SQLAlchemy

app/extensions.py:

python
from flask_sqlalchemy import SQLAlchemy
 
db = SQLAlchemy()

app/__init__.py (inside create_app):

python
from app.extensions import db
 
def create_app(config_name="development"):
    app = Flask(__name__)
    app.config.from_object(config_map[config_name])
    db.init_app(app)
    ...
    return app

Code explanation:

  • db.init_app(app) binds SQLAlchemy to the factory-created app—avoids circular imports

Database URI Configuration

app/config.py:

SQLite for local dev; MySQL for production—install driver:

bash
pip install pymysql

Code explanation:

Tip

Use utf8mb4 in Production

Store emoji and full Unicode—set MySQL charset utf8mb4 at database and connection level.

Define Models

app/models.py:

Code explanation:

  • db.Model maps class to table
  • db.ForeignKey links posts.user_id to users.id
  • relationship / back_populates navigate objects in Python

Table design principles overlap with creating tables in MySQL.

Create Tables (Development)

python
from app import create_app
from app.extensions import db
from app import models
 
app = create_app()
with app.app_context():
    db.create_all()

Or Flask shell:

bash
export FLASK_APP=app:create_app
flask shell
python
>>> from app.extensions import db
>>> from app import models
>>> db.create_all()

Warning

create_all Is Not for Production Teams

db.create_all() does not alter existing tables. Production uses Flask-Migrate (next chapter).

Create and Commit

python
from app.extensions import db
from app.models import User, Post
 
user = User(username="alice", email="alice@example.com")
db.session.add(user)
db.session.commit()
 
post = Post(title="Hello", body="First post", author=user)
db.session.add(post)
db.session.commit()

Rollback on error:

python
try:
    db.session.add(user)
    db.session.commit()
except Exception:
    db.session.rollback()
    raise

Read Queries

Get by primary key:

python
user = db.session.get(User, 1)

Filter:

python
user = User.query.filter_by(username="alice").first()
posts = Post.query.filter(Post.user_id == user.id).order_by(Post.created_at.desc()).all()

Pagination:

python
page = request.args.get("page", 1, type=int)
pagination = Post.query.order_by(Post.created_at.desc()).paginate(
    page=page,
    per_page=10,
    error_out=False,
)
posts = pagination.items

Template:

html
{% for post in posts %}
  <article>{{ post.title }}</article>
{% endfor %}
{% if pagination.has_next %}
  <a href="{{ url_for('main.posts', page=pagination.next_num) }}">Next</a>
{% endif %}

Update and Delete

python
user = db.session.get(User, 1)
user.email = "alice.new@example.com"
db.session.commit()
 
post = db.session.get(Post, 5)
db.session.delete(post)
db.session.commit()

View Example

app/main/routes.py:

python
from flask import Blueprint, render_template, abort
from app.extensions import db
from app.models import Post
 
main_bp = Blueprint("main", __name__)
 
@main_bp.route("/posts/<int:post_id>")
def show_post(post_id):
    post = db.session.get(Post, post_id)
    if post is None:
        abort(404)
    return render_template("post.html", post=post)

Import db from app.extensions in route modules.

FAQ

No such table error?

Run db.create_all() inside app context or apply migrations.

User.query deprecated?

Prefer db.session.get(User, id) and db.session.execute(select(User)...) in SQLAlchemy 2 style; Model.query still works in Flask-SQLAlchemy 3.

MySQL connection refused?

Check server running, credentials, firewall—see MySQL troubleshooting.

Relationship lazy loading errors?

Use lazy="joined" or eager load in query when accessing relations outside session.

Unique constraint failed?

Catch IntegrityError and show friendly form error.

SQLite vs MySQL in dev/prod?

Common pattern: SQLite locally, MySQL in staging/production—test migrations on MySQL before release.