Resetting Your Database

Welcome back! In the last lessons, you learned how to set up your app and design your database models for recipes, ingredients, and reviews. Now, as you continue building your AI Cooking Helper, you might find yourself needing to clear out all the data in your database. This is especially common during development and testing, when you want to start fresh or remove test data.

Resetting your database means deleting all the records from your tables. This can help you:

• Remove test or sample data before going live.
• Fix mistakes if you accidentally added bad data.
• Start over with a clean slate for new features or tests.

In this lesson, you will learn how to create a script that safely deletes all recipes, ingredients, and reviews from your database. This is a powerful tool, so we will also make sure it is safe to use.

Before we dive in, let’s quickly remind ourselves how our database is set up. In a previous lesson, you created three main models using SQLAlchemy:

• Recipe
• Ingredient
• Review

You also set up a special association table called recipe_ingredient to handle the many-to-many relationship between recipes and ingredients. This means:

• Each recipe can have many ingredients.
• Each ingredient can be used in many recipes.

Here’s a quick look at how these models are related:

This structure is important because, when we delete data, we need to be careful about the order, especially with association tables.

Deleting all data from your database is a big step. If you run a script that wipes everything, you can’t get that data back unless you have a backup. That’s why it’s important to add a safety check before doing anything destructive.

A common way to do this is to prompt the user for confirmation. This gives you a chance to stop the script if you didn’t mean to run it.

For example, you can use the input() function to ask the user:

answer = input("⚠️ This will delete ALL recipes, ingredients, and reviews. Are you sure? (y/N): ").strip().lower()
if answer != "y":
    print("Aborted.")
    return

• The script asks the user to type y to continue.
• If the user types anything else, the script stops and prints "Aborted."

This simple step can save you from accidentally deleting important data.

Now, let’s talk about how to actually delete the data. Because of the relationships between tables, the order in which you delete matters.

• The recipe_ingredient table links recipes and ingredients. If you try to delete a recipe or ingredient that is still linked in this table, you might get an error.
• To avoid this, you should delete the links in the association table first, then delete the main records.

1. Delete from the association table first:

   Python

   session.execute(recipe_ingredient.delete())

   This removes all links between recipes and ingredients.

2. Delete from dependent tables:

   Python

   session.query(Review).delete()
   session.query(Recipe).delete()
   session.query(Ingredient).delete()

   • session.query(Review).delete() removes all reviews.
   • session.query(Recipe).delete() removes all recipes.
   • session.query(Ingredient).delete() removes all ingredients.

3. Commit the changes:

   Python

   session.commit()

   This saves all the deletions to the database.

By following this order, you avoid errors and make sure all data is removed cleanly.

Let’s build the script step by step so you can see how each part works.

First, you need to import the necessary modules and set up the environment so you can access your database and models from the script.

from __future__ import annotations

import sys
import os

# Adjust sys.path so "app" is importable when run directly
CURRENT_DIR = os.path.dirname(os.path.abspath(__file__))
PROJECT_ROOT = os.path.abspath(os.path.join(CURRENT_DIR, "..", "cooking_helper"))
if PROJECT_ROOT not in sys.path:
    os.chdir(PROJECT_ROOT)
    sys.path.insert(0, PROJECT_ROOT)

from sqlalchemy.orm import Session

from app.db.session import SessionLocal, engine
from app.db.models import Recipe, Ingredient, Review, recipe_ingredient

• The script adjusts the import path so you can import your app’s modules even when running the script directly, and moves to the correct directory in order to point to the database file correctly.
• It imports the database session and your models.

Next, add the safety prompt we discussed earlier:

def reset_database():
    confirm = input("⚠️ This will delete ALL recipes, ingredients, and reviews. Are you sure? (y/N): ")
    if confirm.lower() != 'y':
        print("Aborted.")
        return

• The code asks the user to confirm before continuing.
• If the user does not type y, the script aborts.

Now, add the code to delete the data in the correct order:

print("🔄 Deleting data...")

    session = SessionLocal()
    try:
        # Delete association table entries first
        session.execute(recipe_ingredient.delete())

        # Then delete dependent tables
        session.query(Review).delete()
        session.query(Recipe).delete()
        session.query(Ingredient).delete()

        session.commit()
        print("✅ Database reset complete!")
    except Exception as e:
        session.rollback()
        print(f"❌ Error: {e}")
    finally:
        session.close()

• The script prints a message before deleting.
• It deletes from the association table first, then the other tables.
• After committing, it prints a success message.

Finally, add the code to run the function if the script is executed directly:

if __name__ == "__main__":
    reset_database(confirm=True)

This lets you run the script from the command line.

If you run the script, you might see:

⚠️ This will delete ALL recipes, ingredients, and reviews. Are you sure? (y/N): y
🔄 Deleting data...
✅ All data deleted successfully.

If you type anything other than y, you’ll see:

⚠️ This will delete ALL recipes, ingredients, and reviews. Are you sure? (y/N): n
Aborted.

In this lesson, you learned how to safely reset your cooking app’s database by:

• Adding a user confirmation prompt to prevent accidental data loss.
• Deleting data in the correct order to avoid errors with relationships.
• Using a script to automate the process and provide clear feedback.

This script is a helpful tool during development and testing. In the next practice exercises, you’ll get hands-on experience writing and running your own reset scripts. This will help you become more comfortable managing your database as you continue building your AI Cooking Helper.