Drizzle createdAt and updatedAt timestamps

Set Drizzle createdAt and updatedAt columns in NestJS. The package stamps them on every write, or the database owns them with defaultNow.

Drizzle createdAt and updatedAt columns in NestJS can be set two ways. By default the package stamps them itself: create writes both, and update, softDelete and restore move updated_at, all with the application clock. Or you turn package timestamps off and let the database own them with defaultNow(), $onUpdate() or a trigger. Pick one per entity; the database is the more reliable source of time.

The settings name keys, not SQL columns

timestamps.createdAt and timestamps.updatedAt are keys in your Drizzle table object, created_at and updated_at by default. A camelCase schema needs them set, and since 3.3.1 a key the table does not have stops the service at startup.

What does the package write, and when?

The package writes a JavaScript new Date() into the configured keys on writes that go through the service:

Operationcreated_atupdated_at
create, massCreateSet to nowSet to now
update, massUpdateLeft aloneSet to now
softDelete, massSoftDelete, restore, massRestoreLeft aloneSet to now
upsert, massUpsert (insert)Set to nowSet to now
upsert, massUpsert (conflict, row updated)Kept from the existing rowSet to now
delete, forceDelete(row removed)(row removed)

Two more rules keep the values honest:

  • The body cannot override them. On create, the package writes both keys after mapping the body, so a client-sent created_at is replaced. On update, created_at is dropped from the body (unless writableColumns names it), so a row cannot move its creation date by accident.
  • An empty update is a 400. update() with no column to change throws ValidationFailedException. It does not bump updated_at on its own.
defaults.timestampsOptional

boolean

forRoot: package-managed timestamps for every entity unless one overrides them.

Defaults to true

timestamps.createdAtOptional

string

forFeature: the key of the creation column in your Drizzle table object.

Defaults to "created_at"

timestamps.updatedAtOptional

string

forFeature: the key of the last-modified column in your Drizzle table object.

Defaults to "updated_at"

defaults.sortOrderOptional

"asc" | "desc"

forRoot: entities without a defaultSort sort findAll by their createdAt key in this direction.

How do I set it up with package-managed timestamps?

Add the columns to the schema

db/schema.ts
import { pgTable, serial, timestamp, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  created_at: timestamp('created_at', { withTimezone: true }).notNull(),
  updated_at: timestamp('updated_at', { withTimezone: true }).notNull(),
});

Leave the project default on

app.module.ts
DrizzleCrudModule.forRoot({
  dialect: 'postgresql',
  connectionString: process.env.DATABASE_URL,
  schema,
  defaults: { timestamps: true }, // the default
});

Point camelCase keys at the right columns

With keys like createdAt: timestamp('created_at'), tell the entity which keys to use:

users/users.module.ts
DrizzleCrudModule.forFeature([
  {
    service: UsersService,
    table: users,
    config: { timestamps: { createdAt: 'createdAt', updatedAt: 'updatedAt' } },
  },
])

The entity setting merges with the project default one level deep, so naming only updatedAt keeps the default createdAt.

Why does my service fail at startup with a timestamps error?

Because a configured key is not on the table. Before 3.3.1 the package wrote updated_at into the values, Drizzle ignored the unknown key, and updatedAt silently never moved. The service now checks both keys when it is built and stops with a message that names the key to use when the column exists under another name.

Fix it one of three ways:

  • Set the keys, as in the step above.
  • Turn timestamps off for that entity with config: { timestamps: undefined }.
  • Turn them off for the project with defaults: { timestamps: false }.

Application clock, package writes only

Package-managed timestamps use the application server's clock, not the database's, and only writes through the service set them. Raw SQL, migrations and other services do not. For timestamps that hold on every write path, let the database manage them.

How do I let the database manage createdAt and updatedAt?

Define the defaults in your Drizzle schema and turn the package's timestamps off.

Define the columns with database defaults

db/schema.ts
import { pgTable, serial, timestamp, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .defaultNow()
    .notNull()
    .$onUpdate(() => new Date()),
});
  • defaultNow() lets the database set created_at on insert.
  • $onUpdate(() => new Date()) makes Drizzle set updatedAt on every update it builds, including the package's own update().

Add a trigger for writes outside Drizzle

$onUpdate runs in Drizzle, so a script that runs UPDATE users SET name = ... directly skips it. For an updated_at that holds even then, add a PostgreSQL trigger or a MySQL ON UPDATE CURRENT_TIMESTAMP clause:

migrations/set_updated_at.sql
-- PostgreSQL
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
migrations/set_updated_at.mysql.sql
-- MySQL
ALTER TABLE users
  MODIFY updated_at TIMESTAMP NOT NULL
    DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP;

Turn package timestamps off

app.module.ts
DrizzleCrudModule.forRoot({
  dialect: 'postgresql',
  connectionString: process.env.DATABASE_URL,
  schema,
  defaults: { timestamps: false },
});

The package then leaves the timestamp columns to the database. Leaving it on would overwrite the database default with the application clock on every create and update.

Package-managed or database-managed?

AspectPackage-managedDatabase-managed
ClockApplication serverDatabase
CoverageWrites through the service onlyEvery write, including raw SQL (with a trigger)
Clock control in testsFake timers on DateNeeds database time control
Setupdefaults: { timestamps: true }defaultNow() and $onUpdate(), or triggers

Package-managed timestamps fit when:

  • Every write goes through the service, so coverage is not a concern.
  • Tests need fixed times, since a faked Date controls the values.

Database-managed timestamps fit when:

  • Several writers touch the table: other services, scripts, manual SQL.
  • Several app servers write, and you want one clock instead of each server's.
  • You run in production and want the database to have the last word on time.

Can findAll sort by created_at by default?

Yes, as an opt-in. Set defaults.sortOrder: 'desc' in forRoot, and every entity without its own defaultSort sorts findAll by its timestamps.createdAt key. An entity with timestamps off gets no fallback. See pagination and sorting for the full sort rules.

Common questions

Next steps

Last updated View change history

On this page