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.
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.
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.
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 "", 204Changing 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.
Users and passwords
- Never store passwords, only hashes:
generate_password_hash(pw)when signing up andcheck_password_hash(stored, pw)when logging in (both fromwerkzeug.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_KEYso it cannot be forged. The Flask-Login extension handles this and gives you@login_requiredfor 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.
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 withapp.register_blueprint(...). - Configuration from environment variables, not hard-coded: the secret key, the database URL, API keys.
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…
📚 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.
- Flask-SQLAlchemy documentation ↗ models, queries and sessions
- Flask tutorial: the Flaskr blog app ↗ the official step-by-step tutorial with an app factory, blueprints and tests