Skip to main content

Working with Databases in Flask: A Practical Introduction to SQLAlchemy

So far in this series, your app has accepted input and rendered pages. That is useful, but temporary. Restart the server and your data disappears.

Databases solve that. SQLAlchemy makes it manageable.

You can think of SQLAlchemy as a translation layer between Python objects and relational tables. Instead of manually writing SQL for every operation, you define models and work with them in Python while still benefiting from SQL databases underneath.

This lesson focuses on the mental model you need to avoid common beginner traps later in CRUD work.

Why use an ORM in the first place?​

Could you write raw SQL everywhere? Yes.

Should you for every route in an early Flask app? Usually no.

SQLAlchemy gives you:

  • model classes that express table structure in Python,
  • safer parameterized queries by default,
  • portability across SQLite/PostgreSQL/MySQL,
  • transaction control through a session abstraction.

It does not remove SQL concepts. It gives you a cleaner interface to them.

Base setup: app + database extension​

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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

db = SQLAlchemy(app)

Two details matter immediately:

  1. SQLALCHEMY_DATABASE_URI decides where data lives.
  2. SQLALCHEMY_TRACK_MODIFICATIONS = False avoids extra overhead.

For learning, SQLite is perfect because it has no server process to configure.

Defining your first model​

class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(40), unique=True, nullable=False)
    email = db.Column(db.String(255), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, nullable=False, server_default=db.func.now())

    def __repr__(self):
        return f"<User id={self.id} username={self.username!r}>"

This class maps directly to a table.

  • class name User is your domain object.
  • each db.Column(...) line defines schema and constraints.
  • unique=True and nullable=False encode business rules at database level.

Those constraints are not optional “nice-to-haves.” They protect data integrity when your app grows.

Creating tables (development only)​

You may see this pattern:

with app.app_context():
    db.create_all()

It is fine for local prototypes. In real projects, use migrations (Alembic/Flask-Migrate) so schema changes are explicit and versioned.

Treat create_all() as training wheels, not deployment strategy.

CRUD operations with explicit transaction boundaries​

Let’s build predictable create and list routes:

from flask import request, render_template, redirect, url_for, flash
from sqlalchemy.exc import IntegrityError

@app.route("/users")
def users_list():
    users = User.query.order_by(User.created_at.desc()).all()
    return render_template("users_list.html", users=users)

@app.route("/users/new", methods=["GET", "POST"])
def users_new():
    if request.method == "POST":
        username = request.form.get("username", "").strip()
        email = request.form.get("email", "").strip().lower()

        if not username or not email:
            flash("Username and email are required.", "error")
            return render_template("users_new.html", username=username, email=email), 400

        user = User(username=username, email=email)
        db.session.add(user)
        try:
            db.session.commit()
        except IntegrityError:
            db.session.rollback()
            flash("Username or email already exists.", "error")
            return render_template("users_new.html", username=username, email=email), 409

        flash("User created.", "success")
        return redirect(url_for("users_list"))

    return render_template("users_new.html", username="", email="")

This pattern is robust because it handles:

  • validation before insert,
  • database uniqueness conflicts,
  • rollback on failed commit,
  • redirect after successful POST.

Query patterns you will use constantly​

# all rows
users = User.query.all()

# first matching row
alice = User.query.filter_by(username="alice").first()

# strict lookup by primary key
user = db.session.get(User, 1)

# ordered and filtered
recent = User.query.filter(User.email.like("%@example.com")).order_by(User.id.desc()).all()

Pick query shape intentionally. If “not found” is an error in your flow, treat it as such rather than silently continuing.

Understanding the session without fear​

db.session is your unit-of-work context. You stage operations into it, then commit() to persist.

Lifecycle:

  1. create object
  2. add to session
  3. commit
  4. handle rollback on exceptions

Many confusing bugs vanish when you remember that “add” is not “saved yet.” Commit is the save boundary.

Structure now, save pain later​

A beginner-friendly but scalable structure:

your_app/
  app.py
  models.py
  templates/
    users_new.html
    users_list.html

models.py:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    ...

Then initialize in app.py via db.init_app(app). This makes testing and future refactors easier.

Pitfalls to avoid early​

1) Doing create_all() on every request​

Schema creation is startup/migration concern, not request concern.

2) Ignoring rollback​

After a failed transaction, session state can be invalid until rollback. Always rollback in exception paths around commit.

3) Treating ORM constraints as optional​

Validate in app code, but still enforce constraints in schema (unique, nullable=False).

4) Cramming query logic inside templates​

Keep data access in routes/services, pass plain objects to templates.

Final takeaways​

SQLAlchemy is not magic and not overhead. It is a disciplined way to model data, execute queries, and control transactions without littering your app with raw SQL strings.

Build your models carefully, commit intentionally, rollback on failure, and keep route logic clean. If you do that, the CRUD lesson that follows becomes straightforward rather than fragile.