Generic query for PostgrestBuilder

[bryan-vh]

Feature request

I'm currently working on trying to make a generic class to handle typical CRUD operations on a database. Rather than create multiple classes for each model instance, I'd like to use one. While this works for simple use cases, when trying to chain together additional operators at will becomes cumbersome, and I have to revert back to using a standalone class for this.

A clear and concise description of what you want and what your use case is.

Rather than having to write supabase.database.from("posts").select().equals(...).notEquals(...).execute().value, it'd be nice if I could do something like the following: supabase.database.from("posts").query(query), where now I can write my custom query in another location and keep the generic supabase.database.from()....

Describe alternatives you've considered

It would be easy to just repeat this code elsewhere or not create my own abstraction on top of the Supabase client, but other folks may find this handy

[therickys93]

👍 it could be a useful function.

[grdsdev]

Hi @bryan-vh thanks for opening this issue.

I think the implementation for this would be very straightforward and could be done via an extension, and not provided by the library, but since you and @therickys93 finds this useful, I'll verify the possibility of adding it in the library.

Do you have any implementation you're already using and don't mind sharing it with us?

[therickys93]

Currently I do not have an implementation, because it is, at least for me, a feature request.

I don't know if it is visible, but I was thinking something like that:

String query = "SELECT * FROM users WHERE id = 1";
client.query(query);

If it is something that it is not ok for you, please ignore this comment.

[grdsdev]

Hi @therickys93 if you want to execute a raw SQL in the DB, the way you can accomplish this today is by creating a RPC function that accepts the SQL and runs it.

Example:

struct RawSQL: Encodable {
   let sql: String
}

try await supabase.rpc("execute_sql", params: RawSQL(sql: "SELECT * FROM users WHERE id = 1").execute()

Edit: Although this is possible, this IS NOT RECOMMENDED and it can lead to serious security problems

[therickys93]

Hi @grdsdev thanks so much for your reply. I have only one question. How can I create a RPC function that accepts the SQL and runs it? Maybe it is off-topic.

[grdsdev]

@therickys93 I edited my comment, although this is possible, this IS NOT RECOMMENDED and it can lead to serious security problems.

Another approach for the generic query could be:

typealias GenericQuery = (PostgrestQueryBuilder) throws -> PostgrestBuilder

extension PostgrestQueryBuilder {
  func query(_ apply: GenericQuery) rethrows -> PostgrestBuilder {
    try apply(self)
  }
}

And then use it as:

func insert(_ todo: Todo) -> GenericQuery {
    {
        $0.insert(todo).single()
    }
}

try await supabase.from("todo")
    .query(insert(todo))
    .execute
    .value as Todo

So it is possible to create a generic query approach, I'm not adding this to the library, as we plan to make some improvements soon that would touch this.