Drizzle Team•9mo ago

Drizzle schema vs SQL when migrating serial id to uuid?

I have a Postgres DB and I want to migrate serial primary key IDs to UUIDs, and I'm running into some potential issues here. I've already added the uuid columns and user_uuid, thing_uuid relation columns to my tables via my Drizzle schema. Using PgAdmin I verified that the various uuid columns are not empty, and I've filled the relational UUIDs with queries like this one:

UPDATE user_usage usage SET user_uuid = u.uuid FROM users u WHERE usage.user_id = u.id;

UPDATE user_usage usage SET user_uuid = u.uuid FROM users u WHERE usage.user_id = u.id;

Problem 1: To prevent having to update my whole application code, I want to basically swap the ID and UUID columns and let them keep the id and user_id column names, but with uuid primary keys of course. Is this something I can solve by editing my schema, or would this be something I need to do by running SQL queries? Problem 2: If I need to do this via SQL queries, how do I then reflect this new state in my Drizzle schema? Maybe introspecting would be a good option here? Or maybe I should just change my schema, generate a migration and then edit the migration by hand before running it? Note: this is a development database migration, so this is essentially practice for whenever I might need to do something similar in prod

27 Replies

Andrii Sherman•9mo ago

thanks twitter I guess you can't use push here, because it will try to remove data

Eddy VinckOP•9mo ago

Exactly

Andrii Sherman•9mo ago

I believe this case should be handled by you. Afterwards, you can use introspection to generate types from Drizzle, which will also produce a commented first migration for your schema. This migration can then be applied. To handle this case, one of the scripts you've sent looks good. Also, note that you made the right decision in migrating the data manually. This should never be handled by an ORM. Unfortunately, you'll have to handle it on your own for now, and then simply introspect a new schema and reuse it in your code. You may need to change the TypeScript object names for tables to match what they were before

Eddy VinckOP•9mo ago

Ok, makes sense! Thanks for confirming! I'm pretty much a noob when it comes to migrations like this (and Google wasn't very helpful) so I really appreciate the advice here 🙏 Hey, I just did my migration in SQL and ran the introspection. Just wanted to let you know that the introspect command did not generate a commented SQL migration file. It did of course generate the schema in TypeScript. (in the generated folder) Maybe I misunderstood, but should it be generating an SQL file? I'm on drizzle-kit 0.20.14

[✓] 6  tables fetched
[✓] 72 columns fetched
[✓] 0  enums fetched
[✓] 1  indexes fetched
[✓] 0  foreign keys fetched

[i] No SQL generated, you already have migrations in project
[✓] You schema file is ready ➜ drizzle/generated/schema.ts 🚀

[✓] 6  tables fetched
[✓] 72 columns fetched
[✓] 0  enums fetched
[✓] 1  indexes fetched
[✓] 0  foreign keys fetched

[i] No SQL generated, you already have migrations in project
[✓] You schema file is ready ➜ drizzle/generated/schema.ts 🚀

Eddy VinckOP•9mo ago

@Andrew Sherman Sorry to bother you about this again, but I'm a little unsure on how to proceed here I now have two schema files, my original schema.ts and the one in generated/schema.ts which is the result of the introspection I just looked at the generated table definitions and applied all the changes from the generated file to my original and then I generated a migration. Looking at the generated migration SQL (see screenshot), I don't think it would break anything to keep the statements in there for my development database since it basically is just changing a bunch of serial IDs to UUIDs, which are already UUIDs since I ran the SQL manually in pgAdmin. Should I commit this migration?

Andrii Sherman•8mo ago

hey sorry, just saw your message notifications in discord are a bit hard

Eddy VinckOP•8mo ago

Yeah no worries Agree lol

Andrii Sherman•8mo ago

seems like you already had migration files in out folder you've specified in drizzle.config, that's why no migration file was generated I guess you should, for any new database you want to apply all the migrations it may be an issue for an existing database because if you already applied all those statements manually and didn't apply this migration yet

Eddy VinckOP•8mo ago

I’ll be creating a new production database soon when I create that environment, so not an issue I think then

Andrii Sherman•8mo ago

then yeah, agree but I would run it firstly in dev env before going prod

Eddy VinckOP•8mo ago

Yeah I did

Andrii Sherman•8mo ago

so I guess no issues then? or something I've missed?

Eddy VinckOP•8mo ago

So in the future would there be a way to add a migration file manually for all the custom work I did in pg admin?

Andrii Sherman•8mo ago

yeah, you can use drizzle-kit generate:pg --custom it will have empty sql for anything you'd like to add

Eddy VinckOP•8mo ago

No issues no I suppose, thanks for clearing these things up for me

Andrii Sherman•8mo ago

but I won't recommend doing it for DDL statements, that are supported in drizzle-orm as long as schema is our source of truth so if you want to add something like triggers, functions, insert(seed) some data, etc. which is not supported in drizzle-orm - you should use --custom option if it's something from a screenshot - it's hard to tell right now if it will be safe

Eddy VinckOP•8mo ago

I see

Andrii Sherman•8mo ago

It will be safe actually, but when you will generate a new migration you will see all the statements generated because we will compare current schema.ts with last snapshot.json and not with database itself

Eddy VinckOP•8mo ago

So only schema modifications in the migrations

Andrii Sherman•8mo ago

If you have it in the Drizzle-ORM API, Drizzle-Kit will handle everything and generate it for you. The only exceptions are additional index fields, check constraints, and views. Those are not supported by Drizzle-Kit yet, but should be in next releases I guess we will need to create a tutorial/guide for this specific case. I'll add it to the backlog for the person responsible for our site. Thanks for the use case

Eddy VinckOP•8mo ago

I was thinking about writing an article too 👍

Andrii Sherman•8mo ago

Because while I'm trying to explain it, I understand how challenging it can be without proper context as we have as kit/orm developers we already working on making drizzle easier

Andrii Sherman•8mo ago

trying to fix drivers api, drizzle.config api, will work on your case as well https://github.com/drizzle-team/drizzle-orm/discussions/2203

GitHub

New Drizzle Kit config API · drizzle-team drizzle-orm · Discussion ...

Drizzle ORM is designed to be dialect specific and used with a particular range of database drivers per dialect. While it lets Drizzle move fast and rapidly add support for whatever new dialects/dr...

Eddy VinckOP•8mo ago

FWIW I am not great at database management but I love using Drizzle. I’m comfortable with queries, joins all that etc (in raw SQL) but the whole migration management is pretty new to me. Maybe with more familiarity about managing migrations like this I wouldn’t be asking this stuff here 😂 Nice, I’m on my phone now but I’ll check it later when I’m on my computer

Andrii Sherman•8mo ago

It is hard and was implemented in Drizzle the way we were doing it, and it definitely won't cover all cases all the devs have, so we are also trying to improve it

Eddy VinckOP•8mo ago

Love that Thanks for all the help here Andrew! Appreciate it ❤️

DiamondDragon•8mo ago

super hyped to see checks implemented in drizzle kit soon❗️

Gaming

Programming

Drizzle schema vs SQL when migrating serial id to uuid?