With fastapi/SQLModel, how to specify the order the columns/fields are shown in a table/response object?

Question

I have the following models/schemas:

class UserBase(SQLModel):
    full_name: str
    email: EmailStr
    is_active: bool = True
    is_superuser: bool = False


class UserRead(UserBase):
    id: uuid.UUID


class UserCreate(UserBase, extra=Extra.forbid):
    password: str


class UserUpdate(UserBase):
    password: Optional[str] = None


class User(UserBase, table=True):
    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )
    hashed_password: Optional[str] = None

In my postgres client the table shows the columns in the order the fields are listed in the models/schemas:

Also, the openapi documentation lists the response object fields in the same order they are specified in the User model (notice the response object on the bottom of the image):

I would like to have the id to be the first column/field to show in the table/response object. In general, how can I enforce a specific order of the columns/fields?

Cybele

Different json client and configurations will show you different order so there is no point of doing that, json clients doesn't care of the data order – Agro

I see, thanks for your comment. And regarding the table columns order, is there anything we can do? – Cybele

Answer 1

Seems no param in sqlmodel Field controls it. I'm using alembic generating migrations, so I just sort the target_metadata.tables[i].c._collection, then I get the SQL fields sorted. Everything will then be in the correct order.

Upanddown

Answer 2

As discussed in comments, ordering the json output isn't necessary since json clients don't care of the data order.

Regarding the data order in your sql db, you can sort them, It is just a matter of model inheritance (it causes also the json order that we discuss earlier)

You should follow some rules regarding what data you put in your db.

In classic sql tables you have your first column as ID and the model you use to generate them should match this structure.

Since the ID is a data that all users will have it should be in your base model, the same rules apply to all your basic user data.

what i would do (folowing the exemple from the lib):

class UserBase(SQLModel, table=True):
    id: uuid.UUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        index=True,
        nullable=False,
    )
    hashed_password: Optional[str] = None
    full_name: str
    email: EmailStr
    is_active: bool = True
    is_superuser: bool = False

With that you can make your other classes that derive from it. (if necessary)

Agro

I know what you mean, but that is not a DRY solution, it leads to unnecessary copy of code. Also the example you link to, is just a toy example, it doesn't benefit from inheritance to specify different schemas for reading, creating, etc. For instance a response to a GET should not return id or hashed_password. – Cybele

There's a typo on my previous comment: "For instance a response to a GET should not return id or hashed_password" should be "For instance a response to a GET should not return hashed_password". The id is very welcome in a GET response :o) – Cybele