fastapi int model field

by

Summary

This article deconstructs the confusion around defining an auto‑incrementing primary key in a FastAPI/SQLAlchemy model. The key takeaway is that SQLAlchemy’s mapped_column creates an auto‑generated integer ID by default; the problem often originates from mixing declarative and SQLAlchemy 2.0 mapping styles or misreading the documentation.

Root Cause

  • Misunderstanding of the mapped_column() signature.
  • Mixing the old declarative_base() style with the new 2.0 style mapped syntax.
  • Explicitly setting init=False (invalid for mapped_column) or manually passing an ID during object creation.
  • Failure to call session.commit() after adding the instance to generate the ID.

Why This Happens in Real Systems

  • Most tutorials mix examples from SQLAlchemy 1.x and 2.0, causing developers to insert legacy patterns that no longer apply.
  • Auto‑incrementing fields are controlled at database level; an ORM model must merely declare the column as a primary key and let the database assign the value.
  • Error messages like “if you have this info please also provide the documentation page” signal that developers are looking for a quick workaround rather than understanding the underlying configuration.

Real-World Impact

  • Data integrity issues: duplicate IDs when the database didn’t assign them correctly.
  • Runtime errors: TypeError: init was an unexpected keyword argument when init=False is used.
  • Developer frustration that slows down feature delivery.
  • Potential security gaps if manual ID assignment is permitted, exposing the system to ID enumeration attacks.

Example or Code

from sqlalchemy import Integer, create_engine
from sqlalchemy.orm import sessionmaker, declarative_base, mapped, mapped_column

Base = declarative_base()

class Role(Base):
    __tablename__ = "roles"

    id: mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    name: mapped[str] = mapped_column(String(50), unique=True, nullable=False)

# Create engine and tables
engine = create_engine("sqlite:///example.db", echo=True)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()
new_role = Role(name="admin")
session.add(new_role)
session.commit()          # DB assigns id, accessible via new_role.id

How Senior Engineers Fix It

  • Adopt the new 2.0 style everywhere: mapped and mapped_column.
  • Remove any manual id arguments when constructing instances.
  • Verify that your database dialect supports auto‑increment (most do).
  • Run session.commit() immediately after adding to ensure the ID is fetched from the DB.
  • Add unit tests that create a new instance without an ID and assert that instance.id is not None.

Why Juniors Miss It

  • They often copy code snippets without context, overlooking that init=False is not a supported parameter for mapped_column.
  • Lack of exposure to database autogeneration semantics; they think the ORM must supply the value.
  • Overreliance on IDE auto‑completions that generate incorrect code.
  • Mistaking the presence of primary_key=True for a requirement to supply a value.

The concrete tip: declare the column with primary_key=True, autoincrement=True and never pass an ID when creating a new object.

Leave a Comment