Database

Goal for this part

Move Campus Wire from temporary lists to lasting data. You will configure SQLite, define User and Post models, create tables, seed sample rows, and query posts for the home page.

After this snapshot, restarting the server should keep your posts. That stability is what makes a blog feel real, and it prepares you for authentication and ownership checks later.

What you should already know

Comfort with routes, templates, and the idea of structured data (like dictionaries) is enough. Some SQL vocabulary helps—tables, rows, primary keys—but Flask-SQLAlchemy lets you work mostly in Python classes.

If SQL is brand new, skim the linked “What is SQL?” and SQLite lessons. You do not need to write raw SQL for this part, but knowing why tables exist will make the models less mysterious.

Concepts: ORM models and relationships

An ORM (Object-Relational Mapper) lets you describe tables as classes. Each instance can represent a row. Columns become attributes, and relationships connect users to the posts they wrote.

SQLite stores everything in a local file, which is perfect for learning. You configure a database URI, bind SQLAlchemy to the app, and subclass db.Model for each table.

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///campus_wire.db"
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    posts = db.relationship("Post", backref="author", lazy=True)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)
    content = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

Walkthrough: create tables, seed, and query

Database work needs an application context so Flask-SQLAlchemy knows which app configuration to use. Inside that context, db.create_all() creates missing tables without deleting existing ones.

A small seed helper inserts authors and posts when the database is empty. The home route then queries posts ordered by newest first and passes them to the template—same idea as the old list, but backed by SQL.

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

@app.route('/')
def home():
    posts = Post.query.order_by(Post.created_at.desc()).all()
    return render_template("home.html", posts=posts)

How to run it and explore in the shell

Start the 04-Database snapshot as usual. On first run, the SQLite file is created and seed data appears on home. Stop and start again: the posts should still be there.

You can also open a Python REPL, push the app context, and inspect rows. Deleting the SQLite file resets the classroom database so the next run can re-seed from scratch.

git clone https://github.com/michaeldunga1/fcc-flask-blog.git
cd fcc-flask-blog/04-Database
python3 -m venv .venv
source .venv/bin/activate
pip install -r ../requirements.txt
python app.py

# optional exploration:
python
>>> from app import app, db, User, Post
>>> app.app_context().push()
>>> User.query.all()
>>> Post.query.all()

Common mistakes and troubleshooting

Errors about working outside an application context usually mean you queried the database from the REPL or a script without app.app_context(). Operational errors on commit often point to unique constraints—duplicate usernames or emails in seed data.

If home is empty after you expected seed rows, confirm the seed function runs only when tables are empty and that you are looking at the same SQLite file the app configured.

Try this

In the shell, create one extra Post, commit, and refresh the browser. Then delete campus_wire.db (or the instance path your snapshot uses), restart, and watch the seed data return.

Compare a User object’s .posts relationship with querying Post rows directly. Seeing both styles builds intuition for the Auth and Posts parts.

• Models describe tables; instances behave like rows
• Foreign keys connect posts to users
• Seeding is for demos—real apps often use migrations later