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)
)

[prurph]

I'm running into a similar problem where mixed-in classes have their columns added first. For example I have a Timestamps class to add timestamp columns (it has a sqlalchemy watcher to automatically touch updated_at, not pictured for simplicity)

class Timestamps:
    created_at: datetime = Field(sa_type=DateTime(), nullable=False)
    updated_at: datetime = Field(sa_type=DateTime(), nullable=False)

class MyModel(SQLModel, Timestamps, table=True)
    id: Optional[int] = Field(default=None, primary_key=True)

This generates the sql (sqlite):

CREATE TABLE mymodel (
	created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        id INTEGER NOT NULL,  -- id is below mixed in fields (other fields in MyModel would appear below this)
	PRIMARY KEY (id)
);

I tried using sa_column_kwargs={"sort_order": 1000} and things like this, but only got a warning and no effect. IMO SQLModel should offer some ability to control field ordering in the create statement.

My workaround is the following hack where I pop columns off of the Tables and then stick them back on when creating the tables.

engine = create_engine(sqlite_url)

def create_db_and_tables():
    for table in SQLModel.metadata.sorted_tables:
        try:
            created_at, updated_at = (
                table.columns.get("created_at"),
                table.columns.get("updated_at"),
            )
            table._columns.remove(created_at)
            table._columns.remove(updated_at)
            table._columns.extend((created_at, updated_at))
        except KeyError:
            pass
    SQLModel.metadata.create_all(engine)

[ideepu]

Hello,

I also have the same problem as @prurph. I have also tried using sa_column_kwargs={"sort_order": 10}, I followed this sqlalchemy doc and used mapped_column(sort_order=10) but this didn't work either.

created_at: datetime = Field(
        default=datetime.now(tz=timezone.utc),
        sa_column=mapped_column(name='created_at', type_=DateTime, index=True, sort_order=10),
    )

I see in the sqlalchemy documentation for sort_order https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.mapped_column.params.sort_order here, that it is in version 2.0.4 but as of today I only see 2.0.34 in pypi https://pypi.org/project/SQLAlchemy/2.0.34/. So I am assuming this yet to be released or maybe I missed something.

I am running the following versions.

pydantic==2.9.0
SQLAlchemy==2.0.34
sqlmodel==0.0.22

[oYASo]

Hi!

I had the same issue as @prurph, and I solved it by changing the inheritance order.

class Timestamps:
    created_at: datetime = Field(sa_type=DateTime(), nullable=False)
    updated_at: datetime = Field(sa_type=DateTime(), nullable=False)


class IntegerSQLModel(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)


class MyModel(Timestamps, IntegerSQLModel, table=True):
    some_file: str

As a result, the following schema is generated:

CREATE TABLE my_model (
        id SERIAL NOT NULL,
        created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        some_file VARCHAR NOT NULL,
        PRIMARY KEY (id)
)

I often use separate base classes for primary keys in the form of UUID or int, so this solution works fine for me.

[tsuga]

Any update on this? I am on the same boat.

[hjqlc]

@prurph Yes, you missed something, because the sort_order isn't the param of Column but MappedColumn, and the MappedColumn doesn't pass it to Column. In the main.py file of SQLModel source code, it uses Column. If you want it to work, SQLModel should change Column to MappedColumn.

[kssion]

This is my solution:

Fix sqlmodel/main.py get_column_from_field

1. if isinstance(sa_column, Column) -> if isinstance(sa_column, (Column, MappedColumn))
2. return Column(sa_type, *args, **kwargs) -> return MappedColumn(sa_type, *args, **kwargs)

mysqlmodel.py

from typing import Dict, Any, cast, Sequence

import sqlmodel.main
from pydantic_core import PydanticUndefined as Undefined
from sqlalchemy.orm import MappedColumn
from sqlmodel.main import (
    IS_PYDANTIC_V2,
    Column,
    get_sqlalchemy_type,
    is_field_noneable,
    ForeignKey,
    UndefinedType,
)


def get_column_from_field(field: Any) -> Column:  # type: ignore
    if IS_PYDANTIC_V2:
        field_info = field
    else:
        field_info = field.field_info
    sa_column = getattr(field_info, "sa_column", Undefined)
    if isinstance(sa_column, (Column, MappedColumn)): 
        return sa_column
    sa_type = get_sqlalchemy_type(field)
    primary_key = getattr(field_info, "primary_key", Undefined)
    if primary_key is Undefined:
        primary_key = False
    index = getattr(field_info, "index", Undefined)
    if index is Undefined:
        index = False
    nullable = not primary_key and is_field_noneable(field)
    # Override derived nullability if the nullable property is set explicitly
    # on the field
    field_nullable = getattr(field_info, "nullable", Undefined)  # noqa: B009
    if field_nullable is not Undefined:
        assert not isinstance(field_nullable, UndefinedType)
        nullable = field_nullable
    args = []
    foreign_key = getattr(field_info, "foreign_key", Undefined)
    if foreign_key is Undefined:
        foreign_key = None
    unique = getattr(field_info, "unique", Undefined)
    if unique is Undefined:
        unique = False
    if foreign_key:
        if field_info.ondelete == "SET NULL" and not nullable:
            raise RuntimeError('ondelete="SET NULL" requires nullable=True')
        assert isinstance(foreign_key, str)
        ondelete = getattr(field_info, "ondelete", Undefined)
        if ondelete is Undefined:
            ondelete = None
        assert isinstance(ondelete, (str, type(None)))  # for typing
        args.append(ForeignKey(foreign_key, ondelete=ondelete))
    kwargs = {
        "primary_key": primary_key,
        "nullable": nullable,
        "index": index,
        "unique": unique,
    }
    sa_default = Undefined
    if field_info.default_factory:
        sa_default = field_info.default_factory
    elif field_info.default is not Undefined:
        sa_default = field_info.default
    if sa_default is not Undefined:
        kwargs["default"] = sa_default
    sa_column_args = getattr(field_info, "sa_column_args", Undefined)
    if sa_column_args is not Undefined:
        args.extend(list(cast(Sequence[Any], sa_column_args)))
    sa_column_kwargs = getattr(field_info, "sa_column_kwargs", Undefined)
    if sa_column_kwargs is not Undefined:
        kwargs.update(cast(Dict[Any, Any], sa_column_kwargs))
    return MappedColumn(sa_type, *args, **kwargs)  # type: ignore


def fix_column():
    # Replace SQLModel internal method
    sqlmodel.main.get_column_from_field = get_column_from_field

---

Test code:

import re
from datetime import datetime
from typing import Optional

from sqlalchemy.orm import declared_attr
from sqlmodel import SQLModel, Field, INTEGER, DATETIME

# ✅ import and fix
import mysqlmodel

mysqlmodel.fix_column()


class Table(SQLModel):

    @declared_attr
    def __tablename__(self) -> str:
        return re.sub(r'(?<!^)(?=[A-Z])', '_', self.__name__).lower()

    # ✅ first column
    id: Optional[int] = Field(sa_type=INTEGER, primary_key=True, sa_column_kwargs=dict(sort_order=-1))

    # ✅ last column
    created_at: datetime = Field(sa_type=DATETIME, sa_column_kwargs=dict(sort_order=1000))
    updated_at: datetime = Field(sa_type=DATETIME, sa_column_kwargs=dict(sort_order=1001))


class MyTable(Table, table=True):
    some_file: str


if __name__ == '__main__':
    from db import engine

    Table.metadata.create_all(engine)

As a result, the following schema is generated:

CREATE TABLE my_table (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	some_file VARCHAR(255) NOT NULL, 
	created_at DATETIME NOT NULL, 
	updated_at DATETIME NOT NULL, 
	PRIMARY KEY (id)
)

It may not be the best, but it works.