feat: new query api

[leynier]

trafficstars

I have a proposal for a new query API for postgrest-py. The objective is to be more strict with respect to the order in that the different methods can be chained like the SQL statements keeping the full type annotation.

In select:

• single and maybe_single can't be chained
• limit and range only can be chained with a single, or a maybe_single
• order only can be chained with a limit, a range, a single, a maybe_single, or other order
• where only can be chained with a limit, a range, a single, a maybe_single, order, or other where
• from_ only can be chained with a where, a limit, a range, a single, a maybe_single, or an order
• select only can be chained with a from_

In update:

• values can't be chained
• where only can be chained with other where, or a values
• update only can be chained with a where, or a values

In delete:

• where only can be chained with other where
• delete only can be chained with a where

The statements are independents of the client execution way following the idea of other ORM like the new API of SQLALchemy

I think that is a good option to implement this proposal as an addition, not a substitution of the actual implementation.

The code below like a proof of concept only shows the typing annotations:

from typing import Generic, Iterable, Type, TypeVar


class Filter:
    def ne(self, key, value) -> "Filter":
        ...

    def eq(self, key, value) -> "Filter":
        ...


ne = Filter().ne
eq = Filter().eq

T = TypeVar("T")


class WhereStatement(Generic[T]):
    def __init__(self, type: Type[T]) -> None:
        self.__type = type

    def where(self, filter: Filter) -> T:
        return self.__type()


class Statement:
    ...


class ValuesStatement:
    def values(self, data: dict) -> Statement:
        ...


class SingleStatement:
    def single(self) -> Statement:
        ...


class MaybeSingleStatement:
    def maybe_single(self) -> Statement:
        ...


class UnionSingleStatement(SingleStatement, MaybeSingleStatement):
    ...


class LimitOrRangeStatement(UnionSingleStatement):
    def limit(self) -> UnionSingleStatement:
        ...

    def range(self) -> UnionSingleStatement:
        ...


class OrderStatement(LimitOrRangeStatement):
    def order(self) -> "OrderStatement":
        ...


class FromStatement(WhereStatement["FromStatement"], OrderStatement):
    def __init__(self) -> None:
        WhereStatement.__init__(self, type(self))


class SelectStatement:
    def __init__(self, *colums: Iterable[str]) -> None:
        pass

    def from_(self, table: str) -> FromStatement:
        ...


class UpdateStatement(WhereStatement["UpdateStatement"], ValuesStatement):
    def __init__(self, table: str) -> None:
        WhereStatement.__init__(self, type(self))


class DeleteStatement(WhereStatement["DeleteStatement"], Statement):
    def __init__(self, table: str) -> None:
        WhereStatement.__init__(self, type(self))


select = SelectStatement
update = UpdateStatement
delete = DeleteStatement


class PostgrestClient:
    def exec(self, statement: Statement):
        ...


client = PostgrestClient()

filter = ne("key1", "value1").eq("key2", "value2")

select_query = select("column1", "column2").from_("table").where(filter).single()
result_select_query = client.exec(select_query)

update_query = update("table").where(filter).values({"key1": "new_value"})
result_update_query = client.exec(update_query)

delete_query = delete("table").where(filter)
result_delete_query = client.exec(delete_query)

[sourcery-ai[bot]]

Sourcery Code Quality Report

Merging this PR leaves code quality unchanged.

Quality metrics	Before	After	Change
Complexity	2.19 ⭐	2.19 ⭐	0.00
Method Length	30.00 ⭐	30.00 ⭐	0.00
Working memory	5.50 ⭐	5.50 ⭐	0.00
Quality	84.75% ⭐	84.75% ⭐	0.00%

Other metrics	Before	After	Change
Lines	17	17	0

Changed files	Quality Before	Quality After	Quality Change
postgrest_py/exceptions.py	84.75% ⭐	84.75% ⭐	0.00%

Here are some functions in these files that still need a tune-up:

File	Function	Complexity	Length	Working Memory	Quality	Recommendation

Legend and Explanation

The emojis denote the absolute quality of the code:

• ⭐ excellent
• 🙂 good
• 😞 poor
• ⛔ very poor

The 👍 and 👎 indicate whether the quality has improved or gotten worse with this pull request.

---

Please see our documentation here for details on how these metrics are calculated.

We are actively working on this report - lots more documentation and extra metrics to come!

Help us improve this quality report!

[codecov[bot]]

Codecov Report

Merging #102 (e653ccf) into master (796687d) will not change coverage. The diff coverage is 100.00%.

@@           Coverage Diff           @@
##           master     #102   +/-   ##
=======================================
  Coverage   90.10%   90.10%           
=======================================
  Files          22       22           
  Lines         869      869           
=======================================
  Hits          783      783           
  Misses         86       86           

Impacted Files	Coverage Δ
postgrest_py/exceptions.py	43.47% <100.00%> (ø)

---

Continue to review full report at Codecov.

Legend - Click here to learn more Δ = absolute <relative> (impact), ø = not affected, ? = missing data Powered by Codecov. Last update 796687d...e653ccf. Read the comment docs.

[silentworks]

Closing this one out as it has been in draft for over a year now. I'm also not sure about changing the API design as we are following the JavaScript library closely and some of these changes would shift us away from that.