Relations

Oxyde supports foreign key relationships and eager loading.

Foreign Keys

Defining a Foreign Key

Foreign keys are defined using type annotations:

class Post(Model):
    id: int | None = Field(default=None, db_pk=True)
    title: str
    author: Author | None = Field(default=None, db_on_delete="CASCADE")

    class Meta:
        is_table = True

The FK target class must be defined before the model that references it. See Type annotations and forward references for details.

This creates: - A column author_id in the database - A foreign key constraint to Author.id

Working with Foreign Keys

# Create with FK ID
post = await Post.objects.create(
    title="Hello World",
    author_id=1  # Use the _id suffix
)

# Access FK value
print(post.author_id)  # 1

FK to Non-PK Field

Reference a different field:

class Resource(Model):
    id: int | None = Field(default=None, db_pk=True)
    tenant: Tenant | None = Field(
        default=None,
        db_fk="uuid",  # Target Tenant.uuid instead of Tenant.id
        db_on_delete="CASCADE"
    )

    class Meta:
        is_table = True

This creates column tenant_uuid referencing Tenant.uuid.

ON DELETE Actions

Action	Description
CASCADE	Delete related rows
SET NULL	Set FK to NULL
RESTRICT	Prevent deletion
NO ACTION	Deferred check

# CASCADE - delete posts when author deleted
author: Author | None = Field(default=None, db_on_delete="CASCADE")

# SET NULL - keep post, set author to NULL
author: Author | None = Field(default=None, db_on_delete="SET NULL")

# RESTRICT - prevent deletion if posts exist
author: Author | None = Field(default=None, db_on_delete="RESTRICT")

Eager Loading

join()

Load related models in a single query (LEFT JOIN):

# Load author with each post
posts = await Post.objects.join("author").all()

for post in posts:
    print(f"{post.title} by {post.author.name}")

Without join(), accessing post.author would require another query.

Multiple Joins

posts = await Post.objects.join("author", "category").all()

Nested Joins

Use __ to traverse relations:

# Post -> Author -> Company
posts = await Post.objects.join("author__company").all()
for post in posts:
    print(f"{post.title} by {post.author.name} at {post.author.company.name}")

Reverse Relations

Defining Reverse FK

On the "one" side of a one-to-many:

class Author(Model):
    id: int | None = Field(default=None, db_pk=True)
    name: str
    posts: list["Post"] = Field(db_reverse_fk="author")  # Virtual field for reverse relation

    class Meta:
        is_table = True


class Post(Model):
    id: int | None = Field(default=None, db_pk=True)
    title: str
    author: Author | None = Field(default=None, db_on_delete="CASCADE")

    class Meta:
        is_table = True

prefetch()

Load reverse relations:

# Load all posts for each author
authors = await Author.objects.prefetch("posts").all()

for author in authors:
    print(f"{author.name} has {len(author.posts)} posts")
    for post in author.posts:
        print(f"  - {post.title}")

prefetch() executes a separate query and attaches results.

Many-to-Many

Defining M2M

Use a junction table:

class Post(Model):
    id: int | None = Field(default=None, db_pk=True)
    title: str
    tags: list["Tag"] = Field(db_m2m=True, db_through="PostTag")

    class Meta:
        is_table = True


class Tag(Model):
    id: int | None = Field(default=None, db_pk=True)
    name: str = Field(db_unique=True)

    class Meta:
        is_table = True


class PostTag(Model):
    id: int | None = Field(default=None, db_pk=True)
    post: Post | None = Field(default=None, db_on_delete="CASCADE")
    tag: Tag | None = Field(default=None, db_on_delete="CASCADE")

    class Meta:
        is_table = True

Working with M2M

M2M relations are supported by prefetch(). The through model must have FK fields to both models:

# Load posts with their tags
posts = await Post.objects.prefetch("tags").all()
for post in posts:
    print(f"{post.title}: {[t.name for t in post.tags]}")

You can also work with the junction table directly:

# Add tag to post
await PostTag.objects.create(post_id=post.id, tag_id=tag.id)

# Remove tag from post
await PostTag.objects.filter(post_id=post.id, tag_id=tag.id).delete()

# Get all tags for a post (alternative approach)
post_tags = await PostTag.objects.filter(post_id=post.id).join("tag").all()
tags = [pt.tag for pt in post_tags]

Automatic FK Column Naming

Oxyde automatically creates FK columns:

Field Definition	Created Column
author: Author	author_id
author: Author (PK is uuid)	author_uuid
category: Category (FK to code)	category_code

Common Patterns

Self-Referential FK

from __future__ import annotations

class Category(Model):
    id: int | None = Field(default=None, db_pk=True)
    name: str
    parent: Category | None = Field(default=None, db_on_delete="SET NULL")

    class Meta:
        is_table = True

from __future__ import annotations is required here because Category references itself.

Polymorphic Relations (Manual)

class Comment(Model):
    id: int | None = Field(default=None, db_pk=True)
    content: str
    commentable_type: str  # "post" or "photo"
    commentable_id: int

    class Meta:
        is_table = True

# Query
post_comments = await Comment.objects.filter(
    commentable_type="post",
    commentable_id=post_id
).all()

Soft Delete with Relations

class Post(Model):
    id: int | None = Field(default=None, db_pk=True)
    title: str
    deleted_at: datetime | None = Field(default=None)
    author: Author | None = Field(default=None, db_on_delete="SET NULL")

    class Meta:
        is_table = True

# Query active posts with author
posts = await Post.objects.filter(
    deleted_at__isnull=True
).join("author").all()

Complete Example

from datetime import datetime
from oxyde import Model, Field, db

class Author(Model):
    id: int | None = Field(default=None, db_pk=True)
    name: str
    posts: list["Post"] = Field(db_reverse_fk="author")

    class Meta:
        is_table = True
        table_name = "authors"


class Post(Model):
    id: int | None = Field(default=None, db_pk=True)
    title: str
    content: str
    author: Author | None = Field(default=None, db_on_delete="CASCADE")
    created_at: datetime = Field(db_default="CURRENT_TIMESTAMP")

    class Meta:
        is_table = True
        table_name = "posts"


async def main():
    async with db.connect("sqlite:///blog.db"):
        # Create author
        author = await Author.objects.create(name="Alice")

        # Create posts
        await Post.objects.create(
            title="First Post",
            content="Hello!",
            author_id=author.id
        )
        await Post.objects.create(
            title="Second Post",
            content="World!",
            author_id=author.id
        )

        # Load posts with author (JOIN)
        posts = await Post.objects.join("author").all()
        for post in posts:
            print(f"{post.title} by {post.author.name}")

        # Load author with posts (prefetch)
        authors = await Author.objects.prefetch("posts").all()
        for author in authors:
            print(f"{author.name}: {len(author.posts)} posts")

Type Annotations and Forward References

Oxyde uses Python type annotations to define foreign keys. The annotation syntax depends on whether the target model is already defined at the point of reference.

Target model is defined above — use the type directly

class Author(Model):
    ...

class Post(Model):
    author: Author | None = Field(default=None, db_on_delete="CASCADE")  # ✓ Author is defined above

Target model is not yet defined — forward reference

If a model references another model defined later in the same file (or itself), Python will raise a NameError or TypeError because the name doesn't exist yet.

Solution: add from __future__ import annotations at the top of the module. This makes all annotations lazy (stored as strings, evaluated later), so any name can be used regardless of definition order:

from __future__ import annotations

class Post(Model):
    author: Author | None = Field(default=None, db_on_delete="CASCADE")  # ✓ Works even though Author is below

class Author(Model):
    ...

Without from __future__ import annotations, you would get:

• author: Author | None → NameError: name 'Author' is not defined
• author: "Author" | None → TypeError: unsupported operand type(s) for |: 'str' and 'NoneType'

String annotations inside generics

For reverse FK and M2M fields, string annotations inside list[] always work because they are resolved by Pydantic, not by Python's | operator:

class Author(Model):
    posts: list["Post"] = Field(db_reverse_fk="author")  # ✓ Always works

Python version notes

Python	Behavior
3.10–3.13	Annotations are evaluated eagerly. Use from __future__ import annotations for forward references.
3.14+	Annotations are lazy by default (PEP 749). Forward references work without any import.

Recommendation: define models in dependency order (referenced models first) and you won't need any special imports. Use from __future__ import annotations only when the order can't be arranged (e.g., self-referential FK, circular references).

Limitations

No M2M in join()

M2M relations are supported by prefetch() but not by join(). Use prefetch() for M2M.

Next Steps

• Transactions — Atomic operations
• Queries — Full query reference
• Fields — Field options reference