Build a Books Collection with Flask

Design a SQLite database in DBeaver and build a Flask web app to manage it.

Introduction

30 Second Summary

Everyone has a list of books they love, but keeping track of them usually means a messy spreadsheet or a forgotten notes app. A proper database with its own web page turns that list into something you actually enjoy using.

In this project, you will design a database in DBeaver, fill it with your favorite books, and build a web application with Flask that displays, adds, and removes books through a clean browser interface.

What You'll Build

You open your browser, see your entire book collection in a styled table, add a new book through a form, click delete on one you no longer want, and watch both the web page and DBeaver update in sync.

By the end of this project, you'll have:

  • A styled web page that displays your book collection in a clean table with title, author, and year columns.
  • A web form to add new books and delete buttons to remove them directly from the browser without writing any SQL.
  • A shared SQLite database file that both your web app and DBeaver read from, so you can verify changes in either tool.
  • Secret Mission: Add an edit feature that lets you update book details in place, completing all four CRUD operations.

Are there any prerequisites?

No prior experience with databases, SQL, or web development is needed. This guide walks you through every step from scratch.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. This step is your moment to commit to the goal so every action in the steps ahead has a clear purpose.

Set Up Your Project and Install Flask

Your books collection app needs a home on your computer. Both DBeaver and your web app will read from the same database file, so everything needs to live in the same folder.

In this step, you'll create that project folder, set up an isolated Python environment using a virtual environment, and install Flask so you're ready to start building.

In this step, get ready to:
  • Create the project folder and a Python virtual environment.
  • Install Flask inside the virtual environment.
  • Open the project in VS Code.
Create the project folder and virtual environment

A virtual environment is an isolated copy of Python just for this project. It keeps Flask and its dependencies separate from other Python projects on your machine.

  • Press Ctrl+Alt+T to open a terminal.
  • Create the project folder, move into it, and set up a virtual environment by running these commands:
mkdir ~/books-collection
cd ~/books-collection
python3 -m venv venv
source venv/bin/activate

Your terminal prompt now shows (venv) at the beginning. This confirms the virtual environment is active.

What just happened?

  • mkdir ~/books-collection created a new folder in your home directory.
  • cd ~/books-collection moved your terminal into that folder.
  • python3 -m venv venv created an isolated Python environment inside a venv subfolder.
  • source venv/bin/activate turned on that environment so any packages you install stay local to this project.

Don't see (venv) in your prompt?

Make sure you typed source venv/bin/activate exactly. If you see an error about the file not existing, double-check you ran python3 -m venv venv first in the same folder.

help me activate my virtual environment on Ubuntu

  • Create the two folders that Flask expects for HTML templates and CSS files by running:
mkdir templates static
  • Confirm your folder structure by running:
ls

You should see static, templates, and venv listed in the output.

Install Flask

Flask is a lightweight Python web framework that lets you build web pages backed by Python code. You'll use it to create the interface for your book collection.

  • With the virtual environment still active, install Flask by running:
pip install Flask==3.1.3

You'll see pip download and install Flask along with its dependencies. The output ends with a "Successfully installed" message.

Seeing a permissions error?

If you see "Permission denied" or the install tries to write to a system directory, your virtual environment is not active. Run source venv/bin/activate again and retry the install.

help me fix a pip permissions error when installing Flask

Before you verify, what version number do you expect to see printed?

  • Verify the installation by running:
python -c "import flask; print(flask.__version__)"

You should see 3.1.3 printed. This confirms Flask is installed and ready to use.

Open the project in VS Code
  • Open the project folder in VS Code by running this command from the same terminal:
code .

VS Code opens with the books-collection folder in the Explorer sidebar. You should see the static, templates, and venv folders listed on the left.

Seeing 'command not found' for code?

If code is not recognized, open VS Code manually. Press the Super key, type Visual Studio Code, and press Enter. Then open the folder with File > Open Folder and select books-collection from your home directory.

help me open my project folder in VS Code on Ubuntu

Your project environment is ready to go. Next up, you'll design a database in DBeaver to store your book collection.

Design Your Database in DBeaver

Your project folder is ready and Flask is installed, but there is nothing to display yet. Before you can show books on a web page, you need a place to store them.

A relational database gives your book collection permanent, structured storage. In this step, you will design the database in DBeaver, a professional database tool, and fill it with sample data so you have something to see immediately.

In this step, get ready to:
  • Create a SQLite database file and connect it to DBeaver.
  • Design a books table with columns for title, author, and year.
  • Add sample book data you can query right away.
Create a new SQLite connection in DBeaver

DBeaver needs a connection to know where your database lives. For SQLite, that means pointing it at a file on your computer. DBeaver will create the file automatically if it does not exist yet.

  • Press the Super key to open the Activities overview.
  • Type DBeaver and press Enter to open it.
  • Click the New Connection Wizard button in the toolbar.
  • In the driver list, search for SQLite and select it.
  • Click Next.
  • In the Path field, type the full path to your database file:
/home/[[USERNAME="your Ubuntu username"]]/books-collection/books.db
  • Click Finish.

You should see the new connection appear in the Database Navigator panel on the left side of DBeaver.

What just happened?

DBeaver created a new file called books.db inside your ~/books-collection folder. This single file is your entire SQLite database.

Unlike larger databases that run as background services, SQLite stores everything in one portable file. Both DBeaver and your Flask app will read and write to this same file.

Don't see the connection in the navigator?

Make sure you typed the path exactly, including your actual Ubuntu username. The folder books-collection must already exist (you created it in the previous step).

If you see a permission error, your DBeaver may have been installed via Snap without classic confinement. Reinstall with sudo snap install dbeaver-ce --classic to resolve path access issues.

Help me troubleshoot my DBeaver connection

Create the books table

A database needs a table before it can store anything. Your table defines what information each book holds: a title, an author, and a publication year.

  • Right-click on the new SQLite connection in the Database Navigator and select SQL Editor > Open SQL Console.
  • Type this SQL statement into the editor:
CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    year INTEGER NOT NULL
);
  • Execute the statement by pressing Ctrl+Enter.

You should see a message confirming the statement executed successfully in the output panel at the bottom.

What does this SQL do?

  • id INTEGER PRIMARY KEY AUTOINCREMENT gives each book a unique number that increases automatically.
  • title TEXT NOT NULL stores the book's title and requires a value (it cannot be left empty).
  • author TEXT NOT NULL stores the author's name.
  • year INTEGER NOT NULL stores the publication year as a whole number.

Seeing a syntax error?

Make sure you included the semicolon at the end of the statement. Also check that each column definition is separated by a comma, and that the closing parenthesis comes before the semicolon.

If you see "table books already exists", you ran the command twice. That is fine because the table was already created on the first run.

Help me fix my CREATE TABLE syntax

Insert sample books and verify your data

An empty table is hard to work with. Adding a few books now means you will see real data the moment your web page connects to this database.

  • In the same SQL console, type this INSERT statement:
INSERT INTO books (title, author, year) VALUES
    ('To Kill a Mockingbird', 'Harper Lee', 1960),
    ('1984', 'George Orwell', 1949),
    ('The Great Gatsby', 'F. Scott Fitzgerald', 1925);
  • Execute the statement by pressing Ctrl+Enter.

You should see a confirmation that the three rows were added successfully.

What does this SQL do?

INSERT INTO adds new rows to the books table. Each set of parentheses after VALUES is one book, providing a title, author, and year in that order.

You do not need to supply an id value because AUTOINCREMENT handles that automatically. SQLite assigns 1, 2, and 3 to these three books.

Before you run the next query, think about what you expect to see. How many rows should appear, and what columns will they have?

  • Type this query on a new line in the SQL console:
SELECT * FROM books;
  • Select only the SELECT line (highlight it with your cursor), then press Ctrl+Enter to execute just that statement.

You should see all three books displayed in the results grid with columns for id, title, author, and year.

Results grid is empty?

Make sure you highlighted only the SELECT * FROM books; line before pressing Ctrl+Enter. If you execute the entire console contents, DBeaver may re-run the INSERT and show an execution message instead of query results.

If you see zero rows, the INSERT may not have executed successfully. Clear the editor, paste the INSERT statement again, execute it, then run the SELECT.

Help me figure out why my books are not showing

Your database is designed, filled with data, and ready to serve. Next up, you will build a web page that reads from this same books.db file and displays your collection in the browser.

Display Your Books on a Web Page

In the last step you designed a database in DBeaver and filled it with three sample books. The data is there, but it is locked inside a file that only DBeaver can read.

In this step you will build a web page that connects to that same database file and displays your books in a browser. You will use Flask to serve the page and Jinja2 templates to render the data as HTML.

In this step, get ready to:
  • Create a Flask application that reads books from your database.
  • Build HTML templates that display your books in a table.
  • Add a stylesheet and run the app to see everything in the browser.
Create the Flask application

Your Flask app needs a way to open the books.db file, run a SQL query, and pass the results to an HTML template.

  • In VS Code, create a new file called app.py in the root of your books-collection folder and add the following code:
import sqlite3
from flask import Flask, render_template, request, redirect, url_for

app = Flask(__name__)


def get_db_connection():
    # Connect to the SQLite database file
    conn = sqlite3.connect('books.db')
    # Allow accessing columns by name instead of index
    conn.row_factory = sqlite3.Row
    return conn


@app.route('/')
def index():
    conn = get_db_connection()
    books = conn.execute('SELECT * FROM books ORDER BY id DESC').fetchall()
    conn.close()
    return render_template('index.html', books=books)


if __name__ == '__main__':
    app.run(debug=True)

What does this code do?

  • get_db_connection() opens your books.db file and sets row_factory so you can access columns by name like book['title'] instead of by number.
  • The index() function runs a SELECT query to grab every book, then hands the list to an HTML template for display.
  • app.run(debug=True) starts a local development server that auto-reloads when you change Python files.
  • Save app.py.
  • Confirm the file exists by running this in your terminal:
ls app.py

You should see app.py printed in the output.

Don't see app.py listed?

  • Make sure your terminal is in the ~/books-collection directory. Run pwd to check.
  • Confirm you saved the file in VS Code with Ctrl+S.

Help me figure out why app.py is missing from my project folder.

✔️ Awesome, I've got everything!

Great. Make sure you have saved the file before moving on.

ⓧ I'd like to double check the full code

import sqlite3
from flask import Flask, render_template, request, redirect, url_for

app = Flask(__name__)


def get_db_connection():
    conn = sqlite3.connect('books.db')
    conn.row_factory = sqlite3.Row
    return conn


@app.route('/')
def index():
    conn = get_db_connection()
    books = conn.execute('SELECT * FROM books ORDER BY id DESC').fetchall()
    conn.close()
    return render_template('index.html', books=books)


if __name__ == '__main__':
    app.run(debug=True)
Create the HTML templates

Flask uses a template inheritance system. You create one base layout, then each page extends it with its own content.

  • Create a new file called base.html inside the templates folder and add this code:
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>My Books Collection</title>
    <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
</head>
<body>
    <div class="container">
        <header>
            <h1><a href="{{ url_for('index') }}">My Books Collection</a></h1>
        </header>
        <main>
            {% block content %}{% endblock %}
        </main>
    </div>
</body>
</html>

What does this template do?

This is the shared skeleton for every page in your app. It loads the stylesheet, renders a header, and defines a {% block content %} placeholder where each child template injects its own HTML.

  • Save templates/base.html.
  • Now create a second file called index.html inside the templates folder and add this code:
{% extends 'base.html' %}

{% block content %}
{% if books %}
<table>
    <thead>
        <tr>
            <th>Title</th>
            <th>Author</th>
            <th>Year</th>
        </tr>
    </thead>
    <tbody>
        {% for book in books %}
        <tr>
            <td>{{ book['title'] }}</td>
            <td>{{ book['author'] }}</td>
            <td>{{ book['year'] }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% else %}
<p>No books in your collection yet.</p>
{% endif %}
{% endblock %}

What does this template do?

  • {% extends 'base.html' %} tells Flask to wrap this content inside the base layout.
  • The {% for book in books %} loop creates one table row for each book in the list your Python code passed in.
  • If the list is empty, a friendly message appears instead of a blank table.
  • Save templates/index.html.
  • Confirm both templates exist by running:
ls templates/

You should see base.html and index.html listed.

Only see one file or neither?

  • Check that you created the files inside the templates subfolder, not in the project root.
  • In VS Code's Explorer sidebar, confirm the files appear under templates/.

Help me find where my template files ended up.

✔️ Awesome, I've got everything!

Great. Double check both files are saved before moving on.

ⓧ I'd like to double check base.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>My Books Collection</title>
    <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
</head>
<body>
    <div class="container">
        <header>
            <h1><a href="{{ url_for('index') }}">My Books Collection</a></h1>
        </header>
        <main>
            {% block content %}{% endblock %}
        </main>
    </div>
</body>
</html>

ⓧ I'd like to double check index.html

{% extends 'base.html' %}

{% block content %}
{% if books %}
<table>
    <thead>
        <tr>
            <th>Title</th>
            <th>Author</th>
            <th>Year</th>
        </tr>
    </thead>
    <tbody>
        {% for book in books %}
        <tr>
            <td>{{ book['title'] }}</td>
            <td>{{ book['author'] }}</td>
            <td>{{ book['year'] }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% else %}
<p>No books in your collection yet.</p>
{% endif %}
{% endblock %}
Add styling and run the app

Your app will work without styles, but the raw HTML table will look rough. A stylesheet gives it clean typography, spacing, and a polished table layout.

  • Create a new file called style.css inside the static folder. Start by adding the base page layout styles:
* {
    margin: 0;
    padding: 0;
    box-sizing: border-box;
}

body {
    font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
    background-color: #f5f5f5;
    color: #333;
    line-height: 1.6;
}

.container {
    max-width: 800px;
    margin: 0 auto;
    padding: 2rem;
}

header h1 {
    margin-bottom: 2rem;
}

header h1 a {
    text-decoration: none;
    color: #2c3e50;
}

What do these styles do?

The reset (*) removes default browser margins so you control all spacing. The .container centers your content at a readable width.

  • Below the header styles in the same file, add the table styles:
table {
    width: 100%;
    border-collapse: collapse;
    margin-top: 1rem;
    background: white;
    border-radius: 8px;
    overflow: hidden;
    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
}

th, td {
    padding: 0.75rem 1rem;
    text-align: left;
}

th {
    background-color: #2c3e50;
    color: white;
}

tr:nth-child(even) {
    background-color: #f8f9fa;
}

What do these styles do?

These give your table a card-like appearance with rounded corners, a subtle shadow, and a dark header row. Alternating row colours make it easy to scan across columns.

  • Save static/style.css.
  • Now start the Flask development server by running this command in your terminal:
python app.py

Before you open the URL, what do you think the page will show?

  • Open http://127.0.0.1:5000 in your browser.

You should see your three sample books displayed in a styled table with a dark header row and alternating shading.

Seeing an error or a blank page?

  • If the terminal shows TemplateNotFound, make sure index.html and base.html are inside a folder named exactly templates (plural).
  • If you see the table without any styling, confirm style.css is inside the static folder and hard-refresh your browser with Ctrl+Shift+R.
  • If you see "No books in your collection yet", the app cannot find books.db. Make sure you run python app.py from the ~/books-collection directory.

Help me debug why my books page is not displaying correctly.

Your table looks great. Now add the remaining styles that prepare your app for buttons and forms you will build in the next steps.

  • Back in VS Code, open static/style.css and add the button styles below the table styles:
.btn {
    display: inline-block;
    padding: 0.5rem 1rem;
    background-color: #3498db;
    color: white;
    text-decoration: none;
    border-radius: 4px;
    border: none;
    cursor: pointer;
    font-size: 0.9rem;
}

.btn:hover {
    background-color: #2980b9;
}

.btn-danger {
    background-color: #e74c3c;
}

.btn-danger:hover {
    background-color: #c0392b;
}
  • Below the button styles, add the form and utility styles:
form {
    background: white;
    padding: 2rem;
    border-radius: 8px;
    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
    margin-bottom: 1rem;
}

label {
    display: block;
    margin-top: 1rem;
    margin-bottom: 0.25rem;
    font-weight: bold;
}

input[type="text"],
input[type="number"] {
    width: 100%;
    padding: 0.5rem;
    border: 1px solid #ddd;
    border-radius: 4px;
    font-size: 1rem;
}

button.btn {
    margin-top: 1.5rem;
}

p {
    margin-top: 1rem;
    color: #666;
}

Why add button and form styles now?

These styles do not change your current page because there are no buttons or forms yet. You are adding them now so the stylesheet is complete and ready when you build the add and delete features in the next steps.

  • Save static/style.css.

✔️ Awesome, I've got everything!

Great. Your stylesheet is ready for the entire project.

ⓧ I'd like to double check the full code

Your static/style.css should contain these sections in order. Scroll up and confirm each block is present:

  • Base layout: *, body, .container, header h1, header h1 a.
  • Table: table, th, td, th, tr:nth-child(even).
  • Buttons: .btn, .btn:hover, .btn-danger, .btn-danger:hover.
  • Forms and utilities: form, label, input[type="text"], button.btn, p.

Your books are out of the database and onto a web page. Next up, you will add a form that lets you add new books directly from the browser instead of writing SQL in DBeaver.

Add New Books Through the Web Interface

Your web page displays books from the database, but right now the only way to add new books is by opening DBeaver and writing SQL. That defeats the purpose of having a web interface.

In this step, you will build a form that lets you add books directly from the browser. You will type in a title, author, and year, hit submit, and see the book appear instantly in your collection.

In this step, get ready to:
  • Add a new route in Flask that handles form submissions.
  • Create an HTML template with input fields for book details.
  • Link the form into your main page and test it with real data.
Add the route to app.py

A web form needs two things on the server side: a way to show the blank form (a GET request) and a way to process the submitted data (a POST request). In Flask, a single route can handle both by checking which type of request came in.

  • In VS Code, open app.py.
  • Find the line that says if __name__ == '__main__': near the bottom of the file.
  • Add the following route directly above that line:
@app.route('/add', methods=('GET', 'POST'))
def add():
    if request.method == 'POST':
        title = request.form['title']
        author = request.form['author']
        year = request.form['year']
        conn = get_db_connection()
        conn.execute('INSERT INTO books (title, author, year) VALUES (?, ?, ?)',
                     (title, author, year))
        conn.commit()
        conn.close()
        return redirect(url_for('index'))
    return render_template('add.html')

What does this code do?

  • methods=('GET', 'POST') tells Flask this route accepts both showing the form and receiving submitted data.
  • When the form is submitted (POST), the function reads the title, author, and year from the form fields.
  • The ? placeholders in the SQL prevent injection attacks by keeping user input separate from the query structure.
  • conn.commit() saves the new row permanently. Without it, the INSERT would be discarded when the connection closes.
  • After inserting, redirect(url_for('index')) sends the browser back to the main page so the learner sees their new book immediately.
  • Save app.py.
Create the add book template

The route you just wrote renders a template called add.html. This template holds the form with input fields for the book's title, author, and publication year.

  • Create a new file called add.html inside the templates folder.
  • Add the following code to templates/add.html:
{% extends 'base.html' %}

{% block content %}
<h2>Add a New Book</h2>
<form method="POST">
    <label for="title">Title:</label>
    <input type="text" id="title" name="title" required>

    <label for="author">Author:</label>
    <input type="text" id="author" name="author" required>

    <label for="year">Year Published:</label>
    <input type="number" id="year" name="year" required>

    <button type="submit" class="btn">Add Book</button>
</form>
<a href="{{ url_for('index') }}">Back to collection</a>
{% endblock %}

What does this template do?

  • It extends base.html so the form page shares the same header and styling as the main page.
  • Each name attribute on the inputs (name="title", name="author", name="year") must match the keys your route reads from request.form.
  • The required attribute prevents the browser from submitting empty fields.
  • Save templates/add.html.
  • In your terminal (with the virtual environment active), start the Flask server by running:
python app.py
  • Open your browser and go to http://127.0.0.1:5000/add directly.

You should see a clean form with fields for Title, Author, and Year Published, plus an "Add Book" button.

  • Type The Catcher in the Rye in the Title field.
  • Type J.D. Salinger in the Author field.
  • Type 1951 in the Year Published field.
  • Click Add Book.

The browser redirects you back to the main page. Your new book appears at the top of the table.

Seeing a TemplateNotFound error?

  • Make sure your file is named exactly add.html and lives inside the templates folder, not the project root.
  • Check that the templates folder name is spelled correctly with no typos.

Help me fix this TemplateNotFound error in Flask.

Add a link to the form and test with a typo

The form works, but the only way to reach it is by typing the URL manually. A link on the main page makes it accessible to anyone using your app.

  • Open templates/index.html in VS Code.
  • Find this line near the top:
{% block content %}
  • Add the following line directly below it:
<a href="{{ url_for('add') }}" class="btn">Add a Book</a>
  • Also find the line that says:
<p>No books in your collection yet.</p>
  • Replace it with:
<p>No books in your collection yet. Add one!</p>
  • Save templates/index.html.
  • Restart the app by pressing Ctrl+C in the terminal, then running:
python app.py
  • Open http://127.0.0.1:5000 in your browser.

You should see a blue "Add a Book" button at the top of your collection. Click it to confirm it takes you to the form page.

Now try adding a book with a deliberate typo to see what happens.

  • Click Add a Book.
  • Type Teh Hobbit in the Title field.
  • Type J.R.R. Tolkein in the Author field.
  • Type 1937 in the Year Published field.
  • Click Add Book.

The typo book appears in your collection. Look at the page. Do you see any way to remove it or fix the mistake?

There is no delete button or edit link anywhere on the page. The only way to remove that typo right now is to open DBeaver and write a DELETE query by hand. That gap is exactly what the next step fixes.

Book not appearing after submit?

  • Check that you included conn.commit() in your add route. Without it, the INSERT is rolled back when the connection closes.
  • Make sure the name attributes in your form inputs (title, author, year) match the keys in request.form['title'] exactly.
  • Verify your form has method="POST" in the opening <form> tag.

Help me debug why my Flask form submission is not saving to the database.

✔️ Awesome, I've got everything!

Great. Double check you have saved both app.py and templates/index.html.

ⓧ I'd like to double check the full code

Here is the full app.py at this point:

import sqlite3
from flask import Flask, render_template, request, redirect, url_for

app = Flask(__name__)


def get_db_connection():
    conn = sqlite3.connect('books.db')
    conn.row_factory = sqlite3.Row
    return conn


@app.route('/')
def index():
    conn = get_db_connection()
    books = conn.execute('SELECT * FROM books ORDER BY id DESC').fetchall()
    conn.close()
    return render_template('index.html', books=books)
@app.route('/add', methods=('GET', 'POST'))
def add():
    if request.method == 'POST':
        title = request.form['title']
        author = request.form['author']
        year = request.form['year']
        conn = get_db_connection()
        conn.execute('INSERT INTO books (title, author, year) VALUES (?, ?, ?)',
                     (title, author, year))
        conn.commit()
        conn.close()
        return redirect(url_for('index'))
    return render_template('add.html')


if __name__ == '__main__':
    app.run(debug=True)

Here is the full templates/index.html:

{% extends 'base.html' %}

{% block content %}
<a href="{{ url_for('add') }}" class="btn">Add a Book</a>

{% if books %}
<table>
    <thead>
        <tr>
            <th>Title</th>
            <th>Author</th>
            <th>Year</th>
        </tr>
    </thead>
    <tbody>
        {% for book in books %}
        <tr>
            <td>{{ book['title'] }}</td>
            <td>{{ book['author'] }}</td>
            <td>{{ book['year'] }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% else %}
<p>No books in your collection yet. Add one!</p>
{% endif %}
{% endblock %}

Here is the full templates/add.html:

{% extends 'base.html' %}

{% block content %}
<h2>Add a New Book</h2>
<form method="POST">
    <label for="title">Title:</label>
    <input type="text" id="title" name="title" required>

    <label for="author">Author:</label>
    <input type="text" id="author" name="author" required>

    <label for="year">Year Published:</label>
    <input type="number" id="year" name="year" required>

    <button type="submit" class="btn">Add Book</button>
</form>
<a href="{{ url_for('index') }}">Back to collection</a>
{% endblock %}

Your web app can now create new books, but that stuck typo proves you need one more feature. Next up, you will add delete buttons so you can remove mistakes directly from the browser.

Delete Books from Your Collection

Remember that typo book from the last step? It is stuck on your page with no way to remove it through the browser.

Without a delete button, you would have to open DBeaver and write SQL every time you want to fix a mistake. In this step, you will add a delete feature directly in the web interface.

In this step, get ready to:
  • Add a delete route to app.py.
  • Add Delete buttons to the book table.
  • Remove the typo book and verify in both the browser and DBeaver.
Add the delete route to app.py

Your web app needs a new route that knows how to remove a specific book from the database. The route will receive a book's ID from the URL and use it to delete the matching row.

  • In VS Code, open app.py.
  • Find the if __name__ == '__main__': line at the bottom of the file.
  • Add this new route directly above that line:
@app.route('/delete/<int:id>')
def delete(id):
    # Connect to the database
    conn = get_db_connection()
    # Remove the book with the matching id
    conn.execute('DELETE FROM books WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    # Send the user back to the main page
    return redirect(url_for('index'))

What does this code do?

  • The route /delete/<int:id> captures a book's ID from the URL. For example, visiting /delete/3 passes the number 3 into the function.
  • The ? placeholder in the SQL prevents SQL injection by safely substituting the ID value instead of pasting it directly into the query string.
  • After deleting the row, redirect(url_for('index')) sends the user back to the main page so they immediately see the updated list.
  • Save app.py.

Seeing a syntax error?

Make sure there is a blank line between the end of your add() function and the new @app.route('/delete/<int:id>') decorator. Python uses blank lines to separate top-level blocks.

Also check that the route is placed above if __name__ == '__main__': and not inside another function.

help me fix a syntax error in my Flask delete route

Add Delete buttons to the table

The route exists, but there is no way for users to trigger it from the browser. You need a clickable Delete link next to each book that points to the delete route with that book's ID.

  • Open templates/index.html in VS Code.
  • Find the <thead> section. Add a new <th>Actions</th> after the Year header.
  • Then find the <tbody> section. Add a new <td> with a Delete link after the year cell in each row.
  • Your full <table> block should now look like this:
<table>
    <thead>
        <tr>
            <th>Title</th>
            <th>Author</th>
            <th>Year</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for book in books %}
        <tr>
            <td>{{ book['title'] }}</td>
            <td>{{ book['author'] }}</td>
            <td>{{ book['year'] }}</td>
            <td>
                <a href="{{ url_for('delete', id=book['id']) }}" class="btn btn-danger"
                   onclick="return confirm('Delete this book?')">Delete</a>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>

What does this code do?

  • Each Delete link points to /delete/<id> for that specific book row. Flask generates the correct URL using url_for('delete', id=book['id']).
  • The onclick="return confirm('Delete this book?')" attribute shows a confirmation dialog before the browser follows the link. If the user clicks Cancel, the deletion does not happen.
  • The btn-danger class applies the red styling from your style.css so the Delete button is visually distinct from the blue Add button.
  • Save templates/index.html.

✔️ Awesome, I've got everything!

Great. Make sure both app.py and templates/index.html are saved before moving on.

ⓧ I'd like to double check the full code

Scroll up and confirm these pieces are in place, in order:

  • app.py: the delete() route sits between the add() function and the if __name__ == '__main__': block, with blank lines separating each.
  • templates/index.html: the <th>Actions</th> header is the fourth column, and each <tr> in the body has a fourth <td> containing the Delete link with class="btn btn-danger".
Test the delete feature

Time to get rid of that typo book. You will restart the app and use the new Delete button to remove it.

  • Switch to your terminal. Stop the running app by pressing Ctrl+C.
  • Restart the app by running:
python app.py
  • Open http://127.0.0.1:5000 in your browser.

You should now see a red Delete button in the Actions column next to each book.

Before you click Delete next to the typo book, what do you expect to happen when you confirm the dialog?

  • Click Delete next to the typo book (the one you added with a mistake in the last step).
  • A dialog asks "Delete this book?" Click OK to confirm.

The page reloads and the typo book is gone. Your collection is clean again.

  • Switch to DBeaver and run this query in the SQL console to confirm the row is gone from the database too:
SELECT * FROM books;

The results grid shows only your correctly entered books. Both tools see the same data because they share the same books.db file.

Delete button not appearing?

  • Make sure you added <th>Actions</th> inside the <thead> row. Without the header, the column still renders but the layout looks broken.
  • Check that the new <td> is inside the {% for book in books %} loop. If it is outside the loop, only one Delete button renders.
  • Confirm you saved templates/index.html and restarted the app with python app.py.

help me fix my Delete button not showing up in my Flask books app

Your web app now handles both adding and removing books. You have experienced the full loop: make a change in the browser, then verify it in DBeaver. Next up, you will take on a secret mission to add even more power to your collection manager.

Secret mission

Add an Edit Feature

Right now, if a book's title has a typo or you want to update the year, you have to delete the entry and re-add it from scratch. That is clunky. In this secret mission, you will add an Edit button that lets you update any book's details directly from the web page, completing the full set of CRUD operations.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • Project folder ~/books-collection (contains app.py, templates, static files, virtual environment, and database).
  • SQLite database file books.db (inside the project folder).
  • DBeaver database connection to books.db.

Keep everything running

No action needed. Choose this if you are still actively building or want to keep adding books to your collection.

  • Your project folder, database, and DBeaver connection all stay in place.
  • To restart the app later, open a terminal, navigate to ~/books-collection, activate the virtual environment with source venv/bin/activate, and run python app.py.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and database stay on disk.

  • If the Flask dev server is still running in your terminal, press Ctrl+C to stop it.
  • Deactivate the virtual environment by running:
deactivate
  • Your project folder and database remain untouched. To resume, navigate back to ~/books-collection and activate the venv again.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your book collection database and all project files.

  • If the Flask dev server is still running, press Ctrl+C to stop it.
  • Deactivate the virtual environment if it is still active:
deactivate
  • Delete the entire project folder by running:
rm -rf ~/books-collection
  • Remove the DBeaver database connection. In DBeaver, right-click the books.db connection in the Database Navigator panel and select Delete. Confirm when prompted.
  • Confirm the folder is gone by running:
ls ~/books-collection

You should see a message saying the directory does not exist.

Nice Work!

Nice Work!

You just built a complete data-backed web application from scratch. Your personal books collection manager connects a database you designed to a styled web interface that anyone can use without writing a single line of SQL.

You've learned how to:

  • Designed a relational database from scratch in DBeaver, writing real SQL to create a table structure and insert sample data.
  • Built a Flask web application that connects to your SQLite database and displays your book collection in a styled HTML table.
  • Implemented Create (add form) and Delete (remove button) operations through the web interface, seeing how both DBeaver and your app read from the same database file.
  • Secret Mission: Added an Update (edit form) route, completing all four CRUD operations and building a fully functional book collection manager.

Ready to quiz yourself?