SQLAlchemy Migrations with Alembic (Production Setup)

 

Introduction

In real-world applications, your database schema evolves over time:

  • Add new columns
  • Modify tables
  • Remove fields

Instead of manually writing SQL each time, we use Alembic the official migration tool for SQLAlchemy.

What is Alembic?

Alembic is a database migration tool that:

  • Tracks schema changes
  • Generates migration scripts
  • Applies updates safely

Why Use Alembic?

  • Version control for database schema
  • Safe updates (no data loss if used correctly)
  • Works with all SQLAlchemy-supported DBs
  • Essential for production apps

Step 1: Install Alembic

pip install alembic

Step 2: Initialize Alembic

alembic init alembic

This creates:

alembic/
    versions/
    env.py
alembic.ini

Project Structure

project/
│
├── models.py
├── database.py
├── alembic/
├── alembic.ini


Step 3: Configure Database Connection
Open alembic.ini:
sqlalchemy.url = sqlite:///example.db
Connect Models in env.py
Edit alembic/env.py:
from models import Base
target_metadata = Base.metadata
This allows Alembic to detect model changes automatically.

Step 4: Create First Migration

alembic revision --autogenerate -m "create users table"

Example Migration File

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('name', sa.String()),
    )

def downgrade():
    op.drop_table('users')

Step 5: Apply Migration

alembic upgrade head

This updates your database schema.

Step 6: Modify Models & Migrate Again

Example: Add Email Column

email = Column(String)

Generate Migration
alembic revision --autogenerate -m "add email column"
Apply Changes
alembic upgrade head


Rollback
Downgrade One Step
alembic downgrade -1
Downgrade to Specific Version
alembic downgrade <revision_id>
Advanced Alembic Features
1. Rename Column
op.alter_column('users', 'name', new_column_name='full_name')
2. Add Column
op.add_column('users', sa.Column('age', sa.Integer()))
3. Drop Column
op.drop_column('users', 'age')
4. Create Index
op.create_index('idx_user_name', 'users', ['name'])

Handling Data Migration

Sometimes you need to modify data, not just schema.

op.execute("UPDATE users SET name='Unknown' WHERE name IS NULL")

Real-World Workflow

  1. Update models.py
  2. Generate migration
  3. Review migration file
  4. Apply migration
  5. Deploy

Handling Data Migration

Sometimes you need to modify data, not just schema.

op.execute("UPDATE users SET name='Unknown' WHERE name IS NULL")

Real-World Workflow

  1. Update models.py
  2. Generate migration
  3. Review migration file
  4. Apply migration
  5. Deploy

Common Mistakes

  • Blindly running --autogenerate without checking
  • Not backing up production database
  • Editing DB manually (breaks migration history)
  • Forgetting downgrade logic

Production Best Practices

  • Always review migration scripts
  • Use version control (Git)
  • Test migrations in staging
  • Backup DB before applying
  • Keep migrations small and clear

Alembic with FastAPI

# database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)

Alembic uses the same database URL.

  • Alembic manages schema changes safely
  • Autogenerate saves time
  • Upgrade & downgrade control versions
  • Essential for production-ready apps 


Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more