0

I am creating a table using the following code:

from sqlmodel import Field, SQLModel, JSON, Column, Time

class MyTable(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    type: str
    slug: str = Field(index=True, unique=True)
    resource_data: dict | None = Field(default=None, sa_column=Column(JSON))  # type: ignore


# ... create engine

SQLModel.metadata.create_all(engine)

The CREATE table script generated for the model above ends up putting the resource_data column above everything else, instead of preserving the natural order of 'id' first

CREATE TABLE mytable (
     resource_data JSON,          <----- why is this the FIRST column created?
     id SERIAL NOT NULL, 
     name VARCHAR NOT NULL, 
     type VARCHAR NOT NULL, 
     slug VARCHAR NOT NULL, 
     PRIMARY KEY (id)
)

This feels unusual when I inspect my postgresql tables in a db tool like pgAdmin.

How do I ensure the table is created with the 'natural' order of the declarative model, that is 'id' first?

epicwhale
  • 1,835
  • 18
  • 26
  • It looks like SQLModel is prioritising the fields which use the sa_column attribute? – epicwhale Jan 29 '23 at 14:18
  • I suspect this is not specific to SQLModel, but that it is just the order in which column instances are created that determines the order in the resulting table. Since you explicitly created one in the class definition, that was before SQLModel had the chance to create the others based on the other field parameters. I have not tested this though. If [this](https://github.com/tiangolo/sqlmodel/pull/436) is ever merged, the `sa_column` paramters will become obsolete anyway. If I'm right, this would solve this issue. – Daniil Fajnberg Jan 29 '23 at 21:29

0 Answers0