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:
Could you please tell me how I'm supposed to update my table?
54 Replies
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...
i don't know how foreign keys will work though
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.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?![No description](https://cdn.answeroverflow.com/1237156724243169290/image.png)
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
.![No description](https://cdn.answeroverflow.com/1237157291590160394/image.png)
here's what I'd get if I edited the sql file.
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.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.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.
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.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
migth be this one
Drizzle ORM - List of commands
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.