Operator does not exist UUID = text

[maxwiseman]

So I have a schema where the ID is a UUID. I want to query the database and get only records that have a specific ID. When I do that, I get the error in the title (operator does not exist: uuid = text). I know why this is happening (because the ID I am looking for is a string, not a UUID), but I am not sure how to tell Drizzle to just ignore this and pretend it is a UUID.

Now that I think about it, should this even be a UUID? The reason I picked UUID over text is because there is a relation that links the userID field of this table to the ID field of the users table (which is of type UUID). Does that even matter?

// getReads.ts
const data = await db.query.reads.findMany({
  where: eq(reads.userId, "5fb341d4-2e7e-4adc-93bf-c756c00ea700"),
  with: {
    course: true,
  },
});
return data;

// schema.ts
export const reads = pgTable("reads", {
  id: uuid("id").defaultRandom().notNull().primaryKey(),
  userId: uuid("user_id"), 
  courseId: text("course_id"),
  createdAt: date("created_at"),
  title: text("title"),
  body: text("body"),
  author: text("author"),
});

Note: I know that hardcoding the ID is a bad idea, this is temporary.

[juanvilladev]

Can you attempt to case read.userId as uuid

where(
sql`${reads.userId}::uuid = '5fb341d4-2e7e-4adc-93bf-c756c00ea700'`
)

[maxwiseman]

Still getting the same error

[maxwiseman]

I also tried this:

where(
sql`${reads.userId}::text = '5fb341d4-2e7e-4adc-93bf-c756c00ea700'`
)

and this:

where(
sql`${reads.userId} = '5fb341d4-2e7e-4adc-93bf-c756c00ea700'::uuid`
)

But I still get the same error

[maxwiseman]

I ended up just doing this:

const initialData = await db.execute(
  sql`select * from ${reads} where ${reads.userId}::text = '5fb341d4-2e7e-4adc-93bf-c756c00ea700'`
);
const data = initialData.rows;

Now the issue that I have is that I cannot also receive my relational data at the same time. (Well, I can, I just don't know how)

[maxwiseman]

The plot thickens. For some reason, this works fine:

 const data = await db.query.reads.findMany({
    where: sql`${reads.userId}::text = '5fb341d4-2e7e-4adc-93bf-c756c00ea700'`,
    // with: {
    //   course: true,
    // },
  });

but this does not:

  const data = await db.query.reads.findMany({
    where: sql`${reads.userId}::text = '5fb341d4-2e7e-4adc-93bf-c756c00ea700'`,
    with: {
      course: true,
    },
  });

[maxwiseman]

I found the problem. I was being stupid. The error was simply because when I defined my schema, I incorrectly defined my relation. In my courses table, the id column was of type uuid, but in my reads table, the courseId column was of type text. I changed it to uuid and everything worked perfectly.