SQLAlchemy mixin:server_default&onupdate timestamps

Use a reusable SQLAlchemy declarative mixin with server_default=func.now() and onupdate=func.now() to let the database manage created_at and updated_at without application code.

The problem: audit columns drift when set in Python

Manually assigning datetime.utcnow() in model __init__ or before commit creates drift between app servers, misses bulk updates, and requires every write path to remember the assignment. The useful takeaway is to let the database set the values with a reusable declarative mixin that uses server_default for creation and onupdate for changes.

\n\n

Desired outcome

Any model that inherits a TimestampMixin gets two columns, created_at and updated_at, populated automatically by the database on INSERT and UPDATE. No application code needs to assign them, and the mixin can be reused across tables without duplication.

\n\n

Prerequisites

SQLAlchemy 1.4 or 2.0 installed. The declarative API and func work the same in both, with the 2.0 style registry or classic declarative_base().
A configured Engine and Session factory. You need write permission on the target schema to create tables or run migrations.
Familiarity with declarative mapping. A declarative base is the class that turns Python classes into tables; a mixin is a class with only mapped attributes that is inherited by real models.

\n\n

Focused procedure

1. Define the mixin

Import the SQL functions and types once. func.now() is a SQL expression that resolves to the database’s current timestamp function.

from sqlalchemy import DateTime\nfrom sqlalchemy.sql import func\n\nclass TimestampMixin:\n    created_at = DateTime(timezone=True, nullable=False, server_default=func.now())\n    updated_at = DateTime(timezone=True, nullable=False, server_default=func.now(), onupdate=func.now())\n

server_default tells the database to supply a default on INSERT. onupdate tells SQLAlchemy to emit a new expression for the column on UPDATE statements.

\n\n

2. Wire the mixin into a declarative model

Use a declarative base. The example below uses the 2.0 registry style; replace with declarative_base() if you prefer 1.4 classic.

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column\n\nclass Base(DeclarativeBase):\n    pass\n\nclass Article(Base, TimestampMixin):\n    __tablename__ = \"article\"\n    id: Mapped[int] = mapped_column(primary_key=True)\n    title: Mapped[str] = mapped_column()\n

Inheritance order matters: Base first, then TimestampMixin, so the mixin attributes are collected by the mapper.

\n\n

3. Create tables

Run metadata creation where you have DDL permissions. For production use Alembic migrations instead of create_all.

from sqlalchemy import create_engine\n\nengine = create_engine(\"{database_url}\")\nBase.metadata.create_all(engine)\n

\n\n

4. Insert and update via Session

No timestamp assignment is required in application code.

from sqlalchemy.orm import Session\n\nwith Session(engine) as s:\n    a = Article(title=\"First\")\n    s.add(a)\n    s.commit()\n\n    a.title = \"Edited\"\n    s.commit()\n

The INSERT relies on the database default for both columns. The UPDATE statement generated by SQLAlchemy includes updated_at = now() because of onupdate.

\n\n

Expected checks

Verify behavior with a read-back query in the same session or a fresh session.

After insert, query the row and confirm both created_at and updated_at are populated and equal within the database’s precision.
After an update to a non-timestamp field, confirm updated_at changes while created_at stays unchanged.
Check the DDL for the table. The created_at column should have a server default, and the ORM should emit updated_at on UPDATE.

A practical check is to print the two attributes after each commit and compare them. Do not rely on Python datetime assignment; the values must come from the database.

\n\n

Limitations and dialect notes

Using func.now() delegates time to the database server. Ensure the DB server time zone matches application expectations, or convert timestamps explicitly in the application layer. Time zone handling differs by dialect.

SQLite has limited timezone support and treats func.now() as UTC text. Test timestamp behavior if you rely on timezone-aware datetimes or need consistent ordering across dialects. PostgreSQL and MySQL support now() natively and are common targets for this pattern.

The onupdate expression is emitted by SQLAlchemy on UPDATE; bulk update operations that bypass the ORM will not trigger it. For bulk changes, use explicit func.now() in the statement.

\n\n

Recovery options

If timestamps are missing or incorrect after a deployment:

Inspect the column definition in the database. Missing server_default or onupdate indicates the migration did not apply the mixin correctly.
Re-run migrations to add the server default and onupdate clauses. With Alembic, generate a revision that alters the column defaults.
As a temporary correction, load affected rows in a session, set updated_at manually to func.now(), and flush. This should be done only for data repair, not as normal flow.

Avoid changing the mixin to set values in Python; that reintroduces drift and defeats the purpose of database-managed audit columns.

SQLAlchemy Declarative Mixin for Database-Managed created_at and updated_at

The problem: audit columns drift when set in Python

Desired outcome

Prerequisites

Focused procedure

1. Define the mixin

2. Wire the mixin into a declarative model

3. Create tables

4. Insert and update via Session

Expected checks

Limitations and dialect notes

Recovery options

0 replies

Your perspective belongs here.