uuid type in db is not a string in typescript

[mzalevski]

in my postgres database user.id type is uuid; but when I try TS string type to filter it results in an error. Am I doing something wrong?

const getUser = (id: string) => {
  const users = sql<
    {
      id: string;
      created_at: Date;
      name: string | null;
    }[]
  >`SELECT * FROM users WHERE id = '${id}'`; // Invalid Query: invalid input syntax for type uuid: "$1::text"
};

this error disappeared when I cast the id to text, but it's not really a solution:

const getUser = (id: string) => {
  const users = sql<
    {
      id: text; // Cannot find name 'text'. Did you mean 'Text'?
      created_at: Date;
      name: string | null;
    }[]
  >`SELECT * FROM users WHERE id::text = '${id}'`;
};

[Newbie012]

What about this?

const getUser = (id: string) => {
  const users = sql`SELECT * FROM users WHERE id = '${id}::uuid'`;
};

Another solution (it's not currently implemented) is to define a type called UUID so SafeQL will know to cast it to uuid rather than a text.

[Newbie012]

I'll take a look at it tomorrow evening. It sounds really strange. What's your postgres version?

[mzalevski]

SELECT version(); -> "PostgreSQL 14.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1~20.04) 10.3.0, 64-bit"

it is a postgres instance on Supabase

[Newbie012]

Thanks! Two last things that I need from you:

• your current config (omit any sensitive part)
• eslint output

(make sure you're using the latest version of the plugin)

[mzalevski]

Damn, I see the problem now. I tried the type override before I tried casting to uuid. So step by step it looked like this:

1. const users = sqlSELECT * FROM users WHERE id = ${id}; // Invalid Query: invalid input syntax for type uuid: "$1::text"
2. const users = sqlSELECT * FROM users WHERE id::text = ${id}; // non existent text TS type
3. I'm trying to hack the config with "overrides": { "types": { "uuid": "text" } } } // :facepalm:
4. const users = sqlSELECT * FROM users WHERE id = ${id}::uuid; // Invalid Query: invalid input syntax for type uuid: "$1::text"
5. You help me
6. I see this override
7. I remove the override
8. Casting the argument to uuid works :)

Thanks @Newbie012

[Newbie012]

Happy it worked out 😄