Generate types from postgres JSON Schemas

[cohlar]

I would find it valuable if the supabase cli could generate TypeScript types from JSON schemas defined with pg_jsonschema.

[mfazail]

Currently to not get errors in vs code, i am manually changing the type Json(generated by supabase cli) to this 👇

Initial(generated)

export type Json = string | number | boolean | null | { [key:string]: Json } | Json[]

Manual

export interface Json {
	[key: string]: Json | string | number | boolean | Json[]
}

[adamstoffel]

Even cooler would be to use something like https://www.npmjs.com/package/json-schema-to-typescript to convert the actual JSON Schema constraint into a type during typegen. :)

[bmichotte]

After watching this video https://www.youtube.com/watch?v=amJo48ChLGs

Everyone will agree that this is an awesome feature to add

[Revadike]

I'd like to see the other way around, generate JSON schemas from the database

[jwmdykes]

It's not so simple because the functions json_matches_schema and jsonb_matches_schema from pg_jsonschema are just that - functions. The schema is not stored in some specific place that Supabase can look for. For example, say I have a check function like this:

create or replace function check_schema(card_type card_type, data jsonb)
returns boolean as 
$$
declare
    schema_word json := '{
        "type": "object",
        "properties": {
            "word": {
                "type": "string"
            }
        },
        "required": ["word"]
    }';
    schema_grammar json := '{
        "type": "object",
        "properties": {
            "grammar": {
                "type": "string"
            }
        },
        "required": ["grammar"]
    }';
begin
    case card_type
        when 'word' then
            return jsonb_matches_schema(word, data);
        when 'grammar' then
            return jsonb_matches_schema(grammar, data);
        else
            return false;
    end case;
end;
$$ language plpgsql immutable;

alter table cards add constraint check_card_type
    check (check_schema(card_type, data));

in this case the schemas are just part of the function, there's no realistic way for Supabase to get at them.

In order to make this possible, I could imagine a more sophisticated extension than pg_jsonschema which manages a checks table and a schemas table, and directly creates and manages the check constraints on columns. The simplest type of check would be checking if the column matches a single schema, and another useful check would be checking that a schema matches one of a number of schemas, depending on a different column which is an enum.

With something like this, Supabase could figure out which json schema is being used for each column and provide the appropriate types when doing gen types