Support structured data in PostgreSQL COMMENTs

[salim-b]

Problem

PostgREST allows to specify additional metadata to document the offered RESTful API via PostgreSQL COMMENTs. Other tools also leverage PostgreSQL COMMENTs to specify additional metadata or configuration, e.g.

• pg_graphql via @graphql(<JSON>) "comment directives"
• PostGraphile via @tag object "smart tags"
• schemats/pg-to-ts via @type {type} annotations which conform to JSDoc tags

If one decides to use one of these tools in conjunction with PostgREST, conflicts arise, i.e. PostgREST won't ignore the above tags but include them in the generated OpenAPI description. The underlying problem could be summarized as the lack of a common standard to include structured data in PostgreSQL COMMENTs¹.

Solution

In another discussion @wolfgangwalther suggested to

use a yaml front matter block to add metadata, while keeping the current openapi comments support backwards compatible

YAML headers seem powerful (e.g. multiple tools could read the same keys) and pragmatic at the same time (not too hard to parse and can be implemented in a backwards-compatible way), so other tools would hopefully add support for it, too. The author of pg_graphql already showed approval and would implement it. YAML headers would also allow to "hide" arbitrary data from PostgREST like the above @-tags by simply defining them inside the YAML header (under a key that has no meaning for PostgREST). In the end, the possible use cases go far beyond PostgREST – but PostgREST could lead the way!

Previous discussion

• Comment directive conflicts with postgREST supabase/pg_graphql#331
• Support for PGroonga operators #2028 (comment)

Further notes

• PostgreSQL's COMMENT keyword is not part of the SQL standard, but various other DBMS' offer the same (e.g. Oracle Database) or very similar (e.g MariaDB/MySQL) functionality. SQLite unfortunately has no such notion.

Footnotes

1. A web search did not reveal any kind of existing initiative to establish such a standard. Please correct me, if I missed something! ↩

[wolfgangwalther]

A more principled approach to this problem would be to work towards a feature in PostgreSQL to support "namespaced comments". Those could work very similar to SECURITY LABEL FOR <provider> ON ..., which is very similar in syntax (and I think the underlying architecture) to COMMENT ON ....

If we had something like COMMENT FOR <tool> ON .. we'd be much better off, I think.