JSON keys ordering when returning SQLModel object directly

[chrisawad]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from datetime import datetime
from fastapi import FastAPI
from sqlalchemy import TIMESTAMP, Column, create_engine, text
from sqlmodel import SQLModel, Field, Session, select

class Customers(SQLModel, table=True):
    customer_id: int | None = Field(default=None, primary_key=True)
    name: str
    contact: str | None = None
    email: str | None = None
    phone_number: str | None = None
    created_ts: datetime | None = Field(sa_column=Column(
        TIMESTAMP(timezone=True),
        nullable=False,
        server_default=text("CURRENT_TIMESTAMP")
    ))
    updated_ts: datetime | None = Field(sa_column=Column(
        TIMESTAMP(timezone=True),
        nullable=False,
        server_default=text("CURRENT_TIMESTAMP"),
        server_onupdate=text("CURRENT_TIMESTAMP"),
    ))
    
app = FastAPI()

engine = create_engine("sqlite:///database.db")

SQLModel.metadata.create_all(engine)

@app.get('/debug', response_model=Customers)
async def debug_customers():
    with Session(engine) as session:
        session.add(Customers(name='Test'))
        statement = select(Customers).limit(1)
        customer = session.exec(statement).first()
        print(customer)
        return customer                  # fields return in order of the query? (not exactly sure)
        # return customer.model_dump()   # fields return in order of the response_model

Description

Testing Steps:

1. Use curl to observe the output: curl http://127.0.0.1:8000/debug
2. Switch return customer to return customer.model_dump()
3. Rerun the curl and observe output

When the SQLModel is returned directly and response_model is set, I would expect the JSON response to return in the field order of the response_model, but it's not. The order returned is the same as it prints before the return (I'm assuming this is how it's selected from the database).

With return customer:

{"contact":null,"phone_number":null,"customer_id":1,"updated_ts":"2024-12-12T16:21:27","name":"Test","email":null,"created_ts":"2024-12-12T16:21:27"}

With return customer.model_dump() (what I would have expected when returning customer directly):

{"customer_id":1,"name":"Test","contact":null,"email":null,"phone_number":null,"created_ts":"2024-12-12T16:22:11","updated_ts":"2024-12-12T16:22:11"}

What's weirder, if I copy the Customer class to Customer2 and set response_model to Customer2, response_model ordering is honored!! It's exactly the same code!!!!!

Something is going on internally where when SQL is executed on a model, the ordering breaks on any future usage.

Operating System

Linux

Operating System Details

My development is taking place in python:3 docker container running in a WSL Ubuntu distro.

SQLModel Version

0.0.22

Python Version

Python 3.12.7

Additional Context

Thank you for developing these tools. They are really interesting and I'm excited to keep using both in my day to day workflow!

[chrisawad]

After going through the docs a lot more, it looks like the suggestion is to create data models and return those, while using the table models inside the routes, only. This does fix the issue since the data models never directly access the db, but it's very unclear why using the db model directly, is causing the explained issue.

One place in the docs it actually said that you could return the table model directly if you wanted to, but never mentions this issue which makes me think it's a bug.

[YuriiMotov]

This is not a bug, but rather limitation.
Models with table=True bypass validation.
So, you should use base model with all common fields specified and inherit you table model and output model from base model.