How to make alembic autogenerate foreign key relations?

Question

I'm trying to get Alembic to autogenerate a first version of my database, which is at this point very simple. The command works, and generates a migration with all of my tables and columns, except all ForeignKey relations.

My code looks like this:

from typing import List
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped, relationship


class Base(DeclarativeBase):
    pass


class DBUnitCategory(Base):
    __tablename__ = 'unit_category'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(20))

    unit_id: Mapped[List["DBUnit"]] = relationship(back_populates="category_id")

class DBUnit(Base):
    __tablename__ = 'unit'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(12))

    category_id: Mapped[DBUnitCategory] = relationship(back_populates="unit_id")

And the generated upgrade migration looks like this:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    
    op.create_table('unit',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=12), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('unit_category',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=20), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('unit_category')
    op.drop_table('unit')

I don't think I messed up my definitions, it looks like in the tutorial to me. There is no issue with this on the github, so I don't understand what's going on. Is it related to the fact I'm using an sqlite database?

Answer 1

Relationships create relationships between entities in the Python layer. You need to declare foreign key columns explicitly to create foreign keys in the database. You might want to review the documentation on relationship configuration.

Assuming that each category can have multiple units, you probably want something like this:

...

class DBUnitCategory(Base):
    __tablename__ = 'unit_category'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(20))

    units: Mapped[List["DBUnit"]] = relationship(back_populates="category")

class DBUnit(Base):
    __tablename__ = 'unit'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(12))
    category_id: Mapped[int] = mapped_column(ForeignKey('unit_category.id'))

    category: Mapped[DBUnitCategory] = relationship(back_populates="units")