Supabase "invalid input syntax for type uuid": read the quotes

You open your app's dashboard and the list of projects stays empty for a moment, then fills in. Or it stays empty. Either way the browser console has a red line, and the request behind it answered with this:

{
  "code": "22P02",
  "details": null,
  "hint": null,
  "message": "invalid input syntax for type uuid: \"undefined\""
}

Supabase "invalid input syntax for type uuid" means Postgres was handed a value that is not a uuid, for a column that holds only uuids, and it refused the whole request. The part in quotes is what your app sent. Here that is the word undefined: the query ran before the id it needed existed.

The quick fixes passed around for this are to change the column to text or to cast the value. Both make the message go away. Neither gives the query its id, so the page goes on asking for the wrong thing and stops telling you about it.

What does "invalid input syntax for type uuid" mean?

It means a value on its way into a uuid column was not written the way a uuid is written, so Postgres stopped before running anything. No rows were read and none were changed.

A uuid is an id in one fixed shape: 32 characters from 0 to 9 and a to f, printed in groups of 8, 4, 4, 4 and 12, such as 3f2b8c1e-9a4d-4e7b-b1c2-5d6e7f8a9b0c. Supabase gives every signed-in user an id like that, and a column that records whose row is whose, such as user_id, holds the same kind of value. Postgres's reader for the type accepts "a series of 32 hexadecimal digits with an optional dash after each group of 4 hexadecimal digits, and optionally surrounded by {}". Anything else ends in this message, with the input quoted back.

Picture the column as a sorting machine that reads one kind of label, a code in those five groups. Your app put a parcel on the belt with a word written where the code goes. The machine stopped the belt and read the word back to you.

Read what is between the quotes

The quoted value is the useful half of the message. It is exactly what reached the database, and each shape it takes points at a different line of your app.

If the error shows up on every visit, look for a name that holds nothing. If it shows up only on the first draw of the page, look for a value that had not loaded yet.

Why does supabase-js send the word undefined?

Because it writes each filter into the address of the request as text, and JavaScript writes a missing value as the word undefined.

A call such as .eq('user_id', user?.id) becomes ?user_id=eq. followed by the value. The library's source builds it from a template, `eq.${value}`, and a comment further down the same file says what that does to a missing value: it would "show up as ?column=eq.undefined". PostgREST reads whatever follows eq. as plain text, and its parser treats null as a keyword only after is.. Postgres receives the nine letters of undefined and tries to read them as a uuid.

.rpc() goes the other way. Its arguments travel as JSON, which leaves an undefined value out entirely, so a function call loses the argument rather than sending the word, and Supabase answers Could not find the function.

In a React app, look in three places for the missing value:

• The user has not loaded yet. Supabase reads the signed-in session asynchronously (await supabase.auth.getSession()), so the first time your page draws, a user kept in state is still empty and user?.id is undefined. A query fired on that first draw sends it.
• The id has another name. A route written as /projects/:projectId puts the value under projectId, and a page that reads id gets undefined on every visit.
• The id comes from data still on its way. An id taken from another query's result, read before that query has answered.

Hold the query until the id exists

Check for the id before the call and let the page show its loading state until the id arrives. For "undefined" and "" that is the whole fix.

useEffect(() => {
  if (!user?.id) return

  supabase
    .from('projects')
    .select('*')
    .eq('user_id', user.id)
    .then(({ data, error }) => {
      if (!error) setProjects(data)
    })
}, [user?.id])

The return sends nothing while the id is missing, and user?.id in the list at the end runs the effect again the moment the id appears. On the sorting machine, that is waiting until the code is on the label before the parcel goes on the belt.

If your app fetches with TanStack Query (the useQuery hook), its enabled option is the same guard. Its guide to dependent queries sets enabled: !!userId with the note "The query will not execute until the userId exists".

const { data: projects } = useQuery({
  queryKey: ['projects', user?.id],
  queryFn: async () => {
    const { data, error } = await supabase
      .from('projects')
      .select('*')
      .eq('user_id', user.id)
    if (error) throw error
    return data
  },
  enabled: !!user?.id,
})

If you build by prompting, ask for it in one sentence: "Don't run the projects query until user.id exists, and show the loading state until then."

Should you change the column to text?

No, unless the ids you store are not uuids at all. Changing the column, or casting it to text in a query, removes the check that raised the error and leaves the missing id where it was.

Follow the same request after the change. user_id=eq.undefined is now a legal question, so Postgres compares the word undefined with every id in the column, finds no match, and returns an empty list. Your page shows "No projects yet" to someone who has ten, and the console has nothing in it to explain why. On a write it goes further: the row is saved with undefined where its owner's id belongs, and a query for the real owner never finds it. On the sorting machine, that is unplugging the label reader. The parcel goes through, matches no address, and is never delivered.

The one time the column is the thing to change is the "user_…" row in the table above. Clerk's ids are text, and Clerk's own guide to Supabase creates the column as user_id text not null default auth.jwt()->>'sub'. That is a different problem: the table was built for Supabase's ids and is being handed somebody else's.

Is the id in your filter what keeps other rows out?

No. The filter decides which rows your page asks for. Which rows a visitor may read is decided by Row Level Security, the per-row rules Supabase checks on every request made with your public key, and anyone can send your request again with a different id or with none.

One supabase-js method shows the difference. .match() takes several columns at once, and its source drops any column whose value is undefined before it builds the request, which is what that comment about eq.undefined was there for. So .match({ user_id: user?.id }) made before the user loads sends no filter at all, raises no error, and returns every row the table's rules let this visitor read. Add .single() to that call and it fails another way, with JSON object requested, multiple (or no) rows returned, because more than one row came back. With the rules on, those rows are still only the visitor's own. With them off, they are the whole table, and switching them on for every table covers the switch and the first rule.

You can see from outside whether a table hands rows to a stranger. Our free scan reads your live app with the same public key a visitor gets, reports which tables answer with rows, and needs no account.

Before you change a column your app reads

Keep a copy of the database from before you change a column's type, and hear about it when a table starts answering strangers.

Reeve Care keeps a copy of your Supabase database:

• an encrypted copy on a schedule, on Reeve's own storage in the EU, outside your Supabase account
• every table with its rows as they were before you changed a column's type
• each copy verified before it counts, by counting the rows in every table
• your uploaded files as well, once you connect a Storage credential
• a one-click restore, with a safety copy of the current state taken first
• everything Monitor does

Reeve Monitor keeps checking from outside:

• whether your app is up, checked every minute
• the full security check every hour, on up to three apps
• a message when a result changes, so a table that starts answering strangers after a deploy reaches you without you going to look
• a monthly report, in plain language, of what it saw

What to do now

Start with the quotes, because the value in them points at the line of your app that sent it. If the fix you are handed changes a column's type, keep a copy of the database from before the change.