SQLAlchemy Schema Generator

TalkingSchema's AI copilot generates SQLAlchemy 2.0 ORM models from your entity relationship diagram. Output uses the modern declarative mapping style with mapped_column() and Python type annotations — the current best practice for new Python projects. The AI also generates relationship() declarations, ForeignKey definitions, UniqueConstraint, and Index objects.

Outputs are provided as Python code blocks, with an option to download as a .py file coming soon

---

Example Output

Using the GSSC (Global Sustainable Supply Chain) schema:

# models.py
from __future__ import annotations
import enum
from datetime import datetime
from decimal import Decimal
from typing import Optional, List
from uuid import UUID

from sqlalchemy import (
    Boolean, CheckConstraint, Date, DateTime, Enum, ForeignKey,
    Integer, Numeric, String, UniqueConstraint, func
)
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column, relationship
)
from sqlalchemy.dialects.postgresql import UUID as PG_UUID


class Base(DeclarativeBase):
    pass


class CarbonTier(enum.Enum):
    A = "A"
    B = "B"
    C = "C"
    D = "D"


class OrderStatus(enum.Enum):
    draft     = "draft"
    confirmed = "confirmed"
    shipped   = "shipped"
    received  = "received"
    cancelled = "cancelled"


class Supplier(Base):
    __tablename__ = "suppliers"

    supplier_id:   Mapped[UUID]          = mapped_column(PG_UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid())
    company_name:  Mapped[str]           = mapped_column(String(200), nullable=False)
    country:       Mapped[str]           = mapped_column(String(100), nullable=False)
    carbon_tier:   Mapped[CarbonTier]    = mapped_column(Enum(CarbonTier), nullable=False)
    certification: Mapped[Optional[str]] = mapped_column(String(100))
    is_active:     Mapped[bool]          = mapped_column(Boolean, default=True, nullable=False)
    created_at:    Mapped[datetime]      = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at:    Mapped[datetime]      = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    products:       Mapped[List["Product"]]       = relationship("Product", back_populates="supplier")
    purchase_orders: Mapped[List["PurchaseOrder"]] = relationship("PurchaseOrder", back_populates="supplier")


class Product(Base):
    __tablename__ = "products"

    product_id:            Mapped[UUID]             = mapped_column(PG_UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid())
    sku:                   Mapped[str]              = mapped_column(String(50), unique=True, nullable=False)
    name:                  Mapped[str]              = mapped_column(String(200), nullable=False)
    category:              Mapped[str]              = mapped_column(String(100), nullable=False)
    unit_weight_kg:        Mapped[Optional[Decimal]] = mapped_column(Numeric(8, 3))
    unit_cost_usd:         Mapped[Decimal]          = mapped_column(Numeric(12, 4), nullable=False)
    carbon_intensity_score: Mapped[Optional[Decimal]] = mapped_column(Numeric(5, 2))
    supplier_id:           Mapped[UUID]             = mapped_column(ForeignKey("suppliers.supplier_id"), nullable=False)
    is_active:             Mapped[bool]             = mapped_column(Boolean, default=True)
    created_at:            Mapped[datetime]         = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at:            Mapped[datetime]         = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    supplier:            Mapped["Supplier"]               = relationship("Supplier", back_populates="products")
    purchase_order_items: Mapped[List["PurchaseOrderItem"]] = relationship("PurchaseOrderItem", back_populates="product")


class PurchaseOrder(Base):
    __tablename__ = "purchase_orders"

    po_id:            Mapped[UUID]             = mapped_column(PG_UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid())
    supplier_id:      Mapped[UUID]             = mapped_column(ForeignKey("suppliers.supplier_id"), nullable=False)
    warehouse_id:     Mapped[UUID]             = mapped_column(ForeignKey("warehouses.warehouse_id"), nullable=False)
    order_date:       Mapped[datetime]         = mapped_column(Date, nullable=False)
    expected_delivery: Mapped[Optional[datetime]] = mapped_column(Date)
    status:           Mapped[Optional[OrderStatus]] = mapped_column(Enum(OrderStatus))
    created_at:       Mapped[datetime]         = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at:       Mapped[datetime]         = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())

    supplier:  Mapped["Supplier"]              = relationship("Supplier", back_populates="purchase_orders")
    items:     Mapped[List["PurchaseOrderItem"]] = relationship("PurchaseOrderItem", back_populates="purchase_order", cascade="all, delete-orphan")


class PurchaseOrderItem(Base):
    __tablename__ = "purchase_order_items"

    item_id:     Mapped[UUID]    = mapped_column(PG_UUID(as_uuid=True), primary_key=True, server_default=func.gen_random_uuid())
    po_id:       Mapped[UUID]    = mapped_column(ForeignKey("purchase_orders.po_id", ondelete="CASCADE"), nullable=False)
    product_id:  Mapped[UUID]    = mapped_column(ForeignKey("products.product_id"), nullable=False)
    quantity:    Mapped[int]     = mapped_column(Integer, nullable=False)
    unit_cost_usd: Mapped[Decimal] = mapped_column(Numeric(12, 4), nullable=False)

    purchase_order: Mapped["PurchaseOrder"] = relationship("PurchaseOrder", back_populates="items")
    product:        Mapped["Product"]       = relationship("Product", back_populates="purchase_order_items")

    __table_args__ = (
        CheckConstraint("quantity > 0", name="ck_poi_quantity_positive"),
    )

---

Customizing the Output

Requirement	Prompt addition
Classic ORM style (SQLAlchemy 1.x)	"Use the classic SQLAlchemy 1.x Column() style, not mapped_column()"
Alembic migration stub	"Also generate an Alembic env.py snippet and an initial migration file"
Pydantic models alongside	"Generate Pydantic v2 models (BaseModel) for API request/response alongside each SQLAlchemy model"
__repr__ methods	"Add a repr method to each model showing the primary key value"
Separate files per domain	"Split output into: models/suppliers.py, models/orders.py, models/logistics.py"

---

Frequently Asked Questions

Is the output compatible with FastAPI?

Yes. The SQLAlchemy models work with FastAPI's async session pattern. Ask the AI: "Generate FastAPI router stubs for CRUD operations using these SQLAlchemy models with async sessions."

Does it support async SQLAlchemy?

Yes. Ask: "Use SQLAlchemy 2.0 async session style with AsyncSession and async_sessionmaker." The AI adjusts the mapped_column() and relationship configuration accordingly.