Drizzle Team•8mo ago

Changing `id` type from serial to uuid, causing error.

I'm sorry, I'm new to Drizzle, but I've read through the documentation and discord and cannot find a solution to my problem. I'm using Next.js 14, Postgresql through Vercel. When I first started, I built a test UsersTable but now I want to change the id from a serial to a uuid. Everytime I try to generate:pg it works, but push:pg fails, giving the following error:

error: column "id" cannot be cast automatically to type uuid
  code: '42804',
  hint: 'You might need to specify "USING id::uuid".',

error: column "id" cannot be cast automatically to type uuid
  code: '42804',
  hint: 'You might need to specify "USING id::uuid".',

Could you please tell me how I'm supposed to update my table?

import { pgTable, uuid, varchar, timestamp } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const UsersTable = pgTable("users", {
    id: uuid("id").defaultRandom(),
    firstName: varchar("first_name", { length: 50 }).notNull(),
    lastName: varchar("last_name", { length: 50 }).notNull(),
    email: varchar("email", { length: 100 }).notNull().unique(),
    password: varchar("password", { length: 100 }).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull()
})

import { pgTable, uuid, varchar, timestamp } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const UsersTable = pgTable("users", {
    id: uuid("id").defaultRandom(),
    firstName: varchar("first_name", { length: 50 }).notNull(),
    lastName: varchar("last_name", { length: 50 }).notNull(),
    email: varchar("email", { length: 100 }).notNull().unique(),
    password: varchar("password", { length: 100 }).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull()
})

54 Replies

Cold River•8mo ago

it's not well documented in Drizzle yet, but I think you're expected to modify the migrations if there's no way to do them automatically. In this case, a serial is an int, which cannot be automatically converted to UUID. The error you're getting seesm to be trying to convert "id", which was a serial/int, to a type uuid. You'll need to specify how to convert old values: https://stackoverflow.com/a/7162961 ALTER TABLE tbl_name ALTER COLUMN id TYPE uuid USING some_expression;

Stack Overflow

How to alter a column's data type in a PostgreSQL table?

Entering the following command into a PostgreSQL interactive terminal results in an error: ALTER TABLE tbl_name ALTER COLUMN col_name varchar (11); What is the correct command to alter the data t...

Cold River•8mo ago

i don't know how foreign keys will work though

samson•8mo ago

serial is a different type than uuid - as the error message suggests you could cast it. Like @ColdRiver suggests you could just change the migration assuming you're not in production yet? I would probably just reset the db schema if you're still in dev.

Gaming

Programming

Changing `id` type from serial to uuid, causing error.