Python for PHP Developers, Part 2: FastAPI with a Real Database

This is Part 2 of a series. If you haven't read Part 1 (syntax side-by-side, gotchas, and your first FastAPI endpoint), start here: Python for PHP Developers: A Practical Crash Course

In Part 1, we built a FastAPI app that stored data in memory. It disappeared every time the server restarted. This time, we connect it to a real database using SQLAlchemy — Python's answer to Eloquent or Doctrine.

1. Install what you need

pip install sqlalchemy fastapi "uvicorn[standard]"

We'll use SQLite for simplicity. The same code works with PostgreSQL or MySQL by changing one connection string.

2. Define the database connection

# database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

DATABASE_URL = "sqlite:///./app.db"

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

If you've used Doctrine, Base plays the same role as your entity base class, and SessionLocal is roughly your EntityManager.

3. Define a model

# models.py
from sqlalchemy import Column, Integer, String, Float
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)

This is your schema and your ORM mapping in one place — closer to Doctrine annotations than to a separate Eloquent migration file.

4. Pydantic schemas vs SQLAlchemy models

This trips up a lot of PHP developers at first: Python keeps database models (SQLAlchemy) separate from API request/response shapes (Pydantic). They look similar but serve different jobs.

# schemas.py
from pydantic import BaseModel

class ItemCreate(BaseModel):
    name: str
    price: float

class ItemOut(ItemCreate):
    id: int

    class Config:
        from_attributes = True   # lets Pydantic read SQLAlchemy objects directly

Think of it this way: the SQLAlchemy model is your table row. The Pydantic model is your API contract. Keeping them separate means you can change your database schema without automatically exposing it in your API, and vice versa.

5. Wire it into FastAPI

# main.py
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session

from database import engine, SessionLocal, Base
import models, schemas

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.ItemOut)
def create_item(item: schemas.ItemCreate, db: Session = Depends(get_db)):
    db_item = models.Item(**item.model_dump())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

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

@app.get("/items", response_model=list[schemas.ItemOut])
def list_items(db: Session = Depends(get_db)):
    return db.query(models.Item).all()

6. The part that confuses PHP developers most: Depends

Depends(get_db) is FastAPI's dependency injection system. Every request gets its own fresh database session, used for that request, then closed automatically — even if the request fails. If you've used Laravel's service container or Symfony's DI container, this is the same idea, just declared inline on each route instead of configured separately.

7. Gotchas for PHP developers

• No automatic migrations by default. Base.metadata.create_all() only creates tables that don't exist yet — it won't alter an existing table if you change a column. For real projects, add Alembic (SQLAlchemy's migration tool, similar to Laravel migrations or Doctrine Migrations) from day one.

• Sessions are not connections. A SQLAlchemy Session batches changes and only hits the database on commit(). Forgetting to commit is a common first bug.

• N+1 queries are just as real in Python. SQLAlchemy has lazy loading too — watch for it the same way you would in Eloquent or Doctrine.

What's next

Part 3 will add JWT authentication to this same API, so only logged-in users can create or view items.

Series: Part 1: Crash Course · Part 2: FastAPI + Database (this post) · Part 3: JWT Auth (coming soon)

Building along with this series? Tell me where you got stuck.