TypeScript错误推断Supabase关联查询结果类型问题

表结构声明 #

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)
);

stock表的created_by和updated_by字段通过id关联profiles表。执行以下关联查询时，代码能正确返回包含full_name的结果，但TypeScript提示full_name列缺失：

查询代码 #

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

TypeScript错误提示 #

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

生成的Supabase类型声明 #

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"];
                    }
                ];
            };
        };

问题原因 #

Supabase自动生成的类型中，stock表的created_by和updated_by被定义为string | null（对应UUID的字符串形式），而非关联的profiles表对象类型。这是因为Supabase类型生成器默认不会自动将外键字段映射为嵌套的关联表类型，导致TypeScript无法识别关联查询返回的嵌套full_name字段。

解决方法 #

方法1：重新生成Supabase类型 #

确保已正确配置Supabase CLI，执行以下命令重新生成类型：

npx supabase gen types typescript --project-id <你的项目ID> --schema public > types/supabase.ts

生成前可先删除旧的类型文件，避免缓存影响。

方法2：手动扩展类型 #

创建自定义类型覆盖关联字段的类型定义：

import { Database } from './supabase';

// 定义包含关联信息的stock类型
type StockWithProfiles = Omit<Database['public']['Tables']['stock']['Row'], 'created_by' | 'updated_by'> & {
  created_by: Pick<Database['public']['Tables']['profiles']['Row'], 'full_name'> | null;
  updated_by: Pick<Database['public']['Tables']['profiles']['Row'], 'full_name'> | null;
};

// 使用扩展后的类型约束查询结果
const { data: stock, error: stockError } = await event.locals.supabase
  .from("stock")
  .select(`id,created_by(full_name),updated_by(full_name)`)
  .returns<StockWithProfiles[]>();

方法3：临时使用类型断言 #

作为快速解决方案，直接用类型断言指定返回结果类型：

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

const typedStock = stock as Array<{
  id: string;
  created_by: { full_name: string | null } | null;
  updated_by: { full_name: string | null } | null;
}>;

内容的提问来源于stack exchange，提问作者Martin