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?
answer
- who renders the SET clause
- set_ is taken as written
- default fires, onupdate does not
- name the timestamp in set_
basics
~10 sColumn(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 linesfrom 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
Recall that Column(onupdate=...) supplies a value when SQLAlchemy runs an update() that does not set that column itself.
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.
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.
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.