Many-to-many model inherits the parent class model. The use of updated_at and created_at in the model reports errors about sa_column.

[disonwei]

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

models
   - __init__.py
   - PurchaseModel.py
   - StoreProduct.py
   - User.py
   - base.py

PurchaseModel.py

class PurchaseModel(Base, PurchaseModelBase, table=True):
tablename = "purchase_user_product"

id: int | None = Field(default=None, primary_key=True)
store_product: StoreProduct = Relationship(back_populates="user_links")
user: User = Relationship(back_populates="store_product_links")

StoreProduct.py

class StoreProduct(Base, StoreProductBase, table=True):
    __tablename__ = "store_product"

    id: int | None = Field(default=None, primary_key=True)
    user_links: list["PurchaseModel"] = Relationship(back_populates="store_product")

User.py

class User(Base, UserBase, table=True):
id: int | None = Field(default=None, primary_key=True)
hashed_password: str
store_product_links: list["PurchaseModel"] = Relationship(back_populates="user")

base.py

class Base(SQLModel):
    # create_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))  
    # update_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc)) 

    created_at: Optional[datetime] = Field(
        sa_column=Column(
            DateTime(timezone=True), server_default=func.now(), nullable=True
        )
    )
    updated_at: Optional[datetime] = Field(
        sa_column=Column(
            DateTime(timezone=True), onupdate=func.now(), nullable=True
        )
    )

    is_delete: bool = Field(default=False) 

`````````````````````````
__init__.py

from .StoreProductModels import StoreProduct
from .PurchaseModels import PurchaseModel
from .users_models import User
```


### Description

I have 3 table models which are User StoreProduct PurchaseModel. Each table model is in a separate file. Their definition and file organization are shown above。


My migration alembic folder env.py is as follows：

```
from sqlmodel import SQLModel
from app import models

target_metadata = SQLModel.metadata
```

Something went wrong when I executed the migration command
```
alembic revision --autogenerate -m "init"
```

```
Traceback (most recent call last):
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/bin/alembic", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/config.py", line 641, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/config.py", line 631, in main
    self.run_cmd(cfg, options)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/config.py", line 608, in run_cmd
    fn(
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/command.py", line 236, in revision
    script_directory.run_env()
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/script/base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 994, in exec_module
  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/alembic/env.py", line 34, in <module>
    from app import models
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/app/models/__init__.py", line 3, in <module>
    from .PurchaseModels import PurchaseModel
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/app/models/PurchaseModels.py", line 8, in <module>
    from .users_models import User
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/app/models/users_models.py", line 21, in <module>
    class User(Base, UserBase, table=True):
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlmodel/main.py", line 549, in __init__
    DeclarativeMeta.__init__(cls, classname, bases, dict_, **kw)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_api.py", line 196, in __init__
    _as_declarative(reg, cls, dict_)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_base.py", line 244, in _as_declarative
    return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_base.py", line 325, in setup_mapping
    return _ClassScanMapperConfig(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_base.py", line 575, in __init__
    self._setup_table(table)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_base.py", line 1726, in _setup_table
    table_cls(
  File "<string>", line 2, in __new__
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 431, in __new__
    return cls._new(*args, **kw)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 485, in _new
    with util.safe_reraise():
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 481, in _new
    table.__init__(name, metadata, *args, _no_init=False, **kw)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 873, in __init__
    self._init_items(
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 231, in _init_items
    spwd(self, **kw)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py", line 1324, in _set_parent_with_dispatch
    self._set_parent(parent, **kw)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 2314, in _set_parent
    raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'store_product'

```

I found that when using xxx in base.py it goes wrong.
```
    created_at: Optional[datetime] = Field(
        sa_column=Column(
            DateTime(timezone=True), server_default=func.now(), nullable=True
        )
    )
    updated_at: Optional[datetime] = Field(
        sa_column=Column(
            DateTime(timezone=True), onupdate=func.now(), nullable=True
        )
    )

```
However, when I define

```
     create_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
     update_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))  

```
, there will be no mistake. I don't know why.

I expect updated_at this field to update automatically every time the data is updated. I read the official documents and set them like this. So I set it this way, but it reports an error in my code.




### Operating System

macOS

### Operating System Details

_No response_

### SQLModel Version

0.0.16

### Python Version

Python 3.12.0

### Additional Context

_No response_

[makisukurisu]

The markup is a bit broken, probably because of the use of ``` symbols all over the place.

But I can confirm this behaviour 😢 with the same SQLModel version and on Linux.

I even tried to separate the Field from the model (declare it as a variable), but it didn't help.

[disonwei]

base.py

from datetime import timezone, datetime

from sqlmodel import Column, Field, SQLModel
from sqlalchemy import DateTime, func
from typing import Optional

class Base(SQLModel):
    create_at: datetime | None = Field(
        default_factory=lambda: datetime.now(timezone.utc)) 
    updated_at: Optional[datetime] = Field(
        sa_column=Column(
            DateTime(timezone=True), onupdate=func.now(), nullable=True
        )
    )
    is_delete: bool | None = Field(default=False)

init.py

# from .base import Base
from .StoreProductModels import StoreProduct
from .PurchaseModels import Purchase
from .users_models import User

PurchaseModels.py

from datetime import datetime
from sqlmodel import Field, Relationship, SQLModel
from .StoreProductModels import StoreProduct
from .base import Base
from .users_models import User
from ..common.type import PaymentMethod, PaymentStatus



class Purchase(Base, PurchaseModelBase, table=True):
    __tablename__ = "purchase_user_product"

    id: int | None = Field(default=None, primary_key=True)
    store_product: StoreProduct = Relationship(back_populates="user_links")
    user: User = Relationship(back_populates="store_product_links")

StoreProductModels.py

from typing import TYPE_CHECKING

from sqlmodel import Field, Relationship, SQLModel

from app.models.base import Base

if TYPE_CHECKING:
    from app.models.PurchaseModels import Purchase


class StoreProduct(Base, StoreProductBase, table=True):
    __tablename__ = "store_product"

    id: int | None = Field(default=None, primary_key=True)
    user_links: list["Purchase"] = Relationship(back_populates="store_product")

users_models.py

from typing import TYPE_CHECKING

from sqlmodel import Field, Relationship, SQLModel

from app.common.type import RegistrationSourceEnum
from app.models.base import Base

if TYPE_CHECKING:
    from app.models.PurchaseModels import Purchase
    from app.models.UserSocialAccountModels import UserSocialAccount



class User(Base, UserBase, table=True):
    id: int | None = Field(default=None, primary_key=True)
    hashed_password: str
    store_product_links: list["Purchase"] = Relationship(back_populates="user")

    social_accounts: list["UserSocialAccount"] = Relationship(back_populates="user")

This is the code I organized. I encountered an error during the migration process, as follows:

➜ alembic revision --autogenerate -m "init"
Traceback (most recent call last):
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/bin/alembic", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/config.py", line 641, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/config.py", line 631, in main
    self.run_cmd(cfg, options)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/config.py", line 608, in run_cmd
    fn(
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/command.py", line 236, in revision
    script_directory.run_env()
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/script/base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 994, in exec_module
  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/alembic/env.py", line 34, in <module>
    from app import models
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/app/models/__init__.py", line 3, in <module>
    from .PurchaseModels import Purchase
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/app/models/PurchaseModels.py", line 5, in <module>
    from .users_models import User
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/app/models/users_models.py", line 25, in <module>
    class User(Base, UserBase, table=True):
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlmodel/main.py", line 549, in __init__
    DeclarativeMeta.__init__(cls, classname, bases, dict_, **kw)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_api.py", line 196, in __init__
    _as_declarative(reg, cls, dict_)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_base.py", line 244, in _as_declarative
    return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_base.py", line 325, in setup_mapping
    return _ClassScanMapperConfig(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_base.py", line 575, in __init__
    self._setup_table(table)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/orm/decl_base.py", line 1726, in _setup_table
    table_cls(
  File "<string>", line 2, in __new__
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 431, in __new__
    return cls._new(*args, **kw)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 485, in _new
    with util.safe_reraise():
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 481, in _new
    table.__init__(name, metadata, *args, _no_init=False, **kw)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 873, in __init__
    self._init_items(
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 231, in _init_items
    spwd(self, **kw)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py", line 1324, in _set_parent_with_dispatch
    self._set_parent(parent, **kw)
  File "/Users/dison/dison/project/HabitDone/HabitDone_FastAPI/.venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 2314, in _set_parent
    raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: Column object 'updated_at' already assigned to Table 'store_product'

[joachimhuet]

Hello there, your problem lies in the use of sqlalchemy Column class on updated_at, the column is instantiated and then sqlalchemy tries to set the same column to several tables which is not allowed.

To avoid that problem you can give sqlalchemy the needed instructions to instantiate the Column on each table inheriting your Base class by not using the sa_column argument.

Example:

# set sa_type and sa_column_kwargs but not sa_column
updated_at: Optional[datetime] = Field(
        default_factory=lambda: datetime.now(timezone.utc),
        sa_type=DateTime(timezone=True),
        sa_column_kwargs={
            "onupdate": func.now(),
            "server_default": func.now(),
        },
    )

And "voilà" this should make it work.

[disonwei]

Thank you! It really works!

There's also a caveat about

sa_type=DateTime(timezone=True), 

Expected type 'type | PydanticUndefinedType' but actually 'DateTime'