Auto update timestamp fields

How to auto update fields like updated_at?

9 Replies

you can add the .onUpdateNow() call to it. I'm doing something similar:

modifiedAt: timestamp('modified_at').defaultNow().onUpdateNow(),

modifiedAt: timestamp('modified_at').defaultNow().onUpdateNow(),

It may not be supported by some db providers such as planetscale

eatmooseOP•2y ago

this doesn't exist

Jim•2y ago

https://github.com/drizzle-team/drizzle-orm/commit/99df063e2f9c9de1c5090bd920f7bc670415741c it does, make sure you have updated your drizzle packages

GitHub

Add has onUpdateNow for mysql timestamp · drizzle-team/drizzle-orm@...

eatmooseOP•2y ago

im on the latest orm version, still giving type error that it doesnt exist @.3819

Jim•2y ago

What driver are you using?

Eternal Mori•2y ago

Planetscale advices to update these values on app level, because if you use mysql functions like: "NOW()" or "CURRENT_TIMESTAMP()" they will use the mysql server time and timezone. If you want to use the timezone of your own app. set these values at app level. My Schema TS file:

const updatedAndCreatedAt = {
  updatedAt: datetime("updatd_at").notNull(),
  createdAt: datetime("created_at").notNull(),
};

export const users = table(
  "users",
  {
    id: varchar("id", { length: 36 }).primaryKey(),
    ...all other fields,
    ...updatedAndCreatedAt,
  }
);

const updatedAndCreatedAt = {
  updatedAt: datetime("updatd_at").notNull(),
  createdAt: datetime("created_at").notNull(),
};

export const users = table(
  "users",
  {
    id: varchar("id", { length: 36 }).primaryKey(),
    ...all other fields,
    ...updatedAndCreatedAt,
  }
);

When creating my record:

await db
  .insert(schema.users)
  .values({
    id,
    ...al other fields,
    createdAt: new Date(),
    updatedAt: new Date(),
  })
  .execute();

await db
  .insert(schema.users)
  .values({
    id,
    ...al other fields,
    createdAt: new Date(),
    updatedAt: new Date(),
  })
  .execute();

When updating:

await db
  .update(schema.users)
  .set({
    ...body,
    updatedAt: new Date(),
  })
  .execute()

await db
  .update(schema.users)
  .set({
    ...body,
    updatedAt: new Date(),
  })
  .execute()

eatmooseOP•2y ago

postgres beep boop

dandadan•2y ago

postgres does not have a feature like that sadly it can be achieved with triggers, but thats a database layer feature and not really recommended on the contrary, mysql seems to have some tools to automatically update timestamps

CREATE TABLE t1 (
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE t1 (
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

the previously mentioned function onUpdateNow() is probably available for mysql only in order for drizzle to support this for postgres they would have to add a module on top of every insert and pass the date there personally, i believe adding that into drizzle would sort of defeat the purpose of drizzle as a typesafe orm on top of drivers the feature does not exist in postgres and drizzle should only provide whatever is available for the database driver youre using

eatmooseOP•2y ago

Ah ok. Yeah I prefer code first approach. I really don’t like adding triggers or functions I can’t see in my code

Gaming

Programming

Auto update timestamp fields