Typescript incorrectly infers supabase select results

Question

Supabase table declarations:

create table public.profiles(
    id uuid unique references auth.users on delete cascade,
    full_name text,
    updated_at timestamp with time zone default now() not null,
    created_at timestamp with time zone default now() not null,
    primary key (id)
);

create extension if not exists "uuid-ossp";

create table public.stock(
  id uuid unique default uuid_generate_v4(),
  purchased_at date,
  length_cm integer default 0 not null,
  colour text,
  description text,
  weight_expected_grams integer default 0 not null,
  weight_received_grams integer default 0 not null,
  code text,
  created_by uuid references profiles,
  updated_by uuid references profiles,
  created_at timestamp with time zone default now() not null,
  updated_at timestamp with time zone default now() not null,
  primary key (id)
);

Table stock has got two fields created_by and updated_by that references profiles table by id. When performing select on the stock table and trying to join full_name from profiles name I get the results correctly but TypeScript is complaining that full_name column is missing.

const { data: stock, error: stockError } = await event.locals.supabase
    .from("stock")
    .select(`id,created_by(full_name),updated_by(full_name)`);

Code runs and returns expected results, however VSCode intellisense is complaingin with the following error:

const stock: {
    id: string;
    created_by: SelectQueryError<"Referencing missing column `full_name`">[];
    updated_by: SelectQueryError<"Referencing missing column `full_name`">[];
}[] | null

Generated supabase type declarations

public: {
        Tables: {
            profiles: {
                Row: {
                    created_at: string;
                    full_name: string | null;
                    id: string;
                    updated_at: string;
                };
                Insert: {
                    created_at?: string;
                    full_name?: string | null;
                    id: string;
                    updated_at?: string;
                };
                Update: {
                    created_at?: string;
                    full_name?: string | null;
                    id?: string;
                    updated_at?: string;
                };
                Relationships: [
                    {
                        foreignKeyName: "profiles_id_fkey";
                        columns: ["id"];
                        referencedRelation: "users";
                        referencedColumns: ["id"];
                    }
                ];
            };
            stock: {
                Row: {
                    code: string | null;
                    colour: string | null;
                    created_at: string;
                    created_by: string | null;
                    description: string | null;
                    id: string;
                    length_cm: number;
                    purchased_at: string | null;
                    updated_at: string;
                    updated_by: string | null;
                    weight_expected_grams: number;
                    weight_received_grams: number;
                };
                Insert: {
                    code?: string | null;
                    colour?: string | null;
                    created_at?: string;
                    created_by?: string | null;
                    description?: string | null;
                    id?: string;
                    length_cm?: number;
                    purchased_at?: string | null;
                    updated_at?: string;
                    updated_by?: string | null;
                    weight_expected_grams?: number;
                    weight_received_grams?: number;
                };
                Update: {
                    code?: string | null;
                    colour?: string | null;
                    created_at?: string;
                    created_by?: string | null;
                    description?: string | null;
                    id?: string;
                    length_cm?: number;
                    purchased_at?: string | null;
                    updated_at?: string;
                    updated_by?: string | null;
                    weight_expected_grams?: number;
                    weight_received_grams?: number;
                };
                Relationships: [
                    {
                        foreignKeyName: "stock_created_by_fkey";
                        columns: ["created_by"];
                        referencedRelation: "profiles";
                        referencedColumns: ["id"];
                    },
                    {
                        foreignKeyName: "stock_updated_by_fkey";
                        columns: ["updated_by"];
                        referencedRelation: "profiles";
                        referencedColumns: ["id"];
                    }
                ];
            };
        };

Answer 1

I was able to resolve the problem by chaining .returns<T>() with a custom type to the query.

Here's an example that may resolve the problems in the asker's code:

export type Stock = {
    id: string;
    created_by: { full_name: string | null };
    updated_by: { full_name: string | null };
}

const { data: stock, error: stockError } = await event.locals.supabase
    .from("stock")
    .select(`id,created_by(full_name),updated_by(full_name)`)
    .returns<Stock[]>();