Migrating FastAPI To-Do App from In-Memory List to SQLAlchemy ORM Database

Goal:

Migrate from storing users and todos in in-memory Python lists to storing them in a SQLite database using SQLAlchemy ORM.

Prerequisites

Your existing project has:

main.py
routers/users.py, routers/todos.py
schemas/user.py, schemas/todo.py
data/user.py, data/todo.py (currently using in-memory Python lists)

You can also clone this repo to access the existing project:

git clone https://github.com/AshminJayson/MITS-FastAPI

Step 1: Install the Required Packages

Install SQLAlchemy if not already installed:

pip install sqlalchemy

Step 2: Create the database.py File

Create a new file database.py in your project root.

📄 database.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./todo.db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)

SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)
Base = declarative_base()

Step 3: Create SQLAlchemy Models

Create a folder models/ and inside it create user.py and todo.py.

📄 models/user.py

from sqlalchemy import Column, Integer, String, Boolean
from database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    email = Column(String, unique=True, index=True)
    phone = Column(String)
    age = Column(Integer)
    address = Column(String)
    is_active = Column(Boolean, default=True)

📄 models/todo.py

from sqlalchemy import Column, Integer, String, Boolean, Date, ForeignKey
from database import Base

class Todo(Base):
    __tablename__ = "todos"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    title = Column(String)
    description = Column(String)
    due_date = Column(Date)
    is_completed = Column(Boolean, default=False)

Step 4: Create the Database Tables

Go to your main.py and add this just after creating the app:

📄 main.py

from database import Base, engine
import models.user
import models.todo

Base.metadata.create_all(bind=engine)  #creates tables automatically

Step 5: Add a Database Dependency

This allows FastAPI to automatically manage DB sessions.

📄 In both routers/users.py and routers/todos.py, add:

from sqlalchemy.orm import Session
from database import SessionLocal

# Dependency
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Step 6: Update Your Schemas

Make sure your Pydantic schemas include orm_mode = True to allow conversion from SQLAlchemy objects.

📄 schemas/user.py

from pydantic import BaseModel

class User(BaseModel):
    id: int
    name: str
    email: str
    phone: str
    age: int
    address: str
    is_active: bool

    class Config:
        orm_mode = True  #Important

class UpdateUser(BaseModel):
    email: str | None = None
    phone: str | None = None
    address: str | None = None
    
    class Config:
        orm_mode = True 

📄 schemas/todo.py

from pydantic import BaseModel
from datetime import date

class Todo(BaseModel):
    id: int
    user_id: int
    title: str
    description: str
    due_date: date
    is_completed: bool

    class Config:
        orm_mode = True  #Important

Step 7: Migrate Routes to Use Database

Now we change each route to use the database instead of the in-memory list.

Example: Create User

📄 routers/users.py

from models.user import User as UserModel
from schemas.user import User as UserSchema

@router.post("/", response_model=UserSchema)
def create_user(
    name: str,
    email: str,
    phone: str,
    address: str,
    age: int,
    db: Session = Depends(get_db)
):
    new_user = UserModel(
        name=name,
        email=email,
        phone=phone,
        address=address,
        age=age,
        is_active=True
    )
    db.add(new_user)
    db.commit()
    db.refresh(new_user)
    return new_user

Follow a similar pattern for all GET, PUT, DELETE routes for users.

Get User:

@router.get("/{user_id}", response_model=UserSchema)
def get_user(user_id: int,  db: Session = Depends(get_db)):
    user = db.query(UserModel).filter(UserModel.id == user_id).first()
    if not user:
        raise HTTPException(status.HTTP_404_NOT_FOUND, "User Not Found")
    return user

Update User:

@router.put("/{user_id}", response_model=UserSchema)
def update_user(user_id: int, up_info: UpdateUser, db: Session = Depends(get_db)):
    user = db.query(UserModel).filter(UserModel.id == user_id).first()
    if not user:
        raise HTTPException(status.HTTP_404_NOT_FOUND, "User Not Found")

    if up_info.email is not None:
        user.email = up_info.email
    if up_info.phone is not None:
        user.phone = up_info.phone
    if up_info.address is not None:
        user.address = up_info.address

    db.commit()
    db.refresh(user)
    return user

Delete User:

@router.delete("/{user_id}")
def delete_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(UserModel).filter(UserModel.id == user_id).first()
    if not user:
        raise HTTPException(status.HTTP_404_NOT_FOUND, "User Not Found")

    db.delete(user)
    db.commit()
    return {"message": "User successfully deleted"}

Similarly, update all the routes for todos by replacing the list logic with database queries using SQLAlchemy (Try this yourself).

Step 8: Remove Old Data Files

Once you confirm that everything works with the database, you can delete:

• data/user.py
• data/todo.py

They are no longer needed.

Your final project structure would be:

├── main.py
├── database.py
├── models/
│   ├── user.py
│   └── todo.py
├── routers/
│   ├── users.py
│   └── todos.py
├── schemas/
│   ├── user.py
│   └── todo.py
└── todo.db  ← (auto-created SQLite file)

This keeps a clear separation of concerns:

• routers/ → for route logic
• models/ → for database table definitions
• schemas/ → for request/response validation
• database.py → for setting up and managing DB connection

Final Testing

Run the app:

uvicorn main:app --reload

Then visit: http://127.0.0.1:8000/docs to test your API in Swagger UI.