Relationships

An employee belongs to an office, and an office can have many employees. Store them in separate tables and link each employee's office_id to Office.id with a foreign key:

from iceaxe import TableBase, Field

class Office(TableBase):
    id: int = Field(primary_key=True)
    name: str

class Employee(TableBase):
    id: int = Field(primary_key=True)
    name: str
    office_id: int = Field(foreign_key="office.id")

Postgres checks that an inserted employee's office_id exists in the office table. If it does not, the insert raises an error:

office = Office(name="HQ")
await conn.insert([office])

valid_employee = Employee(name="Alice", office_id=office.id)
invalid_employee = Employee(name="Bob", office_id=11111111)

await conn.insert([valid_employee]) # Works
await conn.insert([invalid_employee]) # Raises an error

Use join to fetch an employee and their office in one query. Selecting both models returns a list of (Employee, Office) tuples:

from iceaxe import select

query = select((Employee, Office)).join(
    Office,
    Employee.office_id == Office.id,
).where(
    Employee.name == "Alice",
)
results = await conn.exec(query)

alice, office = results[0]
print(f"{alice.name} (office.name)") # Alice (HQ)

Many-to-many relationships

A person can have several favorite colors, and each color can be a favorite of several people. A third table, PersonFavoriteColor, stores one person-color pair per row. Its two foreign keys also form a composite primary key, preventing duplicate pairs:

from iceaxe import TableBase, Field

class Person(TableBase):
    id: int = Field(primary_key=True)
    name: str

class FavoriteColor(TableBase):
    id: int = Field(primary_key=True)
    color: str

class PersonFavoriteColor(TableBase):
    person_id: int = Field(foreign_key="person.id", primary_key=True)
    color_id: int = Field(foreign_key="favorite_color.id", primary_key=True)

Join through PersonFavoriteColor to fetch each person's colors:

query = select((Person, FavoriteColor)).join(
    PersonFavoriteColor,
    Person.id == PersonFavoriteColor.person_id,
).join(
    FavoriteColor,
    PersonFavoriteColor.color_id == FavoriteColor.id,
).where(
    Person.name == "Alice",
)

The two joins first find Alice's person-color pairs, then fetch each color:

SELECT person.*, favorite_color.*
FROM person
JOIN person_favorite_color ON person.id = person_favorite_color.person_id
JOIN favorite_color ON person_favorite_color.color_id = favorite_color.id
WHERE person.name = 'Alice'

A plain JOIN is an INNER JOIN, so it returns only matching pairs. If Alice has three favorite colors, the result contains three rows, each repeating Alice's data alongside one color:

[
    (Person(name="Alice"), FavoriteColor(color="Red")),
    (Person(name="Alice"), FavoriteColor(color="Green")),
    (Person(name="Alice"), FavoriteColor(color="Blue")),
]

For a small result set, group the returned pairs by person in Python:

from itertools import groupby

results = await conn.exec(query)
grouped = groupby(sorted(results, key=lamda x: x.id), lambda x: x.id)
for person_id, person_colors in grouped:
    person, colors = person_colors[0]
    print(f"{person.name} likes {', '.join(color.color for person, color in person_colors)}")

Alternatively, you can separate out your queries: one query to fetch the people and one query to fetch the colors for all matching IDs:

query = select(Person).where(Person.name == "Alice")
people = await conn.exec(query)

query = select((PersonFavoriteColor.person_id, FavoriteColor)).join(
    PersonFavoriteColor,
    FavoriteColor.id == PersonFavoriteColor.color_id,
).where(
    PersonFavoriteColor.person_id.in_([person.id for person in people]),
)

Custom Identifiers

Foreign keys usually point at columns that all share the same storage type, like int or UUID. That can make it easy to accidentally assign the wrong ID to the wrong field. Prefer NewType when you want each table's ID to have its own static type without adding a new runtime object. Define these types in a shared identifiers.py file:

# identifiers.py
from typing import NewType
from uuid import UUID

OfficeId = NewType("OfficeId", UUID)
EmployeeId = NewType("EmployeeId", UUID)

NewType lets your static type checker treat OfficeId and EmployeeId as distinct types, while runtime values remain UUIDs. Use the same identifier type for the table's primary key and for any foreign key that references it:

from uuid import UUID

from iceaxe import TableBase, Field
from myapp.identifiers import EmployeeId, OfficeId

class Office(TableBase):
    id: OfficeId = Field(primary_key=True)
    name: str

class Employee(TableBase):
    id: EmployeeId = Field(primary_key=True)
    name: str
    office_id: OfficeId = Field(foreign_key="office.id")

office = Office(
    id=OfficeId(UUID("87654321-4321-6789-4321-678943216789")),
    name="HQ",
)
employee = Employee(
    id=EmployeeId(UUID("12345678-1234-5678-1234-567812345678")),
    name="Alice",
    office_id=office.id,
)

employee.office_id = employee.id  # type checker error

OfficeId and EmployeeId are still stored as UUID columns in Postgres, but static type checkers can now flag swapped assignments before they become relationship bugs.

Choosing an identifier type

Use NewType("OfficeId", UUID) for IDs whose distinction matters to static type checking. It returns the existing value unchanged:

raw_id = UUID("87654321-4321-6789-4321-678943216789")
office_id = OfficeId(raw_id)

assert office_id is raw_id
assert isinstance(office_id, UUID)

This avoids allocating a separate ID object for each selected value. Iceaxe still runs normal Pydantic validation when constructing table models, including UUID parsing and your field and model validators. OfficeId(value) itself does not parse or validate input; use UUID(text) to parse a string before wrapping it, as in the examples above.

Use a UUID subclass when you need a distinct runtime class, such as for isinstance checks or custom methods. To return that class, Iceaxe must reconstruct each UUID supplied by the database driver as the requested subclass. This adds conversion and allocation work for both full-model and direct column selects, which can be significant for large result sets with several ID columns. Other supported scalar subclasses also incur conversion work.

Changing a UUID subclass to NewType preserves UUID storage in PostgreSQL, but changes the Python runtime behavior. NewType does not provide subclass methods or run a subclass constructor, and isinstance(value, OfficeId) raises TypeError. Check callers that rely on those behaviors before switching. A plain UUID field is sufficient when you do not need distinct ID types.

Composite Primary Keys and Foreign Key Constraints

A foreign key must reference columns that uniquely identify a row. Marking two fields as primary_key=True makes their combination unique; it does not make either field unique by itself.

The Issue

Here, Post.user_id references only one column of User's composite key:

class User(TableBase):
    id: int = Field(primary_key=True)
    tenant_id: int = Field(primary_key=True)  # Composite primary key
    name: str

class Post(TableBase):
    id: int = Field(primary_key=True)
    user_id: int = Field(foreign_key="user.id")  # This will cause an error!
    content: str

Migration generation fails because user.id has no uniqueness constraint of its own.

Why This Happens

Iceaxe creates one primary key across id and tenant_id. PostgreSQL permits only one primary key per table, but that key may contain several columns.

For this single-column foreign key, the target column must have either:

  1. A primary key constraint, OR
  2. A unique constraint

When Iceaxe generates foreign key dependencies, it looks for either:

  • "user.['id'].PRIMARY KEY" (individual primary key on the id column)
  • "user.['id'].UNIQUE" (unique constraint on the id column)

But when you have multiple primary_key=True fields, what actually gets created is:

  • "user.['id', 'tenant_id'].PRIMARY KEY" (composite primary key spanning both columns)

Neither single-column constraint exists, so Iceaxe cannot resolve the reference.

Solutions

Approach 1 - Modify the current table (recommended):

Use a single primary key and add unique constraints for other uniqueness requirements:

class User(TableBase):
    id: int = Field(primary_key=True)  # Single primary key
    tenant_id: int
    name: str
    
    # Add uniqueness constraint if needed
    table_args = [
        UniqueConstraint(columns=["id", "tenant_id"])
    ]

class Post(TableBase):
    id: int = Field(primary_key=True)
    user_id: int = Field(foreign_key="user.id")  # Now works!
    content: str

Approach 2 - Modify the target tables:

If you need to retain the composite primary key, add a separate unique constraint to id. This allows a foreign key to reference id alone:

class User(TableBase):
    id: int = Field(primary_key=True, unique=True)  # Add individual unique constraint
    tenant_id: int = Field(primary_key=True)
    name: str

class Post(TableBase):
    id: int = Field(primary_key=True)
    user_id: int = Field(foreign_key="user.id")  # Now works because id has unique constraint
    content: str

Best Practices

  • Use single primary keys when possible: Most tables benefit from a simple auto-incrementing id field as the primary key
  • Use unique constraints for business logic: If you need uniqueness across multiple fields, use UniqueConstraint in table_args
  • Reserve composite primary keys for junction tables: They're most commonly used in many-to-many relationship tables where the combination of foreign keys forms the natural primary key

Comparison with other ORMs

Some ORMs let you define relationships as attributes on a model:

class Parent(Base):
    children = relationship("Child")

class Child(Base):
    parent_id = Column(Integer, ForeignKey("parent.id"))
    parent = relationship("Parent")

With lazy loading, accessing parent.children issues another query to fetch the children:

# SELECT * FROM parent WHERE id = ?
parent_obj = db_session.get(Parent, parent_id)

# SELECT * FROM child WHERE parent_id = ?
for child in parent_obj.children:
    print(child)

Implicit queries make it harder to see where async code needs to wait for the database. They can also cause the N+1 query problem: fetching N parents, then issuing one query per parent to load its children.

Iceaxe keeps those queries explicit through joins. If you want a model method that loads related rows, give it a connection and make the database call visible:

from iceaxe import TableBase, Field, select

class Parent(TableBase):
    id: int = Field(primary_key=True)
    name: str

    async def children(self, conn: DBConnection):
        query = select(Child).where(Child.parent_id == self.id)
        return await conn.exec(query)

Every call to children issues a query. When loading children for several parents, prefer a join or a single query filtered by all the parent IDs.