How PostgreSQL sequence can be mapped with a column using SqlModel?

[shailendrafaarms]

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

class Orders(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    order_number: str
    item_name: str
    item_price: float

Description

I have a model named Orders, where I need to implement PostgreSQL sequence which will generate an auto-increment alphanumeric order number. I tried searching but no luck on how to implement in SqlModel. I have found it for SqlAlchemy but don't know how to do in SqlModel.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.7

Additional Context

No response

[littlebtc]

(Old wrong answer deleted)

You can try sa_column_args and sa_column_kwargs, which will be appended to the generated SQLAlchemy column.

[YuriiMotov]

You can specify it using sa_column parameter:

order_number: Optional[str] = Field(
        sa_column=Column(
            Integer,
            Sequence("order_number_seq", start=100, increment=2),
            nullable=False,
        )
    )

Runnable code example:

from typing import Optional

from sqlmodel import (
    Column,
    Field,
    Integer,
    Sequence,
    Session,
    SQLModel,
    create_engine,
    select,
)

engine = create_engine("postgresql://postgres:mysecretpassword@127.0.0.1:5432/some_db")

class Orders(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    order_number: Optional[str] = Field(
        sa_column=Column(
            Integer,
            Sequence("order_number_seq", start=100, increment=2),
            nullable=False,
        )
    )
    item_name: str
    item_price: float

if __name__ == "__main__":
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        session.add(Orders(item_name="item 1", item_price=0.1))
        session.add(Orders(item_name="item 2", item_price=0.2))
        session.commit()

    with Session(engine) as session:
        orders = session.exec(select(Orders)).all()
        print(orders)

Output:

[
    Orders(item_price=0.1, order_number=100, id=1, item_name='item 1'),
    Orders(item_price=0.2, order_number=102, id=2, item_name='item 2')
]