DB persisted calculated field

[wladikz]

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

need to create table with following structure
CREATE TABLE [dbo].[cluster](
	[id] [nvarchar](50) NOT NULL,
	[vcenterid] [int] NOT NULL,
	[name] [nvarchar](80) NOT NULL,
	[FK_ID]  AS (CONVERT([nvarchar](60),concat([vcenterid],'_',[id]))) PERSISTED NOT NULL,

Description

i can't find a way to declare using sqlmodel FK_ID field. the idea of this field is to calculate foreign key id using table fields

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.16

Python Version

3.8.10

Additional Context

No response

[joachimhuet]

Hey there, did you try to set the data on sa_column parameter ?
Would looks like this:

class Cluster(SQLModel, table=True):
  id: str = Field(primary_key=True, max_length=50, sa_type=String(50))
  vcenterid: int
  name: str = Field(max_length=80, sa_type=String(80), nullable=False)
  fk_id: Optional[str] = Field(
    default=None,
    sa_column=Column(
      String(60),
      server_default="CONVERT([nvarchar](60),concat([vcenterid],'_',[id]))",
      persisted=True,
      nullable=False
    )
  )

I did not test the code but feel free to come back asking if anything is not working ;-)
I put fk_id as optional defaulting to None so that you don't need to set it when creating an element of type Cluster. But then the server will default it anyway.