Drizzle Team•13mo ago

Error in SQL syntax when pushing schema

Hello, I'm trying to push my database schema to planetscale using drizzle-kit push:mysql , but I got the error below after confirming the execution. Can someone point out for me what did i do wrong?

Error: target: admin-dashboard.-.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

Error: target: admin-dashboard.-.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

this is my schema

export const customers = mysqlTable("customer", {
  id: varchar("id", { length: 256 })
    .default(sql`UUID()`)
    .primaryKey(),
  fullName: varchar("full_name", { length: 256 }).notNull(),
  email: varchar("email", { length: 256 }),
  phoneNumber: varchar("phone_number", { length: 256 }).unique().notNull(),
  createdAt: datetime("created_at", { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP()`),
  updatedAt: datetime("updated_at", { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP()`),
});

export const customers = mysqlTable("customer", {
  id: varchar("id", { length: 256 })
    .default(sql`UUID()`)
    .primaryKey(),
  fullName: varchar("full_name", { length: 256 }).notNull(),
  email: varchar("email", { length: 256 }),
  phoneNumber: varchar("phone_number", { length: 256 }).unique().notNull(),
  createdAt: datetime("created_at", { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP()`),
  updatedAt: datetime("updated_at", { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP()`),
});

and this is the SQL generated by drizzle-kit push:mysql

CREATE TABLE `customer` (
    `id` varchar(256) NOT NULL DEFAULT UUID(),
    `full_name` varchar(256) NOT NULL,
    `email` varchar(256),
    `phone_number` varchar(256) NOT NULL,
    `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    CONSTRAINT `customer_id` PRIMARY KEY(`id`),
    CONSTRAINT `customer_phone_number_unique` UNIQUE(`phone_number`)
);

CREATE TABLE `customer` (
    `id` varchar(256) NOT NULL DEFAULT UUID(),
    `full_name` varchar(256) NOT NULL,
    `email` varchar(256),
    `phone_number` varchar(256) NOT NULL,
    `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    CONSTRAINT `customer_id` PRIMARY KEY(`id`),
    CONSTRAINT `customer_phone_number_unique` UNIQUE(`phone_number`)
);

Thank you

Solution:

i decided to not generate the uuid from the schema, remove the default value from the ID, and generate the ID when i'm calling the function. Thank you

Jump to solution

3 Replies

RorschOP•13mo ago

I found that the error comes from the UUID(), can someone tell me how to generate a default UUID for my id? I tried using sqlUUID() and sqlUUID_TO_BIN(UUID()). Both gives a syntax error. is it because planetscale is using an older version of mysql? or the only way for me to do it is to generate the UUID when i'm calling the function i'll be writing after this? (like addCustomer)

Solution

Rorsch•13mo ago

i decided to not generate the uuid from the schema, remove the default value from the ID, and generate the ID when i'm calling the function. Thank you

Angelelz•13mo ago

This is not possible in older version of mysql And even now it has it's limitations/problems

Gaming

Programming

Error in SQL syntax when pushing schema