FastAPI Creating Database Models and Tables

A database model is a Python class that maps to a table in your database. Each class attribute becomes a column. SQLAlchemy reads these classes and creates the matching tables automatically — no SQL DDL scripts needed.

ORM — The Bridge Between Python and SQL

Python Class               SQL Table
──────────────────────────────────────────
class User(Base):    ←→   CREATE TABLE users (
    id: int               id INTEGER PRIMARY KEY,
    name: str             name VARCHAR,
    email: str            email VARCHAR UNIQUE
                          );

ORM stands for Object-Relational Mapper. You work with Python objects; SQLAlchemy translates them into SQL behind the scenes.

Defining a Model

# models.py

from sqlalchemy import Column, Integer, String, Boolean, DateTime
from sqlalchemy.sql import func
from database 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, nullable=False)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

Column Options Explained

primary_key=True  → this column is the unique identifier
index=True        → speeds up search queries on this column
unique=True       → no two rows can have the same value
nullable=False    → this field is required (cannot be empty)
default=True      → Python-side default when inserting a row
server_default    → database-side default (set by the DB engine)

Common Column Types

SQLAlchemy Type   SQL Equivalent      Python Type
─────────────────────────────────────────────────
Integer           INT                 int
String            VARCHAR             str
Text              TEXT                str
Float             FLOAT               float
Boolean           BOOLEAN             bool
DateTime          TIMESTAMP           datetime
Date              DATE                date
JSON              JSON                dict / list

Creating Tables from Your Models

# In main.py or a setup script

from database import engine
import models

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

This one line reads every class that inherits from Base and creates the matching table if it does not already exist. Existing tables are left untouched.

Defining Relationships Between Tables

Real databases have related tables. A user can have many posts:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = "posts"

    id      = Column(Integer, primary_key=True, index=True)
    title   = Column(String, nullable=False)
    content = Column(String)
    user_id = Column(Integer, ForeignKey("users.id"))

    author  = relationship("User", back_populates="posts")

class User(Base):
    __tablename__ = "users"
    # ... other columns ...
    posts = relationship("Post", back_populates="author")

users table          posts table
───────────          ───────────
id  name             id  title       user_id
1   Meera            10  "Hello"     1        ← belongs to Meera
2   Raj              11  "World"     1        ← also belongs to Meera
                     12  "FastAPI"   2        ← belongs to Raj

Separating Pydantic Schemas from SQLAlchemy Models

Keep database models and API schemas in separate files to avoid confusion:

project/
├── models.py    ← SQLAlchemy models (database tables)
└── schemas.py   ← Pydantic models (request/response shapes)

# schemas.py
from pydantic import BaseModel

class UserCreate(BaseModel):   ← what client sends
    name: str
    email: str

class UserResponse(BaseModel): ← what client receives
    id: int
    name: str
    email: str

    class Config:
        orm_mode = True   ← allows reading SQLAlchemy objects directly

The orm_mode = True setting tells Pydantic to read data from SQLAlchemy model attributes, not just plain dictionaries.

Key Points

• Define database tables as Python classes inheriting from Base.
• Each Column() becomes a table column with optional constraints.
• Call Base.metadata.create_all(bind=engine) to create tables automatically.
• Use ForeignKey and relationship() to link related tables.
• Set orm_mode = True in Pydantic schemas to read SQLAlchemy model objects.