Easier debugging of detected relationships

Problem

Let's say I want to debug if there's a one-to-one relationship detected (sometimes this can be hard in the presence of views).

Using the runtime schema cache and jq it can be done by searching the table name (country) and the O2O tag:

# start postgrest
# PGRST_ADMIN_SERVER_PORT=3001 PGRST_DB_AGGREGATES_ENABLED=true postgrest-with-postgresql-16  -f test/spec/fixtures/load.sql postgrest-run

curl 'localhost:3001/schema_cache' | \
jq '.dbRelationships | .. | objects | select(.relTable.qiName == "country" and .relCardinality.tag == "O2O")'

{
  "relCardinality": {
    "isParent": true,
    "relColumns": [
      [
        "id",
        "country_id"
      ]
    ],
    "relCons": "capital_country_id_fkey",
    "tag": "O2O"
  },
  "relFTableIsView": false,
  "relForeignTable": {
    "qiName": "capital",
    "qiSchema": "test"
  },
  "relIsSelf": false,
  "relTable": {
    "qiName": "country",
    "qiSchema": "test"
  },
  "relTableIsView": false,
  "tag": "Relationship"
}

This has some issues:

• Not straightforward and requires another tool for filtering.
• The Admin API has to be involved, which is internal and there's no reason why this information cannot be public facing. -
• We expose OpenAPI to the public but it doesn't have this information.

Solution

A custom media type that shows all the relationships for a resource.

GET /country
Accept: application/vnd.pgrst.relationships+json

[
    {
      "relCardinality": {
        "isParent": true,
        "relColumns": [
          [
            "id",
            "country_id"
          ]
        ],
        "relCons": "capital_country_id_fkey",
        "tag": "O2O"
      },
      "relFTableIsView": false,
      "relForeignTable": {
        "qiName": "capital",
        "qiSchema": "test"
      },
      "relIsSelf": false,
      "relTable": {
        "qiName": "country",
        "qiSchema": "test"
      },
      "relTableIsView": false,
      "tag": "Relationship"
    }
]

Related

• https://github.com/PostgREST/postgrest/issues/1421#issuecomment-1300680960
  • I knew we had discussed this before. The focus of this issue is the relationships though, which is really the most important part, I think.

Start with the GET /country entry point and the existing OpenAPI and Admin API /schema_cache behavior described in the issue. Trace how detected relationships are exposed, then add the proposed relationships media type and verify that it returns the relationship data for a resource without requiring the Admin API or jq.