Flask CRUD with SQLAlchemy: Create, Read, Update, Delete

CRUD stands for Create, Read, Update, and Delete. The four operations sound simple, but this is where routes, forms, validation, databases, and templates begin working as one complete flow.

We will build note CRUD with the Note model and notes_bp Blueprint from the previous articles. Every data-changing request ends with a redirect, preventing a browser refresh from repeating the last operation.

Routes for All Four Operations

Add the following routes to routes/notes.py:

# File: routes/notes.py
from flask import Blueprint, flash, redirect, render_template, request, url_for
from models import Note, db

notes_bp = Blueprint("notes", __name__, url_prefix="/notes")


@notes_bp.get("")
def index():
    query = db.select(Note).order_by(Note.id.desc())
    notes = db.session.execute(query).scalars().all()
    return render_template("notes/index.html", notes=notes)


@notes_bp.post("")
def store():
    title = request.form.get("title", "").strip()
    if not title:
        flash("A note title is required.", "error")
        return redirect(url_for("notes.index"))

    note = Note(title=title)
    db.session.add(note)
    db.session.commit()
    flash("Note created successfully.", "success")
    return redirect(url_for("notes.index"))


@notes_bp.get("/<int:note_id>/edit")
def edit(note_id):
    note = db.get_or_404(Note, note_id)
    return render_template("notes/edit.html", note=note)


@notes_bp.post("/<int:note_id>")
def update(note_id):
    note = db.get_or_404(Note, note_id)
    title = request.form.get("title", "").strip()

    if not title:
        flash("A note title is required.", "error")
        return redirect(url_for("notes.edit", note_id=note.id))

    note.title = title
    db.session.commit()
    flash("Note updated successfully.", "success")
    return redirect(url_for("notes.index"))


@notes_bp.post("/<int:note_id>/delete")
def destroy(note_id):
    note = db.get_or_404(Note, note_id)
    db.session.delete(note)
    db.session.commit()
    flash("Note deleted successfully.", "success")
    return redirect(url_for("notes.index"))

The index() route runs a query ordered by descending IDs so newer notes appear first. .scalars().all() turns the SQLAlchemy result into a list of Note objects that can be passed to the template.

In store(), a new object must be added with db.session.add() before committing. The update() route does not call add() again because the loaded note object is already tracked by the session. Assigning note.title and committing is enough.

db.get_or_404(Note, note_id) looks up a row by primary key. If it does not exist, Flask immediately returns a 404 page. Without that handling, the code might attempt to read an attribute from None and produce a 500 error.

List, Create, and Delete Forms

The index template renders a create form, note list, edit link, and delete button:

<!-- File: templates/notes/index.html -->
<h1>Notes</h1>

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

<form method="post" action="{{ url_for('notes.store') }}">
  <label for="title">Note title</label>
  <input id="title" name="title" maxlength="120" required>
  <button type="submit">Add</button>
</form>

<ul>
  {% for note in notes %}
    <li>
      {{ note.title }}
      <a href="{{ url_for('notes.edit', note_id=note.id) }}">Edit</a>

      <form method="post"
            action="{{ url_for('notes.destroy', note_id=note.id) }}">
        <button type="submit">Delete</button>
      </form>
    </li>
  {% else %}
    <li>There are no notes yet.</li>
  {% endfor %}
</ul>

Delete deliberately uses a POST form instead of a GET link. A GET request should be safe to open without changing data. HTML forms do not support DELETE directly, so traditional web routes commonly use POST for delete actions.

The Edit Page

The edit form places the existing title into the input:

<!-- File: templates/notes/edit.html -->
<h1>Edit Note</h1>

<form method="post" action="{{ url_for('notes.update', note_id=note.id) }}">
  <label for="title">Note title</label>
  <input id="title" name="title" value="{{ note.title }}" maxlength="120" required>
  <button type="submit">Save changes</button>
</form>

<a href="{{ url_for('notes.index') }}">Back</a>

Jinja escapes note.title before inserting it into HTML. The backend still needs length and content validation because escaping protects presentation, not data quality.

Commits, Errors, and Transactions

Each route above performs only one commit. This creates a clear transaction boundary: all intended changes succeed together or fail before a success response is sent.

In a more serious application, handle database exceptions and call db.session.rollback() before processing further database work. Without a rollback, a session that just failed may remain in an error state.

The create and delete forms also need CSRF tokens before production use. POST prevents deletion through an ordinary link, but it does not prove that the request came from your application’s form.

Try extending this version with a body field, a detail page, and a confirmation before delete. Then request an ID that does not exist. Healthy CRUD does more than store data; it also responds sensibly to empty input and missing resources.