SQLAlchemy psql upsert recipes

Short entry to record a recipe which I have been using and cant ever remember the drawbacks.

This is a database upsert (insert or update). The psql docs descibre upserts as update or insert. In psql upserts are achieved with by an insert with an on conflict update clause. To identify the conflict, one can use either the name of a unique constraint of the table where the insert is done, or a list of unique indexes with optional predicates

SQLAlchemy recipes

These inserts will not trigger sqlalchemy event listeners

Python example using unique constraint name

from sqlalchemy.dialects.postgresql import insert

upsert_statement = (
    insert(DatabaseModel)
    .values(
        unique_col_a=value_a,
        unique_col_b=value_b
        **data_for_update,
    )
    .on_conflict_do_update(
        constraint="uq_col_a_col_b_constraint_name",
        set_=data_for_update,
    )
)
db.session.execute(upsert_statement)

Python example using unique index

from sqlalchemy.dialects.postgresql import insert

upsert_statement = (
    insert(DatabaseModel)
    .values(primary_key=primary_key, **data_for_update)
    .on_conflict_do_update(index_elements=["primary_key"], set_=data_for_update)
    .returning(DatabaseModel)
)

result = db.session.execute(upsert_statement)