Managing Alembic Migrations Across Multiple Environments

Alembic is a powerful tool for handling SQLAlchemy schema migrations. In real-world projects, you often need to manage separate databases for local, staging, and production environments — each with their own migration history and configuration.

---

📁 Why Separate Environments?

• Local: For development and testing on your machine.
• Staging: For testing production-like deployments.
• Production: The live environment serving real users.

---

🔧 Step 1: Extend alembic.ini

Update your alembic.ini file to define multiple environments:

[DEFAULT]
prepend_sys_path = .
version_path_separator = os

[alembic]
script_location = alembic/local
sqlalchemy.url = driver://user:pass@localhost/localdb

[staging]
script_location = alembic/staging
sqlalchemy.url = driver://user:pass@staging-server/stagingdb

[production]
script_location = alembic/production
sqlalchemy.url = driver://user:pass@prod-server/proddb

`

---

📂 Step 2: Initialize Alembic Environments

Run the following commands to create migration folders for each environment:

`bash

Local environment

alembic init alembic/local

Staging environment

alembic -n staging init alembic/staging

Production environment

alembic -n production init alembic/production
`

This creates a separate env.py and versions/ directory for each environment.

---

📝 Step 3: Create Migrations

Autogenerate or write new migration scripts for a specific environment:

`bash

Local development

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

Staging

alembic -n staging revision --autogenerate -m "add users table"

Production

alembic -n production revision --autogenerate -m "add users table"
`

---

⬆️ Step 4: Apply Migrations

Apply your changes to the appropriate database:

`bash

Local

alembic upgrade head

Staging

alembic -n staging upgrade head

Production

alembic -n production upgrade head
`

---

🔍 Step 5: Check Current Revision

`bash

Local

alembic current

Staging

alembic -n staging current

Production

alembic -n production current
`

---

💡 Note
Ensure that each environment's env.py loads the correct SQLAlchemy metadata (i.e. your models) to detect changes.

---

📌 Folder Structure


alembic/
├── local/
│ ├── env.py
│ └── versions/
├── staging/
│ ├── env.py
│ └── versions/
└── production/
├── env.py
└── versions/


---

✅ Summary

• Use the -n flag to isolate environments.
• Keep migrations separate to avoid cross-environment issues.
• Always test in local and staging before applying to production.