New

How To Create A Backend With User Support - Beginner Friendly

Building a Full-Stack Flask Web Application with MySQL (In-Depth Guide)

This article walks through a complete full-stack web application using Flask and MySQL, with a strong focus on understanding how and why everything works. Rather than just showing code, we’ll break down the architecture, patterns, and design decisions behind it.

1. Architecture Overview

A full-stack application like this typically follows a 3-layer architecture:

1. Presentation Layer (Frontend)

Built using HTML, CSS, and Jinja2 templates
Responsible for displaying data and handling user input
Uses dynamic rendering (render_template) to inject backend data

2. Application Layer (Flask Backend)

Handles routing (@app.route)
Processes form data (request.form)
Manages sessions (session)
Communicates with the database

3. Data Layer (MySQL Database)

Stores structured data (users, products, orders)
Enforces relationships using foreign keys
Provides persistence

This separation is important because it improves:

Maintainability
Scalability
Debugging

2. Backend: Flask Application (Deep Dive)

Below is the full backend code. As you read through it, pay attention to patterns like routing, session handling, and database interaction.

from flask import Flask, render_template, request, redirect, url_for, session  
from flask_mysqldb import MySQL  
  
app = Flask(__name__)  
  
# ── SECRET KEY (needed for session) ──  
app.secret_key = 'secret-key'  
  
# ── MYSQL CONFIG ── change these if yours are different  
app.config['MYSQL_HOST']     = 'localhost'  
app.config['MYSQL_USER']     = 'root'  
app.config['MYSQL_PASSWORD'] = 'root'  
app.config['MYSQL_DB']       = 'noeari'  
  
mysql = MySQL(app)  
  
  
# ════════════════════════════  
#  PUBLIC PAGES  
# ════════════════════════════  
  
@app.route('/')  
def home():  
    return render_template('index.html')  
  
  
@app.route('/products')  
def products():  
    # Fetch all available products from the database  
    cur = mysql.connection.cursor()  
    cur.execute("SELECT * FROM product WHERE is_available = 1")  
    products = cur.fetchall()  
    cur.close()  
    return render_template('products.html', products=products)  
  
  
# ════════════════════════════  
#  CUSTOMER AUTH  
# ════════════════════════════  
  
@app.route('/login/customer', methods=['GET', 'POST'])  
def customer_login():  
    error = None  
    if request.method == 'POST':  
        email    = request.form.get('email')  
        password = request.form.get('password')  
        cur = mysql.connection.cursor()  
        cur.execute("SELECT * FROM customer WHERE email = %s AND password_hash = %s", (email, password))  
        customer = cur.fetchone()  
        cur.close()  
        if customer:  
            session['customer_id'] = customer[0]  
            session['customer_name'] = customer[1]  
            return redirect(url_for('home'))  
        else:  
            error = 'Incorrect email or password.'  
    return render_template('login.html', role='Customer', role_key='customer',  
                           signup_url=url_for('customer_signup'), error=error)  
  
  
@app.route('/signup/customer', methods=['GET', 'POST'])  
def customer_signup():  
    error = None  
    if request.method == 'POST':  
        name     = request.form.get('name')  
        email    = request.form.get('email')  
        password = request.form.get('password')  
        confirm  = request.form.get('confirm')  
        if not all([name, email, password, confirm]):  
            error = 'Please fill in all fields.'  
        elif password != confirm:  
            error = 'Passwords do not match.'  
        else:  
            cur = mysql.connection.cursor()  
            cur.execute("SELECT id FROM customer WHERE email = %s", (email,))  
            existing = cur.fetchone()  
            if existing:  
                error = 'An account with that email already exists.'  
            else:  
                cur.execute(  
                    "INSERT INTO customer (first_name, email, password_hash) VALUES (%s, %s, %s)",  
                    (name, email, password)  
                )  
                mysql.connection.commit()  
                cur.close()  
                return redirect(url_for('customer_login'))  
            cur.close()  
    return render_template('signup.html', role='Customer', role_key='customer',  
                           login_url=url_for('customer_login'), error=error)  
  
  
# ════════════════════════════  
#  PRODUCER AUTH  
# ════════════════════════════  
  
@app.route('/login/producer', methods=['GET', 'POST'])  
def producer_login():  
    error = None  
    if request.method == 'POST':  
        email    = request.form.get('email')  
        password = request.form.get('password')  
        cur = mysql.connection.cursor()  
        cur.execute("SELECT * FROM producer WHERE email = %s AND password_hash = %s", (email, password))  
        producer = cur.fetchone()  
        cur.close()  
        if producer:  
            session['producer_id']   = producer[0]  
            session['producer_name'] = producer[1]  
            return redirect(url_for('producer_dashboard'))  
        else:  
            error = 'Incorrect email or password.'  
    return render_template('login.html', role='Producer', role_key='producer',  
                           signup_url=url_for('producer_signup'), error=error)  
  
  
@app.route('/signup/producer', methods=['GET', 'POST'])  
def producer_signup():  
    error = None  
    if request.method == 'POST':  
        business = request.form.get('business')  
        email    = request.form.get('email')  
        password = request.form.get('password')  
        confirm  = request.form.get('confirm')  
        if not all([business, email, password, confirm]):  
            error = 'Please fill in all fields.'  
        elif password != confirm:  
            error = 'Passwords do not match.'  
        else:  
            cur = mysql.connection.cursor()  
            cur.execute("SELECT producer_id FROM producer WHERE email = %s", (email,))  
            existing = cur.fetchone()  
            if existing:  
                error = 'An account with that email already exists.'  
            else:  
                cur.execute(  
                    "INSERT INTO producer (business_name, email, password_hash) VALUES (%s, %s, %s)",  
                    (business, email, password)  
                )  
                mysql.connection.commit()  
                cur.close()  
                return redirect(url_for('producer_login'))  
            cur.close()  
    return render_template('signup.html', role='Producer', role_key='producer',  
                           login_url=url_for('producer_login'), error=error)  
  
  
# ════════════════════════════  
#  PRODUCER DASHBOARD  
# ════════════════════════════  
  
@app.route('/dashboard')  
def producer_dashboard():  
    # Redirect to login if not logged in  
    if 'producer_id' not in session:  
        return redirect(url_for('producer_login'))  
  
    producer_id = session['producer_id']  
    cur = mysql.connection.cursor()  
  
    # Products belonging to this producer  
    cur.execute("SELECT * FROM product WHERE producer_id = %s", (producer_id,))  
    products = cur.fetchall()  
  
    # Orders for this producer's products  
    cur.execute("""  
        SELECT o.order_id, c.first_name, p.name, oi.quantity, o.status, o.placed_at  
        FROM orders o  
        JOIN order_item oi ON o.order_id = oi.order_id  
        JOIN product p     ON oi.product_id = p.product_id  
        JOIN customer c    ON o.customer_id = c.customer_id  
        WHERE p.producer_id = %s  
        ORDER BY o.placed_at DESC  
    """, (producer_id,))  
    orders = cur.fetchall()  
  
    # Stock levels  
    cur.execute("""  
        SELECT p.name, s.quantity_available, s.reorder_threshold  
        FROM stock s  
        JOIN product p ON s.product_id = p.product_id  
        WHERE p.producer_id = %s  
    """, (producer_id,))  
    stock = cur.fetchall()  
  
    cur.close()  
    return render_template('dashboard.html',  
                           producer_name=session['producer_name'],  
                           products=products,  
                           orders=orders,  
                           stock=stock)  
  
  
@app.route('/dashboard/update-stock', methods=['POST'])  
def update_stock():  
    if 'producer_id' not in session:  
        return redirect(url_for('producer_login'))  
    product_id = request.form.get('product_id')  
    quantity   = request.form.get('quantity')  
    cur = mysql.connection.cursor()  
    cur.execute("UPDATE stock SET quantity_available = %s WHERE product_id = %s", (quantity, product_id))  
    mysql.connection.commit()  
    cur.close()  
    return redirect(url_for('producer_dashboard'))  
  
  
@app.route('/dashboard/update-order-status', methods=['POST'])  
def update_order_status():  
    if 'producer_id' not in session:  
        return redirect(url_for('producer_login'))  
    order_id = request.form.get('order_id')  
    status   = request.form.get('status')  
    cur = mysql.connection.cursor()  
    cur.execute("UPDATE orders SET status = %s WHERE order_id = %s", (status, order_id))  
    mysql.connection.commit()  
    cur.close()  
    return redirect(url_for('producer_dashboard'))  
  
  
# ════════════════════════════  
#  LOGOUT  
# ════════════════════════════  
  
@app.route('/logout')  
def logout():  
    session.clear()  
    return redirect(url_for('home'))  
  
  
if __name__ == '__main__':  
    app.run(debug=True)

Key Backend Concepts Explained

Routing

Each @app.route() defines a URL endpoint. Flask maps incoming HTTP requests to Python functions.

GET → retrieve data (e.g., view products)
POST → submit data (e.g., login form)

Request Handling

request.form.get() extracts data from submitted HTML forms. This is how user input flows into your backend.

Sessions

The session object stores user data across requests:

After login → user ID is stored
On each request → Flask checks if the user is still logged in

This is how authentication persists without re-entering credentials.

Database Interaction

cursor.execute() sends SQL queries
Parameterized queries (%s) prevent SQL injection
fetchone() vs fetchall():
- fetchone() → single record
- fetchall() → list of records

3. Frontend: Jinja2 Template System

The frontend uses template inheritance, a powerful concept in Jinja2.

<!DOCTYPE html>  
<html lang="en">  
<head>  
  <meta charset="UTF-8"/>  
  <meta name="viewport" content="width=device-width, initial-scale=1.0"/>  
  <title>{% block title %}Title{% endblock %}</title>  
  <link href="https://fonts.googleapis.com/css2?family=Lora:ital,wght@0,400;0,600;1,400&family=Nunito:wght@400;600;700&display=swap" rel="stylesheet"/>  
  <style>  
  ...  
  </style>  
  {% block extra_style %}{% endblock %}  
</head>  
<body>  
  <nav class="nav">  
    <a href="{{ url_for('home') }}" class="nav__logo">???? </a>  
    <div class="nav__links">  
      <a href="{{ url_for('home') }}" class="nav__link {% if request.endpoint == 'home' %}nav__link--active{% endif %}">Home</a>  
      <a href="{{ url_for('products') }}" class="nav__link {% if request.endpoint == 'products' %}nav__link--active{% endif %}">Shop</a>  
      <div class="nav__divider"></div>  
  
      {% if session.customer_name %}  
        <span class="nav__link">???? {{ session.customer_name }}</span>  
        <a href="{{ url_for('logout') }}" class="nav__btn nav__btn--outline">Log out</a>  
  
      {% elif session.producer_name %}  
        <a href="{{ url_for('producer_dashboard') }}" class="nav__link">Dashboard</a>  
        <a href="{{ url_for('logout') }}" class="nav__btn nav__btn--outline">Log out</a>  
  
      {% else %}  
        <a href="{{ url_for('customer_login') }}" class="nav__btn nav__btn--outline">Customer login</a>  
        <a href="{{ url_for('producer_login') }}" class="nav__btn nav__btn--fill">Producer login</a>  
      {% endif %}  
    </div>  
  </nav>  
  
  {% block content %}{% endblock %}  
  
  <footer class="footer">  
    <p>????  — © 2025 — Supporting local farmers.</p>  
  </footer>  
</body>  
</html>

Key Frontend Concepts Explained

Template Inheritance

{% block content %} allows child templates to inject content
Promotes DRY (Don’t Repeat Yourself) design

Dynamic Rendering

{{ variable }} outputs backend data
{% if %} enables conditional UI logic

URL Routing in Templates

url_for('route_name') dynamically generates URLs
Prevents hardcoding paths

4. Database Design (Relational Thinking)

CREATE DATABASE IF NOT EXISTS ;  
USE noeari;  
  
CREATE TABLE IF NOT EXISTS customer (  
    customer_id INT AUTO_INCREMENT PRIMARY KEY,  
    first_name VARCHAR(100) NOT NULL,  
    email VARCHAR(255) NOT NULL UNIQUE,  
    password_hash VARCHAR(255) NOT NULL,  
    date_joined DATE DEFAULT (CURRENT_DATE)  
);  
  
CREATE TABLE IF NOT EXISTS producer (  
    producer_id INT AUTO_INCREMENT PRIMARY KEY,  
    business_name VARCHAR(255) NOT NULL,  
    email VARCHAR(255) NOT NULL UNIQUE,  
    password_hash VARCHAR(255) NOT NULL,  
    date_joined DATE DEFAULT (CURRENT_DATE)  
);  
  
CREATE TABLE IF NOT EXISTS product (  
    product_id INT AUTO_INCREMENT PRIMARY KEY,  
    producer_id INT NOT NULL,  
    name VARCHAR(255) NOT NULL,  
    description TEXT,  
    category VARCHAR(100),  
    price DECIMAL(6,2) NOT NULL,  
    unit VARCHAR(50),  
    image_url VARCHAR(500),  
    is_available BOOLEAN DEFAULT TRUE,  
    FOREIGN KEY (producer_id) REFERENCES producer(producer_id)  
);  
  
CREATE TABLE IF NOT EXISTS stock (  
    stock_id INT AUTO_INCREMENT PRIMARY KEY,  
    product_id INT NOT NULL UNIQUE,  
    quantity_available INT DEFAULT 0,  
    reorder_threshold INT DEFAULT 10,  
    FOREIGN KEY (product_id) REFERENCES product(product_id)  
);  
  
CREATE TABLE IF NOT EXISTS orders (  
    order_id INT AUTO_INCREMENT PRIMARY KEY,  
    customer_id INT NOT NULL,  
    status VARCHAR(50) DEFAULT 'pending',  
    order_type VARCHAR(20) DEFAULT 'delivery',  
    placed_at DATETIME DEFAULT CURRENT_TIMESTAMP,  
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)  
);  
  
CREATE TABLE IF NOT EXISTS order_item (  
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,  
    order_id INT NOT NULL,  
    product_id INT NOT NULL,  
    quantity INT NOT NULL,  
    unit_price DECIMAL(6,2) NOT NULL,  
    FOREIGN KEY (order_id) REFERENCES orders(order_id),  
    FOREIGN KEY (product_id) REFERENCES product(product_id)  
);  
  
INSERT IGNORE INTO producer (business_name, email, password_hash)  
VALUES ('Hartley Farm', 'hartley@noeari.com', 'password123');  
  
INSERT IGNORE INTO customer (first_name, email, password_hash)  
VALUES ('Jane', 'jane@example.com', 'password123');

Key Database Concepts Explained

Primary Keys

Unique identifiers (customer_id, product_id)
Automatically incremented

Foreign Keys

Create relationships between tables
Example: product.producer_id → producer.producer_id

This enforces referential integrity.

Normalization

The schema avoids redundancy by splitting data:

Products separate from stock
Orders separate from order items

This improves:

Data consistency
Query flexibility

One-to-Many Relationships

One producer → many products
One order → many order items

5. Security Considerations (Important)

This implementation is functional but highlights key learning points:

Passwords are stored as plain text → should use hashing (e.g., bcrypt)
No CSRF protection → should use Flask-WTF
Session security depends on secret_key

Understanding these limitations is critical when moving to production.

6. How Everything Connects

Here’s the full request lifecycle:

User visits /products
Flask route runs SQL query
Data is passed into products.html
Jinja renders dynamic HTML
Browser displays results

For login:

User submits form
Flask validates credentials via SQL
Session is created
User gains access to protected routes

Final Thoughts

This project demonstrates the core principles of full-stack development:

Routing and request handling
Persistent storage with relational databases
Dynamic UI rendering
Session-based authentication
Separation of concerns

Once you understand these fundamentals, you can extend this system with:

APIs (RESTful services)
Payment systems
Real-time updates (WebSockets)
Deployment (Docker, cloud platforms)

Back to Listing

credit: