Generate production-ready FastAPI projects with SQLite, Alembic migrations, layered architecture, comprehensive tests, and best practices for interviews and rapid prototyping.
A skill for creating and extending professional FastAPI REST APIs with clean architecture, type safety, and comprehensive testing. Perfect for interview preparation, rapid prototyping, and showcasing best practices.
Helps you build and extend FastAPI applications following a strict layered architecture:
Includes SQLite with Alembic migrations, comprehensive test suites, and production-ready patterns.
```
User Request
↓
API Layer (FastAPI routes) ← Validates input via Pydantic schemas
↓
CRUD Layer (Database operations) ← Business logic
↓
Model Layer (SQLAlchemy ORM) ← Data structure
↓
Database (SQLite)
```
Follow this exact 6-step pattern when a user requests a new feature (e.g., "Add a Posts feature"):
#### Step 1: Create the Model
Create `app/models/{resource}.py`:
```python
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime
from sqlalchemy.sql import func
from sqlalchemy.orm import relationship
from app.core.database import Base
class Post(Base):
__tablename__ = "posts"
id = Column(Integer, primary_key=True, index=True)
title = Column(String, nullable=False, index=True)
content = Column(Text, nullable=False)
author_id = Column(Integer, ForeignKey("users.id"))
created_at = Column(DateTime(timezone=True), server_default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
author = relationship("User", back_populates="posts")
```
**CRITICAL**: Update `app/models/__init__.py` to import the new model.
#### Step 2: Create the Schemas
Create `app/schemas/{resource}.py` with **all 4 schemas**:
```python
from datetime import datetime
from typing import Optional
from pydantic import BaseModel, ConfigDict
class PostBase(BaseModel):
title: str
content: str
class PostCreate(PostBase):
author_id: int
class PostUpdate(BaseModel):
title: Optional[str] = None
content: Optional[str] = None
class PostResponse(PostBase):
id: int
author_id: int
created_at: datetime
updated_at: Optional[datetime] = None
model_config = ConfigDict(from_attributes=True)
```
**IMPORTANT**: Use Pydantic v2 syntax (`model_config = ConfigDict(from_attributes=True)`), NOT v1 (`class Config: orm_mode = True`).
**CRITICAL**: Update `app/schemas/__init__.py` to import the new schemas.
#### Step 3: Create CRUD Operations
Create `app/crud/{resource}.py` with standard operations:
```python
from typing import List, Optional
from sqlalchemy.orm import Session
from app.models.post import Post
from app.schemas.post import PostCreate, PostUpdate
def get_post(db: Session, post_id: int) -> Optional[Post]:
return db.query(Post).filter(Post.id == post_id).first()
def get_posts(db: Session, skip: int = 0, limit: int = 100) -> List[Post]:
return db.query(Post).offset(skip).limit(limit).all()
def create_post(db: Session, post: PostCreate) -> Post:
db_post = Post(**post.model_dump())
db.add(db_post)
db.commit()
db.refresh(db_post)
return db_post
def update_post(db: Session, post_id: int, post: PostUpdate) -> Optional[Post]:
db_post = get_post(db, post_id)
if not db_post:
return None
update_data = post.model_dump(exclude_unset=True)
for field, value in update_data.items():
setattr(db_post, field, value)
db.commit()
db.refresh(db_post)
return db_post
def delete_post(db: Session, post_id: int) -> bool:
db_post = get_post(db, post_id)
if not db_post:
return False
db.delete(db_post)
db.commit()
return True
```
**CRITICAL**: Update `app/crud/__init__.py` to import the new CRUD module.
#### Step 4: Create API Routes
Create `app/api/{resource}s.py`:
```python
from typing import List
from fastapi import APIRouter, Depends, HTTPException, status
from sqlalchemy.orm import Session
from app.core.database import get_db
from app.crud import post as crud_post
from app.schemas.post import PostCreate, PostUpdate, PostResponse
router = APIRouter(prefix="/posts", tags=["posts"])
@router.post("/", response_model=PostResponse, status_code=status.HTTP_201_CREATED)
def create_post(post: PostCreate, db: Session = Depends(get_db)):
return crud_post.create_post(db=db, post=post)
@router.get("/", response_model=List[PostResponse])
def read_posts(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
return crud_post.get_posts(db, skip=skip, limit=limit)
@router.get("/{post_id}", response_model=PostResponse)
def read_post(post_id: int, db: Session = Depends(get_db)):
db_post = crud_post.get_post(db, post_id=post_id)
if not db_post:
raise HTTPException(status_code=404, detail="Post not found")
return db_post
@router.patch("/{post_id}", response_model=PostResponse)
def update_post(post_id: int, post: PostUpdate, db: Session = Depends(get_db)):
db_post = crud_post.update_post(db, post_id=post_id, post=post)
if not db_post:
raise HTTPException(status_code=404, detail="Post not found")
return db_post
@router.delete("/{post_id}", status_code=status.HTTP_204_NO_CONTENT)
def delete_post(post_id: int, db: Session = Depends(get_db)):
if not crud_post.delete_post(db, post_id=post_id):
raise HTTPException(status_code=404, detail="Post not found")
```
**CRITICAL**: Update `app/api/__init__.py` to import the new router.
#### Step 5: Register the Router
Update `app/main.py`:
```python
from app.api.posts import router as posts_router
app.include_router(posts_router)
```
#### Step 6: Generate and Apply Migration
```bash
uv run alembic revision --autogenerate -m "Add posts table"
uv run alembic upgrade head
```
1. Update the model class with new column
2. Update relevant schemas (Base, Create, Update, Response)
3. Generate migration: `uv run alembic revision --autogenerate -m "Add field_name to table"`
4. Apply migration: `uv run alembic upgrade head`
**ALWAYS** use HTTPException in API routes, **NEVER** return None:
```python
if not user:
raise HTTPException(status_code=404, detail="User not found")
if not user:
return None
```
**ALWAYS** commit and refresh after modifications:
```python
db.add(db_obj)
db.commit()
db.refresh(db_obj)
return db_obj
for field, value in update_data.items():
setattr(db_obj, field, value)
db.commit()
db.refresh(db_obj)
return db_obj
db.delete(db_obj)
db.commit()
return True
```
1. **Using Pydantic v1 syntax** - Always use `model_config = ConfigDict(from_attributes=True)`, NOT `class Config: orm_mode = True`
2. **Forgetting migrations** - Required after ANY model changes
3. **Not updating `__init__.py`** - Required when adding new modules to models/, schemas/, crud/, or api/
4. **Not registering router in main.py** - Router won't be accessible
5. **Wrong status codes** - Use 201 for POST, 204 for DELETE
6. **Returning None from API routes** - Use HTTPException instead
7. **Missing type hints** - Always add type annotations
```
app/
├── main.py # Application entry point
├── api/ # API route handlers
│ ├── __init__.py
│ └── users.py
├── core/ # Core configuration
│ ├── config.py
│ └── database.py
├── crud/ # Database operations
│ ├── __init__.py
│ └── user.py
├── models/ # SQLAlchemy models
│ ├── __init__.py
│ └── user.py
└── schemas/ # Pydantic models
├── __init__.py
└── user.py
```
When users request tests:
Run tests with: `uv run pytest` or `make test`
All tests use in-memory SQLite for speed and isolation.
**User**: "Add a Posts feature where users can create blog posts"
**Steps**:
1. Create Post model with title, content, author_id, timestamps
2. Create PostBase, PostCreate, PostUpdate, PostResponse schemas
3. Create CRUD operations (get, get_multi, create, update, delete)
4. Create API routes with all CRUD endpoints
5. Register router in main.py
6. Generate and apply Alembic migration
7. Suggest testing at http://localhost:8000/docs
**User**: "Add a bio field to the User model"
**Steps**:
1. Add `bio = Column(Text, nullable=True)` to User model
2. Add `bio: Optional[str] = None` to UserBase schema
3. Generate migration: `uv run alembic revision --autogenerate -m "Add bio field to users"`
4. Apply migration: `uv run alembic upgrade head`
5. Suggest testing via `/docs`
**User**: "Add search to posts by title"
**Steps**:
1. Modify `get_posts` in CRUD to accept `search: Optional[str] = None`
2. Add filter: `if search: query = query.filter(Post.title.ilike(f"%{search}%"))`
3. Update API endpoint to accept `search: Optional[str] = None` query param
4. Test at `/docs` with `?search=keyword`
```bash
bash setup.sh # Complete setup
make setup # Alternative
make dev # Start server
make db-init # Initialize database
make db-migrate MSG="..." # Create migration
make db-upgrade # Apply migrations
make test # Run all tests
uv run pytest -v # Verbose tests
```
Leave a review
No reviews yet. Be the first to review this skill!
# Download SKILL.md from killerskills.ai/api/skills/fastapi-interview-skeleton/raw