Drizzle Team•5mo 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

ColdRiver•5mo 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...

ColdRiver•5mo ago

i don't know how foreign keys will work though

samson•5mo 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.

David L. Bowman•5mo ago

How am I supposed to push the change? I was trying bunx drizzle-kit push:pg but this didn't work. I was able to just drop the table directly from Vercel and push a new migration, but at some point, it's not going to be easy. So, how do I use the SQL? I can write the sql code, where am I supposed to put it?

David L. Bowman•5mo ago

David L. Bowman•5mo ago

if i put it in here, i was just getting "No changes made" or something like this when I ran bunx drizzle-kit push:pg.

David L. Bowman•5mo ago

here's what I'd get if I edited the sql file.

samson•5mo ago

I would drop all migrations, and start from scratch drizzle-kit drop (multiple times if you have multiple migration files) then drizzle-kit generate:pg to get a fresh migration file lastly you want push that change up to your database, ie run all migration files-- that kind of depends on what service you're using to host your DB.

David L. Bowman•5mo ago

I'm using Vercel/Postgresql, so it's bunx drizzle-kit push:pg but, when I drop migrations, it only drops it from my repository, not from the actual db itself. the issue is if I drop the migration, then generate:pg / push:pg that's where I encounter the error of mismatching types. I'm wondering where I'm supposed to put the ALTER TABLE "users" ALTER COLUMN "id" uuid code. I was able to do it directly on vercel by "querying" that 😄 but, I was wondering where I can run SQL on drizzle. or, maybe that's not the scope of drizzle and my issue is w/ vercel.

ColdRiver•5mo ago

as samson said, there's two options: 1. reset the whole migrations and lose all your data 2. edit the migration to work and then run it If you're doing 1, that's what dropping the migrations and then generating the new one, and then pushing the change would look like. If you're doing 2, that's when you would edit the migration file and put the ALTER TABLE ... USING ... statement. You'd put it in the last migration file, replacing the ALTER TABLE statement in there. It would have the largest number as a preifx.

David L. Bowman•5mo ago

got it, so after I generate:pg I should edit the .sql file before using push:pg? by the way, i appreciate the help a lot.

ColdRiver•5mo ago

im not sure about push:pg because im using the beta which includes a migrate command. but yes, you edit the .sql file immediately after generate:pg

David L. Bowman•5mo ago

migth be this one

David L. Bowman•5mo ago

https://orm.drizzle.team/kit-docs/commands#maintain-stale-metadata

Drizzle ORM - List of commands

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

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