Skip to main content

Building a Simple CRUD App with Flask and SQLAlchemy

CRUD is where a lot of Flask learners finally feel the pieces click.

Before this point, you usually build isolated features: one route, one template, one form. A CRUD app forces those pieces to work together as a system. You need routes, model design, validation, redirects, template loops, and transaction handling in one coherent flow.

In this lesson, we will build a small to-do application and focus on quality of implementation, not just “it runs on localhost.”

The four operations in one mental model​

CRUD stands for:

  • Create a record
  • Read records
  • Update an existing record
  • Delete a record

In Flask + SQLAlchemy, that usually maps to:

  • POST /todos for create
  • GET /todos for listing
  • POST /todos/<id>/toggle or edit route for update
  • POST /todos/<id>/delete for delete

Notice we are using POST for state-changing actions. That prevents accidental destructive behavior caused by crawlers or prefetching on GET links.

Step 1: project setup and model​

from flask import Flask, render_template, request, redirect, url_for, flash, abort
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///todo.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.secret_key = "dev-secret-change-me"

db = SQLAlchemy(app)

class Todo(db.Model):
    __tablename__ = "todos"

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)
    complete = db.Column(db.Boolean, nullable=False, default=False)

    def __repr__(self):
        return f"<Todo id={self.id} title={self.title!r} complete={self.complete}>"

Create the table once in development:

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

Step 2: read + create routes​

@app.route("/todos", methods=["GET"])
def todos_list():
    todos = Todo.query.order_by(Todo.id.desc()).all()
    return render_template("todos_list.html", todos=todos)

@app.route("/todos", methods=["POST"])
def todos_create():
    title = request.form.get("title", "").strip()
    if not title:
        flash("Title is required.", "error")
        return redirect(url_for("todos_list"))

    todo = Todo(title=title)
    db.session.add(todo)
    db.session.commit()

    flash("Todo created.", "success")
    return redirect(url_for("todos_list"))

This split (GET and POST on same URL) keeps intent obvious:

  • GET renders the current list
  • POST creates and redirects

Step 3: update and delete routes​

@app.route("/todos/<int:todo_id>/toggle", methods=["POST"])
def todos_toggle(todo_id: int):
    todo = db.session.get(Todo, todo_id)
    if not todo:
        abort(404)

    todo.complete = not todo.complete
    db.session.commit()
    flash("Todo updated.", "success")
    return redirect(url_for("todos_list"))

@app.route("/todos/<int:todo_id>/delete", methods=["POST"])
def todos_delete(todo_id: int):
    todo = db.session.get(Todo, todo_id)
    if not todo:
        abort(404)

    db.session.delete(todo)
    db.session.commit()
    flash("Todo deleted.", "success")
    return redirect(url_for("todos_list"))

Using POST instead of simple <a href="/delete/..."> is one of the most important correctness upgrades for beginner CRUD apps.

Step 4: template with safe forms​

templates/todos_list.html:

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8" />
  <title>Todo CRUD</title>
  <style>
    body { font-family: system-ui, sans-serif; max-width: 760px; margin: 2rem auto; }
    .row { display: flex; gap: 0.5rem; margin-bottom: 0.75rem; }
    .done { text-decoration: line-through; color: #666; }
    .error { color: #b00020; }
    .success { color: #0a7a1f; }
  </style>
</head>
<body>
  <h1>Todo list</h1>

  {% with messages = get_flashed_messages(with_categories=true) %}
    {% if messages %}
      <ul>
        {% for category, message in messages %}
          <li class="{{ category }}">{{ message }}</li>
        {% endfor %}
      </ul>
    {% endif %}
  {% endwith %}

  <form class="row" method="post" action="{{ url_for('todos_create') }}">
    <input name="title" placeholder="Add a task..." />
    <button type="submit">Add</button>
  </form>

  <ul>
    {% for todo in todos %}
      <li>
        <span class="{{ 'done' if todo.complete else '' }}">{{ todo.title }}</span>

        <form style="display:inline" method="post" action="{{ url_for('todos_toggle', todo_id=todo.id) }}">
          <button type="submit">{{ "Mark incomplete" if todo.complete else "Mark complete" }}</button>
        </form>

        <form style="display:inline" method="post" action="{{ url_for('todos_delete', todo_id=todo.id) }}">
          <button type="submit">Delete</button>
        </form>
      </li>
    {% else %}
      <li>No todos yet.</li>
    {% endfor %}
  </ul>
</body>
</html>

Why this CRUD design works well​

1) Clear route contracts​

Each endpoint has one responsibility. That makes debugging easier and helps when you later add tests.

2) Correct method semantics​

Reads are GET, state changes are POST. This aligns with HTTP behavior and browser expectations.

3) Redirect after successful writes​

Every create/update/delete ends with redirect to the list page. That avoids duplicate submissions when users refresh.

4) Explicit not-found handling​

If an ID does not exist, the route aborts with 404 instead of crashing with AttributeError.

Real-world pitfalls beginners hit​

Pitfall: trusting title input​

Always .strip() and validate minimum content. Empty or whitespace-only rows are a common first bug.

Do not implement destructive actions as direct hyperlinks.

Pitfall: doing too much inside templates​

Templates should display data and submit forms, not run business logic.

Pitfall: missing transaction rollback in complex flows​

In more advanced CRUD (multi-step updates), wrap commits in try/except and call rollback on exceptions.

Optional extension: edit title​

A simple improvement is adding an edit screen:

  • GET /todos/<id>/edit to show form
  • POST /todos/<id>/edit to validate + update

That gives you a full practical CRUD cycle beyond toggle.

How this connects to nearby lessons​

Closing thoughts​

A CRUD app is not impressive because it has four buttons. It is impressive when those buttons behave predictably, safely, and consistently under real usage.

If you keep method semantics strict, validate input, use redirect-after-write, and structure routes clearly, your Flask CRUD code will stay maintainable even as the project grows from “toy app” into something users actually depend on.