CODEWITHSUNDEEP
javascriptsqlnode.jspostgresqlsupabase
Facundo Serrano
Facundo Serrano

Reputation: 133

PostgreSQL query to Supabase Query

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

Upvotes: 0

Views: 3761

Answers (3)

Emmanuel
Emmanuel

Reputation: 39

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")

Upvotes: 2

Mark Burggraf
Mark Burggraf

Reputation: 191

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.

Upvotes: 2

Mark Burggraf
Mark Burggraf

Reputation: 626

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:

Create View

Or you can create a PosgreSQL function and call that with Supabase .rpc():

Upvotes: 0

Related Questions