Building a REST API with FastAPI and SQLAlchemy: A Step-by-Step Guide

Table of Contents

• Quick Answer
• Why This Matters
• Prerequisites
• Step-by-Step Setup
• Best Practices
• Troubleshooting
• FAQ
• Conclusion

Quick Answer

You can build a REST API with FastAPI and SQLAlchemy by defining models, setting up a database connection, creating Pydantic schemas, and writing route handlers. This combination gives you automatic OpenAPI docs, async support, and robust ORM features.

Why This Matters

If you’re building modern web applications with Python, you want speed, type safety, and clear documentation. FastAPI delivers all that, and SQLAlchemy provides a mature ORM for database interactions. Whether you’re creating a microservice or a full backend, this stack lets you move fast without cutting corners. In the Polish development scene, teams at https://aminalaee.dev/ and elsewhere rely on this exact combination for production systems.

By the end of this guide you’ll have a working API that handles CRUD operations against a SQLite database, ready to be swapped for PostgreSQL or MySQL. You’ll also learn how to avoid common pitfalls and structure your code for maintainability.

Prerequisites

Before you start, make sure you have:

• Python 3.8+ installed
• Basic understanding of Python decorators and type hints
• A virtual environment (recommended)

Here are the packages you’ll need:

Step-by-Step Setup

Step 1: Initialize the Project

Create a new folder and set up a virtual environment. This isolates dependencies and prevents version conflicts.

Expected result: A clean environment with no packages yet.

Pro tip: Use python -m venv venv and activate it.

Step 2: Install Dependencies

Run pip install fastapi uvicorn sqlalchemy pydantic. Optionally add asyncpg for async PostgreSQL.

Reason: These are the core libraries you need.

Common mistake: Forgetting to activate the virtual environment before installing – packages go to global scope instead.

Step 3: Create the Database Models

Define a models.py file. For example, a simple Item model with id, name, price, and is_offer.

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

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    price = Column(Float)
    is_offer = Column(Boolean, default=False)

Expected result: A mapped class ready for table creation.

Step 4: Set Up the Database Connection

In database.py, create the engine and session factory. Use sqlite:///./test.db for local development.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

Pro tip: For async support, use asyncpg and create_async_engine.

Step 5: Create Pydantic Schemas

Define schemas.py with read and create schemas. This ensures proper validation and serialisation.

from pydantic import BaseModel

class ItemBase(BaseModel):
    name: str
    price: float
    is_offer: bool = False

class ItemCreate(ItemBase):
    pass

class Item(ItemBase):
    id: int
    class Config:
        orm_mode = True

Expected result: Schemas that match your model but are decoupled from the ORM.

Step 6: Write API Routes

In main.py, set up the FastAPI app and add routes for creating and reading items.

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
import models, schemas
from database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)
app = FastAPI()

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

@app.post("/items/", response_model=schemas.Item)
def create_item(item: schemas.ItemCreate, db: Session = Depends(get_db)):
    db_item = models.Item(**item.dict())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

@app.get("/items/{item_id}", response_model=schemas.Item)
def read_item(item_id: int, db: Session = Depends(get_db)):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()
    if db_item is None:
        raise HTTPException(status_code=404, detail="Item not found")
    return db_item

Reason: The dependency injection system in FastAPI handles database sessions cleanly.

Common mistake: Not calling models.Base.metadata.create_all – tables won’t be created.

Step 7: Run the Server

Execute uvicorn main:app --reload. Visit http://127.0.0.1:8000/docs to see the interactive API documentation.

Expected result: A working API with auto-generated Swagger UI.

Best Practices

• Use async endpoints when dealing with I/O-bound operations. FastAPI supports both sync and async natively.
• Separate concerns – put models, schemas, and routes in different files. It scales better.
• Handle migrations with Alembic instead of create_all for production.
• Validate input with Pydantic – avoid raw SQL injection.
• Log errors and set up proper exception handlers.

Checklist for your final API:

Troubleshooting

FAQ

1. Can I use SQLAlchemy with FastAPI without an ORM?

Yes, you can use raw SQL with SQLAlchemy Core, but the ORM simplifies object mapping and reduces boilerplate. Most developers prefer ORM for CRUD apps.

2. How do I switch from SQLite to PostgreSQL?

Change the connection string in database.py to postgresql://user:pass@localhost/dbname. Install psycopg2 (or asyncpg for async) and update connect_args accordingly.

3. Why is my API slow when using SQLite?

SQLite is single‑threaded and less optimised for concurrent reads/writes. For production, migrate to PostgreSQL or MySQL. Also ensure you’re using async routes where possible.

4. Do I need Alembic for database migrations?

Not for a simple prototype, but for any project that evolves, yes. Alembic keeps track of schema changes and lets you upgrade/downgrade safely.

5. How do I handle authentication in this stack?

FastAPI integrates easily with OAuth2, JWT, or API keys. You can add dependency functions that check tokens before allowing access to routes.

Conclusion

You now have a functional REST API built with FastAPI and SQLAlchemy. The setup is lightweight, type‑safe, and ready for expansion. Stick to the best practices shown here – separate your models, schemas, and routes; use dependency injection for database sessions; and always validate input. With these foundations, you can add more endpoints, switch databases, and ship to production without rewriting everything. The real power of this stack is how little friction it creates between idea and implementation.

— System Account