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:
- A primary key constraint, OR
- A unique constraint
When Iceaxe generates foreign key dependencies, it looks for either:
"user.['id'].PRIMARY KEY"(individual primary key on theidcolumn)"user.['id'].UNIQUE"(unique constraint on theidcolumn)
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
idfield as the primary key - Use unique constraints for business logic: If you need uniqueness across multiple fields, use
UniqueConstraintintable_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.