How to refactor columns that needs sa_solumn attribute across multiple SQLModel tables?

[vinodkumars]

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, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime


def get_utc_now()->datetime:
    return datetime.now(timezone.utc)

class BaseModel(SQLModel):
    id:int=Field(primary_key=True)
    created_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now))
    updated_at:Optional[datetime]=Field(sa_column=Column(DateTime(timezone=True),default=get_utc_now,onupdate=get_utc_now))

class User(BaseModel,table=True):
    user_details:str

class Product(BaseModel,table=True):
    product_details:str

engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)

def create_user():
    with Session(engine) as session:
        user = User(id=1)
        session.add(user)
        session.commit()
        session.refresh(user)
    print(user)

def create_product():
    with Session(engine) as session:
        product = Product(id=1)
        session.add(product)
        session.commit()
        session.refresh(product)
    print(product)

create_user()
create_product()

Description

My tables have a few columns whose definition are similar. I would love to write this code once and use it across all tables. Unfortunately, this does not work when I use the sa_column attribute.

For example, created_at is a datetime with timezone aware type. I couldn't find a way to do this in SQLModel. Hence I used the sa_column attribute.
But the columns with sa_column attribute throw an exception when used across tables.

Exception:

Exception has occurred: ArgumentError
Column object 'created_at' already assigned to Table 'user'
File "/Users/foobar/Desktop/tmp/sqlmodel/main.py", line 18, in
class Product(BaseModel,table=True):
sqlalchemy.exc.ArgumentError: Column object 'created_at' already assigned to Table 'user'

Operating System

macOS

Operating System Details

Tried on both MacOS 14.4 and 12.7

SQLModel Version

0.0.18

Python Version

3.11.8

Additional Context

No response

[vinodkumars]

Found the answer. Had to use sa_type. Here's the fixed code:

from datetime import datetime, timezone
from typing import Optional
from sqlmodel import SQLModel, Field, create_engine, Session
from sqlalchemy import Column, DateTime


def get_utc_now()->datetime:
    return datetime.now(timezone.utc)

class BaseModel(SQLModel):
    id:int=Field(primary_key=True)
    created_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now)
    updated_at:Optional[datetime]=Field(sa_type=DateTime(timezone=True),default_factory=get_utc_now,sa_column_kwargs={'onupdate':get_utc_now})

class User(BaseModel,table=True):
    user_details:Optional[str]

class Product(BaseModel,table=True):
    product_details:Optional[str]

engine=create_engine("sqlite:///testdb.db")
SQLModel.metadata.create_all(engine)

def create_user():
    with Session(engine) as session:
        user = User(id=1)
        session.add(user)
        session.commit()
        session.refresh(user)
    print(user)

def update_user():
    with Session(engine) as session:
        user=session.get(User,1)
        user.user_details="test_user_details"
        session.add(user)
        session.commit()
        session.refresh(user)
    print(user)

def create_product():
    with Session(engine) as session:
        product = Product(id=1)
        session.add(product)
        session.commit()
        session.refresh(product)
    print(product)

create_user()
create_product()
update_user()

[vinodkumars]

However I now get a mypy error:

No overload variant of "Field" matches argument type "DateTime"