Order of columns in the table created does not have 'id' first, despite the order in the SQLModel. Looks like it's prioritising fields with sa_column

[epicwhale]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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 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)

Description

The CREATE table script generated for the model above ends up putting resource_data as the first column, 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?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.11.1

Additional Context

No response

[H-Plus-Time]

I've just encountered this problem too - it originates from the way sqlalchemy deals with a Column's _creation_order property (discussed wrt mixin column ordering at stackoverflow.com/sqlalchemy-move-mixin-columns-to-end). Because the Column() call executes first, the auto-generated Column objects always have higher _creation_order values. sa_args and sa_kwargs don't have this problem (sadly you're stuck with types detectable via get_sqlachemy_type if you use those).

My workaround ended up exploiting Column::copy and the __setattr__ override in the sqlmodel metaclass:

from sqlmodel.main import SQLModelMetaclass
from sqlmodel import SQLModel, Column

class ColumnCloningMetaclass(SQLModelMetaclass):
    def __setattr__(cls, name: str, value: Any) -> None:
        if isinstance(value, Column):
            return super().__setattr__(name, value.copy())
        return super().__setattr__(name, value)

class MyTable(SQLModel, table=True, meta=ColumnCloningMetaclass):
    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))

# _creation_order will match order of field declaration/annotation

Works well enough (negligible perf impact since it's at class generation time), though would be obsoleted by #436 .

[LastNever]

I've just encountered this problem too - it originates from the way sqlalchemy deals with a Column's _creation_order property (discussed wrt mixin column ordering at stackoverflow.com/sqlalchemy-move-mixin-columns-to-end). Because the Column() call executes first, the auto-generated Column objects always have higher _creation_order values. sa_args and sa_kwargs don't have this problem (sadly you're stuck with types detectable via get_sqlachemy_type if you use those).

My workaround ended up exploiting Column::copy and the __setattr__ override in the sqlmodel metaclass:

from sqlmodel.main import SQLModelMetaclass
from sqlmodel import SQLModel, Column

class ColumnCloningMetaclass(SQLModelMetaclass):
    def __setattr__(cls, name: str, value: Any) -> None:
        if isinstance(value, Column):
            return super().__setattr__(name, value.copy())
        return super().__setattr__(name, value)

class MyTable(SQLModel, table=True, meta=ColumnCloningMetaclass):
    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))

# _creation_order will match order of field declaration/annotation

Works well enough (negligible perf impact since it's at class generation time), though would be obsoleted by #436 .

i copy your code and run ,but its not work , resource_data in the first
---i'm sorry ,it's worked , need use 'metaclass' in python 3.10

[cout]

Is this still a problem? I cannot reproduce it (sqlmodel 0.0.18, sqlalchemy 2.0.29):

CREATE TABLE mytable (
        id INTEGER NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        type VARCHAR(255) NOT NULL,
        slug VARCHAR(255) NOT NULL,
        resource_data JSON,
        PRIMARY KEY (id)
)