For some strange reason, I can’t get append to work when updating SQLModel with the List column. I spend too much time trying different things and Google, and I can’t get it to work.

I am using SQLModel, SQLAlchemy and Postgres

Table (I tried different configurations)

class TestList(SQLModel, table=True):
    id: int = Field(default=None, primary_key=True)
    texts: Optional[List[str]] = Field(default=None, sa_column=Column(ARRAY(String)))
    texts2: List[str] = Field(default=[], sa_column=Column(ARRAY(String)))
    json1: Optional[List[str]] = Field(default=[], sa_column=Column(JSON))

    # Needed for Column(JSON)
    class Config:
        arbitrary_types_allowed = True

Test code:

## Create the instance data and insert to DB.
with Session(engine) as session:
    try:
        test_list = TestList(texts=["one", "two", "three"], texts2=["four", "five", "six"], json1=["value1", "value2", "value3"])
        session.add(test_list)
        session.commit()
        session.refresh(test_list)
    except exc.SQLAlchemyError as e:
        print(e)

## Update the list. I am using a different session because in real life the update will 
## be down by different session
with Session(engine) as session:
    try:
        session.add(test_list)
        test_list.texts.append("seven")
        test_list.texts2.append("eleven")
        test_list.json1.append("value4")
        session.commit()
        session.refresh(test_list)
    except exc.SQLAlchemyError as e:
        print(e)


for ts in test_list.texts:
    print(ts)

for ts in test_list.texts2:
    print(ts)

for ts in test_list.json1:
    print(ts)

Results, as you can see the new data wasn’t inserted into the DB 🙁

one
two
three
four
four
five
six
value1
value2
value3

I hope there is an obvious easy solution, that I am missing.