OPTIONS no longer works in v0.4.0.0

[RB14]

In the new version released yesterday, it doesn't seem possible to get the schema definition for a particular table/view using OPTIONS request.

It is possible to get the schema for the entire db by issuing a GET request on the root node, but that seems like an overkill, and too verbose.

It is still noted in the docs though, that OPTIONS is a valid verb.

Also, without OPTIONS, it doesn't seem possible to get information about foreign key constraints (GET request on the root node does not provide this info).

* Using Windows binary

[begriffs]

Yeah you're right, OPTIONS no longer provides API documentation about a route. However it is still a supported verb and responds with CORS and Allow headers.

The API information has been moved to the OpenAPI description available at the root path. Foreign key information is not yet included in the OpenAPI output though. That will have to be a later feature.

Are you working on a front-end admin panel or other generic code that needs to learn about the foreign keys between tables?

[RB14]

Yes, I was working on some sort of admin panel, but eventually I decided to just hard-code the relationships, as it is more appropriate for my use case.

I still think it would be better to be able to consult each node for its relationships rather than having to fetch the entire schema from the root path (which can get pretty huge).

[majorcode]

As someone who is not fully familiar with the OpenAPI spec, may I ask: Is the absence of information about the foreign key or embedded resources due to limitations in the OpenAPI specification? Or is it something you have not had an opportunity to implement?

In addition to UIs that auto-discover which resources can be embedded, I think this information is really useful to someone learning to use PostgREST. First, appearance of foreign key / embedded resource data during development confirms that the relationship is correct, and that PostgREST picked up on the change (after a HUP). Second, it's obvious that showing the structure makes it easier to explore and use PostgREST.

@begriffs In my opinion, I really liked your use of OPTIONS to discover the API. However, I can understand the switch to OpenAPI. I'd love to help reinstate this functionality. Alas, my Haskell foo is too weak. Would you be open to a Haskell noob attempting to assist?

[begriffs]

To be honest I don't know the best way to structure this information in OpenAPI. I have a feeling there's a way to do it though. Are you interested in researching this and providing your recommendation?

I agree that it's useful functionality, for instance for building admin panels like in #717.

(I'm also happy to help you with any Haskell questions as you begin learning. A good starting place would be to try building the project from source, explained here: https://postgrest.com/en/v0.4/install.html#build-from-source)

[timwis]

If you're just trying to get a list of the tables available, and perhaps their field names/types, OpenAPI adds a lot of weight :/ for instance, here's a fresh postgres DB with a single table added:

// 20170410070412
// http://localhost:3000/

{
  "swagger": "2.0",
  "info": {
    "version": "0.4.0.0",
    "title": "PostgREST API",
    "description": "This is a dynamic API generated by PostgREST"
  },
  "host": "0.0.0.0:3000",
  "basePath": "/",
  "schemes": [
    "http"
  ],
  "paths": {
    "/": {
      "get": {
        "tags": [
          "/"
        ],
        "produces": [
          "application/openapi+json"
        ],
        "responses": {
          "200": {
            "description": "OK"
          }
        }
      }
    },
    "/contacts": {
      "get": {
        "tags": [
          "contacts"
        ],
        "produces": [
          "application/json",
          "application/vnd.pgrst.object+json",
          "text/csv"
        ],
        "parameters": [
          {
            "required": false,
            "in": "header",
            "name": "Range",
            "type": "string",
            "description": "Limiting and Pagination"
          },
          {
            "default": "items",
            "required": false,
            "in": "header",
            "name": "Range-Unit",
            "type": "string",
            "description": "Limiting and Pagination"
          },
          {
            "required": false,
            "in": "query",
            "name": "offset",
            "type": "string",
            "description": "Limiting and Pagination"
          },
          {
            "required": false,
            "in": "query",
            "name": "limit",
            "type": "string",
            "description": "Limiting and Pagination"
          },
          {
            "required": false,
            "in": "query",
            "name": "select",
            "type": "string",
            "description": "Filtering Columns"
          },
          {
            "required": false,
            "in": "query",
            "name": "order",
            "type": "string",
            "enum": [
              "id.asc.nullsfirst",
              "id.asc.nulllast",
              "id.asc",
              "id.desc.nullsfirst",
              "id.desc.nulllast",
              "id.desc",
              "id.nullsfirst",
              "id.nulllast",
              "id",
              "first_name.asc.nullsfirst",
              "first_name.asc.nulllast",
              "first_name.asc",
              "first_name.desc.nullsfirst",
              "first_name.desc.nulllast",
              "first_name.desc",
              "first_name.nullsfirst",
              "first_name.nulllast",
              "first_name",
              "last_name.asc.nullsfirst",
              "last_name.asc.nulllast",
              "last_name.asc",
              "last_name.desc.nullsfirst",
              "last_name.desc.nulllast",
              "last_name.desc",
              "last_name.nullsfirst",
              "last_name.nulllast",
              "last_name",
              "email.asc.nullsfirst",
              "email.asc.nulllast",
              "email.asc",
              "email.desc.nullsfirst",
              "email.desc.nulllast",
              "email.desc",
              "email.nullsfirst",
              "email.nulllast",
              "email",
              "gender.asc.nullsfirst",
              "gender.asc.nulllast",
              "gender.asc",
              "gender.desc.nullsfirst",
              "gender.desc.nulllast",
              "gender.desc",
              "gender.nullsfirst",
              "gender.nulllast",
              "gender",
              "ip_address.asc.nullsfirst",
              "ip_address.asc.nulllast",
              "ip_address.asc",
              "ip_address.desc.nullsfirst",
              "ip_address.desc.nulllast",
              "ip_address.desc",
              "ip_address.nullsfirst",
              "ip_address.nulllast",
              "ip_address"
            ],
            "description": "Ordering"
          },
          {
            "required": false,
            "in": "header",
            "name": "Prefer",
            "type": "string",
            "enum": [
              "count=none"
            ],
            "description": "Preference"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "integer",
            "required": false,
            "in": "query",
            "name": "id",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "first_name",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "last_name",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "email",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "gender",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "ip_address",
            "type": "string"
          }
        ],
        "responses": {
          "200": {
            "description": "OK"
          },
          "206": {
            "description": "Partial Content"
          }
        }
      },
      "post": {
        "tags": [
          "contacts"
        ],
        "consumes": [
          "application/json",
          "application/vnd.pgrst.object+json",
          "text/csv"
        ],
        "produces": [
          "application/json",
          "application/vnd.pgrst.object+json",
          "text/csv"
        ],
        "parameters": [
          {
            "required": false,
            "in": "header",
            "name": "Prefer",
            "type": "string",
            "enum": [
              "return=representation",
              "return=minimal",
              "return=none"
            ],
            "description": "Preference"
          },
          {
            "required": false,
            "schema": {
              "$ref": "#/definitions/contacts"
            },
            "in": "body",
            "name": "body",
            "description": "contacts"
          }
        ],
        "responses": {
          "200": {
            "description": "OK"
          },
          "201": {
            "description": "Created"
          }
        }
      },
      "delete": {
        "tags": [
          "contacts"
        ],
        "produces": [
          "application/json",
          "application/vnd.pgrst.object+json",
          "text/csv"
        ],
        "parameters": [
          {
            "required": false,
            "in": "header",
            "name": "Prefer",
            "type": "string",
            "enum": [
              "return=representation",
              "return=minimal",
              "return=none"
            ],
            "description": "Preference"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "integer",
            "required": false,
            "in": "query",
            "name": "id",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "first_name",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "last_name",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "email",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "gender",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "ip_address",
            "type": "string"
          }
        ],
        "responses": {
          "200": {
            "description": "OK"
          }
        }
      },
      "patch": {
        "tags": [
          "contacts"
        ],
        "consumes": [
          "application/json",
          "application/vnd.pgrst.object+json",
          "text/csv"
        ],
        "produces": [
          "application/json",
          "application/vnd.pgrst.object+json",
          "text/csv"
        ],
        "parameters": [
          {
            "required": false,
            "in": "header",
            "name": "Prefer",
            "type": "string",
            "enum": [
              "return=representation",
              "return=minimal",
              "return=none"
            ],
            "description": "Preference"
          },
          {
            "required": false,
            "schema": {
              "$ref": "#/definitions/contacts"
            },
            "in": "body",
            "name": "body",
            "description": "contacts"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "integer",
            "required": false,
            "in": "query",
            "name": "id",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "first_name",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "last_name",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "email",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "gender",
            "type": "string"
          },
          {
            "pattern": "^not[.]eq[.]|^not[.]gte[.]|^not[.]gt[.]|^not[.]lte[.]|^not[.]lt[.]|^not[.]neq[.]|^not[.]like[.]|^not[.]ilike[.]|^not[.]in[.]|^not[.]notin[.]|^not[.]isnot[.]|^not[.]is[.]|^not[.]@@[.]|^not[.]@>[.]|^not[.]<@[.]|^eq[.]|^gte[.]|^gt[.]|^lte[.]|^lt[.]|^neq[.]|^like[.]|^ilike[.]|^in[.]|^notin[.]|^isnot[.]|^is[.]|^@@[.]|^@>[.]|^<@[.]",
            "format": "character varying",
            "required": false,
            "in": "query",
            "name": "ip_address",
            "type": "string"
          }
        ],
        "responses": {
          "200": {
            "description": "OK"
          },
          "204": {
            "description": "No Content"
          }
        }
      }
    }
  },
  "definitions": {
    "contacts": {
      "properties": {
        "id": {
          "format": "integer",
          "type": "integer"
        },
        "first_name": {
          "format": "character varying",
          "type": "string"
        },
        "last_name": {
          "format": "character varying",
          "type": "string"
        },
        "email": {
          "format": "character varying",
          "type": "string"
        },
        "gender": {
          "format": "character varying",
          "type": "string"
        },
        "ip_address": {
          "format": "character varying",
          "type": "string"
        }
      },
      "type": "object"
    }
  }
}

[begriffs]

We could serve a different representation at the root depending on the Accept header. Is there a more concise standard you would prefer?

[majorcode]

During my review of Open API with an eye on adding nested resource details (re. #717 ) I came to a conclusion similar to @timwis. I believe the Open API service description rendered on the root route (I'll resort to calling it the "description" for brevity) is verbose. It's also incomplete. However, a complete description is inevitably untenable.

I'm not trying to disparage anyone or start a flame war. I think @begriffs invented something really cool in PostgREST. It's sparked my latent interest in Haskell. And I hope I can rise to the challenge of contributing to this project. Let me explain my opinion and hopefully offer some solutions.

Some things seem overly verbose

Header and query parameters (in #paths/NOUN/VERB) for Range, Range-Unit, offset, limit, select, order, and Prefer are repeated often. This in itself isn't a bad thing. But the repeated information tends to hide details in a wall of boilerplate. The order parameter is particularly problematic.

Problems with the order parameter

The order query parameter attempts to enumerate all of the valid result set orderings. It's actually pretty slick! But it's large, repetitive, and incomplete. It's large and repetitive because it lists the cartesian product of each entity column with every possible ordering for that column. It's incomplete because PostgREST supports ordering results based on columns in nested entities. But, only top-level columns are included in the enumeration. Enumerating all of the possible orderings to include nested entities could be enormous. In fact, if there's a cycle in your ER schema, it would create an infinite recursion.

The pattern parameter attribute is repetitive

The pattern parameter attribute, ostensibly used to enumerate and validate query filters, is repeated in every table column's query parameter description. While this information could be used by automation tools (admins and the like) to expose a a better UI and to validate parameters before making a request, I fear that most such tools would not go to the trouble of performing either task. Humans reading the API description will likely find pattern to be overly noisy. And, humans will learn PostgREST query operators from the documentation.

The select parameter is hard to describe

The presence of pattern on entity columns and enum on the order parameter--while select lacks any validation attribute--could be interpreted by readers as a missing detail, an oversight. However, describing the format of the select parameter is probably impossible when the child{...} syntax of select is taken into account. Therefore, If we stipulate that the format of the select parameter is unique to PostgREST, maybe order can be simplified and pattern can be removed?

Missing parameter descriptions for nested entities

Repetitive or verbose parameters would be justifiable if there weren't so many missing due to the lack of parameters for nested entity columns. To be considered complete, the API description would have to include parameter details for all nested entities. However, doing so would recursively bloat the description. And, in cases of a cyclic ER schema, would create an infinite loop. If we stipulate that PostgREST has a unique (nonstandard) syntax for the parameters of nested entities, maybe it would suffice to simply document this fact and make nested entities easily discoverable?

Nested entitles are not easily discoverable

There are no query parameter details or entity definitions describing nested entities or relationships. Including them in the #path/... descriptions would cause explosive growth of the API description and infinite recursion for cyclic schemas. Note that having a cycle in your schema is not restricted to exotic table layouts. For example: user->posts->author(::user)->posts and posts->comments->user->posts both create a cycle. We could make nested entities discoverable by adding $ref properties to entity definitions (see below).

Proposed solutions

If the enum attribute on order parameters were simply removed, then it wouldn't misrepresent how many result orderings are possible. And there would be a little saving on size and visual noise.

If we accept that the pattern listed on every #paths/NOUN/VERB/... column parameter has minimal utility, removing it would save on size and reduce visual noise.

We could stipulate that the parameters for filtering queries on nested entity columns is unique to how PostgREST works (just like the format of select). Thus, not every possible query parameter is documented in the #paths/... portion of the API description. Users can be directed to refer to #definitions/... for details about nested entities.

It follows that the #paths/NOUN/VERB/parameters/... details are incomplete. Knowing what we know about query parameters for nested entitles, are the parameters under #paths/NOUN/VERB even necessary? Maybe it's consistent to exclude them altogether?

We can make nested entities discoverable by adding properties to entity #definitions under #definitions/NOUN/properties/NESTED_NOUN that employ $ref entries to refer to other entity definitions. This gives us a way of linking between entities and allows for cycles. For example:

• A nested object on #definitions/posts/properties/:
  • Includes a key like: "author": {"$ref": "#definitions/users"}
  • This tells the reader that this entity has a nested object, described elsewhere
  • Maybe a property can be added to indicate the relationship derived from FK constraints?
• A nested array on #definitions/posts/properties/:
  • Includes a key like "comments": {"type": "array", "items": {"$ref": "#definitions/comments"}}
  • This tells the reader that this entity has an array of comments, described elsewhere
  • Maybe a property can be added to indicate the relationship derived from FK constraints?

From my reading of the Open API specification, these $ref entries can be used to describe the format of API responses in #paths/NOUN/VERB/responses. But the current PostgREST implementation doesn't provide this information for the main, top-level entity. I think this omission is reasonable because it would probably not be used by API consumers anyway. I mention it here as one case where Open API has options that PostgREST doesn't use. And I believe this is a good thing.

Finally...

With all of this said, I don't think Open API provides serviceable API description. Look at how select works. There's no way to completely describe a PostgREST API with Open API in a way that doesn't require you to read the PostgREST manual.

The Open API specification is long and involved. As a result I'm not sure I was able to properly interpret all of the uses for $ref entries. I get the impression that there's a way of employing schema entries to refer to definitions in more places. It's possible that a more vigorous investigation would bear this out. But, after all of that re-alignment, I don't think we'd end up with a more readable API description. It should be simpler than this.

The beauty of using OPTION requests to describe entities was that you could investigate the API one resource at a time, and follow references to learn about nested entities. As it stands, the Open API description is so large that it's unmanageable unless you use a tool that supports collapsing portions of the JSON. The JSON description of the Pagila database is about 280k. This level of verbosity triggers memories of the days when we thought we could use XML to describe APIs for both humans and machines. That didn't turn out too well.

I think the recommendations I made above can help make the Open API description easier to read by humans and machines. That said, I would not be opposed to returning to, or reinstating alongside Open API, the original OPTION method of introspection.

[timwis]

Woah. Some really interesting points there, but a slightly different road than a more concise introspection method. As for an alternate, more concise standard, the only two I know that are relevant are:

• JSON Table Schema
• JSON Schema

I think the table schema probably makes more sense and is the most concise (it also has field order, though that's not relevant to postgres), but the vanilla schema may be able to handle more of the gotchas.

Though, I don't recall what OPTIONS used to provide -- maybe this is simple enough data structure that you don't need a standard for it? But if it's close enough to something like JSON Table Schema, why not.

[majorcode]

I don't know first hand. But I think the decision to use Open API was integration with Swagger, since it seemed popular. Do either JSON Table Schema or JSON Schema have this?

Maybe there were discussions about API description being a misuse of the OPTIONS verb. And that prompted removing support. I don't know. My opinion is that API description is a valid use for the OPTION verb.

[timwis]

There's a ton of tools out there that use the JSON Schema spec, but probably not an API doc generator. JSON Table Schema is newer, but some tools do exist.

[begriffs]

I agree with @majorcode's excellent detailed assessment that much information in our openapi output is sadly both verbose and incomplete. One thing I liked about the spec was that I could connect interactive api exploring tools to postgrest and it would look pretty. I thought there was a wow factor in seeing a collapsable list of endpoints and verbs with buttons to make the web requests. But the real day-to-day usability outside of a superficial demo is just not that good with the openapi output we have.

The most useful output on the root route would probably be a list of the endpoints and the possible embedding combinations. Then each endpoint like /foo could have a description available somewhere like /foo/meta. For hypermedia goodness the original /foo route could include a describedby http header link pointing at the description url. The OPTIONS verb itself actually has an existing meaning in HTTP and isn't the right place for that metadata.

I think simplicity usually wins in the long run, even if it feels less exciting than adopting a fancy standard like openapi. I feel kind of embarrassed taking this position since for a long time I was pushing for us to use the standard and a number of people contributed significant work to make it happen.

Let the commentary begin.

[jackfirth]

I'm in favor of primarily using a PostgREST-specific schema format and secondarily using content negotiation to support other formats like OpenAPI or JSON Table Schema. The describedby relationship from a table resource to a schema resource is format-independent, so clients can operate in terms of whatever formats they understand.

Throwing out my two cents, what I'd like from the reflection capabilities of PostgREST is a way to tell that some /foos resource is unambiguously a PostgREST resource without knowing that the service in question is a PostgREST API. The easiest way to do this would be to make PostgREST return table responses with the content type application/vnd.pgrst.rows+json instead of plain application/json. Alternatively, application/json with a Link header with the relationship describedby pointing to a schema description resource with a content type of application/vnd.pgrst.table-meta+json would be workable, as it identifies the response as a PostgREST response. Another option is the profile content type parameter, but that has it's own separate set of issues.

Creating content types for PostgREST's concepts makes it possible to use content negotiation when asking for schema information. Additionally, it makes it possible to write services that are compatible with multiple systems including PostgREST, since different services will have different content types instead of everyone responding with application/json and leaving the rest up to the client to guess.

[jackfirth]

Oh and one other thing: for the root resource, you might consider something like a JSON Home document.

[ruslantalpa]

... TLDR, you can do it on your own in SQL, you can use GraphQL schema format

I would say this is outside the scope of PostgREST

• one can write a (complicated) query that would return the list of endpoints, their columns, their description and the relation between them even in pure SQL (i've done it)
• as a format, able to describe relations, the best would be to use the schema description json that graphql uses. Once you have that, you can leverage a lot of existing client side libs that leverage that format (i've seen autocomplete in editors based on that schema info).
• using that json format you can use existing tools to create user readable formats like

  
  scalar Date

scalar Url

type User { id: ID name: String photo: Url }

type Post { author: User body: String id: ID postedAt: Date title: String }

[begriffs]

We could remove the self-documentation from postgrest and suggest other standalone database api documenting servers people could use. However our internal code will still be gathering the db structure to handle the requests (embedding etc) so it's kind of a waste to not serve even a simple api self-description endpoint since we have the info and all.

True that many people will want the whole graphql thing which seems out of scope, but I was thinking postgrest could serve more like a list of the available tables and procs, and per table/proc a list of columns, args, keys, and foreign keys.

[ruslantalpa]

just the simple list is easy to implement with a view https://gist.github.com/ruslantalpa/b2f10eb1b5f6dd0fc1c154e071a1c91b

also some ideas here https://github.com/begriffs/postgrest/issues/13#issuecomment-319890673

[steve-chavez]

Reopening, issue contains good points against OpenAPI and with 3.0 it seems to get even more verbose/complex, this doesn't look good for maintenance in the long run.

@ruslantalpa Having an user run a separate sql query is redundant work(also could get out of sync with what pgrst exposes) since we already have a structure cached, would be better to expose this structure in a standard way.

Even a simple non-standard json structure(like tables with insertable/updatable/deletable permissions) would be useful and easier to work with than OpenAPI format, so this could be another option and let the user transform this structure to OpenAPI/JSON schema/GraphQL in the proxy layer.

[danielstaleiny]

I will just throw this idea here, What if we take out open api spec from the codebase and utilize psql to output information about tables and generate open api spec from it ?

This is just an idea, I haven't seen anything like that but I could try to make it.

I think it would work like this. I would call describe table with psql and output it into temp file.

From that point you have all information about the table and you can generate the open api spec.

[steve-chavez]

@danielstaleiny Right now we run several sql queries(in this module: DbStructure) that return more information than \d. We query all foreign keys source/target tables, all views column sources, etc.

That being said, the idea of generating the spec in SQL could be a good one. We'd have more flexibility‐it'd be easier to support multiple specs(OpenAPI v2/v3, RAML, etc) and users could customize the format to their needs.

The design needs more thought, but we could offer a config option that allows a VIEW(or better yet a MATERIALIZED VIEW) that would be queried when you request the root / route. This VIEW would have to be in sync with PostgREST schema cache(should be based on the queries in DbStructure), otherwise PostgREST would see different tables than the ones that are shown in the output.

[steve-chavez]

A rough design idea, PostgREST would provide:

• A config option for the schema cache VIEW: schema-cache = postgrest.schema_cache

  • This would need a refactor of all the queries in DbStructure, consolidating them in a single VIEW and changing the codebase to inspect this VIEW.
  • Would be optional, if the user doesn't want to customize the VIEW we just run the queries as usual.
  • We'd need a way to create this VIEW at startup or perhaps just print the CREATE VIEW statement to stdout so the user can create the view with its own highly privileged db user.
  • Ideally, the user shouldn't mess with this VIEW so we can continue enforcing the single SCHEMA exposure.

• A config option for the OpenAPI VIEW: openapi-view = postgrest.openapi

  • This postgrest.openapi VIEW would have to be based on postgrest.schema_cache.
  • Would generate the OpenAPI spec and the user can manipulate it at will.
  • PostgREST could provide a default CREATE VIEW statement.

• More config options could be provided for different specs, graphql-view, raml-view, etc.

[ruslantalpa]

This is https://github.com/PostgREST/postgrest/issues/260 all over again. This is not the job of postgrest (responding with schemas describing the database). If the user wants a openapi spec?... have an endpoint served by php/node/rpc/whatever and change it to your hearts content. Just because postgrest "knows" something about the db, does not mean it has to tell the rest of the world about it, it's internal data. The whole openapi code should be removed altogether from 6.x ( and if anyone is inclined to put in the work, have a documentation page with a plpgsql function reaturing a openapi spec json which ppl can modify in their specific deployment and addapt, it's that simple )

[Qu4tro]

I agree that we should deprecate OpenAPI on 6.X, for several reasons, all already discussed over many issues, but it does leave us with an empty root, which I think we can take advantage of it.

Taking @steve-chavez idea, we could have the same config option config-cache (different name though), but it's only usage would be to point to a view / mview / plpgsql, that would be served on /. DbStructure wouldn't change. It should be documented that this is usually a documentation endpoint as per good API practices and point to resources like the plpgsql function, you've mention @ruslantalpa . I understand this is also redundant if a reverse-proxy is used, but I think the ease of use here is important for what I think would be a common use-case. I don't think it would involve complicated changes either.

This brings another point that I've been thinking for a while. PostgREST function is and should be simple. Despite that, the world doesn't like simple and there are several useful feature requests that were closed, because indeed they are external to its current function.
My suggestion: Create a separate repo postgrest-contrib, that would have different building blocks for a quicker project bootstrap. I'm aware of projects like postgrest-starter-kit (and user, thanks guys :D ), but again I think this should be building blocks, hopefully not very opinionated, so I think we should try to stick with sql, plpgsql and maybe reverse-proxy configs aka nginx.
This would also create a home for community contributions that are outside of the scope of the server itself.

[danielstaleiny]

I agree that PostgREST should do one thing and do it well. I am all for leaving creating of specs outside of the scope of the project and I like idea from Qua4tro to make a community/additional-components repos.

I see couple of options to create the specs from.

Option 1. Rely only on psql and get all the information. (still not sure if possible, convenient) Option 2. Get the information from PostgREST. I would imagine that we can just list all the information needed for making any kind of spec out of it in convenient way (JSON?). I don't know the internals of this project but I would like to help where I can. Option 3. CLI command to get all the information about database?

[steve-chavez]

It wasn't my intention to cause a breaking change or deprecate the OpenAPI spec, but to provide an easier way to improve/customize it and let the community help us in doing so(since it'll be SQL).

Though OpenAPI has many issues, it's still of high value and I've seen PostgREST OpenAPI support cited as one of its main strengths.

This is not the job of postgrest (responding with schemas describing the database)

@ruslantalpa I think providing resources metadata is well in the scope of REST.

If the user wants a openapi spec?... have an endpoint served by php/node/rpc/whatever and change it to your hearts content

That's exactly the reason why I propose to do it in SQL, to avoid the complexity of all the extra tooling the user has to choose from or implement from scratch and keep the db as the single source of truth.

Just because postgrest "knows" something about the db, does not mean it has to tell the rest of the world about it, it's internal data

The problem is that if we don't expose what pgrst knows the custom OpenAPI spec could be invalid, the output could show tables that pgrst doesn't know. Ideally we'd have a way to validate if the spec comes from the schema cache(not sure if feasible for now).

Also regarding #260, I don't see how this issue is related to it, but in any case the whole auth feature set started as being harcoded and then pgrst offered a more extendable way to do it in SQL, which is a similar idea to what I proposed.

My suggestion: Create a separate repo postgrest-contrib, that would have different building blocks for a quicker project bootstrap.

@Qu4tro Could be a good idea. We could have the OpenAPI SQL there and test it with postgres-json-schema.

[steve-chavez]

I also worry about exposing our schema cache for customization, I'd prefer not risk losing the guarantees that the PostgREST sandbox offers.

So, how about if we leave the schema cache alone for now and just offer a openapi-spec config option that would accept a SQL function/view. If specified, this would override our current openapi output. Once we have a SQL function/view that produces an output that is as good as the one we offer by default(we'd have to test this), we can remove openapi from our haskell codebase and recommend instead creating that function/view(or somehow create it ourselves and avoid a breaking change).

We can recommend creating the openapi-spec based on this https://gist.github.com/steve-chavez/eae6a67ec81b195c133bcb9ff0c917fb. If the user includes other database objects in that spec, then that's no problem for PostgREST since it only knows its schema cache. The spec could show tables in another schema but PostgREST wouldn't be able to query them.

[steve-chavez]

Users have also been asking about a simpler json spec that can be used to build an admin panel, as shown in #717.

So, similarly to openapi-spec we could also offer a json-spec as an escape hatch for users that needs this. This could be requested from root / with an Accept: application/vnd.pgrst.schema+json. I'd also like to do this properly at the HTTP level(using describedby, Link as mentioned in the excellent suggestions above) but for now having this could be a good start.

[steve-chavez]

How about this, we offer a way to override the root response with a custom function that will use our usual RPC interface and take advantage of GUC headers. The function can be like this:

create or replace function root() returns jsonb as $_$
begin
-- openapi v2 spec
if current_setting('request.header.accept', true) = 'application/openapi+json' then
  set local "response.headers" = '[{"Content-Type": "application/openapiv2+json"}]';
  return $$
    {
      "swagger": "2.0",
      "info":{"version":"5.2.0","title":"PostgREST API","description":"This is a dynamic API generated by PostgREST"}
    }
  $$::jsonb;
else
-- simpler json spec
  set local "response.headers" = '[{"Content-Type": "application/json"}]';
  return $$
    [
      {
        "table":"items"
      },
      {
        "table":"subitems"
      }
    ]
  $$::jsonb;
-- ... other specs
end if;
end
$_$ language plpgsql;

Name of the config option could be like root_spec = schema.root.

The function can even return a composite type and filters could be used, I think this would be the most flexible and future-proof option.

[Qu4tro]

I like it. Solves keeping swagger intact and allowing for new queries that allow for PostgreREST admin dashboards.

How would this default function be distributed?

[steve-chavez]

@Qu4tro Since this wouldn't break anything I was thinking to have a recommended default function in the docs(function could be in a postgrest-contrib repo as you suggested). We would need to refactor/update this base spec and if possible offer a default openapi function.

I think we can slowly figure that out, for now having the possibility to use the root function would help a lot in solving openapi issues.

[steve-chavez]

A while ago I've noticed we need to refactor the codebase and replace some parts in Haskell with SQL so we can offer a true mirror of our schema cache in the "base spec". This will require some work but it's doable.

For now, for anyone that would like to help us constructing the OpenAPI function, the root-spec config is already available(added in #1317) and you can use the base spec, later we can update it with up-to-date schema cache queries.

Also you can use https://github.com/gavinwahl/postgres-json-schema for testing.

[bwbroersma]

@steve-chavez the base spec link is 404'ing, I would really like to help/work on this!

[steve-chavez]

@bwbroersma I've updated the link to this fork https://gist.github.com/steve-chavez/eae6a67ec81b195c133bcb9ff0c917fb