Flask & Deployment
Topic 4 · Web apps with Flask · ⏱ about 4 h

Databases, Users & Structuring an App

Store data with Flask-SQLAlchemy models, create, read, update and delete records safely, change the schema with migrations, add logins with hashed passwords, and organise a growing app with an app factory and blueprints.

Show:
In this topic
Beginner

Why an ORM?

You can use sqlite3 directly with parameterised SQL (see SQL from Python). Most Flask apps instead use SQLAlchemy through Flask-SQLAlchemy: an ORM (object-relational mapper) that maps each table to a Python class and each row to an object. It writes safe, parameterised SQL for you and lets you switch from SQLite on your laptop to PostgreSQL in production by changing one setting.

Beginner

Code: models and CRUD with Flask-SQLAlchemy

This uses the current (version 3) style with type-annotated columns. pip install flask flask-sqlalchemy, then run it with flask --app app run --debug.

Python (app.py)
from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

db = SQLAlchemy(model_class=Base)
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///clinic.db"   # PostgreSQL in production
db.init_app(app)

class Patient(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    suburb: Mapped[str | None]

with app.app_context():
    db.create_all()                       # create tables that do not exist yet

@app.get("/patients")
def list_patients():                      # Read (many)
    rows = db.session.execute(db.select(Patient).order_by(Patient.name)).scalars()
    return [{"id": p.id, "name": p.name, "suburb": p.suburb} for p in rows]

@app.post("/patients")
def create_patient():                     # Create
    data = request.get_json()
    p = Patient(name=data["name"], suburb=data.get("suburb"))
    db.session.add(p)
    db.session.commit()
    return {"id": p.id}, 201

@app.patch("/patients/<int:pid>")
def update_patient(pid):                  # Update
    p = db.get_or_404(Patient, pid)        # 404 if it does not exist
    p.suburb = request.get_json().get("suburb", p.suburb)
    db.session.commit()
    return {"id": p.id, "suburb": p.suburb}

@app.delete("/patients/<int:pid>")
def delete_patient(pid):                  # Delete
    db.session.delete(db.get_or_404(Patient, pid))
    db.session.commit()
    return "", 204
This code runs a web server with Flask, which cannot run inside this page. To run it on your computer: install Python, run pip install flask, save the code as app.py, then run flask --app app run --debug and open http://127.0.0.1:5000. Press Copy to take it with you.
Beginner

Changing the schema: migrations

db.create_all() only creates missing tables; it never changes existing ones. When you add a column to a live app, you need a migration: a small script that alters the database without losing data. Flask-Migrate (built on Alembic) generates them: flask db migrate -m "add phone" writes the script, flask db upgrade applies it. Commit migration scripts to Git so every copy of the app (your laptop, the server) can be brought to the same schema.

Beginner

Users and passwords

  • Never store passwords, only hashes: generate_password_hash(pw) when signing up and check_password_hash(stored, pw) when logging in (both from werkzeug.security, installed with Flask). Hashes are slow on purpose and salted, so a stolen database does not reveal passwords.
  • After login, Flask keeps the user's id in a session: a cookie signed with your SECRET_KEY so it cannot be forged. The Flask-Login extension handles this and gives you @login_required for protected pages.
  • Authentication asks "who are you?"; authorisation asks "are you allowed to do this?". Check authorisation on every request that touches data: a patient may see their own record, not someone else's, even if they guess the URL. Broken access control is the most common serious web vulnerability in the OWASP Top 10.
Beginner

Structuring a bigger app

  • App factory: build the app inside a function, def create_app(config=None): ...; return app. Tests can then create a fresh app with a test database each time.
  • Blueprints group related routes (auth, patients, api) in their own files, registered with app.register_blueprint(...).
  • Configuration from environment variables, not hard-coded: the secret key, the database URL, API keys.
clinic-app/ ├── app/ │ ├── __init__.py # create_app() │ ├── models.py │ ├── auth.py # blueprint │ ├── patients.py # blueprint │ ├── templates/ │ └── static/ ├── migrations/ ├── tests/ ├── requirements.txt └── Dockerfile
Check yourself

Quiz

1. You added a phone column to a model in a live app. What updates the existing database?

2. How should passwords be stored?

3. A logged-in patient changes the URL from /records/12 to /records/13 and sees someone else's record. This is…

Go deeper

📚 Further reading

Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.

Ready