Relationships#

A field can refer to another row. Restly handles such a field in both directions:

  • In a request, the client sends the id of the other row. On create and update, Restly looks the id up. If the row does not exist, the request returns 404. Otherwise Restly writes the foreign key or the relationship.

  • In a response, the field shows the other row as an id or as a full object. Restly loads every relationship that the response class names before it serializes the response, so this also works on an async view.

Restly reads the SQLAlchemy mapper to decide what a field writes. A field named after a foreign key column writes that column. A field named after a relationship writes the relationship, and Restly sets the foreign key column of that relationship to match.

The type of the field says what kind of reference it is: fr.MustExist for a foreign key column, and fr.IDRef or fr.IDSchema for a relationship.

Note

FastAPI-Restly uses the word schema for a Pydantic request or response model, and model for a SQLAlchemy ORM model.

Choose a reference style#

Most APIs send ids. Forms and dropdowns submit them, and a client such as React Admin can fetch the row behind an id to show it. Show the whole object only when one larger response is better for the client than a second request.

Declaration

JSON value

What the field is

author_id: fr.MustExist[int, User]

1

a foreign key column, checked

author: fr.IDRef[User]

1

a relationship, checked

author: fr.IDSchema[User]

{"id": 1}

a relationship, checked

author: fr.ReadOnly[UserSchema]

the full object

a relationship, in responses only

author_id: int

1

a foreign key column, not checked

  • Use MustExist for the common case: a *_id column that must point to a row that exists.

  • Use IDRef or IDSchema when the field is a relationship. The two differ only in the JSON: a flat id for IDRef, an object with an id for IDSchema.

  • Use a nested schema to show the whole object in responses; see Return related objects in a response.

  • A plain int writes the column without a check. If the row is missing, the database rejects the foreign key, and the request returns 409 (see database conflicts).

Accept the id of another row#

The examples on this page use two models. Each Article has an author, a User:

import fastapi_restly as fr
from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(fr.ModelDefaults, DeclarativeBase):
    pass


class User(fr.IDMixin, Base):
    name: Mapped[str]


class Article(fr.IDMixin, Base):
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("user.id"))

fr.ModelDefaults derives the table name from the class name, so User gets the table user. That is why ForeignKey("user.id") is correct.

Declare the foreign key column on the schema with MustExist. Name the type of the primary key first, then the model:

class UserSchema(fr.IDSchema):
    name: str


class ArticleSchema(fr.IDSchema):
    title: str
    author_id: fr.MustExist[int, User]


@fr.include_view(app)
class ArticleView(fr.AsyncRestView):
    prefix = "/articles"
    model = Article
    schema = ArticleSchema

The view needs nothing else. In JSON, author_id is a plain number, in the request and in the response:

{
  "id": 10,
  "title": "Intro",
  "author_id": 1
}

On create and update, Restly checks that the User with id 1 exists. In your own code, such as authorize, data.author_id is a plain integer.

The blog example runs the same pattern for the comments of a post, in blog/part1.py.

Check that the referenced row exists#

MustExist, IDRef and IDSchema each check on create and update that the referenced row exists. A missing row returns 404, and the message names the field and the id:

{"detail": "Id not found for author_id: 99"}

Details

  • The message names a field by the name that the client sends in the body: its validation alias when that is a string, else its alias, else its name, such as authorId.

  • Restly checks the references after authorize and before your create or update. So the errors come in this order: the row in the URL (404), then the permission (403), then the references (404).

  • Restly checks the data of every write: the CRUD routes, and write_action(data=...). It also checks the schemas inside the data, such as the lines of an order, and each item when the data is a list. The message then names the place: Id not found for lines[1].product_id: 7, or lines.k.product_id for the key k of a dict.

  • One query checks all the ids of one model. Restly checks only the fields that the client sent, and it skips a null value. In one write, Restly does not look up the same row twice.

  • The check does not autoflush, so it never writes an object that your code is still building. So it also does not find a row that your code added and did not flush yet. Call flush() first for such a row.

  • A list of ids, such as list[fr.MustExist[int, Tag]], checks each id. The message names every missing id of the list: Id not found for tag_ids: [7, 9]. A reference deeper in another type, such as in a dict, is refused, because the check cannot see it there.

  • The builders in fr.objects check too, so a script that builds objects with them is safe.

  • An id that reaches the database in another way is yours to check, such as a value that your code sets in a write_action block without data=. The foreign key in the database still refuses an id that does not exist.

  • For another primary key type, name that type first: fr.MustExist[UUID, Account]. Restly does not take the model from the foreign key, because a schema can also write another model, and then the check would use the wrong table.

Which rows can a reference reach?#

A reference check applies the default scope of the referenced model. A reference to a row that the scope hides returns 404, as a missing row does. So if the scope of User hides deleted users, a deleted user cannot become the author of a new article.

A session rule, such as the tenant rule, also reaches every reference check.

A model without a default scope is checked by its primary key alone. Then a reference can reach a soft-deleted row, or a row that the caller cannot read. To hide such rows, give the model a default scope.

The scope of a view does not apply to references. It is a rule for the reads of that one view. If a rule must also hold for references, make it the default_scope of the model.

Details

A script that calls the fr.objects builders by itself loads an IDRef or IDSchema row of a model without a default scope with session.get(). For an object that is already in the session, session.get() returns it without a query, so a session rule does not run there. Inside a write, Restly always runs the query.

A rule that is neither a scope nor a session rule goes in authorize (see Authorization). There, data holds the requested id, before Restly looks it up: data.author_id for a MustExist field, and data.author.id for an IDRef or IDSchema field. A list field holds a list of references.

class Current(fr.ContextNamespace):
    user_id: fr.ContextParam[int]
    is_editor: fr.ContextParam[bool]


@fr.include_view(app)
class ArticleView(fr.AsyncRestView):
    prefix = "/articles"
    model = Article
    schema = ArticleSchema

    async def authorize(self, action, obj=None, data=None):
        if action == fr.Action.CREATE and data.author_id != Current.user_id():
            if not Current.is_editor():
                raise fr.exc.Forbidden("Only an editor can name another author")

authorize does not have the row itself. If a rule needs it, read it in before_action_commit, for example with await self.session.get(User, new.author_id).

The SaaS example lets a member attach labels only to a task that is assigned to them. That is a permission, so it is in TaskLabelView.authorize, in app/labels/views.py.

Name the field after the model attribute#

Restly matches the Python name of the field against the model. A MustExist field names a foreign key column. An IDRef or IDSchema field names a relationship. The column can have any name. The _id suffix is common, but Restly does not need it:

author_id: fr.MustExist[int, User]  # the Article.author_id column
post_fk: fr.MustExist[int, Post]    # a column without _id works the same way
author: fr.IDRef[User]              # the Article.author relationship

To use another name in the JSON only, for example for a camelCase API, keep the Python name and add a Pydantic alias:

from pydantic import Field

author_id: fr.MustExist[int, User] = Field(alias="authorId")  # JSON: "authorId"

When the field names a to-one relationship, Restly sets the relationship to the row it looked up, and sets the foreign key column of the relationship to its id. Restly finds that column through the mapper, so its name does not matter, also when the column has its own database name (mapped_column("db_name", ...)).

Reference a relationship: a flat id or an object#

Name the relationship on the schema, and choose the JSON shape with the type. IDRef gives a flat id. IDSchema[Model] gives an object with an id, for clients that model a relationship as an object:

class ArticleSchema(fr.IDSchema):
    title: str
    author: fr.IDSchema[User]

{
  "title": "Intro",
  "author": {"id": 1}
}

Details

Both types check the row and look it up in the same way. In a request, each type accepts both forms: {"author": 1} and {"author": {"id": 1}}. The response always uses the declared form. This helps when some clients already send the other form.

In your code, data.author is a reference object, not the row. Read the requested id as data.author.id. Restly looks the row up when it writes.

IDSchema is also the usual base class of a schema. As a base class, it only adds a read-only id field; Schemas & Validation covers that use.

Reference several rows#

A to-many relationship takes a list of ids with list[fr.IDRef[Model]]. The shop example uses one for the products of an order, next to a MustExist for its customer:

class OrderSchema(fr.TimestampsSchemaMixin, fr.IDSchema):
    customer_id: fr.MustExist[int, Customer]
    products: list[fr.IDRef[Product]]

In JSON the list is an array of ids, such as "products": [1, 2, 3]. Each item of a request can also be an object, such as {"id": 1}. On create and update, Restly looks up all ids in one query, returns 404 if one is missing, and sets the relationship to those rows. A repeated id counts once. A list relationship stores no order: responses show the rows in database order, unless the relationship sets order_by. The full file is shop/main.py.

A plain list of ids that sets no relationship, such as the projects that a route body names, is list[fr.MustExist[int, Project]]. Restly checks each id in one query, and the list stays a list of ints.

Many-to-many relationships#

The products of an order in the shop example above are a many-to-many relationship. An order has many products, and a product is in many orders. Such a relationship goes through an association table. Map it with SQLAlchemy’s secondary, as in this example of posts and tags:

from sqlalchemy import Column, ForeignKey, Table
from sqlalchemy.orm import Mapped, relationship

post_tag = Table(
    "post_tag",
    Base.metadata,
    Column("post_id", ForeignKey("post.id"), primary_key=True),
    Column("tag_id", ForeignKey("tag.id"), primary_key=True),
)


class Tag(fr.IDMixin, Base):
    name: Mapped[str]
    posts: Mapped[list["Post"]] = relationship(
        secondary=post_tag, back_populates="tags", default_factory=list
    )


class Post(fr.IDMixin, Base):
    title: Mapped[str]
    tags: Mapped[list[Tag]] = relationship(
        secondary=post_tag, back_populates="posts", default_factory=list
    )


class PostSchema(fr.IDSchema):
    title: str
    tags: list[fr.IDRef[Tag]] = []

The schema takes the tags as a list of ids, as in Reference several rows. POST /posts with "tags": [1, 2] links the post to those tags. A PATCH with "tags": [2] replaces the whole list.

Responses show the ids, also on an async view. Restly loads each relationship that the response class names. So the relationship needs no lazy option.

The schema of a tag can show the ids of its posts with posts: fr.ReadOnly[list[fr.IDRef[Post]]]. The field is read-only here.

Details

To return the tags as objects, while clients still send ids, set your own response class:

class PostResponse(fr.IDSchema):
    title: str
    tags: list[TagSchema] = []


class PostView(fr.AsyncRestView):
    prefix = "/posts"
    model = Post
    schema = PostSchema
    schema_response = PostResponse

A link with columns of its own, such as the position of a tag on a post, is an association object: a model. Give it a view of its own, with a fr.MustExist for each end:

from sqlalchemy import ForeignKey, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship


class PostTag(fr.IDMixin, Base):
    __table_args__ = (UniqueConstraint("post_id", "tag_id"),)
    post_id: Mapped[int] = mapped_column(ForeignKey("post.id"))
    tag_id: Mapped[int] = mapped_column(ForeignKey("tag.id"))
    position: Mapped[int] = mapped_column(default=0)
    tag: Mapped[Tag] = relationship(init=False)


class Post(fr.IDMixin, Base):
    title: Mapped[str]
    links: Mapped[list[PostTag]] = relationship(
        cascade=fr.models.CASCADE_ALL_DELETE_ORPHAN_ASYNC, default_factory=list
    )


class PostTagSchema(fr.IDSchema):
    post_id: fr.MustExist[int, Post]
    tag_id: fr.MustExist[int, Tag]
    position: int = 0

The unique constraint answers a second link of the same tag with 409. The cascade deletes the links of a post with the post. Without it, deleting a post that has links answers 409. The post can show its links as a nested list, such as links: fr.ReadOnly[list[PostTagSchema]], and Restly loads each level.

A key of the list params cannot go through a list of related rows. So include_filters = ("*", "tags.*") fails when the view registers, because tags.* matches no key. Write a filter of your own instead:

from sqlalchemy import ColumnElement


class PostView(fr.AsyncRestView):
    ...

    @fr.list_filter("tag")
    def filter_tag(self, value: list[str]) -> ColumnElement[bool]:
        """Posts with one of these tags."""
        return Post.tags.any(Tag.name.in_(value))

A client then sends ?tag=news, or ?tag=news&tag=tech for posts with either tag.

Declare the foreign key and the relationship on the model#

On a base with fr.ModelDefaults, each model is a dataclass: SQLAlchemy’s MappedAsDataclass generates an __init__ from the fields. Restly passes that constructor the values it needs, whether the schema writes the foreign key, the relationship, or both. So you can declare both with any init and default options:

author_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
author: Mapped["User"] = relationship(default=None)

Details

A relationship that the request does not send never replaces a foreign key that it does send. If that relationship is None after the constructor ran, for example from default=None, Restly removes the None. Otherwise SQLAlchemy would write NULL over the foreign key when it flushes.

A reference can be optional. A field typed author: fr.IDRef[User] | None accepts an explicit null, and the client can leave it out when it defaults to None. Then the row gets a NULL foreign key, unless another field sends it. A null for a NOT NULL column fails in the database, which returns 409.

A schema can name both sides, the foreign key and the relationship, as two reference fields. Then the two must name the same row:

{
  "author_id": 1,
  "author": {"id": 1}
}

Two different rows, such as "author_id": 1 with "author": {"id": 2}, return 422. An explicit null counts too: "author_id": 1 with "author": null returns 422. Leaving author out does not. This check needs both fields to be IDRef or IDSchema. A MustExist field does not take part: when a request sends it with a relationship value, including null, Restly saves the relationship value.

On a plain DeclarativeBase without fr.ModelDefaults, the models are not dataclasses, and there is no generated __init__ to satisfy:

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Article(Base):
    __tablename__ = "article"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
    author: Mapped["User"] = relationship()

Restly builds the object and sets the foreign key column, and the relationship when there is one. Use Your Existing Models covers models like these.

Filter a list by a reference#

A MustExist field filters the list endpoint by its own name, as in GET /articles?author_id=1. An IDSchema[Model] field filters by its id, as in GET /articles?author.id=1. That key has a dot, so the view turns it on first, for example with include_filters = ("*", "author.*"), as Choose the keys of a list shows. A flat IDRef on a relationship gets no filter. Filter by a foreign key lists the operators that each one gets.

Build references in a custom route#

The default POST and PATCH routes validate the body first. So when the default create or update builds the object, every reference field has the right type: a MustExist field holds a plain id, and an IDRef or IDSchema field holds a reference object.

A custom route that builds a schema object itself must give each field that type. Pydantic’s model_construct() builds a schema object without validation. It helps when the schema has required fields that only responses fill, such as id or timestamps. For a MustExist field, pass the plain id:

from fastapi_restly.objects import async_make_new_object


link_schema = TaskLabelSchema.model_construct(
    task_id=request.task_id,
    label_id=label.id,
)

task_label = await async_make_new_object(
    self.session,
    TaskLabel,
    link_schema,
)

async_make_new_object still checks that the rows exist, and writes the foreign key columns. A schema that validates, such as self.schema_create(task_id=..., label_id=...), needs none of this, and a handler can take it. The SaaS example’s POST /task-labels/create-and-attach route does that, in app/labels/views.py.

Details

For an IDRef or IDSchema field, build the reference object yourself:

link_schema = TaskLabelSchema.model_construct(
    task=fr.IDRef[Task](id=request.task_id),
    label=fr.IDRef[Label](id=label.id),
)

A custom route can also return a relationship as an object. Give its response_model an IDSchema[Model] field on the relationship, and return the ORM object. The response then reads that relationship, so on an async view load it first:

class TaskLabelLinks(fr.IDSchema):
    task: fr.IDSchema[Task]
    label: fr.IDSchema[Label]


@fr.post("/attach", response_model=TaskLabelLinks, status_code=201)
async def attach(self, request: AttachRequest):
    task_label = await create_task_label(...)
    await task_label.awaitable_attrs.task
    await task_label.awaitable_attrs.label
    return task_label

self.to_single_response(obj) returns the response class of the view, not this route’s class. Use it only when the route returns the view’s own response class.

Internals#

How Restly loads the relationships of a response#

get_loader_options() walks the fields of the response class. For each field whose name is a relationship of the model, it adds a selectinload(...) option. When the field type is a nested schema, it walks that schema against the related model in the same way, and adds the result as child options.

select() adds these options to every read. On a write, the save after the business method flushes and then refreshes the object. The refresh reloads the columns, but it unloads the relationships that load lazily, so a changed foreign key never shows an old related row. The save also unloads the relationships of the other rows of the view’s model that the action wrote. Then one query by primary key reloads those rows with all the loader options, and the object too when the view has loader options. It does not apply the scope, and it does not use populate_existing, so it fills only what is not loaded. A related object that is already in the session keeps the values it has.

How Restly writes a reference#

Before it builds or changes the object, Restly looks up the rows behind the IDRef and IDSchema fields, and checks the ids of the MustExist fields. It then routes each value by the mapper. A relationship gets the row. A foreign key column gets the id. When a field has a partner, the relationship of a column or the column of a relationship, Restly sets that partner too. On a dataclass model, a value goes to the constructor when __init__ takes it, and is set on the object after the constructor otherwise.

See also#