FastAPI SQLModel and Pydantic: flatten out an object into a table

I would like to use the same model for the api and the SQLDatabase but I am struggling with the configuration.

I am writing an API in fastAPI and I would like to return an object with nested objects, but without creating additional tables in the DB.

class InnerObject(...):
  first_attribute: str
  second_attribute: str

class OuterObject(SQLModel):
  id: str = Field(sa_column=Column(nullable=False, primary_key=True))
  name: str
  attributes: InnerObject = Field(...)

Is it possible to configure SQLModel to flatten out the attributes in the OuterObject table?

SQLModel does not support flattening of nested objects into single table directly. But this can be done by serializing the nested objects into single column.

We can use Pydantic for API models and Flatten SQLModel...

class InnerObject(BaseModel):
    first: str
    second: str

class OuterObject(SQLModel, table=True):
    id: str = Field(default=None, primary_key=True)
    name: str
    first: str
    second: str

Now we can use pydantic for the API and the flattened OuterObject for the database...

class OuterObjectAPI(BaseMode):
    id: str
    name: str
    attributes: InnerObject

    @classmethods
    def from_db_model(cls, db_model: OuterObject):
        attributes = InnerObject(
            first=db_model.first,
            second=db_model.second
        )
        return cls(
                id=db_model.id,
                name=db_model.name,
                attrubutes=attributes
        )

Make sure you handle serialization and deserialization properly, while saving or fetching...