Better understanding create_pydantic_model with foreign keys

[badlydrawnrob]

So I'm having a little trouble understanding create_pydantic_model where foreign keys are concerned — I have a feeling it might be easier (in some cases) to just create your own Pydantic models — perhaps the documentation could be a little more clear and extensive in this area? I imagined the automatic Pydantic models are flexible enough for most queries, but can't figure out how to do it reliably.

See example code here, I've tried:

Flat shape

I'm happy with a flat shape, but how do you create_pydantic_model for it? I think the way I've been trying it is wrong.

Without any pydantic model

fruits = await (
    Fruits.select(
        Fruits.all_columns(exclude=[Fruits.id, Fruits.color]), # Return the fruits table
        Fruits.color.all_columns() # Join on the colors table
    )
)

return fruits

Output without a response_model pydantic type (a list of fruits):

[
  {
    "image": null,
    "name": "Banana",
    "url": "4352d224-b0ce-4639-8696-942b2220fc0a",
    "color.id": 1,
    "color.color": "#cc0f35",
    "color.background": "#feecf0",
    "color.name": "red"
  }
]

What I've tried to do (flat shape)

My first thought was to include_columns=(Fruits.name, Fruits.color, Fruits.color.name, ...) but it seems only Fruits columns are allowed there (not the foreign table fields).

I've also tried a plain model with basic arguments (single result example)

FruitsModelOut: Any = create_pydantic_model(
    table=Fruits,
    model_name="FruitsModelOut",
)

fruits = await (
    Fruits.select(
        Fruits.all_columns(exclude=[Fruits.id, Fruits.color]), # Return the fruits table
        Fruits.color.all_columns() # Join on the colors table
    ).first()
)

FruitsModelOut(**fruits)

Outputs this, with none of the extra fields I'd expect (with color also set to null)

{
  "color": null,
  "image": null,
  "name": "Banana",
  "url": "4352d224-b0ce-4639-8696-942b2220fc0a"
}

Nested shape

This feels a little easier to understand, but I'd prefer a flatter model ...

FruitsAllModelOut: Any = create_pydantic_model(
    table=Fruits,
    model_name="FruitsAllModelOut",
    nested=True,
    include_default_columns=True,
)

@fruits_router.get("/", response_model=List[FruitsAllModelOut])
async def retrieve_all_fruits():
    fruits = await (
        Fruits.select(
            Fruits.all_columns(exclude=[Fruits.id, Fruits.color]), # Return the fruits table
            Fruits.color.all_columns() # Join on the colors table
        ).output(nested=True)
    )

    return fruits

Response (unfortunately it gives null value for id, but you can remove the include_default_columns argument to hide it)

[
  {
    "id": null,
    "color": {
      "id": 1,
      "color": "#cc0f35",
      "background": "#feecf0",
      "name": "red"
    },
    "image": null,
    "name": "Banana",
    "url": "4352d224-b0ce-4639-8696-942b2220fc0a"
  }
]

TL;DR

So yeah, using "magic" at the moment feels a little harder to understand than just creating your own Pydantic models for foreign key fields with a flat shape, or for more complex queries. Perhaps this is the limitation of create_pydantic_types? Or I'm misunderstanding.

[sinisaos]

@badlydrawnrob If you look at the source code of create_pydantic_model, if you want to use FK, you have to use nested=True argument and I think that is the correct way to deal with nested Pydantic models. You can achieve this with objects or select queries as well. Something like this.

# nested Pydantic model
FruitsModelOut: Any = create_pydantic_model(
    table=Fruits,
    model_name="FruitsModelOut",
    nested=True,
)

# using objects query
@app.get("/", response_model=List[FruitsModelOut])
async def retrieve_all_fruits():
    fruits = await Fruits.objects(Fruits.all_related())
    return fruits

# using select query
@app.get("/", response_model=List[FruitsModelOut])
async def retrieve_all_fruits():
    fruits = await Fruits.select(
        Fruits.all_columns(exclude=[Fruits.id, Fruits.color]),
        Fruits.color.all_columns(),
    ).output(nested=True)
    return fruits

The result is nicely nested JSON response

[
  {
    "color": {
      "color": "#cc0f35",
      "background": "#feecf0",
      "name": "red"
    },
    "image": null,
    "name": "Banana",
    "url": "ece2894a-be27-4723-ba5a-ee5d5a163f82"
  }
]

If you want, as you said, a flat shape (if I understand correctly, you need a flat JSON response where the FK columns are not nested), you can write your own Pydantic models and there is nothing wrong with that. I mostly write custom Pydantic models and I don't use create_pydantic_model a lot (I think it is primarily used for Piccolo Admin). My main weakness of create_pydantic_model is the lack of support for M2M columns which can be easily solved with a custom Pydantic model.
To get back to the flat shape (although I don't understand why you would do that) you can do something like this

from pydantic import BaseModel, UUID4

class FruitsModelOut(BaseModel):
    name: str
    image: str | None = None
    url: UUID4
    # flatten Colors FK
    color_color: str
    color_background: str
    color_name: str

@app.get("/", response_model=List[FruitsModelOut])
async def retrieve_all_fruits():
    fruits = await Fruits.select(
        Fruits.image,
        Fruits.name,
        Fruits.url,
        Fruits.color.color.as_alias("color_color"),
        Fruits.color.background.as_alias("color_background"),
        Fruits.color.name.as_alias("color_name"),
    )
    return fruits

and result then is

[
  {
    "name": "Banana",
    "image": null,
    "url": "ece2894a-be27-4723-ba5a-ee5d5a163f82",
    "color_color": "#cc0f35",
    "color_background": "#feecf0",
    "color_name": "red"
  }
]

[badlydrawnrob]

Ok so right now create_pydantic_model is best for a basic Table model (with no foreign key joins) and .output(nested=True) for any foreign keys.

I'm happy to write my own Pydantic models: Elm Lang prefers a flatter model, but uses decoders so can consume almost any json shape. I haven't yet got into M2M yet so will have to look that up later. But that leads me to a follow up question ...

• What was the design decision to have both flat and nested style? Could it perhaps be better to pick one shape?

It might simplify the API to pick one shape and standardise it.

[sinisaos]

Ok so right now create_pydantic_model is best for a basic Table model (with no foreign key joins) and .output(nested=True) for any foreign keys.

I can't agree with this statement. create_pydantic_model is also a valid choice for use with foreign keys because (as shown in previous comments) in combination with .output(nested=True) it gives a nice nested json response (same as the raw response, but you also get validation).

• What was the design decision to have both flat and nested style? Could it perhaps be better to pick one shape?

It might simplify the API to pick one shape and standardise it.

Nested json structure is commonly used because frontend frameworks like React or Vue easily render it directly in the UI (I don't know anything about Elm so I don't know what practices are used there). You can use any response structure (which is also shown in the previous comment) and the most important thing is that it suits your needs.

[badlydrawnrob]

@sinisaos You've misunderstood me. As you've said before, create_pydantic_model is only good for a flat shape with a basic table without any joins (otherwise you've got to create your own flat models). For joins, create_pydantic_model only works in a nested=True format. This made me wonder why Piccolo doesn't just use a nested style for every response?

All you need to know about Elm is that it's types and return values are much stricter, so they tend to be more predictable. Anyway, for now I'm happy with a flat style and will create my own models like your example above, thanks.