skip to content

A SQLAlchemy upsert built with on_conflict_do_update() leaves updated_at unchanged although its Column declares onupdate; why, and how do you fix it?

level: seniorimportance: should knowfreq 25%

answer

  1. who renders the SET clause
  2. set_ is taken as written
  3. default fires, onupdate does not
  4. name the timestamp in set_

basics

~10 s

Column(onupdate=...) is applied only to SET clauses SQLAlchemy builds for update() statements. The DO UPDATE SET of on_conflict_do_update() is your set_ dict verbatim, so list updated_at there, for example func.now().

solid answer

~40 s

`Column.onupdate` is a client-side default: when SQLAlchemy compiles an `update()` and the column is not in the SET clause, it adds it. The `set_` dict of `on_conflict_do_update()` is rendered as written, and the PostgreSQL and SQLite dialect docs warn that it does **not** take `onupdate` into account; MySQL's `on_duplicate_key_update()` carries the same warning. So prices change but `updated_at` keeps its old value. The insert branch still works because `Column.default` fills the VALUES clause for new SKUs. Fix it by adding `'updated_at': func.now()` to `set_` for the database clock, or `stmt.excluded.updated_at` to reuse the Python default computed for the proposed row. `server_onupdate=FetchedValue()` does not help: it only tells SQLAlchemy the database sets the value, for example through a trigger.

code

python · 39 lines
python
from datetime import datetime, timezone

from sqlalchemy import (Column, DateTime, Integer, MetaData, String, Table,
                        create_engine, select, update)
from sqlalchemy.dialects import sqlite


def utcnow():
    return datetime.now(timezone.utc)


metadata = MetaData()
products = Table(
    "products", metadata,
    Column("id", Integer, primary_key=True),
    Column("sku", String(32), unique=True, nullable=False),
    Column("price_cents", Integer, nullable=False),
    Column("updated_at", DateTime(timezone=True), default=utcnow, onupdate=utcnow),
)

engine = create_engine("sqlite://")
metadata.create_all(engine)

with engine.begin() as conn:
    conn.execute(sqlite.insert(products), [{"sku": "SKU-1", "price_cents": 100}])
    first = conn.execute(select(products.c.updated_at)).scalar_one()

    stmt = sqlite.insert(products)
    stmt = stmt.on_conflict_do_update(
        index_elements=[products.c.sku],
        set_={"price_cents": stmt.excluded.price_cents},
    )
    conn.execute(stmt, [{"sku": "SKU-1", "price_cents": 250}])
    after_upsert = conn.execute(select(products.c.updated_at)).scalar_one()
    print("upsert moved updated_at:", after_upsert != first)

    conn.execute(update(products).where(products.c.sku == "SKU-1").values(price_cents=300))
    after_update = conn.execute(select(products.c.updated_at)).scalar_one()
    print("update() moved updated_at:", after_update != first)

go deeper

for a junior

Recall that Column(onupdate=...) supplies a value when SQLAlchemy runs an update() that does not set that column itself.

for a middle

Explain that on_conflict_do_update() renders set_ exactly as given, so onupdate never reaches the DO UPDATE SET clause, while default still fills the insert branch.

for a senior

Show you diagnose the stale timestamp from the SQL log, fix it in set_, and add a test that exercises the conflict path rather than only fresh inserts.

for a principal

Decide where audit timestamps live: client-side onupdate repeated in every upsert, or a database trigger that every writer gets, and who audits upsert calls for the gap.

## The symptom The nightly importer upserts products by SKU. New products arrive with a correct `updated_at`, and changed prices land, but `updated_at` on existing products never moves. The table declares: ```python Column("updated_at", DateTime(timezone=True), default=utcnow, onupdate=utcnow) ``` and the upsert is `stmt.on_conflict_do_update(index_elements=[products.c.sku], set_={"price_cents": stmt.excluded.price_cents})`. Nothing raises; the timestamp is simply stale. ## Where `onupdate` actually fires `Column.onupdate` accepts a scalar, a Python callable or a SQL expression. The `Column` docstring defines it as a value "invoked upon update if this column is not present in the SET clause of the update". In practice: - SQLAlchemy adds it when it **compiles** an `update()` construct, through Core or through the ORM's flush. - It is a **client-side** rule: the database knows nothing about it, so an UPDATE from another tool or a trigger never runs it. - It is skipped when the statement already sets the column explicitly. ## Why the upsert skips it `on_conflict_do_update()` is part of an **INSERT** statement. Its `DO UPDATE SET` clause is not built by SQLAlchemy's UPDATE compiler; it is the `set_` dict, rendered as written. The `set_` documentation of both the PostgreSQL and SQLite dialects carries a warning that the dictionary does not take into account Python-specified UPDATE defaults or generation functions, such as `Column.onupdate`, unless they are put into `set_` manually. MySQL's `on_duplicate_key_update()` has the same warning. The insert branch is different: `Column.default` is an INSERT default, and SQLAlchemy does evaluate it for the INSERT's VALUES clause. That is why new SKUs look right and hides the defect in tests that only insert. ## Fixes | Change | Effect | Notes | |---|---|---| | `set_={..., "updated_at": func.now()}` | the database clock stamps every updated row | uses server time; renders `now()` on PostgreSQL | | `set_={..., "updated_at": stmt.excluded.updated_at}` | reuses the value the Python `default` produced for the proposed row | works because `default` fired for the INSERT side | | a database trigger, declared as `server_onupdate=FetchedValue()` | the database stamps every UPDATE from any client | `FetchedValue` only tells SQLAlchemy a value appears; it creates no trigger | | an ORM mapper event such as `before_update` | none for this statement | mapper events fire for flushed objects, not for Core statements | Pair the fix with the `where=` argument when only real changes should count: `where=products.c.price_cents != stmt.excluded.price_cents` leaves unchanged rows, and their timestamp, alone. ## Proving it 1. Turn on `echo=True` and read the statement: the `DO UPDATE SET` lists exactly the keys of `set_`, so a missing `updated_at` is visible. 2. Test the conflict path, not only the insert path: insert a SKU, upsert it again with a new price, and assert that `updated_at` moved. The snippet below does this on SQLite and compares the upsert with a plain `update()`. 3. Search the codebase for every `on_conflict_do_update(` and `on_duplicate_key_update(` call on tables that declare `onupdate`, because each one has the same gap. ## The four column default parameters side by side | Parameter | Fires for | Applied by | |---|---|---| | `default` | INSERT, including an upsert's insert branch | SQLAlchemy, when the column is missing from VALUES | | `onupdate` | UPDATE statements SQLAlchemy compiles | SQLAlchemy, when the column is missing from SET | | `server_default` | INSERT | the database, from the table's DDL | | `server_onupdate` | nothing by itself | a marker for a database trigger you create separately | Read this way the defect explains itself: the conflict branch of an upsert is an UPDATE that SQLAlchemy did not compile, so only a value you write into `set_`, or a trigger in the database, can change the timestamp there. ## Keeping every upsert consistent - Build `set_` in one helper per table that adds `updated_at` alongside the feed columns, so a new upsert cannot forget it. - Add a test per upsert that runs the conflict path twice and checks the timestamp, not only the first insert. - If several services or tools write the same table, prefer a database trigger, because a client-side rule only covers the code that remembers it.

  • Why do newly inserted SKUs get a correct updated_at from the same upsert?
    `Column.default` is an INSERT default, and the upsert is an INSERT: SQLAlchemy evaluates `default` and puts the value in the VALUES clause. Only the conflict branch's SET clause is hand-written, so only it misses `onupdate`. That is also why `stmt.excluded.updated_at` works as a fix: the proposed row already carries a fresh timestamp.
  • Would declaring server_onupdate=FetchedValue() on the column fix the upsert?
    Not by itself. `server_onupdate` only tells SQLAlchemy that the database produces a new value on UPDATE; its docstring says it implements no generation function. Without a trigger in the database nothing changes. Listing the column in `set_` is the direct fix.

  • Column.onupdate fires for every UPDATE the database performs, including ON CONFLICT DO UPDATE.
  • The upsert must have failed, since updated_at did not change.
  • server_onupdate=FetchedValue() makes the database stamp the column on update.
  • Column.default is ignored for the insert branch of an upsert as well.
  • A mapper before_update listener stamps rows changed by a Core upsert.