[Solved] Drizzle timestamp datenow invalid default value – Drizzle

default(sql`CURRENT_TIMESTAMP`)

The Problem:

I am new to Drizzle ORM and I am trying to make a simple schema where there is a created_at column that has default value of date of when did the account got created.

The schema is as follows:

export const users = mysqlTable('users', {
    id: serial('id').primaryKey(),
    email: varchar('email', { length: 100 }).notNull(),
    accountType: varchar('accountType', { length: 15 }).references(() => accountTypes.types),
    password: varchar('password', { length: 100 }),
    emailVerified: boolean('verified').default(false),
    verificationToken: varchar('verificationToken', { length: 256 }),
    createdAt: timestamp('created_at').notNull().defaultNow(),
}, (users) => ({
    userIndex: uniqueIndex('user_idx').on(users.email)
}))

I am getting this error whenever I push it to planetscale:

Error: target: quiz.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: r6e50nzpgoxvw6iizt6g): Sql: "alter table users add UNIQUE INDEX user_idx (email)", BindVars: {REDACTED}
    at PromiseConnection.query (D:\\xampp2\\htdocs\\quiz\\node_modules\\drizzle-kit\\index.cjs:34740:26)
    at Command.<anonymous> (D:\\xampp2\\htdocs\\quiz\\node_modules\\drizzle-kit\\index.cjs:52122:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
{
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'CREATE UNIQUE INDEX `user_idx` ON `users` (`email`);',
  sqlState: '42000',
  sqlMessage: `target: quiz.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: r6e50nzpgoxvw6iizt6g): Sql: "alter table users add UNIQUE INDEX user_idx (email)", BindVars: {REDACTED}`
}

It says that the default value for created_at is invalid but it still got pushed to my database.

The Solutions:

Solution 1: Use `CURRENT_TIMESTAMP` for default value

To fix the error, use `CURRENT_TIMESTAMP` as the default value for the `created_at` column instead of `defaultNow()`. This workaround is suggested in the [Drizzle documentation][1].

timestamp('created_at').notNull().default(sql`CURRENT_TIMESTAMP`)

Solution 2: Change to CURRENT_TIMESTAMP

The solution provided inserts a new timestamp column called "updated_at" with a default value of the current timestamp (the moment the row is inserted). This solves the issue with the invalid default value for the "created_at" column.

Video Explanation:

The following video, titled "How to Get SPAULDER OF RUIN (ESO Mythic Leads) | The Elder ...", provides additional insights and in-depth exploration related to the topics discussed in this post.

Online - Deadlands DLC Timestamps: 0:00 - Introduction 1:16 - Aureal Armor Glaze 1:28 - Petrified Daedroth Horn 1:50 - Void ... for the Spaulder ...