In SQLAlchemy 2.1's mapped_column(), how do default, insert_default and server_default differ for columns like created_at and status?
answer
- Python process versus database
- Column.default versus DDL DEFAULT
- one slot, two parameter names
- quoted literal versus func.now()
basics
~20 sdefault (or insert_default) is a Python-side value SQLAlchemy adds to the INSERT when the attribute is unset; server_default writes a DEFAULT clause into CREATE TABLE so the database fills it for every writer. In 2.1, default and insert_default cannot be combined.
solid answer
~40 s`default=` becomes the Core `Column.default`: a scalar, a callable such as `dict`, or a SQL expression such as `func.now()` that SQLAlchemy adds to the INSERT when the object has no value for that column. It never appears in DDL, and on a plain declarative class the attribute stays `None` until the flush. `insert_default=` fills the same slot; it exists because on `MappedAsDataclass` classes `default=` also sets the `__init__()` default, and in SQLAlchemy 2.1 passing both raises `ArgumentError`. `server_default=` renders `DEFAULT ...` in `CREATE TABLE`, so the database supplies the value for every insert from any client, once the schema has it. A plain string is a quoted literal, so use `func.now()` or `text()` for functions. With the mapper's `eager_defaults="auto"`, server-generated values come back through `RETURNING` where the backend supports it.
code
python · 30 linesimport datetime
from typing import Any
from sqlalchemy import JSON, String, create_engine, func
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "user_account"
id: Mapped[int] = mapped_column(primary_key=True)
email: Mapped[str] = mapped_column(String(255))
preferences: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict)
status: Mapped[str] = mapped_column(String(20), server_default="active")
created_at: Mapped[datetime.datetime] = mapped_column(server_default=func.now())
engine = create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
user = User(email="[email protected]")
print(user.preferences, user.status) # None None: nothing applied yet
session.add(user)
session.flush()
print(user.preferences, user.status) # {} activego deeper
Recall the split: default= is applied by SQLAlchemy when it builds the INSERT, server_default= becomes a DEFAULT clause the database applies.
Explain the accepted values, scalars, callables and SQL expressions for default=, quoted strings, text() and func expressions for server_default=, and why insert_default= exists and clashes with default= in 2.1.
Show what you have seen go wrong: a quoted 'now()' default, a default that raw SQL inserts never see, a server default missing from the live table, and how eager_defaults and RETURNING decide when the object sees the value.
Set a team rule for where defaults live, database-owned values such as timestamps as server defaults, Python-generated ones as column defaults, so every writer to a shared table gets the same row.
## Two places a default can live A **column default** is the value a row gets when the code inserting it says nothing about that column. SQLAlchemy can keep such a default in one of two places, and `mapped_column()` has a parameter for each: | Parameter | Lives in | Who produces the value | Shows in `CREATE TABLE` | Applies to inserts from other clients | |---|---|---|---|---| | `default=` | `Column.default` | SQLAlchemy, while building the INSERT | no | no | | `insert_default=` | `Column.default` (same slot) | SQLAlchemy, while building the INSERT | no | no | | `server_default=` | the table's DDL | the database | yes, as `DEFAULT ...` | yes | ## `default` and `insert_default`: SQLAlchemy supplies the value `default=` accepts three kinds of value: - a **scalar**, such as `default="active"`; - a **Python callable**, such as `default=dict` or a function returning a new token, called once per row; - a **SQL expression**, such as `default=func.now()`, which SQLAlchemy renders into the INSERT itself. The value is used only when the object has nothing for that attribute at flush time. On a plain declarative class the constructor does not apply it: right after `User(email=...)`, `user.preferences` is `None`, and the `{}` appears only when the flush builds the INSERT. Rows inserted by raw SQL, another service or a database console never see a `default=`, because it exists only in your Python process. **`insert_default=`** fills the very same `Column.default` slot. It exists because of `MappedAsDataclass`: on a mapped dataclass, `default=` also becomes the default of the generated `__init__()`, and `insert_default=` lets you set a column default without touching the constructor. In SQLAlchemy 2.1 the two are **mutually exclusive**; passing both raises `ArgumentError: The 'default' and 'insert_default' parameters of Column are mutually exclusive`. Older material, including examples written for 2.0, shows them combined. ## `server_default`: the database supplies the value `server_default=` becomes part of the column definition, so the database applies it for every INSERT that omits the column, whoever sends it. It accepts: 1. a **string**, rendered as a quoted literal: `server_default="active"` gives `DEFAULT 'active'`; 2. a **`text()`** construct, rendered verbatim: `server_default=text("CURRENT_TIMESTAMP")`; 3. a **SQL expression** such as `func.now()`, rendered for the dialect (`now()` on PostgreSQL, `CURRENT_TIMESTAMP` on SQLite). Two traps follow from rule 1. `server_default="now()"` stores the five characters `now()` as text, because the string is quoted. And an integer such as `server_default=0` is refused with `ArgumentError`; write `"0"` or `text("0")`. Remember also that a server default does nothing until the database schema contains it: changing the model changes only the metadata, and the live table follows only when `create_all()` builds it fresh or a migration alters it. ## After the INSERT: when the object sees the value - **Python-side defaults** are known immediately, because SQLAlchemy computed them and sent them as parameters. - **Server-generated values** live only in the database. The mapper option `eager_defaults` defaults to `"auto"`: where the backend supports `RETURNING` for the INSERT, SQLAlchemy adds the server-defaulted columns to it (the example's `RETURNING id, status, created_at`) and populates the object at once. - Where `RETURNING` is not available, the attributes are left **expired**, and the first access afterwards loads them with a SELECT. ## Updates: `onupdate` and `server_onupdate` The same split exists for UPDATE statements. `onupdate=` is a Python-side value or SQL expression that SQLAlchemy adds to an UPDATE when the column is not already in its SET clause, the usual way to maintain an `updated_at`. `server_onupdate=` only tells SQLAlchemy that the database will produce a new value on update, for example through a trigger, so the ORM knows to fetch it; it creates nothing in the database itself. ## Choosing A few rules of thumb follow from the table above: - Mutable defaults should be **callables**: `default=dict` builds a fresh dict per row, whereas a literal `{}` would be one shared object. - A `NOT NULL` column with a server default needs no `| None` in its annotation; the database supplies the value. - Adding a server default to an existing model is a schema change, so it ships with a migration, not just a model edit. Use `default=` or `insert_default=` when the value comes from Python (a new dict, a generated token, an application constant) and only this code base inserts rows. Use `server_default=` when the value must hold for every writer, or when the database should own it, as with timestamps from the database clock. A timestamp from `func.now()` uses the database clock either way; `server_default=` additionally covers the inserts that bypass this code.
- Why does `server_default="now()"` store the text `now()` rather than a timestamp?A plain string given to `server_default` is rendered as a quoted literal, so the DDL reads `DEFAULT 'now()'` and rows receive those characters. Use `func.now()`, which renders the dialect's function (`now()` on PostgreSQL, `CURRENT_TIMESTAMP` on SQLite), or `text("now()")` to write SQL verbatim. An integer such as `0` is refused with `ArgumentError`; pass `"0"` or `text("0")`.
- When does the ORM learn a server-generated `created_at` after the INSERT?It depends on the mapper's `eager_defaults`, which defaults to `"auto"`. Where the backend supports `RETURNING` for that INSERT, SQLAlchemy adds the server-defaulted columns to it and fills the object during the flush. Otherwise the attribute is left expired and the first access afterwards runs a SELECT to load it.
- How do `onupdate` and `server_onupdate` differ for an `updated_at` column?`onupdate=` is a Python-side value or SQL expression that SQLAlchemy adds to an UPDATE whenever the column is not already in the SET clause, so it works with no database support. `server_onupdate=` only declares that the database will generate a new value on update, for example through a trigger, so the ORM knows to fetch it; it creates no trigger itself.
A default is a form the sender fills in before posting it; a server_default is the date stamp the post office applies on arrival, to every letter, whoever sent it.
- default= adds a DEFAULT clause to the CREATE TABLE statement
- server_default="now()" makes the database call its now() function
- On a plain mapped class the default is applied when the constructor runs
- Passing both default and insert_default lets insert_default win in SQLAlchemy 2.1
- server_onupdate creates the trigger that refreshes the column on UPDATE
Assumes SQLAlchemy 2.1, where passing both default and insert_default to mapped_column() raises ArgumentError; 2.0 accepted the pair, with insert_default taking the Column default.