Support Automatic updatedAt Timestamps in Astro DB

[mauriciabad]

Body

Summary

Add support for automatic updatedAt timestamp updates in Astro DB, similar to how createdAt works with NOW, to prevent developers from forgetting to update timestamps manually.

Background & Motivation

Currently, Astro DB supports automatic createdAt timestamps using default: NOW, but there's no equivalent for updatedAt. This means developers need to manually update the timestamp in every update query, which is error-prone and can lead to inconsistent data.

Users coming from other ORMs like Drizzle or Prisma expect this functionality out of the box, as it's a common pattern in modern databases. The lack of this feature might push developers to use alternative solutions or implement potentially inconsistent workarounds.

Goals

• Add automatic timestamp updates for updatedAt columns when records are modified
• Maintain consistency with existing timestamp patterns in Astro DB
• Reduce boilerplate code in update queries
• Prevent human error in maintaining update timestamps
• Keep the developer experience simple and intuitive

Example

Current approach (requires manual updates):

// Schema
const MyTable = defineTable({
  columns: {
    title: column.text(),
    updatedAt: column.date({ default: NOW }),
  },
});
// Update query - easy to forget updating the timestamp
await db
  .update(MyTable)
  .set({
    title: "New Title",
    updatedAt: new Date(), // Must remember to add this
  })
  .where(eq(MyTable.id, 1));

Proposed solution:

// Schema
const MyTable = defineTable({
  columns: {
    title: column.text(),
    updatedAt: column.date({
      default: NOW,
      onUpdate: NOW, // New feature
    }),
  },
});
// Update query - timestamp updates automatically
await db
  .update(MyTable)
  .set({
    title: "New Title",
  })
  .where(eq(MyTable.id, 1));

This would align Astro DB more closely with other modern ORMs while maintaining its simplicity and developer-friendly approach.

[ematipico]

I think it's a reasonable proposal and I don't think we need a full RFC. Feel free to send a PR :)