More information in `/schema` about column types.

[divyenduz]

To better support enums in the frontend, we need some more information in the /schema API, here is an example, this column (Field.usage) is an enum but we have no way of telling it from, say, a custom domain type or custom types from extensions. Eventually, we want to make all types editable from the UI. So, we need the /schema API to return that this is an enum and its possible values for enum's case. This will allow us to build features like dropdown select for enum and/or client side validation for enum value.

Current output:

{
  "schema": {
    "name": "",
    "display_name": "",
    "tables": {
      "Field": {
        "oid": "6526469",
        "name": "Field",
        "comment": "",
        "columns": {
          "usage": {
            "name": "usage",
            "type": "bb_mmco0u98b56618sjl6glp6csh8_d5mrcj.\"FieldUsage\"",
            "default": null,
            "nullable": false,
            "unique": false,
            "comment": ""
          }
        },
        "indexes": {
          "Field_pkey": {
            "name": "Field_pkey",
            "unique": true,
            "columns": ["id"]
          }
        },
        "primaryKey": ["id"],
        "foreignKeys": {},
        "checkConstraints": {},
        "uniqueConstraints": {},
        "xataCompatible": false
      }
    }
  }
}

Potential future output

{
  "schema": {
    "name": "",
    "display_name": "",
    "tables": {
      "Field": {
        "oid": "6526469",
        "name": "Field",
        "comment": "",
        "columns": {
          "usage": {
            "name": "usage",
            "type": "bb_mmco0u98b56618sjl6glp6csh8_d5mrcj.\"FieldUsage\"",
            "default": null,
            "nullable": false,
            "unique": false,
            "comment": "",
            "postgresType": "enum",
            "possibleValues": ["TOP_HALF", "BOTTOM_HALF", "FULL_FIELD"]
          }
        },
        "indexes": {
          "Field_pkey": {
            "name": "Field_pkey",
            "unique": true,
            "columns": ["id"]
          }
        },
        "primaryKey": ["id"],
        "foreignKeys": {},
        "checkConstraints": {},
        "uniqueConstraints": {},
        "xataCompatible": false
      }
    }
  }
}

Other considerations (very hand-wavey)

1. We can add "checkConstraint" too, which has the PostgreSQL check, e.g. {"checkConstraint": {"length(xata_id) < 256"}}. The frontend can then potentially parse this into a TypeScript validation rule.
2. Knowing that a type is array, composite, range and their dimensions. Currently, we interpret this in the frontend with some lazy parsing, pgroll should be the source of truth for this information.
3. Knowing that a type is domain type
4. Knowing that a type is a custom type
5. With all these considerations, the output of postgresType can be 'enum' | 'array' | 'range' | 'composite' | 'domain' | 'custom-type'

For the purpose of this ticket, enum is enough.

Related https://github.com/xataio/pgroll/issues/376

[ryanslade]

@divyenduz The enum specific issue has been fixed by #443