Invalid default value for timestamp

MPmakaron pelnoziarnisty3/30/2023
Hi, I have recently started learning SQL and I've run into this problem with Drizzle Kit. I have this promoCodes table:
// PlanetScale (MySQL) + Drizzle ORM
export const promoCodes = mysqlTable(
'promo_codes',
{
id: serial('id').primaryKey().autoincrement(),
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at').onUpdateNow(),
// ...
}
)
// PlanetScale (MySQL) + Drizzle ORM
export const promoCodes = mysqlTable(
'promo_codes',
{
id: serial('id').primaryKey().autoincrement(),
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at').onUpdateNow(),
// ...
}
)
and when I try to push it using pnpm drizzle-kit push:mysql, I get this error:
target: xxx.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updated_at' (errno 1067) (sqlstate 42000) (CallerID: ues4twwhaqs5wh3lm76u): Sql: "alter table promo_codes modify column created_at timestamp default now()", BindVars: {REDACTED}
target: xxx.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'updated_at' (errno 1067) (sqlstate 42000) (CallerID: ues4twwhaqs5wh3lm76u): Sql: "alter table promo_codes modify column created_at timestamp default now()", BindVars: {REDACTED}
Bbloberenober3/30/2023
@Andrii Sherman
ASAndrii Sherman3/31/2023
If you are using PlanetScale try to change .defaultNow() to
.default(sql`CURRENT_TIMESTAMP`)
.default(sql`CURRENT_TIMESTAMP`)
PlanetScale is restricting (now()) expressions
FFyzz3/31/2023
Just inquiring further about this, any fix yet? I did as suggested and changed .defaultNow() to
.default(sql`CURRENT_TIMESTAMP`)
.default(sql`CURRENT_TIMESTAMP`)
but i'm still receiving the same error Ps I am also using planetscale
Bbloberenober3/31/2023
Could you try running the alter column query manually on planetscale to check if it works at all?
FFyzz3/31/2023
looks to be the same error on their end ill check the docs
create table users (
id varchar(256) not null primary key
created_at timestamp(2) not null default current_timestamp()
);
target: hobby.-.primary: vttablet: rpc error: code = InvalidArgument desc = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'created_at timestamp(2) not null default now() )' at line 3 (errno 1064) (sqlstate 42000) (CallerID: oyl1qfn8efl5jv0d0jot): Sql: "create table users (\nid varchar(256) not null primary key\ncreated_at timestamp(2) not null default now()\n)", BindVars: {REDACTED}
create table users (
id varchar(256) not null primary key
created_at timestamp(2) not null default current_timestamp()
);
target: hobby.-.primary: vttablet: rpc error: code = InvalidArgument desc = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'created_at timestamp(2) not null default now() )' at line 3 (errno 1064) (sqlstate 42000) (CallerID: oyl1qfn8efl5jv0d0jot): Sql: "create table users (\nid varchar(256) not null primary key\ncreated_at timestamp(2) not null default now()\n)", BindVars: {REDACTED}
LLiltripple_reid4/1/2023
Hey is this error still showing up or have you found a workaround?
FFyzz4/1/2023
For now I just removed the created at and updated at, lol If I figure it out I’ll post in here 😁
Rryanagillie4/3/2023
I had to change it to
.default(sql`CURRENT_TIMESTAMP(3)`)
.default(sql`CURRENT_TIMESTAMP(3)`)
and for updateNow()
.default(sql`CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)`)
.default(sql`CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)`)
but that updateNow is probably really not recommended For some reason planetsale requires CURRENT_TIMESTAMP to be called as a function and not just an interpreted value
Bbloberenober4/3/2023
Weird We'll probably need to allow customizing the onUpdate value, in addition to onUpdateNow

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
drizzle-orm/mysql-core has no InferModel or MySqlRawQueryResult exportsHi there, I'm not sure if I'm misunderstanding something here, but as per the documentation, `drizzlSQLite migrations are not being appliedFirst time using SQLite and drizzleORM, so forgive me if this is a stupid question. I've been tryingQuestion: Wouldnt it make sense for value to be automatically notNull if you set a defaultRight now you would need to use notNull().default(true), is there a reason for it be still nullable Code generated valueI'm using SQLite. How can I create a custom type with an auto-generated value which is generated in BUG: Postgres migration script generates a few syntax errorsContext: I was running migrations with drizzle-kit's migration function from `node-postgres` , and kuuid's being inferred as stringsWhen I use InferModel on a table that has a uuid type, I see that the column gets inferred as a striAny easy way to create a typescript ENUM type from pgEnum?Prisma used to auto-gen all the enum types as well which are heavily used in our application.Plans to add array related support for Postgres?+ features to append an object to an array field in postgres?Migrating from PrismaHi, I have an existing project already in production built using prisma and postgres (supabase). I aNumeric // Decimal Postgres types are inferred as strings and expect string as an input as wellCannot read properties of undefined (reading '0')What is the expected behavior when using `get()` when there would be no matching results for a giveIs it recommended to create a SQL transaction every time we try to create a foreign relationship?prisma used to wrap every foreign upsert (2 entities) within a transaction to guarantee that both roDoesn't drizzle-kit generate:pg work yet when schema imports from esm package?``` > drizzle-kit generate:pg --out src/db/migrations --schema src/db/schemas drizzle-kit: v0.17.0 Is there `.returning()` in insert statement in MySQL like SQLite?There is `.returning()` insert statement in SQLite but MySQL not. how do I `.returning()` in insertCan you please release this commit?https://github.com/drizzle-team/drizzle-orm/commit/63134f21ab5ccbdb651a63c1ac0f6d5e8bbe2002 I'm getGetting results in document form rather than recordHi, new to drizzle but liking it so far. Is there away to get the results in more of a 'document' strequire() of ES Module is not supported planetscale serverless + sveltekitHi, I am getting this error when I user planetscale serverless with drizzle orm ``` require() of ESMySQL Standalone queryBuilder?https://discord.com/channels/1043890932593987624/1052239146699210863/1085708651374837810 Where shouWhat is the type for an .orderBy() parameterInside of a function, i have a query more complicated than this, but something like: ```ts db.selecMySqlInsertValue<> vs typeof Table type mismatchI've got this line of code: ```ts await db.insert(table).values(props); ``` Where `props` is showi