Add single record queries by pk to every table that defines a pk

[wesharper]

Summary

Automatically generate queries for all tables with a defined primary key, where primary key is the singular argument and the query returns one singular record.

Rationale

While there are a handful of well-documented workarounds and approaches, they each have their own set of drawbacks. I believe that adding first-party support for this would simplify the learning curve, reduce ramp time, and better align with consumer expectations.

Other approaches and their issues

Using the node(nodeId) query:

From the documentation, it seems as though this is the preferred method of consumption for singular records. While this approach works, there are some quality-of-life drawbacks.

Because the query necessitates the use of a fragment, usage in typescript front-ends can be cumbersome. This typically requires that the consumer check whether or not the node is of the correct type before they can unwrap. This leads to quite a bit of boilerplate in front-end code.

Additionally, because nodeId is required to be globally unique, and because nodeId is not stored in a location accessible to the end user, there is no way for the application to know which record it is attempting to fetch. This makes the type-checking boilerplate necessary, because the intent of the application is unknown.

This could also make URLs unstable i.e. (/users/:userId), depending on how, when, and where nodeId is generated.

Creating UDFs:

It is fairly trivial to implement a UDF when this functionality is desired. However, if this is a pattern that the application developers adopt, it means generating a host of one-off functions that are practically identical.

These functions, while very simple, can quickly make the Supabase UI less useful (imagine 30 table_by_id functions in the api docs section and the database functions view).

UDFs are also more prone to user error, and increase the surface area through which policies and RBAC are accidentally overruled.

The TL;DR

Adding first-party support provides the following:

• Meets likely user expectation that they will be able to fetch a singular record by its primary key
• Prevents having to jump through hoops on the client when using the node(nodeId) query
  • i.e. object type boilerplate (js/ts), url stability, typescript boilerplate
• Makes one of the more common operations (get 1) more consistent with surfaces that cannot rely on nodeId (filter, order, update)
• Reduces UDF boilerplate
• Prevents RBAC "footguns" possible in UDFs
• Prevents more relevant UDFs from getting buried in Supabase UI

Design

• Adds a <tablename>_by_pk(pk: pk_type) method to all tables that define a pk
• Ensures RBAC and custom policies are honored
• Supports PKs of various types, as long as certain pk constraints are met
• Does not support primary keys that are not defined in the database schema (in other words, no comment directive overrides)

Examples

Illustrations and examples to clarify descriptions from previous sections.

Drawbacks

• The scope and responsibility of the library increases, as well as the maintenance burden
• The generated graphql schema could potentially balloon in size
• May lead to user confusion about which approach to use by preference

Alternatives

Detailed in rationale section.

Unresolved Questions

Unfortunately, I don't currently have deep understanding of the codebase, so I don't have the necessary knowledge to generate a proper design.

What should happen if the record doesn't exist (err or null)?

[olirice]

thanks for the writeup

We originally wanted exactly one way to query each collection. The project is mature enough that it might be time to expand that for the user QOL you mentioned

This unlikely to get attention given right away given our other priorities but I'll leave it open for when the time comes