Drizzle Team•15mo ago

Unable to generate uuid as primary key

Hey everyone! I've searched through the help posts with similar titles but I could not get the answer I wanted. I have a prisma current setup, and trying to switch over, but my uuid is generated as my postgres primary key but I keep getting the following error:

"null value in column \"id\" of relation \"User\" violates not-null constraint"

"null value in column \"id\" of relation \"User\" violates not-null constraint"

I've tried all of the following:

export const patient = pgTable('User', {
  id: uuid('id')
    .default(sql`uuid_generate_v7()`)
    .primaryKey()
    .defaultRandom()

export const patient = pgTable('User', {
  id: uuid('id')
    .default(sql`uuid_generate_v7()`)
    .primaryKey()
    .defaultRandom()

  id: uuid('id').defaultRandom().primaryKey(),

  id: uuid('id').defaultRandom().primaryKey(),

and even the migration introspection tool set my id as this:

  id: text('id').primaryKey().notNull(),

  id: text('id').primaryKey().notNull(),

which definitely didnt work. Any ideas?

11 Replies

Angelelz•15mo ago

Did you generate and run the migration after changing your schema? This is a schema change that would only be applied after running the migration or using the push command

imobyOP•15mo ago

Ohhh I did not! Thanks for pointing that out actually. Sorry one last question, @angelelz does this mean that the current UUID method that auto generated from prisma was not the same then? Would I run into issues if I push this to an existing db

Angelelz•15mo ago

I believe prisma generates the UUID in JS and passes it to the DB in that case the answer is no But you can always generate the migration, evaluate the resulting SQL and decide y you want to apply it or make changes

imobyOP•15mo ago

@angelelz sorry to bring this up again, but if I did want to generate it on the db level, how would I: 1) possibly use gen_random_uuid which requires pgcrypto. Is it possible to install pg extensions using a migration or something? 2) Is it possible to generate it on the db an easier way without manually doing it

Angelelz•15mo ago

According to the docs it's available in postgres: https://www.postgresql.org/docs/current/functions-uuid.html

PostgreSQL Documentation

9.14. UUID Functions

9.14. UUID Functions PostgreSQL includes one function to generate a UUID: gen_random_uuid () → uuid This function returns a version 4 …

Angelelz•15mo ago

You have defaultRandom available in the uuid column type That will generate it at db level everytime you insert But again, you have to generate and run the migration or run the push command

imobyOP•15mo ago

@angelelz last question I promise, when I try to do a push it tells me

error: column "id" cannot be cast automatically to type uuid
    at Parser.parseErrorMessage

even after changing it to " id: uuid('id').unique().primaryKey().defaultRandom(), ". any ideas why?

Angelelz•15mo ago

When you run the push command, it writes the generated SQL to the console, can you paste that here?

imobyOP•15mo ago

my issue is/was that I am trying to convert my current text one to a uuid one and its not letting me because of foreign key mappings. seems like a very much sql specific issue not related to drizzle. not sure whats the best route here though

Angelelz•15mo ago

You have to drop all the references and/or indexes, change the type on your table and all related tables, and then recreate the references again

imobyOP•15mo ago

yes that was my fear too, but Ill see if I can do it in a secure way

Gaming

Programming

Unable to generate uuid as primary key