Database-Driven Flask Applications

Introduction

Most useful web applications do more than display fixed pages. They store users, products, bookings, payments, messages, or other information; retrieve that information efficiently; and preserve it when the application restarts. A database-driven Flask application combines three major components:

1. The presentation layer, usually HTML templates and forms.
2. The application layer, implemented through Flask routes, validation, and business rules.
3. The data layer, consisting of a relational database and code that communicates with it.

This chapter introduces relational modelling, SQL, object-relational mapping (ORM), CRUD operations, relationships, transactions, migrations, data integrity, and query efficiency. Examples use Flask with Flask-SQLAlchemy, although the underlying ideas apply to other Python frameworks and database libraries.

Learning Objectives

By the end of this chapter, you should be able to:

• design tables, keys, constraints, and relationships for a Flask application;
• distinguish raw SQL from ORM-based data access;
• implement create, read, update, and delete operations;
• query one-to-many and many-to-many relationships;
• use transactions safely and handle failures;
• manage schema changes with migrations;
• improve integrity, security, and query performance;
• evaluate practical trade-offs in a database-backed application.

1. Relational Data Modelling

A relational database stores data in relations, commonly represented as tables. A table contains rows, called records or tuples, and columns, called attributes. Each column has a defined data type and meaning.

For example, an online course system may contain:

A primary key uniquely identifies each row. A foreign key refers to a key in another table. In the example, enrolments.user_id refers to users.id, while enrolments.course_id refers to courses.id.

A relationship may be:

• One-to-one: one user has one profile.
• One-to-many: one instructor offers many courses.
• Many-to-many: many users enrol in many courses. This requires an associative table such as enrolments.

Normalisation

Normalisation reduces duplication and update anomalies. In a simplified design:

• First normal form requires atomic values and no repeating groups.
• Second normal form requires first normal form and removal of partial dependency on part of a composite key.
• Third normal form requires second normal form and removal of dependencies on non-key attributes through another non-key attribute.

Normalisation is a design guide rather than an absolute rule. Highly normalised data improves consistency, while carefully controlled denormalisation can improve read performance. Denormalisation should be justified by measured access requirements, not convenience.

Constraints and Integrity

Constraints make invalid states difficult or impossible to store:

• PRIMARY KEY identifies a row.
• NOT NULL requires a value.
• UNIQUE prevents duplicates.
• CHECK restricts values according to a condition.
• FOREIGN KEY protects references between tables.

For example, an email address may be required and unique, while a course fee may be checked to ensure it is non-negative. Application validation improves user feedback, but database constraints remain essential because data can also enter through scripts, administrative tools, or other services.

2. SQL and the ORM Approach

SQL is a declarative language for defining and manipulating relational data. Typical commands include:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

SELECT id, name FROM users WHERE email = '[email protected]';

The SQL statement describes what data is required; the database engine determines how to retrieve it. SQL provides precise control and is indispensable for complex reporting, database administration, and performance analysis.

An ORM maps database tables to Python classes and rows to objects. Flask-SQLAlchemy uses SQLAlchemy for this purpose. A model might be written as follows:

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String

class Base(DeclarativeBase):
    pass

db = SQLAlchemy(model_class=Base)

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

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    email: Mapped[str] = mapped_column(String(255), unique=True,
                                        nullable=False, index=True)

The ORM improves maintainability by placing data structure near Python code and by parameterising values safely. However, it does not eliminate the need to understand SQL. Poor ORM code can generate inefficient queries, hide transactions, or retrieve more data than required.

Parameterised Queries and Injection Prevention

Never construct SQL by concatenating user input:

# Unsafe
query = "SELECT * FROM users WHERE email = '" + email + "'"

Use parameters or ORM expressions:

from sqlalchemy import text

result = db.session.execute(
    text("SELECT id, name FROM users WHERE email = :email"),
    {"email": email}
)

Parameter binding treats input as data rather than executable SQL. It is a core security practice, but it does not replace authorisation, validation, or secure session management.

3. Flask Application Configuration

A production application should separate configuration from source code and use an application factory.

import os
from flask import Flask
from flask_migrate import Migrate
from models import db

def create_app():
    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = os.environ["DATABASE_URL"]
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    db.init_app(app)
    Migrate(app, db)

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

The database URL identifies the database driver and location. SQLite is convenient for teaching and small local applications; PostgreSQL or another server database is generally more appropriate for concurrent production workloads. Credentials should be supplied through protected environment configuration or a secrets manager, not committed to source control.

4. CRUD Operations

CRUD means create, read, update, and delete. A route should coordinate HTTP input, validation, business rules, persistence, and a response.

Create

from flask import Blueprint, request, redirect, url_for, render_template
from models import db, User

main = Blueprint("main", __name__)

@main.post("/users")
def create_user():
    name = request.form["name"].strip()
    email = request.form["email"].strip().lower()

    if not name or not email:
        return render_template("user_form.html", error="Both fields are required"), 400

    user = User(name=name, email=email)
    db.session.add(user)
    try:
        db.session.commit()
    except Exception:
        db.session.rollback()
        return render_template("user_form.html", error="Email may already exist"), 409

    return redirect(url_for("main.user_detail", user_id=user.id))

A commit makes the transaction durable according to the database’s configuration. If a commit fails, rollback returns the session to a usable state. Production code should catch specific integrity exceptions where possible and log unexpected failures.

Read

from flask import abort

@main.get("/users/<int:user_id>")
def user_detail(user_id):
    user = db.session.get(User, user_id)
    if user is None:
        abort(404)
    return render_template("user_detail.html", user=user)

A list operation should normally be paginated:

@main.get("/users")
def users():
    page = request.args.get("page", 1, type=int)
    page_size = 20
    result = db.paginate(
        db.select(User).order_by(User.name),
        page=page, per_page=page_size, error_out=False
    )
    return render_template("users.html", result=result)

Pagination prevents a large table from being loaded into memory or sent in one response.

Update

@main.post("/users/<int:user_id>/edit")
def update_user(user_id):
    user = db.session.get(User, user_id)
    if user is None:
        abort(404)

    user.name = request.form["name"].strip()
    db.session.commit()
    return redirect(url_for("main.user_detail", user_id=user.id))

The route should validate length, format, permissions, and any domain rules before committing.

Delete

@main.post("/users/<int:user_id>/delete")
def delete_user(user_id):
    user = db.session.get(User, user_id)
    if user is None:
        abort(404)

    db.session.delete(user)
    db.session.commit()
    return redirect(url_for("main.users"))

Deletion requires careful policy decisions. Should related enrolments be deleted, retained, or anonymised? Foreign-key actions such as CASCADE, RESTRICT, and SET NULL must reflect business requirements rather than being selected automatically.

5. Relationships in SQLAlchemy

Consider users and courses connected by enrolments:

from datetime import datetime
from sqlalchemy import ForeignKey, DateTime
from sqlalchemy.orm import relationship

class Enrolment(db.Model):
    __tablename__ = "enrolments"

    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), nullable=False)
    course_id: Mapped[int] = mapped_column(ForeignKey("courses.id"), nullable=False)
    enrolled_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)

    user: Mapped["User"] = relationship(back_populates="enrolments")
    course: Mapped["Course"] = relationship(back_populates="enrolments")

class User(db.Model):
    # id, name and email omitted here
    enrolments: Mapped[list[Enrolment]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )

class Course(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(150), nullable=False)
    enrolments: Mapped[list[Enrolment]] = relationship(back_populates="course")

The Enrolment model is an association object because the relationship has its own attribute, enrolled_at. A simple many-to-many association table is sufficient when no additional relationship data is needed.

Queries can use joins:

from sqlalchemy import select

stmt = (
    select(User)
    .join(User.enrolments)
    .where(Enrolment.course_id == course_id)
    .order_by(User.name)
)
users = db.session.scalars(stmt).all()

The N+1 Query Problem

Suppose an application retrieves 100 courses and then accesses each course’s instructor separately. The initial query plus one query per course creates 101 queries. This is the N+1 problem.

Eager-loading strategies reduce unnecessary round trips:

from sqlalchemy.orm import selectinload

stmt = select(User).options(selectinload(User.enrolments))
users = db.session.scalars(stmt).all()

selectinload commonly uses an additional query with an IN condition. joinedload can use a join, but joins may duplicate parent rows and create large result sets. The correct strategy depends on cardinality and measured performance.

6. Transactions and Concurrency

A transaction is a logical unit of work. It should satisfy the ACID properties:

• Atomicity: all operations succeed or none are applied.
• Consistency: constraints remain satisfied.
• Isolation: concurrent transactions do not observe unacceptable intermediate states.
• Durability: committed changes survive failures according to database guarantees.

For example, transferring a balance requires both a debit and a credit in one transaction. If only the debit succeeds, the data is incorrect.

from sqlalchemy import update

def transfer(source_id, destination_id, amount):
    if amount <= 0:
        raise ValueError("Amount must be positive")

    with db.session.begin():
        source = db.session.get(Account, source_id)
        destination = db.session.get(Account, destination_id)
        if source is None or destination is None:
            raise ValueError("Account not found")
        if source.balance < amount:
            raise ValueError("Insufficient funds")
        source.balance -= amount
        destination.balance += amount

The context commits when it exits normally and rolls back if an exception escapes. In a concurrent system, two requests can still race. Atomic database updates, appropriate isolation, row locking where supported, and optimistic version checks may be required. A web request should keep a transaction short and should not hold locks while waiting for an external API.

7. Migrations and Schema Evolution

A model definition is not a complete database-change strategy. Existing databases need controlled schema evolution. A migration records a change such as adding a column, creating an index, or splitting a table.

Typical Flask-Migrate commands are:

flask db init
flask db migrate -m "add course fee"
flask db upgrade

A generated migration must be reviewed. Automatic detection may miss data transformations, renamed columns, or changes requiring a staged rollout. A safe production sequence may be:

1. Add a nullable column.
2. Deploy application code that writes both old and new representations.
3. Backfill existing rows in batches.
4. Validate the backfill.
5. Make the new column non-nullable and remove the old representation later.

Migrations should be version-controlled, tested against a copy of realistic data, and applied consistently across environments. db.create_all() is useful for a disposable exercise but does not replace migrations in a maintained system.

8. Query Efficiency and Reliability

Efficiency is influenced by schema design, indexes, query shape, data volume, and hardware. An index accelerates searches on selected columns but consumes storage and slows writes. Good index candidates include columns frequently used in filtering, joining, or ordering, especially foreign keys and unique identifiers.

However, an index is not automatically beneficial. A low-selectivity column, such as a boolean with almost equal true and false values, may provide little advantage. Use the database query planner and execution plans to evaluate actual behaviour.

Practical measures include:

• select only required columns for large results;
• paginate user-facing lists;
• use stable ordering when paginating;
• avoid loading large binary objects unnecessarily;
• batch inserts and updates where appropriate;
• inspect generated SQL and query plans;
• monitor latency, error rates, lock waits, and connection-pool use;
• test with realistic data volumes.

A database connection pool reuses connections, reducing setup overhead. Pool size must match the database’s capacity and the deployment’s number of worker processes. Excessive connections can make a system less reliable rather than faster.

9. Data Validation, Security, and Governance

Validation should occur at several levels:

• HTML and client-side validation improves usability.
• Flask-side validation provides immediate application feedback.
• Database constraints provide durable protection.

Authentication answers “Who is the user?” Authorisation answers “What may this user do?” A route that retrieves a record by ID must also check that the current user is permitted to view or modify it. This prevents insecure direct object references.

Other protections include:

• CSRF protection for state-changing browser forms;
• secure password hashing, never plaintext storage;
• least-privilege database accounts;
• encrypted connections where appropriate;
• careful error messages that do not reveal schema or credentials;
• audit records for sensitive actions;
• backups tested through restoration, not merely created;
• retention and deletion policies consistent with applicable law.

Personal data should be collected only for legitimate purposes, accessed by authorised personnel, and protected throughout its lifecycle. Logs should not accidentally contain passwords, tokens, or unnecessary personal information.

10. Illustrative Case: Course Enrolment Portal

Background

A training institute operates a Flask portal in which learners browse courses and enrol online. An early prototype stores enrolment data in a CSV file.

Problem

The CSV design permits duplicate enrolments, cannot reliably support concurrent requests, and makes it difficult to list all learners in a course. A failed payment can also leave an enrolment recorded without a corresponding payment status.

Analysis

The domain contains users, courses, enrolments, and payments. A learner may enrol in many courses, and a course may contain many learners, so enrolment is an associative entity. The enrolment needs a unique constraint on (user_id, course_id). Payment and enrolment changes may need one transaction when the business policy requires them to succeed together.

Approach

The team creates normalised tables with foreign keys and timestamps. Flask-SQLAlchemy models represent the entities; Flask-Migrate manages schema changes. The enrolment endpoint validates that the course exists, checks capacity, verifies that the learner is authorised, and inserts an enrolment. The unique constraint protects against duplicate submissions. A transaction encloses capacity checking and insertion, with a suitable concurrency strategy for the selected database.

The course list is paginated. A query uses eager loading for the instructor and a count query for enrolments rather than retrieving every learner object. An index on enrolments.course_id supports course-based searches, while the composite unique constraint supports duplicate prevention.

Outcome

The portal can recover from partial failures, report meaningful validation errors, and support larger datasets. Administrators can audit enrolments, and migrations provide a reproducible path from the development schema to production.

Learning Points

The central lesson is that database work is not merely object storage. A sound design combines domain modelling, constraints, transactions, security, and measured performance. Application code and database rules reinforce each other.

11. Contemporary Practice and Limitations

Modern systems may combine relational databases with caches, queues, search engines, or analytical stores. These tools can improve responsiveness or specialised search, but they introduce consistency and operational complexity. A relational database remains an excellent source of truth for strongly structured transactional data.

AI-assisted coding tools can generate models, queries, and migrations, but generated code must be reviewed for injection risks, incorrect relationships, missing constraints, privacy problems, and inefficient queries. A developer remains responsible for testing and deployment decisions. AI should assist reasoning, not replace database design or verification.

ORM abstraction also has limits. Complex aggregations, window functions, vendor-specific features, and performance-critical paths may be clearer in carefully parameterised SQL. Conversely, raw SQL can become difficult to maintain if repeated throughout route functions. A professional solution selects the simplest abstraction that preserves correctness and observability.

Best-Practice Checklist

Before releasing a database-driven Flask feature, verify that:

• the schema reflects the domain and avoids unjustified duplication;
• primary keys, foreign keys, unique constraints, and nullability are explicit;
• user input is validated and parameterised;
• authorisation is checked for every protected object;
• transactions cover related changes;
• failures trigger rollback and useful logging;
• migrations are reviewed and tested;
• lists are paginated and queries are inspected;
• indexes are supported by measured access patterns;
• backups, privacy, retention, and recovery procedures are documented.

Chapter Summary

A database-driven Flask application connects HTTP routes and templates to persistent relational data. Effective development begins with a model of entities, keys, relationships, and constraints. SQL gives direct control over relational operations, while an ORM maps tables and rows to Python objects; both require an understanding of query behaviour. CRUD operations must include validation, authorisation, error handling, and transaction management. Relationships should be represented explicitly, and eager loading or carefully designed queries should prevent N+1 performance problems. Migrations provide controlled schema evolution, while indexes, pagination, query plans, and monitoring support efficiency. Finally, integrity, security, privacy, backups, and responsible use of AI are part of professional database engineering rather than optional additions.

Key Terms

Relational database: A database that represents data through related tables.

Table: A structured collection of rows and columns.

Primary key: A column or combination of columns that uniquely identifies a row.

Foreign key: A column that references a key in another table.

Constraint: A database rule that restricts invalid data.

Normalisation: The organisation of data to reduce redundancy and anomalies.

SQL: A language for defining, querying, and modifying relational data.

ORM: Software that maps database structures to programming-language objects.

CRUD: Create, read, update, and delete operations.

Transaction: A logical unit of database work that commits or rolls back as a whole.

ACID: Atomicity, consistency, isolation, and durability properties of transactions.

Migration: A versioned change to a database schema.

Index: An auxiliary structure that can accelerate data retrieval.

Pagination: Dividing a large result set into manageable pages.

N+1 problem: Excessive queries caused by retrieving related records one parent at a time.

Eager loading: Loading related data as part of an intentional query strategy.

Authorisation: Determining what an authenticated user is allowed to do.

Referential integrity: Correctness of references between related tables.

Connection pool: A managed set of reusable database connections.

Denormalisation: Deliberate duplication or restructuring for a justified performance or access benefit.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why is a many-to-many relationship commonly represented by an associative table?
3. What problem does parameter binding prevent?
4. State the four ACID properties.
5. Why should db.create_all() not replace migrations in a maintained application?

Long Answer Questions

1. Explain how a Flask route should implement a reliable create operation, including validation, commit, rollback, and error handling.
2. Compare direct SQL and ORM-based data access, including their strengths and limitations.
3. Describe normalisation and explain when controlled denormalisation might be justified.
4. Explain the N+1 query problem and compare at least two strategies for addressing it.
5. Discuss the role of database constraints, application validation, and authorisation in protecting data.

Analytical / Application Questions

1. Design a relational schema for a library in which members borrow books and each borrowing records a due date. Identify keys, relationships, and at least three constraints.
2. A course page performs one query for courses and one additional query for each instructor. Diagnose the performance problem and propose a query and loading strategy.
3. An enrolment request checks that ten places remain, but two simultaneous requests both observe ten places and both insert. Explain why this occurs and propose transaction or concurrency controls to preserve capacity.

Multiple Choice Questions with Answers

Question 1

Which database object uniquely identifies each row in a table?

A. Primary key

B. Foreign key

C. Index page

D. View template

Correct Answer: A. Primary key

Question 2

Why should user input not be concatenated directly into an SQL string?

A. It prevents pagination

B. It can enable SQL injection

C. It disables database backups

D. It removes all indexes

Correct Answer: B. It can enable SQL injection

Question 3

Which relationship normally requires an associative table?

A. One-to-one

B. One-to-many

C. Many-to-many

D. Self-free relationship

Correct Answer: C. Many-to-many

Question 4

What should normally happen after a database commit raises an exception?

A. The session should be rolled back

B. The application should commit again immediately

C. All validation should be skipped

D. The database should be deleted

Correct Answer: A. The session should be rolled back

Question 5

Which property means that a transaction’s changes are all applied or none are applied?

A. Isolation

B. Durability

C. Atomicity

D. Indexing

Correct Answer: C. Atomicity

Question 6

What is the N+1 query problem?

A. A query containing exactly N plus one columns

B. One initial query followed by one query for each related object

C. A failed migration after version N

D. A query with no foreign keys

Correct Answer: B. One initial query followed by one query for each related object

Question 7

What is the main purpose of a migration?

A. To style an HTML form

B. To version and apply schema changes

C. To hash user passwords

D. To replace all database constraints

Correct Answer: B. To version and apply schema changes

Question 8

Which practice is most appropriate for a large user list?

A. Load every row into memory

B. Disable ordering permanently

C. Use pagination

D. Remove the primary key

Correct Answer: C. Use pagination

Question 9

What does authorisation determine?

A. Whether a user may perform a requested action

B. Whether a table has columns

C. How an index is physically stored

D. Whether Python syntax is valid

Correct Answer: A. Whether a user may perform a requested action

Question 10

Which statement about indexes is correct?

A. They always improve every query

B. They can speed reads but add storage and write cost

C. They replace foreign keys

D. They make transactions unnecessary

Correct Answer: B. They can speed reads but add storage and write cost