General & Type-Specific Endpoints for a Single Table Model

[jacob-a-brown]

Problem Statement

With the use of a single-table schema, as outlined in the ADR Single Table or Multi Table Inheritance for Thing Model, there is now the ability to return a record with all fields even if they are not relevant to that type of record. Take, for example, this thing model

from pydantic import BaseModel

# CREATE ======

class CreateBaseThing(ORMBaseModel):
    name: str
    thing_type: str
    id: int


class CreateWellThing(CreateBaseThing):
    """
    Response schema for well details.
    """

    well_type: str  # e.g., "Production", "Observation", etc.


class SpringThing(CreateBaseThing):
    """
    Response schema for spring details.
    """

    spring_type: str # e.g., "Natural", "Artifical", etc.


# UPDATE ======

class UpdateBaseThing(BaseModel):
    name: str | None = None


class UpdateWellThing(UpdateBaseThing):
    well_type: str | None = None


class UpdateSpringThing(UpdateBaseThing):
    spring_type: str | None = None


# RESPONSE ======

class BaseThingResponse(BaseModel):
    id: int
    name: str
    thing_type: str


class WellThingResponse(BaseThingResponse):
    well_type: str | None


class SpringThingResponse(BaseThingResponse):
    spring_type: str | None

class ThingResponse(WellThingResponse, SpringThingResponse):
    pass

WellThing and SpringThing share the same BaseThing fields (name, thing_type, and id), but have type-specific fields that are unique to that type and irrelevant for the other type (well_type and spring_type). The composite Thing represents the model in the database and contains the shared fields, as well as the type-specific fields that are relevant to only one thing type.

The question becomes, how can we create endpoints to:

1. Get the desired data;
2. Create a record with the correct and necessary data validations; and
3. Update a record with the correct and necessary data validations.

GET

Most users will only want type-specific fields, such as well_type or spring_type and won't care for non-related fields (such as well-specific fields if getting data for a spring and vice versa). That being said, some database administrators or other interested parties may want or need to see the full record.

POST

When creating a new record for a specific type of object, all users should only be able to submit data for thing-type relevant fields.

PATCH

When updating a record for a specific type of object, all users should only be able to change data for thing-type relevant fields.

Solution

The solutions below use the Thing models defined above, but this can be applied to any shared-table schema.

GET

For the general record with all fields (for database administrators or similarly privileged users), the following endpoints should exist to enable getting data:

1. GET /thing
   a. Returns all records of the Thing.
   b. Includes all shared and type-specific fields, even if they are not relevant for a particular row.
2. GET /thing/{thing_id}
   a. Returns a specific record of the Thing.
   b. Includes all shared and type-specific fields, even if they are not relevant for a particular row.
   c. If the record with thing_id does not exist a 404 error should be raised

Otherwise, for type-specific records, the following endpoints should exist:

1. GET /thing/well/
   a. Returns all records of the Thing whose thing_type is well
   b. Only includes the shared fields and the type-specific fields
2. GET /thing/well/{thing_id}
   a. Returns a specific Thing record whose thing_type is well
   b. Only includes the shared fields and the type-specific fields
   c. If the record with thing_id does not exist a 404 error should be raised.
   d. If the record with thing_id does exist, but its thing_type is not well, a 404 error should be raised. If there is no record in the Thing table with that ID the error message should indicate as much. If there is a record in the Thing table with that ID, but its thing_type is not well, the error message should state as much (and state what the thing_type is for the record with that ID).

POST

All POST endpoints should be type-specific; there should be no POST endpoint for a general record. This ensures data fidelity and that all necessary fields are required. It also ensures that non-relevant fields remain unpopulated.

PATCH

All PATCH endpoints should be type-specific; there should be no PATCH endpoint for a general record. This ensures data fidelity and that non-relevant fields remain unpopulated.

Development Notes

FastAPI evaluates endpoint priority based off of the order in which they are defined in the router, from top to bottom. Because of this, type-specific endpoints should be defined before the general endpoints. If the endpoints are ordered as

@router.get("/thing")
....

@router.get("/thing/{thing_id}"
...

@router.get("/thing/well")
...

when a user calls GET /thing/well the API will try to inject well into {thing_id}, resulting in a 404 error. To avoid this problem, the router should be defined as

@router.get("thing/well")
...

@router.get("thing")
...

@router.get("thing/{thing_id}")
...