SQLAlchemy & Database Migrations

Today's Agenda

• Part 1: Prep Work Recap - SQLAlchemy fundamentals
• Part 2: SQLAlchemy Deep Dive - Models and relationships
• Part 3: CRUD Operations - Create, Read, Update, Delete
• Part 4: Database Migrations - Flask-Migrate and Alembic
• Part 5: Best Practices - Production-ready databases
• Demo: SQLAlchemy + Flask-Migrate (7-8 min)
• In-Class Exploration: Library Book Manager (45 min)

What You Learned in Prep

• SQLAlchemy: An ORM that maps Python classes to database tables
• Models: Python classes that define your table structure
• Sessions: Track changes and commit them to the database
• Queries: Retrieve data with Model.query
• Flask-Migrate: Version control for your database schema

The Problem We're Solving

• Before: Data stored in Python lists/dicts
• Problem: Server restarts = data gone!
• Solution: Store data in a real database

# Our Week 3 API - data lost on restart!
items = [
    {"id": 1, "name": "Chair", "price": 49.99},
    {"id": 2, "name": "Table", "price": 149.99}
]

# Server restarts... all items gone!

# With SQLAlchemy - data persists forever
item = ItemModel.query.get(1)  # Still there after restart!

Why Use an ORM?

INSERT INTO items (name, price)
VALUES ('Chair', 49.99);

SELECT * FROM items
WHERE price > 20;

item = ItemModel(
    name='Chair',
    price=49.99
)
db.session.add(item)

items = ItemModel.query.filter(
    ItemModel.price > 20
).all()

Quick Check: SQLAlchemy Vocabulary

• Session = like git staging area
• Commit = like git commit (saves to disk!)

Prep Recap: Key Takeaways

• Models define schema: Class attributes become columns
• Session tracks changes: add(), delete(), then commit()
• Queries are Pythonic: Model.query.filter().all()
• Migrations track changes: flask db migrate, flask db upgrade

Defining a Model

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class BookModel(db.Model):
    __tablename__ = "books"  # Table name in database

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    author = db.Column(db.String(100), nullable=False)
    year = db.Column(db.Integer)
    isbn = db.Column(db.String(13), unique=True)

    def to_dict(self):
        return {
            'id': self.id,
            'title': self.title,
            'author': self.author,
            'year': self.year,
            'isbn': self.isbn
        }

• __tablename__: Name of the actual database table
• primary_key=True: Auto-incrementing ID
• nullable=False: Required field (cannot be NULL)
• unique=True: No duplicate values allowed

Column Types

class ArticleModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text)  # Unlimited text
    views = db.Column(db.Integer, default=0)
    published = db.Column(db.Boolean, default=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

Column Constraints

class UserModel(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)

    # Required field - cannot be NULL
    email = db.Column(db.String(120), nullable=False, unique=True)

    # Optional field with default
    is_active = db.Column(db.Boolean, default=True)

    # Field with index for faster queries
    username = db.Column(db.String(80), index=True)

    # Default value from function
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

• nullable=False: Must have a value (required)
• unique=True: No duplicates in this column
• default=value: Used if no value provided
• index=True: Faster queries on this column

Relationships: One-to-Many

class StoreModel(db.Model):
    __tablename__ = "stores"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)

    # One store has many items
    items = db.relationship('ItemModel', back_populates='store', lazy='dynamic')


class ItemModel(db.Model):
    __tablename__ = "items"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    price = db.Column(db.Float, nullable=False)

    # Foreign key: links to stores.id
    store_id = db.Column(db.Integer, db.ForeignKey('stores.id'), nullable=False)

    # Relationship to access the store object
    store = db.relationship('StoreModel', back_populates='items')

• ForeignKey: Column that references another table
• db.relationship: Access related objects as Python attributes
• back_populates: Links both sides of the relationship

Using Relationships

# Get a store's items
store = StoreModel.query.get(1)
for item in store.items:
    print(f"{item.name}: ${item.price}")

# Get an item's store
item = ItemModel.query.get(5)
print(f"Sold at: {item.store.name}")

# Create an item for a store
new_item = ItemModel(
    name="Laptop",
    price=999.99,
    store_id=store.id  # Link via foreign key
)
db.session.add(new_item)
db.session.commit()

• store.items returns all items for that store
• item.store returns the store object
• SQLAlchemy handles the SQL joins automatically!

The lazy='dynamic' Option

# Without lazy='dynamic' - all items loaded immediately
items = db.relationship('ItemModel', back_populates='store')
store = StoreModel.query.get(1)
# store.items is a list - all items already loaded!

# With lazy='dynamic' - query object returned
items = db.relationship('ItemModel', back_populates='store', lazy='dynamic')
store = StoreModel.query.get(1)
# store.items is a query - no items loaded yet!

# Now you can filter before loading
expensive = store.items.filter(ItemModel.price > 100).all()
cheap = store.items.filter(ItemModel.price < 20).all()

• Without lazy='dynamic': All items loaded when you access store.items
• With lazy='dynamic': Returns a query, load only what you need
• Use lazy='dynamic' for large collections!

Create: Adding New Records

from sqlalchemy.exc import SQLAlchemyError, IntegrityError

@app.route('/api/books', methods=['POST'])
def create_book():
    data = request.get_json()

    book = BookModel(
        title=data['title'],
        author=data['author'],
        year=data.get('year'),
        isbn=data.get('isbn')
    )

    try:
        db.session.add(book)      # Stage the new record
        db.session.commit()       # Save to database
    except IntegrityError:
        abort(400, description="A book with that ISBN already exists")
    except SQLAlchemyError:
        abort(500, description="An error occurred while creating the book")

    return jsonify(book.to_dict()), 201

• db.session.add() stages the object (not saved yet)
• db.session.commit() saves to disk
• Catch IntegrityError for unique constraint violations

Read: Querying Records

# Get all books
@app.route('/api/books')
def get_books():
    books = BookModel.query.all()
    return jsonify([book.to_dict() for book in books])

# Get one book by ID (404 if not found)
@app.route('/api/books/<int:book_id>')
def get_book(book_id):
    book = BookModel.query.get_or_404(book_id)
    return jsonify(book.to_dict())

# Filter with conditions
@app.route('/api/books/search')
def search_books():
    author = request.args.get('author')
    query = BookModel.query
    if author:
        query = query.filter(BookModel.author.ilike(f'%{author}%'))
    return jsonify([b.to_dict() for b in query.all()])

• .all() returns a list of all matching records
• .get_or_404(id) returns one record or aborts with 404
• .filter() adds WHERE conditions
• .ilike() for case-insensitive LIKE queries

Update: Modifying Records

@app.route('/api/books/<int:book_id>', methods=['PUT'])
def update_book(book_id):
    book = BookModel.query.get_or_404(book_id)
    data = request.get_json()

    # Update only provided fields
    book.title = data.get('title', book.title)
    book.author = data.get('author', book.author)
    book.year = data.get('year', book.year)
    book.isbn = data.get('isbn', book.isbn)

    db.session.commit()  # No need to add() - it's already tracked

    return jsonify(book.to_dict())

• Fetch the existing record first
• Modify the attributes directly
• No need to add() again - SQLAlchemy tracks changes
• commit() saves all changes

Delete: Removing Records

@app.route('/api/books/<int:book_id>', methods=['DELETE'])
def delete_book(book_id):
    book = BookModel.query.get_or_404(book_id)

    db.session.delete(book)
    db.session.commit()

    return jsonify({'message': 'Book deleted'}), 200

• db.session.delete() marks for deletion
• commit() actually removes from database
• Foreign key constraints may prevent deletion

Cascade Deletes

class StoreModel(db.Model):
    __tablename__ = "stores"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)

    # cascade='all, delete' - delete items when store is deleted
    items = db.relationship(
        'ItemModel',
        back_populates='store',
        lazy='dynamic',
        cascade='all, delete'  # Delete orphans automatically
    )

• cascade='all, delete': Delete related items when store is deleted
• Without cascade: deleting store with items throws error
• Choose carefully: sometimes you want to prevent deletion!

The Schema Evolution Problem

• Day 1: Deploy app with books table
• Day 30: Need to add page_count column
• Problem: How do you update the production database?

Setting Up Flask-Migrate

# app.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///books.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
migrate = Migrate(app, db)  # Connect Flask-Migrate

# Your models...
class BookModel(db.Model):
    # ...

# Install
pip install flask-migrate

# Initialize migrations (run once)
flask db init

• Migrate(app, db) connects Flask-Migrate to your app
• flask db init creates the migrations/ folder

The Migration Workflow

# 1. Initialize (only once per project)
flask db init

# 2. Create first migration (from empty database)
flask db migrate -m "Create books table"

# 3. Apply migration to database
flask db upgrade

# 4. Later: add a column to your model
# ... edit BookModel to add page_count column ...

# 5. Generate new migration
flask db migrate -m "Add page_count to books"

# 6. Apply the new migration
flask db upgrade

• migrate: Generates a migration script (doesn't apply it)
• upgrade: Applies pending migrations
• downgrade: Rolls back the last migration

Understanding Migration Files

# migrations/versions/a1b2c3d4_add_page_count.py
revision = 'a1b2c3d4'
down_revision = '9z8y7x6w'  # Previous migration

def upgrade():
    # Apply change
    op.add_column('books',
        sa.Column('page_count', sa.Integer(), nullable=True)
    )

def downgrade():
    # Undo change
    op.drop_column('books', 'page_count')

• revision: Unique ID stored in database
• down_revision: Links to previous migration
• upgrade(): What to do when applying
• downgrade(): How to undo (rollback)

Flask-Migrate Commands

When to Manually Edit Migrations

# Problem: Adding a NOT NULL column with default
# Existing rows would have NULL, but we want a default value

def upgrade():
    # 1. Add column as nullable first
    op.add_column('books',
        sa.Column('status', sa.String(20), nullable=True)
    )

    # 2. Set default value for existing rows
    op.execute("UPDATE books SET status = 'available'")

    # 3. Now make it NOT NULL (optional)
    # op.alter_column('books', 'status', nullable=False)

• Auto-generated migrations are 99% correct
• Sometimes you need to add data transformations
• Always review migrations before applying!

SQLite vs PostgreSQL

app.config['SQLALCHEMY_DATABASE_URI'] = \
    'sqlite:///books.db'

app.config['SQLALCHEMY_DATABASE_URI'] = \
    'postgresql://user:pass@host/db'

Environment Variables for Database URLs

import os

app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv(
    'DATABASE_URL',
    'sqlite:///books.db'  # Default for development
)

# .env file (never commit this!)
DATABASE_URL=postgresql://user:password@localhost/myapp

# Or set in your environment
export DATABASE_URL=postgresql://user:password@localhost/myapp

• Never hardcode credentials in your code
• Use environment variables for database URLs
• Add .env to .gitignore!

Indexing for Performance

class BookModel(db.Model):
    __tablename__ = "books"

    id = db.Column(db.Integer, primary_key=True)

    # Indexed: fast lookups by ISBN
    isbn = db.Column(db.String(13), unique=True, index=True)

    # Indexed: fast search by author
    author = db.Column(db.String(100), index=True)

    # Not indexed: rarely searched alone
    page_count = db.Column(db.Integer)

• Index columns you search frequently
• Primary keys are automatically indexed
• Unique constraints create indexes
• More indexes = faster reads, slower writes

Error Handling Patterns

from sqlalchemy.exc import SQLAlchemyError, IntegrityError

@app.route('/api/books', methods=['POST'])
def create_book():
    data = request.get_json()

    try:
        book = BookModel(**data)
        db.session.add(book)
        db.session.commit()
        return jsonify(book.to_dict()), 201

    except IntegrityError:
        db.session.rollback()  # Undo any partial changes
        return jsonify({'error': 'Book with that ISBN exists'}), 400

    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({'error': 'Database error'}), 500

• Catch specific exceptions first (IntegrityError)
• Always rollback on error to clear the session
• Log the actual error for debugging

Best Practices Summary

• Use environment variables for database credentials
• Always use migrations - never modify schema manually
• Review migrations before applying
• Test migrations locally before production
• Index frequently searched columns
• Handle errors gracefully with rollback
• Use SQLite for dev, PostgreSQL for production

What You Learned Today

• SQLAlchemy models: Python classes that map to database tables
• Columns and constraints: Types, nullable, unique, index
• Relationships: ForeignKey + db.relationship for one-to-many
• CRUD operations: add, commit, query, delete
• Flask-Migrate: Version control for database schema

Looking Ahead

Demo: What We'll Build

• Start with a fresh Flask app
• Configure SQLAlchemy with SQLite
• Create a BookModel with CRUD routes
• Set up Flask-Migrate
• Generate and apply our first migration
• Test persistence across server restarts

Project Overview

• You receive a working Flask API with in-memory storage
• Your job: convert it to use SQLAlchemy + SQLite!
• Tasks:
  • Configure Flask-SQLAlchemy (10 min)
  • Create the Book model (15 min)
  • Update CRUD routes to use SQLAlchemy (15 min)
  • Test persistence and submit (5 min)

Getting Started

1. Click the GitHub Classroom link (in bCourses)
2. Clone your repo:

   git clone <your-repo-url>
   cd in-class-exploration-week-7-<your-username>

3. Set up and run:

   python3 -m venv venv
   source venv/bin/activate  # Windows: venv\Scripts\activate
   pip install -r requirements.txt
   python app.py

4. Test the API works (data lost on restart!):

   curl http://localhost:5000/api/books
   curl -X POST http://localhost:5000/api/books \
     -H "Content-Type: application/json" \
     -d '{"title": "Clean Code", "author": "Robert Martin"}'
   # Restart server... book is gone!

Testing Your Implementation

# Create a book
curl -X POST http://localhost:5000/api/books \
  -H "Content-Type: application/json" \
  -d '{"title": "Clean Code", "author": "Robert Martin", "year": 2008}'

# Get all books
curl http://localhost:5000/api/books

# Restart the server (Ctrl+C, then python app.py)

# Get books again - should still be there!
curl http://localhost:5000/api/books

Submitting (at 10:45 AM)

1. Commit everything you have (even if incomplete):

   git add .
   git commit -m "In-class exploration submission"

2. Push to your repository:

   git push

3. Submit on bCourses: Add your GitHub repo URL

Want to keep working? Continue after submitting - just push additional changes.

Questions?

Website: groups.ischool.berkeley.edu/i253/sp26

Email: kay@ischool.berkeley.edu