Skip to content


p3orm supports two types of relationships

  • ForeignKeyRelationship - Analogous to the table having a foreign key. This will always retrieve a single referenced model.
  • ReverseRelationship - Analagous to a different table having a foreign key referencing the current table. This will always retrieve a list of referenced models.

Default behavior

By default, relationships are not loaded from the database. Instead, all relationship fields are instantiated with the p3orm.table.UNLOADED object. This object will raise a p3orm.exceptions.UnloadedRelationship exception if you try to access any property on it.

If a relationship is fetched but there are no values to be fetched (because the foreign key is null, or there is nothing in the reverse relationship), a foreign key relationship field will become None and a reverse relationship field will become the empty list [].

Defining relationships

from __future__ import annotations

from p3orm import Table, ForeignKeyRelationship, ReverseRelationship, Column

class Parent(Table):
  id = Column(int, pk=True, autogen=True)

  children: list[Child] = ReverseRelationship(self_column="id", foreign_column="parent_id")

class Child(Table):
  id = Column(int, pk=True, autogen=True)
  name = Column(str)
  parent_id = Column(int)

  parent: Parent = ForeignKeyRelationship(self_column="parent_id", foreign_column="id")

It's not necessary to import from future import __annotations__. If you don't import, just know you will have to mark your anotations as strings, e.g. children: list["Child"] or parent: "Parent"

Fetching relationships

Fetching foreign key relationships

child = await Child.fetch_one( == 1)

child.parent # <p3orm.table.UNLOADED>

[child] = await Child.fetch_related([child], [[Child.parent]])

child.parent # <Parent>

fetch_related accepts a Sequence[Sequence[_Relationship]] to allow for fetching multiple relationships and deeply nested relationships.

Fetching reverse foreign key relationships

parent = await Parent.fetch_one( == 1)

parent.children # <p3orm.table.UNLOADED>

[parent] = await Parent.fetch_related([parent], [[Parent.children]])

parent.children # <list[Child]>

Fetching multiple relationships

thing = await Thing.fetch_one( == 1)

[thing] = await Thing.fetch_related([thing], [[thing.f1], [thing.f2], [thing.rr1]])

thing.f1 # <Model>
thing.f2 # <Model2>
thing.rr1 # <list[Model3]>

Fetching deeply nested relationships

thing = await Thing.fetch_one( == 1)

[thing] = await Thing.fetch_related([thing], [[thing.children, thing.children.child]])

thing.children # <list[Child]>

thing.children[0].child # <Child>

Prefetching relationships

p3orm allows you to prefetch relationships while making other requests, rather than having to relationships explicitly. These are passed in as the prefetch keyword argument that is of the same format as the relationships passed into fetch_related.

The methods that support prefetching are:

  • Table.fetch_one
  • Table.fetch_first
  • Table.fetch_all
  • Table.insert_one
  • Table.insert_many
  • Table.update_one


company_with_employees = await Company.fetch_one( == 1, prefetch=[[Company.employees]])