How can i make once of this PostgreSQL querys in supabase? I tried reading supabase doc but it doesn't work
select rooms.id as room_id, members.id as member_id, user_id from rooms
inner join members
on rooms.id = members.room_id
where rooms.id = members.room_id
and user_id = 1
Or
select room_id, user_id, members.id from members
inner join rooms
on rooms.id = members.room_id
where members.user_id = 1
No, there's not really any performance difference from calling an .rpc() vs. just calling the api directly. You should be fine. Just make sure to set up your database correctly (indexes, joins, etc.) for best performance.
Hello this might not be supabase specific solution, but in my case what I do use when I need to run custom queries like in your own case here is to use the pg package from npm https://www.npmjs.com/package/pg.
I would just create a client and parse my supabase db credentials to it.
export const pgClient = () => {
console.error(`using Postgres ${process.env.host}`)
const client = new Client({
user: process.env.user,
host: process.env.host,
database: process.env.database,
password: process.env.password,
port: parseInt(process.env.port!)
})
return client
}
export it to my router and simply connect
const client = pgClient()
await client.connect()
Run my custom query directly on the DB
await client.query("SELECT * from auth.users")
You can query a foreign table using the Supabase client, but only if there's a foreign key set up:
Supabase select(): Query foreign tables
As stated in the comments, you can create a view for this purpose:
Or you can create a PosgreSQL function and call that with Supabase .rpc():
