Database Lifecycle: Connection Pooling and Migrations

Mapping objects to tables is the easy part. Operating that database under load and evolving the schema as your app grows is the hard part.

In this lesson, we master the lifecycle of your data layer: from the first connection to the 50th schema update.

---

1. Connection Pooling

You should never open a new database connection for every incoming user request. It's too slow. Instead, your app maintains a Pool of open connections. When a request arrives, it "borrows" a connection from the pool and "returns" it when it's done.

Key Pool Strategy:

• pool_size: How many connections to keep open.
• max_overflow: How many extra connections to allow during a traffic spike.
• pool_recycle: How often to kill and recreate a connection (to prevent timeouts).

---

2. Transactions: ACID Compliance

A transaction ensures that a group of database operations either all succeed or all fail.

Imagine a bank transfer:

1. Subtract $100 from User A.
2. Add $100 to User B.

If step 2 fails (e.g., the server crashes), you must rollback step 1. In SQLModel/SQLAlchemy, we use a context manager:

with Session(engine) as session:
    try:
        session.add(transfer)
        session.commit()  # Both actions saved!
    except:
        session.rollback() # Neither action saved!

---

3. Database Migrations with Alembic

In production, you never delete and recreate your database to add a new column. You use Migrations.

A migration is a version-controlled script that tells the database: "Add a column 'bio' to table 'users'." In the Python world, the tool of choice is Alembic.

The Alembic Workflow:

1. Initialize: alembic init alembic.
2. Generate: Update your SQLModel, then run alembic revision --autogenerate -m "Add bio".
3. Upgrade: alembic upgrade head.

---

4. Why Migrations are Mandatory

Without migrations:

• Your local database will look different from your staging and production databases.
• You have no way to "Undo" a change if it breaks something.
• Team members cannot easily keep their local data in sync with yours.

---

Visualizing Migrations

graph TD
    A["V1: Users Table (id, name)"] --> B["Migration Script: add_email.py"]
    B --> C["V2: Users Table (id, name, email)"]
    C --> D["Migration Script: add_bio.py"]
    D --> E["V3: Users Table (id, name, email, bio)"]
    
    style B fill:#f9f,stroke:#333
    style D fill:#f9f,stroke:#333

---

Summary

• Pooling: Borrow, don't build, your connections.
• Transactions: Ensure your data never ends up in a "Partial" state.
• Alembic: Treat your database schema like code—versioned and reversible.
• Autogenerate: Let Alembic detect the differences between your Python models and your SQL tables automatically.

In the next lesson, we wrap up Module 10 with Exercises on database design and management.

---

Exercise: The Schema Evolution

You have a User table. You just realized you need to track a user's last_login timestamp.

1. Describe the steps you would take to add this field using Alembic.
2. Why is it safer to do this with a migration script than by running ALTER TABLE manually?