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 #376

[ryanslade]

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

[agedemenli]

for the first item in the list, which is:

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.

Looks like we already have a checkConstraints part in the output, which gives definitions of all check constraints defined on that table. It doesn't make much sense to me to add it again for the columns, as these constraints are defined on table level. Also, a constraint might depend on multiple columns. In that case should we add it into each column definition in the json separately?

I thought maybe this was requested before we had checkConstraints at the table level. Looking into repo history, that's not the case. Looks like checkConstraints was added like a year ago, by @andrew-farries .

One other thing: In case we still want to add constraints at column level, should we remove it from table level?

[andrew-farries]

I think keeping checkConstraints at the table level is the right thing to do for the reasons you mention.

Looking at the 'other considerations' section, it seems as though we don't currently report the array dimensions as part of the schema:

{
  "name": "01_create_table",
  "operations": [
    {
      "create_table": {
        "name": "items",
        "columns": [
          {
            "name": "id",
            "type": "serial",
            "pk": true
          },
          {
            "name": "names",
            "type": "text[3]",
          }
        ]
      }
    }
  ]
}

resulting schema

+-----------------------------------------------------------------------------------------------------+
| jsonb_pretty                                                                                        |
|-----------------------------------------------------------------------------------------------------|
| {                                                                                                   |
|     "name": "public",                                                                               |
|     "tables": {                                                                                     |
|         "items": {                                                                                  |
|             "oid": "16415",                                                                         |
|             "name": "items",                                                                        |
|             "columns": {                                                                            |
|                 "id": {                                                                             |
|                     "name": "id",                                                                   |
|                     "type": "integer",                                                              |
|                     "unique": true,                                                                 |
|                     "comment": null,                                                                |
|                     "default": "nextval('public.items_id_seq'::regclass)",                          |
|                     "nullable": false,                                                              |
|                     "enumvalues": null                                                              |
|                 },                                                                                  |
|                 "names": {                                                                          |
|                     "name": "names",                                                                |
|                     "type": "text[]",                                                               |
|                     "unique": false,                                                                |
|                     "comment": null,                                                                |
|                     "default": null,                                                                |
|                     "nullable": false,                                                              |
|                     "enumvalues": null                                                              |
|                 }                                                                                   |
|             },                                                                                      |
|             "comment": null,                                                                        |
|             "indexes": {                                                                            |
|                 "items_pkey": {                                                                     |
|                     "name": "items_pkey",                                                           |
|                     "method": "btree",                                                              |
|                     "unique": true,                                                                 |
|                     "columns": [                                                                    |
|                         "id"                                                                        |
|                     ],                                                                              |
|                     "predicate": null,                                                              |
|                     "definition": "CREATE UNIQUE INDEX items_pkey ON public.items USING btree (id)" |
|                 }                                                                                   |
|             },                                                                                      |
|             "primaryKey": [                                                                         |
|                 "id"                                                                                |
|             ],                                                                                      |
|             "foreignKeys": {                                                                        |
|             },                                                                                      |
|             "checkConstraints": {                                                                   |
|             },                                                                                      |
|             "uniqueConstraints": {                                                                  |
|             }                                                                                       |
|         }                                                                                           |
|     }                                                                                               |
| }                                                                                                   |
+-----------------------------------------------------------------------------------------------------+

Postgres doesn't enforce array sizes but I think we should not lose that information, so perhaps we could do that next.

[agedemenli]

I have pushed changes to add check constraints on column level: #584
without removing if from table level.
Let's keep it there in case we need in the future. We can discuss with @divyenduz when he's back.

[agedemenli]

Adding other requirements in #592 (except for the array size)

For array sizes, I don't know what to put into the schema output. Because array size is flexible in Postgres. User doesn't have to provide a size when creating an array column. Even if they provide a size during creation, Postgres doesn't enforce it as a requirement.

No idea what the array sizes should be in the read_schema output, for columns a, b and c in the below example

create table table1 (a int[], b int[2], c int[][]);
insert into table1 (a,b,c) values ('{0,1}','{0}','{1}');
insert into table1 (a,b,c) values ('{}','{0,1,2,3,4,5}','{{1},{2}}');
select * from table1 ;
   a   │       b       │     c
───────┼───────────────┼───────────
 {0,1} │ {0}           │ {1}
 {}    │ {0,1,2,3,4,5} │ {{1},{2}}
(2 rows)

[andrew-farries]

It's a good point. As you say, array size is flexible in Postgres and not enforced.

I don't think the system catalogs allow you to discover the size that the array column b in your example was created with.

For dimensions, this is discoverable via pg_attribute. attndims, but not enforced.

The psql output for the table in your example is:

                                           Table "public.table1"
 Column |   Type    | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+-----------+-----------+----------+---------+----------+-------------+--------------+-------------
 a      | integer[] |           |          |         | extended |             |              |
 b      | integer[] |           |          |         | extended |             |              |
 c      | integer[] |           |          |         | extended |             |              |

ie, all columns are shown as integer[] without size or dimensions.

I think it's best that we don't record anything for array columns beyond the type in the schema representation, as it's either not possible or not enforced.