Build an Authors & Books Database

Design a relational SQLite database and connect it to a Flask web app.

Introduction

30 Second Summary

Think about how a library keeps track of thousands of books and who wrote them. Every time you search for a book online, a database is working behind the scenes to connect authors to their works.

In this project, you will build a local web application that stores authors and books in a database. You'll learn how to design related tables, write SQL queries, and connect everything to a browser-based interface you can interact with.

What You'll Build

A local web application powered by Flask and SQLite that lets you add, view, edit, and delete authors and their books through your browser.

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

A SQLite database file (library.db) with two related tables for authors and books.
A Python/Flask web server with routes for all CRUD operations (create, read, update, delete).
HTML templates with forms for listing, adding, and editing authors and books.
Secret Mission: Add a search bar that filters authors and books by name or genre.

Are there any prerequisites?

You should have basic Python knowledge (variables, functions, and loops). No prior experience with databases, SQL, or web development is needed.

Before We Start

Every web application starts with a solid foundation. Before you write any database queries or build web pages, you need a clean project folder, an isolated environment for your dependencies, and the right tools installed.

In this step, you will create your project folder, set up a Python virtual environment to keep your dependencies organized, install Flask (your web framework), and create the starter files you will build on throughout this project.

In this step, get ready to:

Create the project folder and virtual environment.
Install Flask.
Create the project file structure.

Create the project folder and virtual environment

A virtual environment is like a walled garden for your project's packages. It keeps this project's dependencies separate from any other Python projects on your machine.

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.

Move to your Desktop by running this command:

cd ~/Desktop

Create your project folder and move into it by running these commands:

mkdir library-app
cd library-app

Create a Python virtual environment by running this command:

macOS

python3 -m venv venv

Windows

python -m venv venv

What is a virtual environment?

The venv module creates an isolated Python environment inside your project folder. Any packages you install while the environment is active only exist inside this project.

This prevents conflicts between projects that might need different versions of the same package.

Activate the virtual environment by running this command:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

You should see (venv) appear at the beginning of your terminal prompt. This confirms the virtual environment is active.

Don't see (venv) in your prompt?

Make sure you ran the correct activation command for your operating system. On macOS, the command starts with source. On Windows, you run the activate script directly from the venv\Scripts folder.

If you see a permissions error on Windows, try running your terminal as Administrator. Help me activate my virtual environment.

Now open your project folder in VS Code. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type VS Code and press Enter to open it.
In VS Code, click File in the top menu bar.
Click Open Folder.
Navigate to your Desktop, select the library-app folder, and click Open.

Trust the folder if prompted

VS Code may ask if you trust the authors of the files in this folder. Click Yes, I trust the authors to continue.

Install Flask

Flask is the web framework that will power your application. It handles routing browser requests to the right code and rendering your HTML pages.

Open a terminal inside VS Code by pressing Ctrl+` (backtick key, usually above Tab).
Make sure you see (venv) in your terminal prompt. If you do not, activate the virtual environment again using the command from the previous substep.

Install Flask by running this command:

pip install Flask

You should see output showing Flask and its dependencies being downloaded and installed.

Verify the installation by running:

flask --version

You should see a version number for Flask, Werkzeug, and Python printed in your terminal.

Seeing 'command not found' for flask?

This usually means the virtual environment is not active. Check that (venv) appears at the start of your terminal prompt. If it does not, run the activation command again.

Help me fix my Flask installation.

Create the project file structure

Your project needs two Python files and a folder for HTML templates. Creating these now gives you a clear structure to build on.

Create the Python files and templates folder by running these commands in your VS Code terminal:

macOS

touch app.py database.py
mkdir templates

Windows

New-Item app.py, database.py
mkdir templates

What are these files for?

app.py will hold your Flask web application and all your routes (the URLs your app responds to).

database.py will contain the code that creates and connects to your SQLite database.

The templates/ folder is where Flask looks for your HTML pages by default.

Confirm the files exist by running:

ls

You should see app.py, database.py, templates, and venv in the output.

Missing a file or folder?

Check that your terminal is in the library-app folder. Run pwd (macOS) or cd (Windows) to see your current location. If you are not inside library-app, navigate there and run the file creation commands again.

Help me find my missing project files.

Your project skeleton is ready. Next up, you will design your database tables and bring them to life with SQL.

Design and Create the Database

Your environment is ready with Flask installed and your project files in place. Now it's time to build the database that stores all your authors and books.

You'll use SQLite, a database that lives in a single file on your computer. Python includes the sqlite3 module in its standard library, so there's nothing extra to install.

In this step, get ready to:

Write the database helper functions that create your tables.
Initialize and verify the database file.
Seed the database with sample authors and books.

Write the database setup script

A relational database organizes data into tables that link to each other. Your database needs two tables: one for authors and one for books. Each book connects to its author through a foreign key, which is a column that references a row in another table.

In VS Code, open database.py from the Explorer sidebar.
Add the import, database constant, and connection helper by pasting this code:

import sqlite3

DATABASE = "library.db"


def get_db():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

What does this code do?

DATABASE = "library.db" sets the filename where SQLite stores all your data.
sqlite3.connect(DATABASE) opens a connection to that file, creating it if it doesn't exist yet.
conn.row_factory = sqlite3.Row makes query results accessible by column name (like row["name"]) instead of just by position number.
PRAGMA foreign_keys = ON tells SQLite to enforce relationships between tables. SQLite doesn't do this by default.

Below the get_db() function, add the init_db() function that creates your two tables:

def init_db():
    conn = get_db()
    conn.execute("""
        CREATE TABLE IF NOT EXISTS authors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            birth_year INTEGER,
            nationality TEXT
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            year_published INTEGER,
            genre TEXT,
            author_id INTEGER NOT NULL,
            FOREIGN KEY (author_id) REFERENCES authors (id)
        )
    """)
    conn.commit()
    conn.close()

What do these tables do?

The authors table stores each author's name, birth year, and nationality. The id column auto-generates a unique number for each row (the primary key).

The books table stores title, year, and genre. Its author_id column is a foreign key that points to an author's id. This is how you link each book to the author who wrote it.

CREATE TABLE IF NOT EXISTS means the command only creates the table when it doesn't already exist. This makes it safe to run multiple times without errors.

Save database.py.

Seeing a red underline or syntax error?

Make sure the triple quotes """ are on the same line as conn.execute( and on their own line to close.
Check that the FOREIGN KEY line is inside the CREATE TABLE parentheses, not after them.
help me fix a syntax error in my database.py init_db function

Initialize the database

Your code defines how to create the tables, but hasn't actually created them yet. You need to run the init_db() function to generate the library.db file on disk.

Create the database by running this command in your terminal:

python -c "from database import init_db; init_db()"

What does this command do?

The -c flag lets you run a short Python snippet directly from the terminal without creating a script file. This imports your init_db function and calls it, which creates library.db and sets up both tables inside it.

Confirm the database file was created by running:

ls library.db

You should see library.db listed in the output.

Verify the tables were created by querying sqlite_master (SQLite's internal record of all tables):

python -c "from database import get_db; conn = get_db(); print(conn.execute('SELECT name, type FROM sqlite_master').fetchall()); conn.close()"

You should see authors and books listed as tables in the output.

Seeing ModuleNotFoundError?

Make sure your terminal is in the library-app folder where database.py lives. Run ls to check that you can see database.py in the current directory.
Confirm your virtual environment is still activated. You should see (venv) or similar at the start of your terminal prompt.
help me fix a ModuleNotFoundError when running my database.py

Seed the database with sample data

Empty tables aren't very useful for testing. You'll add a few sample authors and books so you have real data to display when building the web interface in the next steps.

Switch back to database.py in VS Code.
Below the init_db() function, add the seed_db() function:

def seed_db():
    conn = get_db()
    cursor = conn.cursor()

    cursor.execute("SELECT COUNT(*) FROM authors")
    if cursor.fetchone()[0] > 0:
        conn.close()
        return

    authors = [
        ("George Orwell", 1903, "British"),
        ("Jane Austen", 1775, "British"),
        ("Gabriel Garcia Marquez", 1927, "Colombian"),
    ]
    cursor.executemany(
        "INSERT INTO authors (name, birth_year, nationality) VALUES (?, ?, ?)",
        authors,
    )

    books = [
        ("1984", 1949, "Dystopian Fiction", 1),
        ("Animal Farm", 1945, "Political Satire", 1),
        ("Pride and Prejudice", 1813, "Romance", 2),
        ("Sense and Sensibility", 1811, "Romance", 2),
        ("One Hundred Years of Solitude", 1967, "Magical Realism", 3),
    ]
    cursor.executemany(
        "INSERT INTO books (title, year_published, genre, author_id) VALUES (?, ?, ?, ?)",
        books,
    )

    conn.commit()
    conn.close()

What does this code do?

The function first checks if authors already exist using SELECT COUNT(*). If the table already has data, it exits early to avoid creating duplicates.
cursor.executemany() inserts multiple rows in one call. The ? placeholders prevent SQL injection by safely substituting values.
Each book's last value (like 1) is the author_id foreign key linking it to the matching author. George Orwell gets id 1 because he's inserted first.

Below the seed_db() function, add the main block that runs both functions when you execute the file directly:

if __name__ == "__main__":
    init_db()
    seed_db()
    print("Database initialized and seeded successfully!")

What is name == "main"?

This Python pattern means "only run this code when the file is executed directly." If another file imports from database.py, this block won't run automatically. It gives you a convenient way to set up the database on demand.

Save database.py.
Delete the existing database file so the seed runs fresh, then run the full script:

rm library.db
python database.py

You should see the message Database initialized and seeded successfully! printed in your terminal.

Seeing an OperationalError?

If you see an error about a table already existing, make sure you deleted library.db before running the script again.
If the error mentions a column name, double-check that your INSERT statements list the same columns as your CREATE TABLE statements.
help me fix an OperationalError in my database.py seed function

✔️ Awesome, I've got everything!

Great. Double check you've saved database.py and move on to building the Flask app.

ⓧ I'd like to double check the full code

import sqlite3

DATABASE = "library.db"


def get_db():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init_db():
    conn = get_db()
    conn.execute("""
        CREATE TABLE IF NOT EXISTS authors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            birth_year INTEGER,
            nationality TEXT
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            year_published INTEGER,
            genre TEXT,
            author_id INTEGER NOT NULL,
            FOREIGN KEY (author_id) REFERENCES authors (id)
        )
    """)
    conn.commit()
    conn.close()


def seed_db():
    conn = get_db()
    cursor = conn.cursor()

    cursor.execute("SELECT COUNT(*) FROM authors")
    if cursor.fetchone()[0] > 0:
        conn.close()
        return

    authors = [
        ("George Orwell", 1903, "British"),
        ("Jane Austen", 1775, "British"),
        ("Gabriel Garcia Marquez", 1927, "Colombian"),
    ]
    cursor.executemany(
        "INSERT INTO authors (name, birth_year, nationality) VALUES (?, ?, ?)",
        authors,
    )

    books = [
        ("1984", 1949, "Dystopian Fiction", 1),
        ("Animal Farm", 1945, "Political Satire", 1),
        ("Pride and Prejudice", 1813, "Romance", 2),
        ("Sense and Sensibility", 1811, "Romance", 2),
        ("One Hundred Years of Solitude", 1967, "Magical Realism", 3),
    ]
    cursor.executemany(
        "INSERT INTO books (title, year_published, genre, author_id) VALUES (?, ?, ?, ?)",
        books,
    )

    conn.commit()
    conn.close()


if __name__ == "__main__":
    init_db()
    seed_db()
    print("Database initialized and seeded successfully!")

Your database is built, verified, and populated with sample data. Next up, you'll create the Flask web app that reads from this database and displays your authors in a browser.

Build the Flask App and Author Routes

Your database is built and loaded with sample data. Now it's time to create the web application that will let you interact with that data through your browser.

Flask is a Python web framework that maps browser requests (like visiting a URL) to Python functions. Each function handles a specific page or action, and together they form a set of CRUD operations (Create, Read, Update, Delete). In this step, you'll write the routes for viewing, adding, editing, and deleting authors.

In this step, get ready to:

Set up the Flask application with automatic database initialization.
Add a route to display all authors from the database.
Add routes to create, edit, and delete authors.

Set up the Flask application

Every Flask app starts with an app instance and some configuration. You also need to import the database helper functions you wrote in the previous step so Flask can use them.

In VS Code, open app.py and add the following code to set up the Flask application:

from flask import Flask, render_template, request, redirect, url_for
from database import get_db, init_db, seed_db

# Create the Flask application instance
app = Flask(__name__)


# Run init_db before every request to ensure tables exist
@app.before_request
def setup():
    init_db()

What does this code do?

The first two lines import everything you need. Flask creates the web app, and render_template, request, redirect, and url_for are Flask utilities you'll use in your routes.

The @app.before_request decorator tells Flask to run init_db() before handling any request. This guarantees your database tables exist, no matter which route the user visits first.

Save app.py.
You won't see a change yet. The next chunk adds the first route that uses this setup.

Now you need a route that displays all authors when someone visits the home page.

Add the index route below the setup() function in app.py:

@app.route("/")
def index():
    # Connect to the database and fetch all authors alphabetically
    conn = get_db()
    authors = conn.execute("SELECT * FROM authors ORDER BY name").fetchall()
    conn.close()
    return render_template("index.html", authors=authors)

What does this route do?

The @app.route("/") decorator maps the home URL (/) to the index() function. When someone visits your site, Flask calls this function.

Inside, you open a database connection, run a SQL query to get all authors sorted by name, then pass that data to an HTML template. The template doesn't exist yet (you'll create it in Step 5), but the route logic is ready.

Save app.py.
Verify that Flask recognizes your route by running this command in the terminal:

python -c "from app import app; print(app.url_map)"

You should see output that includes a rule for / mapped to the index endpoint.

Seeing an ImportError?

Make sure your virtual environment is still activated. You should see (venv) at the start of your terminal prompt.

Also check that both app.py and database.py are in the same library-app folder. If they're in different locations, the from database import line will fail.

Help me debug my Flask import error.

Add the "Add Author" route

The index route only reads data. To let users create new authors, you need a route that handles two scenarios: showing the blank form (a GET request) and processing the submitted form data (a POST request).

Add the add_author route below the index() function in app.py:

@app.route("/authors/add", methods=["GET", "POST"])
def add_author():
    if request.method == "POST":
        # Extract form data submitted by the user
        name = request.form["name"]
        birth_year = request.form.get("birth_year") or None
        nationality = request.form.get("nationality") or None
        # Insert the new author into the database
        conn = get_db()
        conn.execute(
            "INSERT INTO authors (name, birth_year, nationality) VALUES (?, ?, ?)",
            (name, birth_year, nationality),
        )
        conn.commit()
        conn.close()
        # Send the user back to the homepage
        return redirect(url_for("index"))
    # If GET request, show the empty form
    return render_template("add_author.html")

What does this route do?

The methods=["GET", "POST"] parameter tells Flask this route accepts both types of requests. A GET request shows the form, and a POST request processes the submitted data.

The request.form dictionary holds the values the user typed into the HTML form. After inserting the data, redirect(url_for("index")) sends the user back to the homepage so they can see their new author in the list.

The or None pattern converts empty strings (from blank form fields) into None, which stores as NULL in the database.

Save app.py.
Verify that Flask recognizes the new route by running:

python -c "from app import app; print(app.url_map)"

You should now see /authors/add in the output alongside the original / route.

Route not appearing in the output?

Check that your add_author function is at the same indentation level as index() (not nested inside it). Also make sure the @app.route decorator is directly above the function with no blank lines between them.

Help me fix my Flask route registration.

Add the "Edit Author" and "Delete Author" routes

To complete the author management features, you need an edit route that pre-fills a form with existing data and a delete route that removes an author and all their books.

Add the edit_author route below the add_author() function in app.py:

@app.route("/authors/<int:author_id>/edit", methods=["GET", "POST"])
def edit_author(author_id):
    conn = get_db()
    if request.method == "POST":
        # Extract updated values from the form
        name = request.form["name"]
        birth_year = request.form.get("birth_year") or None
        nationality = request.form.get("nationality") or None
        # Update the author's record in the database
        conn.execute(
            "UPDATE authors SET name = ?, birth_year = ?, nationality = ? WHERE id = ?",
            (name, birth_year, nationality, author_id),
        )
        conn.commit()
        conn.close()
        return redirect(url_for("index"))
    # If GET request, fetch the author's current data to pre-fill the form
    author = conn.execute(
        "SELECT * FROM authors WHERE id = ?", (author_id,)
    ).fetchone()
    conn.close()
    return render_template("edit_author.html", author=author)

What does this route do?

The <int:author_id> part of the URL is a dynamic parameter. Flask extracts the number from the URL (like /authors/2/edit) and passes it to your function as author_id.

On a GET request, the function fetches the author's current data and sends it to the template so the form fields are pre-filled. On a POST request, it updates the database with the new values.

Save app.py.
You won't verify this one separately. The next chunk adds the delete route, and then you'll confirm all routes together.

The delete route is simpler because it only handles POST requests (you never want a GET request to delete data accidentally).

Add the delete_author route below the edit_author() function in app.py:

@app.route("/authors/<int:author_id>/delete", methods=["POST"])
def delete_author(author_id):
    conn = get_db()
    # Delete the author's books first to maintain referential integrity
    conn.execute("DELETE FROM books WHERE author_id = ?", (author_id,))
    # Then delete the author
    conn.execute("DELETE FROM authors WHERE id = ?", (author_id,))
    conn.commit()
    conn.close()
    return redirect(url_for("index"))

Why delete books first?

The books table has a foreign key pointing to authors. If you delete an author while their books still reference them, the database would have orphaned records. Deleting the books first keeps your data consistent.

Save app.py.
Verify all your routes are registered by running:

python -c "from app import app; print(app.url_map)"

You should see rules for /, /authors/add, /authors/<author_id>/edit, and /authors/<author_id>/delete in the output.

Seeing a SyntaxError?

Python will show the exact line number where the error is. Common causes include missing closing parentheses on conn.execute() calls, mismatched quotes, or incorrect indentation inside the if request.method == "POST": block.

Help me fix my Flask app syntax error.

✔️ Awesome, I've got everything!

Great. Make sure you've saved app.py before moving on.

ⓧ I'd like to double check the full code

Here's the complete app.py file up to this point:

from flask import Flask, render_template, request, redirect, url_for
from database import get_db, init_db, seed_db

app = Flask(__name__)


@app.before_request
def setup():
    init_db()


@app.route("/")
def index():
    conn = get_db()
    authors = conn.execute("SELECT * FROM authors ORDER BY name").fetchall()
    conn.close()
    return render_template("index.html", authors=authors)


@app.route("/authors/add", methods=["GET", "POST"])
def add_author():
    if request.method == "POST":
        name = request.form["name"]
        birth_year = request.form.get("birth_year") or None
        nationality = request.form.get("nationality") or None
        conn = get_db()
        conn.execute(
            "INSERT INTO authors (name, birth_year, nationality) VALUES (?, ?, ?)",
            (name, birth_year, nationality),
        )
        conn.commit()
        conn.close()
        return redirect(url_for("index"))
    return render_template("add_author.html")


@app.route("/authors/<int:author_id>/edit", methods=["GET", "POST"])
def edit_author(author_id):
    conn = get_db()
    if request.method == "POST":
        name = request.form["name"]
        birth_year = request.form.get("birth_year") or None
        nationality = request.form.get("nationality") or None
        conn.execute(
            "UPDATE authors SET name = ?, birth_year = ?, nationality = ? WHERE id = ?",
            (name, birth_year, nationality, author_id),
        )
        conn.commit()
        conn.close()
        return redirect(url_for("index"))
    author = conn.execute(
        "SELECT * FROM authors WHERE id = ?", (author_id,)
    ).fetchone()
    conn.close()
    return render_template("edit_author.html", author=author)


@app.route("/authors/<int:author_id>/delete", methods=["POST"])
def delete_author(author_id):
    conn = get_db()
    conn.execute("DELETE FROM books WHERE author_id = ?", (author_id,))
    conn.execute("DELETE FROM authors WHERE id = ?", (author_id,))
    conn.commit()
    conn.close()
    return redirect(url_for("index"))

Your author routes are locked in. Next up, you'll add the book routes so users can manage books linked to each author.

Add Book Routes

Your author routes handle creating, reading, updating, and deleting authors. Now you need the same CRUD capabilities for books.

Book routes are slightly different from author routes because every book belongs to an author. Each book route needs to know which author it relates to, creating a parent-child relationship in your URL structure.

In this step, get ready to:

Create a route to view all books for a specific author.
Add a route to insert new books linked to an author.
Build edit and delete routes for individual books.

Create the "View Books" route

When someone clicks on an author, you want to show all the books that author has written. This means querying the books table filtered by author_id.

In app.py, scroll down to the bottom of the file (after the delete_author route).
Add the view books route by copying this code at the bottom:

@app.route("/authors/<int:author_id>/books")
def view_books(author_id):
    conn = get_db()
    # Fetch the author's details to display their name
    author = conn.execute(
        "SELECT * FROM authors WHERE id = ?", (author_id,)
    ).fetchone()
    # Fetch all books for this author, sorted by publication year
    books = conn.execute(
        "SELECT * FROM books WHERE author_id = ? ORDER BY year_published",
        (author_id,),
    ).fetchall()
    conn.close()
    return render_template("books.html", author=author, books=books)

What does this code do?

The URL pattern /authors/<int:author_id>/books captures the author's ID directly from the URL. Flask automatically converts it to an integer and passes it to your function.

The route queries both the author (for their name) and their books (filtered by author_id). The ORDER BY year_published sorts books chronologically.

Save app.py.

You won't see a change yet. The next route builds on this same pattern for adding books.

Add the "Add Book" route

Just like your add-author route, the add-book route needs to handle two scenarios. A GET request shows the empty form, and a POST request processes the submitted data and inserts a new book into the database.

Below the view_books route you just added, paste the add book route:

@app.route("/authors/<int:author_id>/books/add", methods=["GET", "POST"])
def add_book(author_id):
    conn = get_db()
    if request.method == "POST":
        # Extract form data from the submitted book form
        title = request.form["title"]
        year_published = request.form.get("year_published") or None
        genre = request.form.get("genre") or None
        # Insert the new book linked to the current author
        conn.execute(
            "INSERT INTO books (title, year_published, genre, author_id) VALUES (?, ?, ?, ?)",
            (title, year_published, genre, author_id),
        )
        conn.commit()
        conn.close()
        # Redirect back to the author's book list
        return redirect(url_for("view_books", author_id=author_id))
    # For GET requests, fetch the author to display their name on the form
    author = conn.execute(
        "SELECT * FROM authors WHERE id = ?", (author_id,)
    ).fetchone()
    conn.close()
    return render_template("add_book.html", author=author)

What does this code do?

This route uses the same GET/POST pattern as add_author. The key difference is the author_id parameter in both the URL and the INSERT statement. This links the new book to its author.

After a successful insert, redirect(url_for("view_books", author_id=author_id)) sends the user back to that author's book list so they can see their new entry.

Save app.py.
Verify there are no syntax errors by running this in your terminal:

python -c "from app import app; print('Routes loaded successfully!')"

You should see Routes loaded successfully! printed in the terminal.

Seeing a SyntaxError?

Check that each route decorator starts with @app.route and that the function definition on the next line is indented correctly.

Make sure every opening parenthesis and quotation mark has a matching closing one. A missing closing quote on a SQL string is a common culprit.

Help me debug my Flask route syntax error.

Add the "Edit Book" and "Delete Book" routes

The final two routes let users modify an existing book's details or remove a book entirely. These routes use the book's own ID rather than the author's ID, since you're targeting a specific book record.

Below the add_book route, paste the edit book route:

@app.route("/books/<int:book_id>/edit", methods=["GET", "POST"])
def edit_book(book_id):
    conn = get_db()
    if request.method == "POST":
        # Extract updated values from the form
        title = request.form["title"]
        year_published = request.form.get("year_published") or None
        genre = request.form.get("genre") or None
        # Update the book record in the database
        conn.execute(
            "UPDATE books SET title = ?, year_published = ?, genre = ? WHERE id = ?",
            (title, year_published, genre, book_id),
        )
        conn.commit()
        # Fetch the book to get its author_id for the redirect
        book = conn.execute(
            "SELECT * FROM books WHERE id = ?", (book_id,)
        ).fetchone()
        conn.close()
        return redirect(url_for("view_books", author_id=book["author_id"]))
    # For GET requests, fetch the book to pre-fill the form
    book = conn.execute("SELECT * FROM books WHERE id = ?", (book_id,)).fetchone()
    conn.close()
    return render_template("edit_book.html", book=book)

What does this code do?

The edit route uses /books/<int:book_id>/edit instead of nesting under authors. This is because you're editing a specific book regardless of which author page you came from.

After updating, the route fetches the book again to get its author_id. This tells Flask which author's book list to redirect the user back to.

Save app.py.

You won't see a change yet. The delete route below completes the book CRUD operations.

Below the edit_book route, paste the delete book route:

@app.route("/books/<int:book_id>/delete", methods=["POST"])
def delete_book(book_id):
    conn = get_db()
    # Fetch the book first to get its author_id for the redirect
    book = conn.execute("SELECT * FROM books WHERE id = ?", (book_id,)).fetchone()
    author_id = book["author_id"]
    # Delete the book from the database
    conn.execute("DELETE FROM books WHERE id = ?", (book_id,))
    conn.commit()
    conn.close()
    return redirect(url_for("view_books", author_id=author_id))

What does this code do?

The delete route only accepts POST requests. This prevents accidental deletions from someone simply visiting a URL. The route fetches the book first to grab the author_id before deleting, since after deletion that information is gone.

Why only POST for delete?

GET requests should never change data. If a delete action used GET, a web crawler or a browser pre-fetching links could accidentally delete records. Restricting to POST means the action only happens when a user deliberately submits a form.

Save app.py.
Confirm all your routes load without errors by running:

python -c "from app import app; print('All routes loaded successfully!')"

You should see All routes loaded successfully! in the terminal.

Seeing an ImportError or IndentationError?

Make sure each new route function is at the top level of the file (no extra indentation). Every @app.route decorator and its def line should start at column zero.

Check that you did not accidentally paste a route inside another function. Each route should be its own separate function.

Help me fix my Flask import or indentation error.

✔️ Awesome, I've got everything!

Great. Double check you've saved app.py before moving on.

ⓧ I'd like to double check the full code

from flask import Flask, render_template, request, redirect, url_for
from database import get_db, init_db, seed_db

app = Flask(__name__)


@app.before_request
def setup():
    init_db()


@app.route("/")
def index():
    conn = get_db()
    authors = conn.execute("SELECT * FROM authors ORDER BY name").fetchall()
    conn.close()
    return render_template("index.html", authors=authors)


@app.route("/authors/add", methods=["GET", "POST"])
def add_author():
    if request.method == "POST":
        name = request.form["name"]
        birth_year = request.form.get("birth_year") or None
        nationality = request.form.get("nationality") or None
        conn = get_db()
        conn.execute(
            "INSERT INTO authors (name, birth_year, nationality) VALUES (?, ?, ?)",
            (name, birth_year, nationality),
        )
        conn.commit()
        conn.close()
        return redirect(url_for("index"))
    return render_template("add_author.html")


@app.route("/authors/<int:author_id>/edit", methods=["GET", "POST"])
def edit_author(author_id):
    conn = get_db()
    if request.method == "POST":
        name = request.form["name"]
        birth_year = request.form.get("birth_year") or None
        nationality = request.form.get("nationality") or None
        conn.execute(
            "UPDATE authors SET name = ?, birth_year = ?, nationality = ? WHERE id = ?",
            (name, birth_year, nationality, author_id),
        )
        conn.commit()
        conn.close()
        return redirect(url_for("index"))
    author = conn.execute(
        "SELECT * FROM authors WHERE id = ?", (author_id,)
    ).fetchone()
    conn.close()
    return render_template("edit_author.html", author=author)


@app.route("/authors/<int:author_id>/delete", methods=["POST"])
def delete_author(author_id):
    conn = get_db()
    conn.execute("DELETE FROM books WHERE author_id = ?", (author_id,))
    conn.execute("DELETE FROM authors WHERE id = ?", (author_id,))
    conn.commit()
    conn.close()
    return redirect(url_for("index"))


@app.route("/authors/<int:author_id>/books")
def view_books(author_id):
    conn = get_db()
    author = conn.execute(
        "SELECT * FROM authors WHERE id = ?", (author_id,)
    ).fetchone()
    books = conn.execute(
        "SELECT * FROM books WHERE author_id = ? ORDER BY year_published",
        (author_id,),
    ).fetchall()
    conn.close()
    return render_template("books.html", author=author, books=books)


@app.route("/authors/<int:author_id>/books/add", methods=["GET", "POST"])
def add_book(author_id):
    conn = get_db()
    if request.method == "POST":
        title = request.form["title"]
        year_published = request.form.get("year_published") or None
        genre = request.form.get("genre") or None
        conn.execute(
            "INSERT INTO books (title, year_published, genre, author_id) VALUES (?, ?, ?, ?)",
            (title, year_published, genre, author_id),
        )
        conn.commit()
        conn.close()
        return redirect(url_for("view_books", author_id=author_id))
    author = conn.execute(
        "SELECT * FROM authors WHERE id = ?", (author_id,)
    ).fetchone()
    conn.close()
    return render_template("add_book.html", author=author)


@app.route("/books/<int:book_id>/edit", methods=["GET", "POST"])
def edit_book(book_id):
    conn = get_db()
    if request.method == "POST":
        title = request.form["title"]
        year_published = request.form.get("year_published") or None
        genre = request.form.get("genre") or None
        conn.execute(
            "UPDATE books SET title = ?, year_published = ?, genre = ? WHERE id = ?",
            (title, year_published, genre, book_id),
        )
        conn.commit()
        book = conn.execute(
            "SELECT * FROM books WHERE id = ?", (book_id,)
        ).fetchone()
        conn.close()
        return redirect(url_for("view_books", author_id=book["author_id"]))
    book = conn.execute("SELECT * FROM books WHERE id = ?", (book_id,)).fetchone()
    conn.close()
    return render_template("edit_book.html", book=book)


@app.route("/books/<int:book_id>/delete", methods=["POST"])
def delete_book(book_id):
    conn = get_db()
    book = conn.execute("SELECT * FROM books WHERE id = ?", (book_id,)).fetchone()
    author_id = book["author_id"]
    conn.execute("DELETE FROM books WHERE id = ?", (book_id,))
    conn.commit()
    conn.close()
    return redirect(url_for("view_books", author_id=author_id))

All your backend routes are now complete. Both authors and books have full CRUD functionality. Next up, you'll create the HTML templates that give these routes a visible face in the browser.

Create the HTML Templates

Your Flask routes are ready, but they reference templates that don't exist yet.

In this step, you'll create the HTML templates that give your app its visual interface. Flask uses Jinja2 template inheritance, so you'll build a shared base layout once, then extend it for every page.

In this step, get ready to:

Build a shared base layout and the authors list page.
Create forms for adding and editing authors.
Create templates for viewing, adding, and editing books.

Build the base layout and authors list page

Template inheritance lets you define shared structure (navigation, styles) in one file and reuse it across every page.

In VS Code, create a new file inside the templates/ folder called base.html.
Paste the following code into base.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Library App</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 0 auto;
            padding: 20px;
            background-color: #f5f5f5;
        }
        h1, h2 {
            color: #333;
        }
        nav {
            background-color: #333;
            padding: 10px 20px;
            margin-bottom: 20px;
            border-radius: 5px;
        }
        nav a {
            color: white;
            text-decoration: none;
            margin-right: 15px;
        }
        nav a:hover {
            text-decoration: underline;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            background-color: white;
            border-radius: 5px;
            overflow: hidden;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
        }
        th, td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #eee;
        }
        th {
            background-color: #333;
            color: white;
        }
        form {
            background-color: white;
            padding: 20px;
            border-radius: 5px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
        }
        label {
            display: block;
            margin-bottom: 5px;
            font-weight: bold;
        }
        input[type="text"], input[type="number"] {
            width: 100%;
            padding: 8px;
            margin-bottom: 15px;
            border: 1px solid #ddd;
            border-radius: 3px;
            box-sizing: border-box;
        }
        button, .btn {
            padding: 8px 16px;
            border: none;
            border-radius: 3px;
            cursor: pointer;
            text-decoration: none;
            font-size: 14px;
            display: inline-block;
        }
        .btn-primary {
            background-color: #4CAF50;
            color: white;
        }
        .btn-edit {
            background-color: #2196F3;
            color: white;
        }
        .btn-delete {
            background-color: #f44336;
            color: white;
        }
        .btn-back {
            background-color: #666;
            color: white;
        }
        button:hover, .btn:hover {
            opacity: 0.9;
        }
        .actions {
            display: flex;
            gap: 5px;
        }
    </style>
</head>
<body>
    <nav>
        <a href="/">Home</a>
        <a href="/authors/add">Add Author</a>
    </nav>
    {% block content %}{% endblock %}
</body>
</html>

What does this template do?

This file defines the shared shell for every page in your app. It includes:

A <style> block with CSS for tables, forms, buttons, and layout.
A <nav> bar with links to the home page and the add-author form.
A {% block content %}{% endblock %} placeholder where child templates inject their unique content.

Save base.html.
Confirm that base.html appears inside the templates/ folder in your VS Code Explorer sidebar.

Don't see templates/ in your sidebar?

Make sure you created the file inside the templates/ folder, not in the project root. Flask expects templates in a folder named exactly templates (lowercase, plural).

If the folder does not exist, right-click in the Explorer sidebar and select New Folder, then name it templates.

help me find where my template files should go

Create another new file inside templates/ called index.html.
Paste the following code into index.html:

{% extends "base.html" %}

{% block content %}
<h1>Authors</h1>

{% if authors %}
<table>
    <thead>
        <tr>
            <th>Name</th>
            <th>Birth Year</th>
            <th>Nationality</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for author in authors %}
        <tr>
            <td>{{ author["name"] }}</td>
            <td>{{ author["birth_year"] or "Unknown" }}</td>
            <td>{{ author["nationality"] or "Unknown" }}</td>
            <td class="actions">
                <a href="/authors/{{ author['id'] }}/books" class="btn btn-primary">Books</a>
                <a href="/authors/{{ author['id'] }}/edit" class="btn btn-edit">Edit</a>
                <form action="/authors/{{ author['id'] }}/delete" method="POST" style="display:inline;">
                    <button type="submit" class="btn btn-delete" onclick="return confirm('Delete this author and all their books?')">Delete</button>
                </form>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% else %}
<p>No authors yet. <a href="/authors/add">Add one!</a></p>
{% endif %}
{% endblock %}

What does this template do?

This is your app's home page. It extends base.html to inherit the shared layout, then fills in the content block with a table of authors.

The {% for author in authors %} loop iterates over the list passed from your index() route.
Each row shows the author's name, birth year, and nationality, with buttons to view books, edit, or delete.
The {% if authors %} check handles the empty state gracefully with a message and link.

Save index.html.
Confirm that both base.html and index.html appear inside templates/ in your Explorer sidebar.

Seeing a red underline in the Jinja2 syntax?

VS Code may not recognize Jinja2 syntax by default. The file still works. The {% %} and {{ }} tags are processed by Flask at runtime, not by your editor.

help me understand the Jinja2 syntax errors in my editor

✔️ Awesome, I've got everything!

Great. Make sure both files are saved before moving on.

ⓧ I'd like to double check the full code

Here is base.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Library App</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 0 auto;
            padding: 20px;
            background-color: #f5f5f5;
        }
        h1, h2 {
            color: #333;
        }
        nav {
            background-color: #333;
            padding: 10px 20px;
            margin-bottom: 20px;
            border-radius: 5px;
        }
        nav a {
            color: white;
            text-decoration: none;
            margin-right: 15px;
        }
        nav a:hover {
            text-decoration: underline;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            background-color: white;
            border-radius: 5px;
            overflow: hidden;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
        }
        th, td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #eee;
        }
        th {
            background-color: #333;
            color: white;
        }
        form {
            background-color: white;
            padding: 20px;
            border-radius: 5px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
        }
        label {
            display: block;
            margin-bottom: 5px;
            font-weight: bold;
        }
        input[type="text"], input[type="number"] {
            width: 100%;
            padding: 8px;
            margin-bottom: 15px;
            border: 1px solid #ddd;
            border-radius: 3px;
            box-sizing: border-box;
        }
        button, .btn {
            padding: 8px 16px;
            border: none;
            border-radius: 3px;
            cursor: pointer;
            text-decoration: none;
            font-size: 14px;
            display: inline-block;
        }
        .btn-primary {
            background-color: #4CAF50;
            color: white;
        }
        .btn-edit {
            background-color: #2196F3;
            color: white;
        }
        .btn-delete {
            background-color: #f44336;
            color: white;
        }
        .btn-back {
            background-color: #666;
            color: white;
        }
        button:hover, .btn:hover {
            opacity: 0.9;
        }
        .actions {
            display: flex;
            gap: 5px;
        }
    </style>
</head>
<body>
    <nav>
        <a href="/">Home</a>
        <a href="/authors/add">Add Author</a>
    </nav>
    {% block content %}{% endblock %}
</body>
</html>

Here is index.html:

{% extends "base.html" %}

{% block content %}
<h1>Authors</h1>

{% if authors %}
<table>
    <thead>
        <tr>
            <th>Name</th>
            <th>Birth Year</th>
            <th>Nationality</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for author in authors %}
        <tr>
            <td>{{ author["name"] }}</td>
            <td>{{ author["birth_year"] or "Unknown" }}</td>
            <td>{{ author["nationality"] or "Unknown" }}</td>
            <td class="actions">
                <a href="/authors/{{ author['id'] }}/books" class="btn btn-primary">Books</a>
                <a href="/authors/{{ author['id'] }}/edit" class="btn btn-edit">Edit</a>
                <form action="/authors/{{ author['id'] }}/delete" method="POST" style="display:inline;">
                    <button type="submit" class="btn btn-delete" onclick="return confirm('Delete this author and all their books?')">Delete</button>
                </form>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% else %}
<p>No authors yet. <a href="/authors/add">Add one!</a></p>
{% endif %}
{% endblock %}

Build the author form templates

Your add and edit routes need forms where users type in author details. Each form uses the same field names that your Flask routes expect in request.form.

Create a new file inside templates/ called add_author.html.
Paste the following code into add_author.html:

{% extends "base.html" %}

{% block content %}
<h1>Add Author</h1>

<form method="POST">
    <label for="name">Name (required):</label>
    <input type="text" id="name" name="name" required>

    <label for="birth_year">Birth Year:</label>
    <input type="number" id="birth_year" name="birth_year">

    <label for="nationality">Nationality:</label>
    <input type="text" id="nationality" name="nationality">

    <button type="submit" class="btn btn-primary">Add Author</button>
    <a href="/" class="btn btn-back">Cancel</a>
</form>
{% endblock %}

What does this form do?

The method="POST" attribute tells the browser to send the form data as a POST request to the same URL.
Each name attribute on the inputs (name, birth_year, nationality) matches the keys your add_author() route reads from request.form.
The required attribute on the name input prevents form submission if it is left empty.

Save add_author.html.
Confirm that add_author.html appears in the templates/ folder in your Explorer sidebar.

File not appearing in the right place?

Double-check that you created the file inside templates/ and not in the project root. The path should be templates/add_author.html.

help me organize my template files correctly

Create another file inside templates/ called edit_author.html.
Paste the following code into edit_author.html:

{% extends "base.html" %}

{% block content %}
<h1>Edit Author</h1>

<form method="POST">
    <label for="name">Name (required):</label>
    <input type="text" id="name" name="name" value="{{ author['name'] }}" required>

    <label for="birth_year">Birth Year:</label>
    <input type="number" id="birth_year" name="birth_year" value="{{ author['birth_year'] or '' }}">

    <label for="nationality">Nationality:</label>
    <input type="text" id="nationality" name="nationality" value="{{ author['nationality'] or '' }}">

    <button type="submit" class="btn btn-primary">Save Changes</button>
    <a href="/" class="btn btn-back">Cancel</a>
</form>
{% endblock %}

How is this different from add_author.html?

The edit form pre-fills each input with the author's existing data using value="{{ author['name'] }}". This way the user sees the current values and only changes what they need to.

The author variable is passed from your edit_author() route's render_template() call.

Save edit_author.html.
You should now see add_author.html and edit_author.html alongside base.html and index.html in your templates/ folder.

Typo in the value attribute?

Make sure the value attributes use the exact keys from your database columns: author['name'], author['birth_year'], author['nationality']. A misspelled key causes a blank field or an error at runtime.

help me fix my edit author template

✔️ Awesome, I've got everything!

Great. Make sure both author form files are saved before moving on.

ⓧ I'd like to double check the full code

Here is add_author.html:

{% extends "base.html" %}

{% block content %}
<h1>Add Author</h1>

<form method="POST">
    <label for="name">Name (required):</label>
    <input type="text" id="name" name="name" required>

    <label for="birth_year">Birth Year:</label>
    <input type="number" id="birth_year" name="birth_year">

    <label for="nationality">Nationality:</label>
    <input type="text" id="nationality" name="nationality">

    <button type="submit" class="btn btn-primary">Add Author</button>
    <a href="/" class="btn btn-back">Cancel</a>
</form>
{% endblock %}

Here is edit_author.html:

{% extends "base.html" %}

{% block content %}
<h1>Edit Author</h1>

<form method="POST">
    <label for="name">Name (required):</label>
    <input type="text" id="name" name="name" value="{{ author['name'] }}" required>

    <label for="birth_year">Birth Year:</label>
    <input type="number" id="birth_year" name="birth_year" value="{{ author['birth_year'] or '' }}">

    <label for="nationality">Nationality:</label>
    <input type="text" id="nationality" name="nationality" value="{{ author['nationality'] or '' }}">

    <button type="submit" class="btn btn-primary">Save Changes</button>
    <a href="/" class="btn btn-back">Cancel</a>
</form>
{% endblock %}

Build the book templates

Books belong to authors, so these templates need to know which author they relate to. Your routes pass the author object into each template so it can display the author's name and link back to the right page.

Create a new file inside templates/ called books.html.
Paste the following code into books.html:

{% extends "base.html" %}

{% block content %}
<h1>Books by {{ author["name"] }}</h1>

<a href="/authors/{{ author['id'] }}/books/add" class="btn btn-primary" style="margin-bottom: 15px; display: inline-block;">Add Book</a>
<a href="/" class="btn btn-back" style="margin-bottom: 15px; display: inline-block;">Back to Authors</a>

{% if books %}
<table>
    <thead>
        <tr>
            <th>Title</th>
            <th>Year Published</th>
            <th>Genre</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for book in books %}
        <tr>
            <td>{{ book["title"] }}</td>
            <td>{{ book["year_published"] or "Unknown" }}</td>
            <td>{{ book["genre"] or "Unknown" }}</td>
            <td class="actions">
                <a href="/books/{{ book['id'] }}/edit" class="btn btn-edit">Edit</a>
                <form action="/books/{{ book['id'] }}/delete" method="POST" style="display:inline;">
                    <button type="submit" class="btn btn-delete" onclick="return confirm('Delete this book?')">Delete</button>
                </form>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% else %}
<p>No books yet for this author. <a href="/authors/{{ author['id'] }}/books/add">Add one!</a></p>
{% endif %}
{% endblock %}

What does this template do?

The heading shows the author's name dynamically: Books by {{ author["name"] }}.
The Add Book button links to the add-book route scoped to this specific author.
Each book row has Edit and Delete buttons, similar to the authors table on the home page.

Save books.html.
Confirm it appears in your templates/ folder.

URLs look confusing?

The URLs like /authors/{{ author['id'] }}/books/add match the routes you defined in app.py. The {{ author['id'] }} part gets replaced with the actual author ID number when the page renders.

help me understand how Flask template URLs work

Create a new file inside templates/ called add_book.html.
Paste the following code into add_book.html:

{% extends "base.html" %}

{% block content %}
<h1>Add Book for {{ author["name"] }}</h1>

<form method="POST">
    <label for="title">Title (required):</label>
    <input type="text" id="title" name="title" required>

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

    <label for="genre">Genre:</label>
    <input type="text" id="genre" name="genre">

    <button type="submit" class="btn btn-primary">Add Book</button>
    <a href="/authors/{{ author['id'] }}/books" class="btn btn-back">Cancel</a>
</form>
{% endblock %}

What does this form do?

This form collects a book's title, year, and genre. The heading includes the author's name so the user knows who they are adding a book for.

The Cancel link takes the user back to that author's book list, not to the home page.

Save add_book.html.

Create the final template file inside templates/ called edit_book.html.
Paste the following code into edit_book.html:

{% extends "base.html" %}

{% block content %}
<h1>Edit Book</h1>

<form method="POST">
    <label for="title">Title (required):</label>
    <input type="text" id="title" name="title" value="{{ book['title'] }}" required>

    <label for="year_published">Year Published:</label>
    <input type="number" id="year_published" name="year_published" value="{{ book['year_published'] or '' }}">

    <label for="genre">Genre:</label>
    <input type="text" id="genre" name="genre" value="{{ book['genre'] or '' }}">

    <button type="submit" class="btn btn-primary">Save Changes</button>
    <a href="/authors/{{ book['author_id'] }}/books" class="btn btn-back">Cancel</a>
</form>
{% endblock %}

How does the cancel link know which author to go back to?

The book object includes an author_id field (the foreign key you defined in your database schema). The cancel link uses book['author_id'] to navigate back to the correct author's book list.

Save edit_book.html.
Verify all your template files exist by running this in your terminal:

ls templates/

You should see seven files: add_author.html, add_book.html, base.html, books.html, edit_author.html, edit_book.html, and index.html.

Missing a file?

If you see fewer than seven files, check that you saved every template. Also confirm you ran ls templates/ (with the trailing slash) from your library-app project folder, not from inside the templates folder itself.

help me find my missing template files

✔️ Awesome, I've got everything!

Great. Make sure all seven files are saved before moving on.

ⓧ I'd like to double check the full code

Here is books.html:

{% extends "base.html" %}

{% block content %}
<h1>Books by {{ author["name"] }}</h1>

<a href="/authors/{{ author['id'] }}/books/add" class="btn btn-primary" style="margin-bottom: 15px; display: inline-block;">Add Book</a>
<a href="/" class="btn btn-back" style="margin-bottom: 15px; display: inline-block;">Back to Authors</a>

{% if books %}
<table>
    <thead>
        <tr>
            <th>Title</th>
            <th>Year Published</th>
            <th>Genre</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for book in books %}
        <tr>
            <td>{{ book["title"] }}</td>
            <td>{{ book["year_published"] or "Unknown" }}</td>
            <td>{{ book["genre"] or "Unknown" }}</td>
            <td class="actions">
                <a href="/books/{{ book['id'] }}/edit" class="btn btn-edit">Edit</a>
                <form action="/books/{{ book['id'] }}/delete" method="POST" style="display:inline;">
                    <button type="submit" class="btn btn-delete" onclick="return confirm('Delete this book?')">Delete</button>
                </form>
            </td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% else %}
<p>No books yet for this author. <a href="/authors/{{ author['id'] }}/books/add">Add one!</a></p>
{% endif %}
{% endblock %}

Here is add_book.html:

{% extends "base.html" %}

{% block content %}
<h1>Add Book for {{ author["name"] }}</h1>

<form method="POST">
    <label for="title">Title (required):</label>
    <input type="text" id="title" name="title" required>

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

    <label for="genre">Genre:</label>
    <input type="text" id="genre" name="genre">

    <button type="submit" class="btn btn-primary">Add Book</button>
    <a href="/authors/{{ author['id'] }}/books" class="btn btn-back">Cancel</a>
</form>
{% endblock %}

Here is edit_book.html:

{% extends "base.html" %}

{% block content %}
<h1>Edit Book</h1>

<form method="POST">
    <label for="title">Title (required):</label>
    <input type="text" id="title" name="title" value="{{ book['title'] }}" required>

    <label for="year_published">Year Published:</label>
    <input type="number" id="year_published" name="year_published" value="{{ book['year_published'] or '' }}">

    <label for="genre">Genre:</label>
    <input type="text" id="genre" name="genre" value="{{ book['genre'] or '' }}">

    <button type="submit" class="btn btn-primary">Save Changes</button>
    <a href="/authors/{{ book['author_id'] }}/books" class="btn btn-back">Cancel</a>
</form>
{% endblock %}

All seven templates are in place. Next up, you'll start the Flask development server and see your app come to life in the browser.

Run and Test Your Application

You have all your templates built and every route wired up. The database is seeded with sample data, and Flask is installed and ready to go.

Now it's time to start the server and actually use your app in the browser. You'll walk through every feature you built, from adding authors to deleting books, and confirm your data survives a server restart.

In this step, get ready to:

Start the Flask development server and load the app in your browser.
Test all CRUD operations: adding, viewing, editing, and deleting authors and books.
Verify your database file persists data independently of the server.

Start the Flask development server

Flask includes a built-in development server that watches your code for changes and automatically reloads when you save a file. The --debug flag enables this auto-reload behavior along with an interactive debugger that shows helpful error messages directly in the browser.

Make sure your virtual environment is still activated (you should see (venv) at the start of your terminal prompt).
Start the Flask development server by running this command:

flask --app app run --debug

What does this command do?

flask is the CLI tool that comes with Flask.
--app app tells Flask to look in app.py for your application. Since the file is named app.py, Flask would find it automatically, but including the flag makes the command explicit.
run starts the development server.
--debug enables the interactive debugger and auto-reload on code changes.

You should see output in your terminal that includes the line Running on http://127.0.0.1:5000.
Open your web browser and navigate to http://127.0.0.1:5000.

You should see the Authors page with a table listing George Orwell, Jane Austen, and Gabriel Garcia Marquez. The dark navigation bar at the top shows Home and Add Author links.

Don't see the authors table?

If you see "No authors yet", your database might not be seeded. Stop the server with Ctrl+C, run python database.py to seed it, then start the server again.
If you see an error about a missing module, make sure your virtual environment is activated. Look for (venv) in your terminal prompt.
If the page won't load at all, check that the terminal shows the server is running. Make sure no other process is using port 5000.

Help me troubleshoot my Flask server not starting correctly.

Test all the features

With the server running, you can now exercise every CRUD operation you built. Walk through the full lifecycle: creating records, viewing them, editing them, and deleting them.

Click Add Author in the navigation bar.
Enter a name like Toni Morrison, a birth year of 1931, and a nationality of American.
Click Add Author to submit the form.

You should be redirected back to the authors list, and your new author now appears in the table.

Click the green Books button next to your new author.
Click Add Book.
Enter a title like Beloved, a year of 1987, and a genre of Historical Fiction.
Click Add Book to submit.

You should see the book listed in the table on that author's books page.

Click the blue Edit button next to the book you just added.
Change the genre to Literary Fiction and click Save Changes.
Confirm the genre column now shows the updated value.

Click the red Delete button next to the book.
A confirmation dialog appears asking if you want to delete. Click OK.
The book disappears from the table.

Why test persistence?

SQLite stores data in a file on disk, not in memory. This means your data survives even when the server stops. Verifying this confirms that your app writes to the database correctly and doesn't just hold data in temporary memory.

Return to your terminal and press Ctrl+C to stop the server.
Start the server again by running:

flask --app app run --debug

Refresh your browser at http://127.0.0.1:5000.
Confirm the author you added (Toni Morrison) still appears in the table. Your data survived the restart.

Data disappeared after restart?

Check that you're running the server from the same folder that contains library.db. SQLite creates a new empty file if it can't find the existing one.
Make sure you didn't accidentally delete library.db. Run ls in your terminal to confirm the file exists.

Help me figure out why my database data disappeared after restarting Flask.

Verify your database file

Your web app reads and writes to library.db behind the scenes. You can also query this file directly with Python to confirm what's stored inside, independent of the Flask server.

Stop the server with Ctrl+C.
Create a file called check_db.py in your library-app folder with the following code:

import sqlite3

# Connect to the database file directly
conn = sqlite3.connect("library.db")
conn.row_factory = sqlite3.Row

# Print all authors
print("=== Authors ===")
authors = conn.execute("SELECT * FROM authors").fetchall()
for author in authors:
    print(f"  {author['id']}: {author['name']} ({author['birth_year']}, {author['nationality']})")

# Print all books
print("\n=== Books ===")
books = conn.execute("SELECT * FROM books").fetchall()
for book in books:
    print(f"  {book['id']}: {book['title']} ({book['year_published']}, {book['genre']}) - Author ID: {book['author_id']}")

conn.close()

What does this script do?

This script connects directly to your library.db file using the same sqlite3 module your app uses. It queries both tables and prints every row to the terminal. This proves the data lives in the file itself, not just in your Flask app's memory.

Save check_db.py and run it with this command:

python check_db.py

You should see a list of all your authors (including the one you added through the browser) followed by all books, each with their IDs and details.

Seeing an empty list or an error?

If you see an error about the file not existing, make sure you're running the command from inside the library-app folder where library.db lives.
If the authors list is empty, run python database.py to re-seed the database, then run check_db.py again.

Help me debug why my check_db.py script isn't showing the expected data.

Start the server one more time to leave it running for any further testing:

flask --app app run --debug

Secret mission

Add Search Functionality

Your library app works great for browsing, but what happens when you have dozens of authors and hundreds of books? You need a way to find things fast. Add a search bar that filters authors by name and books by title or genre.

Clean Up Your Resources

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

Resources you used:

Flask development server (currently running in your terminal).
Python virtual environment inside library-app.
The library-app folder on your Desktop (contains all project files and library.db).

Keep everything running

No action needed. Choose this if you're still actively building or want to keep experimenting with your library app.

Your Flask server stays running at http://127.0.0.1:5000.
Your library.db file keeps all the authors and books you added.
Your virtual environment stays activated and ready to use.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your project files stay on disk so you can pick up where you left off.

Switch to the terminal where Flask is running and press Ctrl+C to stop the server.
Deactivate the virtual environment by running:

deactivate

When you're ready to come back, reactivate the environment and start the server again:

cd ~/Desktop/library-app
source venv/bin/activate
flask --app app run --debug

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes your database, all code files, and the virtual environment.

Switch to the terminal where Flask is running and press Ctrl+C to stop the server.
Deactivate the virtual environment by running:

deactivate

Delete the entire library-app folder by running:

macOS

cd ~/Desktop
rm -rf library-app

Windows

cd ~\Desktop
rmdir /s /q library-app

Confirm the folder is gone by running ls (macOS) or dir (Windows) on your Desktop. You should no longer see library-app in the output.

Nice Work!

You built a complete database-backed web application from scratch. You can now design relational databases, write SQL, and connect everything to a browser-based interface.

What you learned:

Designed a relational database with two properly linked tables using foreign keys to connect authors and books.
Wrote SQL to create tables, insert data, query with joins, update records, and delete entries.
Built a Flask web app with full CRUD functionality and HTML templates with forms for user interaction.
Added search functionality using SQL LIKE queries to filter your data.

Ready to quiz yourself?

Build an Authors & Books Database

Introduction

30 Second Summary

What You'll Build

Are there any prerequisites?

Before We Start

In this step, get ready to:

Create the project folder and virtual environment

macOS

Windows

What is a virtual environment?

macOS

Windows

Don't see (venv) in your prompt?

Trust the folder if prompted

Install Flask

Seeing 'command not found' for flask?

Create the project file structure

macOS

Windows

What are these files for?

Missing a file or folder?

Design and Create the Database

In this step, get ready to:

Write the database setup script

What does this code do?

What do these tables do?

Seeing a red underline or syntax error?

Initialize the database

What does this command do?

Seeing ModuleNotFoundError?

Seed the database with sample data

What does this code do?

What is __name__ == "__main__"?

Seeing an OperationalError?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Build the Flask App and Author Routes

In this step, get ready to:

Set up the Flask application

What does this code do?

What does this route do?

Seeing an ImportError?

Add the "Add Author" route

What does this route do?

Route not appearing in the output?

Add the "Edit Author" and "Delete Author" routes

What does this route do?

Why delete books first?

Seeing a SyntaxError?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Add Book Routes

In this step, get ready to:

Create the "View Books" route

What does this code do?

Add the "Add Book" route

What does this code do?

Seeing a SyntaxError?

Add the "Edit Book" and "Delete Book" routes

What does this code do?

What does this code do?

Why only POST for delete?

Seeing an ImportError or IndentationError?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Create the HTML Templates

In this step, get ready to:

Build the base layout and authors list page

What does this template do?

Don't see templates/ in your sidebar?

What does this template do?

Seeing a red underline in the Jinja2 syntax?

✔️ Awesome, I've got everything!

ⓧ I'd like to double check the full code

Build the author form templates

What does this form do?

File not appearing in the right place?

How is this different from add_author.html?

Typo in the value attribute?

What is name == "main"?