Hello, I have an edge function in kotlin and need ...

# supabase-kt

[Thomas Valloo]

Hello,
I have an edge function in kotlin and need to access the postgres database directly, not through the API. API is great, but each query takes between 30 to 70ms (and the function executes a lot) and I need to work in a transaction.
I saw this example in JS https://github.com/thorwebdev/edgy-edge-functions/blob/main/supabase/functions/postgres-on-the-edge/index.ts, is there anyway to do the same in Kotlin ?
Anyone did it already ?

[Jan]

supabase-kt does not support writing Edge Functions, nor does any other client library. Although you can look into the community repo https://github.com/manriif/supabase-edge-functions-kt

[Jan]

But you won't get support for this here.

[Thomas Valloo]

I'm already working with an edge function using supabase-kt, like the repo you linked.
I'm also using postgrest API, but each query takes to much time, so I'm exploring new possibilitites

[Jan]

Also accessing the db directly through a postgres library works, but this is also not a client lib functionality

[Thomas Valloo]

Each query (even simple select in small db) takes between 30 to 70ms, do you have any idea why by any chance ??

[Thomas Valloo]

I just tested with direct connection in js, it's much faster, and opening the connection once really reduce the total amount of time spent.

[Jan]

So you are using the repo I linked above?

[Thomas Valloo]

no, I developed my edge function prior to that repo.
when I have something better than a POC for the db connection, I will share it

[Maanrifa Bacar Ali]

You can access the posgresql database directly, you just have to use a postregsql specific or any other sql client. I did something similar but in Kotlin (JVM) using pgjdbc for introspection purposes. I do not tried it myself but you can have a look at postgres.js. You can obtain the settings for connecting to your database through the project dashboard. By experience, when you connect to your database, use the port 5432 and not 6543 to avoid surprises.

Hope this will help you.

[Thomas Valloo]

Thanks !
Any idea on how to connect to database using the supabase user connected ? I need to have RLS working.

[Maanrifa Bacar Ali]

You can login to the database as a user that has RLS restriction. But this is not what you want to do. I’m not a Supabase expert nor a Postgres one but i think the key is the JWT and more precisely the claims. You should update something in the database after you’ve connected to it in order to make RLS working based on the JWT claims of the logged in user. Maybe exploring Supabase code can help you find the trick.

[Maanrifa Bacar Ali]

I’m planning to make something similar as what you are trying to achieve but I’m busy for the next two months. It could be great if you share your findings here 🥹

[Thomas Valloo]

Here is how I made it work :

in a js file :

import * as postgres from '<https://deno.land/x/postgres@v0.19.3/mod.ts>'
export async function getUserDbClient(userId) {
    const databaseUrl = Deno.env.get('SUPABASE_DB_URL')

    const client = new postgres.Client(databaseUrl);
    await client.connect();

    await client.queryObject(`SET my.current_user_id TO '${userId}';`)
    await client.queryObject(`SET SESSION ROLE 'authenticated';`)

    return client
}

Expose the javascript objects in Kotlin :

@JsName("getUserDbClient")
external suspend fun getUserDbClient(userId: String): Promise<Client>


// <https://github.com/denodrivers/postgres/blob/main/client.ts>
external class Client : DbConnection {
    fun createTransaction(name: String): Transaction

    override suspend fun queryObject(query: String): Promise<QueryObjectResult>

    suspend fun end()
}

// <https://github.com/denodrivers/postgres/blob/main/query/query.ts>
external class QueryObjectResult {
    val rows: Array<Record<String, Any>>
}

// <https://github.com/denodrivers/postgres/blob/main/query/transaction.ts>
external class Transaction : DbConnection {
    /**
     * The begin method will officially begin the transaction, and it must be called before
     * any query or transaction operation is executed in order to lock the session.
     */
    suspend fun begin(): Promise<Unit>

    /**
     * The commit method will make permanent all changes made to the database in the
     *  current transaction and end the current transaction.
     */
    suspend fun commit(): Promise<Unit>

    override suspend fun queryObject(query: String): Promise<QueryObjectResult>
}

external interface Record<T, R>

you can then use this connection in your kotlin code.
To get userId in the edge function, use the jwt token and import user :

val supabase = provideSupabaseAnonClient()
supabase.auth.importAuthToken(jwt, retrieveUser = true)
val userInfo = supabase.auth.currentUserOrNull()

To use it in tour RLS policies, create this sql function :

CREATE OR REPLACE FUNCTION get_current_user_id() RETURNS TEXT AS $$
DECLARE
    user_id TEXT;
BEGIN
    BEGIN
        SELECT auth.uid()::text INTO user_id;
    EXCEPTION WHEN others THEN
        user_id := NULL;
    END;


    IF user_id IS NULL THEN
        user_id := current_setting('my.current_user_id');
    END IF;

    RETURN user_id;
END;
$$ LANGUAGE plpgsql;

and for the policies, instead of calling auth.uid(), call get_current_user_id()

create policy "Users can update."
on your_table for UPDATE
to authenticated
using ((select get_current_user_id()) = user_id)
with check ((select get_current_user_id()) = user_id);

That way, you can work directly on your postgres database from your edge function, or use the supabase API from the client, RLS policies will work the same.

Hope it helps, tell me if you find anything suspicious.

[Maanrifa Bacar Ali]

Nice, thank you for sharing your solution, will try it one of these days