SqlAlchemy uses None/null as default value which conflicts with SqlServer

Question

So I'm using sqlmodel (sqlalchemy with pedantic) and I have something like this model:

class SequencedBaseModel(BaseModel):
    sequence_id: str = Field(alias="sequence_id")

    @declared_attr
    def sequence_id(cls):
        return Column(
            'sequence_id',
            VARCHAR(50),
            server_default=text(f"SELECT '{cls.__tablename__}_'"
                                f" + convert(varchar(10), NEXT VALUE FOR dbo.sequence)"))


class Project(SequencedBaseModel, table=True):
    pass

with SqlAlchemy I now try to insert rows over an API. The request-json looks like this:

{
    "name": "test_project"
}

So the sequence_id is not entered, and will be generated by database. SqlAlchemy generates a statement like:

insert into Project (name, sequence_id) values ("test_project", null)

which wouldn't be wrong, if it wasn't SQL Server... therefore I get the exception that NULL cannot be inserted into the column sequence_id. For SQL Server we need the default keyword instead of null. If I execute the statement

insert into Project (name, sequence_id) values ("test_project", default)

it works...

any idea on how to make sql-alchemy to use default instead of null/None if there is a default-value?

I also tried to change the sequence_id to use something like

sequence_id: str = Field(alias="sequence_id", default=sqlalchemy.sql.elements.TextClause('default'))

but this doesn't work either

Answer 1

Use the server_onupdate argument to apply the text function with the 'default' keyword, that is already defined when update is performed.

See example below which is applied to your code:

    @declared_attr
    def sequence_id(cls):
        return Column(
            'sequence_id',
            VARCHAR(50),
            server_default=text(f"SELECT '{cls.__tablename__}_'"
                                f" + convert(varchar(10), NEXT VALUE FOR dbo.sequence)"),
            server_onupdate=text('default')
        )

The server_onupdate argument is part of SQLAlchemy's Column constructor.
It is used to specify the value that should be used when the column is updated.
It allows the definition of a SQL expression or keyword to be used instead of explicitly providing a value during updates.