Relationships and Migrations

🏠 Flask Database Relationships & Migrations

Building a Family Tree for Your Data

Imagine your database is like a big family album. Each photo (table) shows different people (data), but what makes it interesting is how everyone is connected — who is whose parent, sibling, or friend.

In Flask, we use SQLAlchemy to create these family connections. Let’s learn how!

🎯 What We’ll Learn

graph LR
    A["Database Relationships"] --> B["One-to-Many"]
    A --> C["Many-to-Many"]
    A --> D["One-to-One"]
    A --> E["Foreign Keys"]
    A --> F["Backref & back_populates"]
    G["Migrations"] --> H["Flask-Migrate Setup"]
    G --> I["Migration Operations"]

🔗 Foreign Keys: The Connection Thread

What is a Foreign Key?

Think of a foreign key like a name tag that says “I belong to this family.”

When you write your name on your lunchbox, everyone knows it’s yours. A foreign key does the same thing — it tells one table “this record belongs to that record over there.”

Simple Example

class Author(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    name = db.Column(db.String(100))

class Book(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    title = db.Column(db.String(200))
    # Foreign key points to Author
    author_id = db.Column(
        db.Integer,
        db.ForeignKey('author.id')
    )

📌 Key Point: author_id is the name tag that connects each book to its author.

👨‍👧‍👦 One-to-Many Relationships

The Story

Imagine a teacher with many students. One teacher → Many students. But each student has only one teacher (in that class).

This is One-to-Many — the most common relationship!

Real-World Examples

• 🏫 One School → Many Students
• 📚 One Author → Many Books
• 🛒 One Customer → Many Orders

Code Example

class Teacher(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    name = db.Column(db.String(100))

    # One teacher has MANY students
    students = db.relationship(
        'Student',
        backref='teacher'
    )

class Student(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    name = db.Column(db.String(100))

    # Foreign key links to teacher
    teacher_id = db.Column(
        db.Integer,
        db.ForeignKey('teacher.id')
    )

How to Use It

# Get all students of a teacher
teacher = Teacher.query.first()
print(teacher.students)  # List!

# Get a student's teacher
student = Student.query.first()
print(student.teacher)  # One teacher

👯 Many-to-Many Relationships

The Story

Think about students and clubs. One student can join many clubs. And one club has many students.

This is like a web of friendships — everyone connected to everyone!

The Secret: Association Table

We need a “helper table” that keeps track of who belongs to what.

# Helper table (no class needed!)
student_clubs = db.Table(
    'student_clubs',
    db.Column('student_id', db.Integer,
        db.ForeignKey('student.id')),
    db.Column('club_id', db.Integer,
        db.ForeignKey('club.id'))
)

class Student(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    name = db.Column(db.String(100))

    # Many-to-many relationship
    clubs = db.relationship(
        'Club',
        secondary=student_clubs,
        backref='members'
    )

class Club(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    name = db.Column(db.String(100))

How to Use It

# Add student to club
chess_club.members.append(alice)

# Get all clubs a student joined
print(alice.clubs)

# Get all members in a club
print(chess_club.members)

💑 One-to-One Relationships

The Story

Think of a person and their passport. Each person has exactly one passport. And each passport belongs to exactly one person.

Code Example

class Person(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    name = db.Column(db.String(100))

    # One-to-one: uselist=False
    passport = db.relationship(
        'Passport',
        backref='owner',
        uselist=False  # Key!
    )

class Passport(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    number = db.Column(db.String(20))

    person_id = db.Column(
        db.Integer,
        db.ForeignKey('person.id'),
        unique=True  # Only one!
    )

📌 Magic Trick: uselist=False tells Flask “give me ONE object, not a list.”

🔄 Backref vs back_populates

The Quick Choice

backref (The Shortcut)

class Parent(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    children = db.relationship(
        'Child',
        backref='parent'  # Auto-magic!
    )

class Child(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    parent_id = db.Column(
        db.Integer,
        db.ForeignKey('parent.id')
    )
# child.parent works automatically!

back_populates (The Clear Way)

class Parent(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    children = db.relationship(
        'Child',
        back_populates='parent'
    )

class Child(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    parent_id = db.Column(
        db.Integer,
        db.ForeignKey('parent.id')
    )
    # Explicit declaration
    parent = db.relationship(
        'Parent',
        back_populates='children'
    )

📌 When to use which?

• backref: Quick projects, simple models
• back_populates: Large projects, need clarity

🚀 Flask-Migrate Setup

Why Migrations?

Imagine you built a LEGO castle. Now you want to add a tower. You don’t destroy the whole castle — you just add pieces.

Migrations let you change your database structure without losing data!

Step 1: Install

pip install Flask-Migrate

Step 2: Setup in Your App

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = \
    'sqlite:///myapp.db'

db = SQLAlchemy(app)
migrate = Migrate(app, db)  # Add this!

Step 3: Initialize

flask db init

This creates a migrations folder — your database diary!

📝 Database Migration Operations

The Three Magic Commands

graph TD
    A["Change Model"] --> B["flask db migrate"]
    B --> C["flask db upgrade"]
    D["Want to undo?"] --> E["flask db downgrade"]

1. Create Migration

After changing your models:

flask db migrate -m "Add user table"

This creates a recipe file describing changes.

2. Apply Changes

flask db upgrade

This actually updates your database!

3. Undo Changes

Made a mistake? Go back:

flask db downgrade

Real Example Workflow

# Step 1: Add new column to model
class User(db.Model):
    id = db.Column(db.Integer,
                   primary_key=True)
    name = db.Column(db.String(100))
    email = db.Column(db.String(120))  # NEW!

# Step 2: Create migration
flask db migrate -m "Add email to user"

# Step 3: Apply it
flask db upgrade

View Migration History

flask db history

Check Current Version

flask db current

🎯 Quick Summary

🌟 You Did It!

You now understand:

• ✅ How tables connect with foreign keys
• ✅ Three types of relationships
• ✅ The magic of backref and back_populates
• ✅ How to safely change your database with migrations

Your data now has a family tree! 🌳

💡 Pro Tip: Always test your migrations on a copy of your database before running on production. Safety first!