How to Set Up and Version Your Database with SQLAlchemy + Alembic (Step-by-Step Guide)

📌 Introduction

When starting a Python project using SQLAlchemy, many developers face challenges around organizing the database layer and versioning schema changes. In this article, I’ll walk you through how to properly configure SQLAlchemy with Alembic, create your first migrations, and keep your database versioned from day one.

---

🛠️ 1. Project Structure Example

my_project/
├── app/
│   ├── models/
│   │   └── user.py
│   ├── db/
│   │   ├── base.py
│   │   └── session.py
├── alembic/
│   └── versions/
├── alembic.ini
├── env.py
└── pyproject.toml

---

⚙️ 2. Installing Dependencies

pip install sqlalchemy alembic psycopg2-binary
# with poetry
poetry add sqlalchemy alembic psycopg2-binary
# with uv

For SQLite:

pip install sqlalchemy alembic
# if poetry
poetry add sqlalchemy alembic

---

🧱 3. Creating SQLAlchemy Models (Declarative Base)

# app/models/user.py
from sqlalchemy import Column, Integer, String
from app.db.base import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, index=True)

---

🗃️ 4. Database Session Configuration

# app/db/session.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

DATABASE_URL = "sqlite:///./test.db"  # or PostgreSQL URL

engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

---

🧩 5. Base Declaration

# app/db/base.py
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

---

📌 6. Initializing Alembic

alembic init alembic

Update the alembic.ini file:

sqlalchemy.url = sqlite:///./test.db

Then, update alembic/env.py to import your metadata:

from app.db.base import Base
from app.models import user  # import all models

target_metadata = Base.metadata

---

🔁 7. Creating and Running Migrations

alembic revision --autogenerate -m "create user table"
alembic upgrade head

This will apply the migration and create your initial database schema.

---

🧪 8. Validating with a Query

With the database ready, test it by querying your model:

from app.models.user import User
from app.db.session import SessionLocal

session = SessionLocal()
users = session.query(User).all()
print(users)

---

💡 9. Extra Tips

• Always include __init__.py in your models/ folder.

• Keep business logic separate from database models.

• In team environments, manage migrations carefully using pull requests and version control.

---

📎 Conclusion

With a clean and well-structured SQLAlchemy + Alembic setup, you ensure a scalable, maintainable, and reliable foundation for your application’s data layer. Schema evolution becomes traceable and safe, especially when working in teams.