5

I'm running Supabase locally, using Docker, on MacOS. I initialized a Next.js project with Supabase, and connected the locally created database to JetBrains DataGrip. I can see and adjust the data in DataGrip without issue. But when I try to query the data from my app's API, I get an error that 'relation "public.users" does not exist'

However the users table I have is within the public schema. So I'm not quite sure why it doesn't exist when I try to access it with the API.

In DataGrip, my database file structure is:

postgres@localhost > postgres > schemas > public > tables > users

My app uses the Supabase client to connect:

import { createClient } from '@supabase/supabase-js'

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY

export const supabase = createClient(supabaseUrl, supabaseAnonKey)

The API call I'm using to access the data is:

async function getData(req, res) {
  try {
    let { data, error, status } = await supabase
      .from('users')
      .select()
    if (data) {
      res.status(200).json({ data })
    }
  } catch (error) {
    res.send(error)
  }
}

Just in case there was some sort of naming collision with the users table a hosted Supabase db uses, I also tried the above with an accounts table as well, but that did not have any impact.

sandypockets
  • 350
  • 3
  • 13
  • 1
    Hi, usually the table `users` is in `auth` schema and might be reserved by Supabase engine and PGSQL, I believe this is the reason, which we can't access table in other schema than `public` in Supabase engine. What I suggest you to do is make sure that changing the name of that table would solve the problem. If this solves the problem and you still need to grab data from `auth.users` you can make a trigger to fill your table with user data. Interesting article about [query users table](https://nikofischer.com/supabase-how-to-query-users-table) – Sávio Batista Feb 20 '23 at 00:28
  • 1
    You can also try to set *Introspect using JDBC metadata* in Expert Options of you data source in Advanced tab. – Vasilii Chernov Mar 29 '23 at 19:33
  • We would probably need to see your public.users table and RLS policies you have there to be able to comment here with how to get this data. – Mansueli Apr 24 '23 at 13:04

0 Answers0