supabase · soedirgo · Jan 16, 2023 · Dec 14, 2022 · Dec 14, 2022 · Jan 12, 2023
@@ -1,33 +1,47 @@
-SELECT
-  t.oid :: int8 AS id,
-  t.typname AS name,
-  n.nspname AS schema,
-  format_type (t.oid, NULL) AS format,
-  array_to_json(
-    array(
-      SELECT
-        e.enumlabel
-      FROM
-        pg_enum e
-      WHERE
-        e.enumtypid = t.oid
-      ORDER BY
-        e.oid
-    )
-  ) AS enums,
-  obj_description (t.oid, 'pg_type') AS comment
-FROM
+select
+  t.oid::int8 as id,
+  t.typname as name,
+  n.nspname as schema,
+  format_type (t.oid, null) as format,
+  coalesce(t_enums.enums, '[]') as enums,
+  coalesce(t_attributes.attributes, '[]') as attributes,
+  obj_description (t.oid, 'pg_type') as comment
+from
   pg_type t
-  LEFT JOIN pg_namespace n ON n.oid = t.typnamespace
-WHERE
+  left join pg_namespace n on n.oid = t.typnamespace
+  left join (
+    select
+      enumtypid,
+      jsonb_agg(enumlabel order by enumsortorder) as enums
+    from
+      pg_enum
+    group by
+      enumtypid
+  ) as t_enums on t_enums.enumtypid = t.oid
+  left join (
+    select
+      oid,
+      jsonb_agg(
+        jsonb_build_object('name', a.attname, 'type_id', a.atttypid::int8)
+        order by a.attnum asc
+      ) as attributes
+    from
+      pg_class c
+      join pg_attribute a on a.attrelid = c.oid
+    where
+      c.relkind = 'c'
+    group by
+      c.oid
+  ) as t_attributes on t_attributes.oid = t.typrelid
+where
   (
     t.typrelid = 0
-    OR (
-      SELECT
+    or (
+      select
         c.relkind = 'c'
-      FROM
+      from
         pg_class c
-      WHERE
+      where
         c.oid = t.typrelid
     )
   )
@@ -37,7 +37,7 @@ export const postgresColumnSchema = Type.Object({
   is_nullable: Type.Boolean(),
   is_updatable: Type.Boolean(),
   is_unique: Type.Boolean(),
-  enums: Type.Array(Type.Unknown()),
+  enums: Type.Array(Type.String()),
   comment: Type.Union([Type.String(), Type.Null()]),
 })
 export type PostgresColumn = Static<typeof postgresColumnSchema>
@@ -326,7 +326,13 @@ export const postgresTypeSchema = Type.Object({
   name: Type.String(),
   schema: Type.String(),
   format: Type.String(),
-  enums: Type.Array(Type.Unknown()),
+  enums: Type.Array(Type.String()),
+  attributes: Type.Array(
+    Type.Object({
+      name: Type.String(),
+      type_id: Type.Integer(),
+    })
+  ),
   comment: Type.Union([Type.String(), Type.Null()]),
 })
 export type PostgresType = Static<typeof postgresTypeSchema>

@@ -60,6 +60,9 @@ export interface Database {
       const schemaEnums = types
         .filter((type) => type.schema === schema.name && type.enums.length > 0)
         .sort(({ name: a }, { name: b }) => a.localeCompare(b))
+      const schemaCompositeTypes = types
+        .filter((type) => type.schema === schema.name && type.attributes.length > 0)
+        .sort(({ name: a }, { name: b }) => a.localeCompare(b))
       return `${JSON.stringify(schema.name)}: {
           Tables: {
             ${
@@ -294,6 +297,28 @@ export interface Database {
                   )
             }
           }
+          CompositeTypes: {
+            ${
+              schemaCompositeTypes.length === 0
+                ? '[_ in never]: never'
+                : schemaCompositeTypes.map(
+                    ({ name, attributes }) =>
+                      `${JSON.stringify(name)}: {
+                        ${attributes.map(({ name, type_id }) => {
+                          const type = types.find(({ id }) => id === type_id)
+                          if (type) {
+                            return `${JSON.stringify(name)}: ${pgTypeToTsType(
+                              type.name,
+                              types,
+                              schemas
+                            )}`
+                          }
+                          return 'unknown'
+                        })}
+                      }`
+                  )
+            }
+          }
         }`
     })}
 }`
@@ -305,7 +330,7 @@ export interface Database {
   return output
 }
 
-// TODO: Make this more robust. Currently doesn't handle composite types - returns them as unknown.
+// TODO: Make this more robust. Currently doesn't handle range types - returns them as unknown.
 const pgTypeToTsType = (
   pgType: string,
   types: PostgresType[],
@@ -340,12 +365,24 @@ const pgTypeToTsType = (
   } else if (pgType.startsWith('_')) {
     return `(${pgTypeToTsType(pgType.substring(1), types, schemas)})[]`
   } else {
-    const type = types.find((type) => type.name === pgType && type.enums.length > 0)
-    if (type) {
-      if (schemas.some(({ name }) => name === type.schema)) {
-        return `Database[${JSON.stringify(type.schema)}]['Enums'][${JSON.stringify(type.name)}]`
+    const enumType = types.find((type) => type.name === pgType && type.enums.length > 0)
+    if (enumType) {
+      if (schemas.some(({ name }) => name === enumType.schema)) {
+        return `Database[${JSON.stringify(enumType.schema)}]['Enums'][${JSON.stringify(
+          enumType.name
+        )}]`
+      }
+      return enumType.enums.map((variant) => JSON.stringify(variant)).join('|')
+    }
+
+    const compositeType = types.find((type) => type.name === pgType && type.attributes.length > 0)
+    if (compositeType) {
+      if (schemas.some(({ name }) => name === compositeType.schema)) {
+        return `Database[${JSON.stringify(
+          compositeType.schema
+        )}]['CompositeTypes'][${JSON.stringify(compositeType.name)}]`
       }
-      return type.enums.map((variant) => JSON.stringify(variant)).join('|')
+      return 'unknown'
     }
 
     return 'unknown'

@@ -6,6 +6,7 @@ test('list', async () => {
     { id: expect.any(Number) },
     `
     {
+      "attributes": [],
       "comment": null,
       "enums": [
         "ACTIVE",
@@ -54,3 +55,34 @@ test('list types with excluded schemas and include System Schemas', async () =>
     expect(type.schema).not.toBe('public')
   })
 })
+
+test('composite type attributes', async () => {
+  await pgMeta.query(`create type test_composite as (id int8, data text);`)
+
+  const res = await pgMeta.types.list()
+  expect(res.data?.find(({ name }) => name === 'test_composite')).toMatchInlineSnapshot(
+    { id: expect.any(Number) },
+    `
+    {
+      "attributes": [
+        {
+          "name": "id",
+          "type_id": 20,
+        },
+        {
+          "name": "data",
+          "type_id": 25,
+        },
+      ],
+      "comment": null,
+      "enums": [],
+      "format": "test_composite",
+      "id": Any<Number>,
+      "name": "test_composite",
+      "schema": "public",
+    }
+  `
+  )
+
+  await pgMeta.query(`drop type test_composite;`)
+})